[postgis-users] multilinestring/polygon display lookup questions

[Paragon Corporation]

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: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Jedrin
Sent: Thursday, January 14, 2010 4:16 PM
To: postgis-users at postgis.refractions.net
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
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users