That worked decent on a table that was 300,000 + entries. It ran in about 3 seconds up from having been around 9 seconds before. I created a gist index .. Not as fast on a table that is already polygons, but not too bad
On Jan 15, 12:51 am, Jedrin <[email protected]> wrote: > Thanks, > > I will try it at work when I get in tommorow. I had named the field > 'poly' at a time when that's what it always was. I had ruby code that > assumed the geo field had that name and hadn't refactored the code > thus far. > > I am still somewhat of a novice with postgis, and find it hard to find > advanced tutorials, thanks .. > > On Jan 14, 2:35 pm, "Paragon Corporation" <[email protected]> wrote: > > > Jedrin, > > > This is one of the rare cases when you really need to use && directly > > instead of the embedded on in ST_Intersects. > > > The reason its so slow is that your spatial index (assuming you have one), > > is on poly not that calculated thing you have there. So you are essentially > > forcing a table scan. > > > I think your best bet is to create a function that does the right thing and > > still uses an index. Also I don't quite understand your need for ST_ASText > > and is your line called "poly"? > > > Anyrate I would write a function something like > > > CREATE OR REPLACE FUNCTION polylineintersects(polyline geometry, obs > > geometry) > > RETURNS boolean AS > > $$ SELECT $1 && $2 AND _ST_Intersects(ST_Polygon(ST_LineMerge($1)), $2) $$ > > LANGUAGE 'sql' IMMUTABLE STRICT > > COST 100; > > > Then replace your query with > > > SELECT gid,label from shape_import_mass_tiles900913 > > WHERE > > polylineintersects(poly, ST_GeomFromText('Point(-7972592.6161289 > > 5242414.1716097)', 900913) ) ; > > > Hope that helps, > > Regina > > > -----Original Message----- > > From: [email protected] > > > [mailto:[email protected]] On Behalf Of Jedrin > > Sent: Thursday, January 14, 2010 4:16 PM > > To: [email protected] > > Subject: [postgis-users] multilinestring/polygon display lookup questions > > > I have a shapefile that was imported as multilinestring geo type. When I > > display it on a map it looks good as each shape is a square. When a user > > clicks on this however the postgis st_intesects() function and the like > > don't detect the click as it's not a polygon. > > > If I do a conversion on the fly like this to a polygon, it seems like the > > st_astext() causes the query to run for too long: > > > select gid,label from shape_import_mass_tiles900913 where > > ST_Intersects('srid=900913;Point(-7972592.6161289 > > 5242414.1716097)',st_polygon(st_linemerge(ST_AsText(poly)), > > 900913)::geometry); > > > If the shapefile is imported as polygons instead, then wms through > > geoserver displays the shapes over the map and obscures the map, even though > > I set transparency in openlayers/wms. I'm not sure what is the easiest way > > to get this to work to do what I want .. Currently I have two tables as a > > hack, one is polygons and the other lines, but that is really not optimal at > > all .. > > > thanks > > > _______________________________________________ > > postgis-users mailing list > > [email protected]http://postgis.refractions.net/mailman/listinfo/postgis-users > > > _______________________________________________ > > postgis-users mailing list > > [email protected]http://postgis.refractions.net/mailman/listinfo/postgis-users > > _______________________________________________ > postgis-users mailing list > [email protected]http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
