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
