[postgis-users] multilinestring/polygon display lookup questions

[Paragon Corporation]

Jedrin,
3 seconds still sounds like quite a bit.  How many records does it return?

Did you run vacuum analyze on your table.

vacuum analyze sometablehere

Thanks,
Regina 

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Jedrin
Sent: Friday, January 15, 2010 1:49 PM
To: postgis-users at postgis.refractions.net
Subject: Re: [postgis-users] multilinestring/polygon display lookup
questions

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/ma
> ilman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users