[postgis-users] multilinestring/polygon display lookup questions

[Jedrin]

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 <jrubia... at gmail.com> 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" <l... at pcorp.us> 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: postgis-users-boun... at postgis.refractions.net
>
> > [mailto:postgis-users-boun... at postgis.refractions.net] On Behalf Of Jedrin
> > Sent: Thursday, January 14, 2010 4:16 PM
> > To: postgis-us... 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-us... at postgis.refractions.nethttp://postgis.refractions.net/mailman/listinfo/postgis-users
>
> > _______________________________________________
> > postgis-users mailing list
> > postgis-us... at postgis.refractions.nethttp://postgis.refractions.net/mailman/listinfo/postgis-users
>
> _______________________________________________
> postgis-users mailing list
> postgis-us... at postgis.refractions.nethttp://postgis.refractions.net/mailman/listinfo/postgis-users