[postgis-users] How to speed up a spatial intersection where the intersecting polygon is drawn on-the-fly?

[Shira Bezalel]

Hi Stefan. Thanks for your response. Can you clarify: Where would we put the functional index? The polygon is being drawn dynamically. 


Shira 

----- Original Message -----

From: "Stefan Keller" <sfkeller at gmail.com> 
To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net> 
Cc: "Patty Frontiera" <pattyf at sfei.org> 
Sent: Monday, September 17, 2012 11:23:13 PM 
Subject: Re: [postgis-users] How to speed up a spatial intersection where the intersecting polygon is drawn on-the-fly? 

Hi, 

I'm unsure how good ST_Transform performs. Did you consider adding a 
partial functional index like described in [1] ? 

-S. 

[1] http://postgis.refractions.net/documentation/manual-1.5/ST_Transform.html 

2012/9/18 Shira Bezalel <shira at sfei.org>: 
> 
> I'm trying to determine if the response time we're seeing on a query is 
> reasonable or if there's anything we can do to speed it up. Looking for 
> advice and/or a reality check. 
> 
> In general, we benefit from dicing our large polygon layers to speed up 
> intersection queries (a big thanks to Paul Ramsey for that pointer), but the 
> catch with this particular query is that it is issued from a web-based GIS 
> application where a user draws a (potentially very large) polygon on-the-fly 
> and then total length calculations are made for intersecting line features 
> within this dynamically drawn polygon. I've even tried dicing dynamically, 
> but it seems to only add more overall time. 
> 
> Here is the SQL for one particular polygon I drew while testing: 
> 
> SELECT s.legcode, s.strahler, sum ( st_length ( st_intersection ( the_geom, 
> st_transform ( ST_GeomFromText ( 'POLYGON((-13648936.363952 
> 4623692.0844833,-13611023.597928 4607181.686376,-13648324.867726 
> 4592505.7769473,-13590844.222464 4581498.8448758,-13665446.76206 
> 4572326.4014828,-13674007.709226 4607181.686376,-13653828.333762 
> 4591282.7844949,-13648936.363952 4623692.0844833))', 900913 ) , 3310 ) ) ) ) 
> * 0.00062137119AS miles 
> FROM baari_streams s 
> WHERE ST_Intersects ( the_geom, st_transform ( ST_GeomFromText ( 
> 'POLYGON((-13648936.363952 4623692.0844833,-13611023.597928 
> 4607181.686376,-13648324.867726 4592505.7769473,-13590844.222464 
> 4581498.8448758,-13665446.76206 4572326.4014828,-13674007.709226 
> 4607181.686376,-13653828.333762 4591282.7844949,-13648936.363952 
> 4623692.0844833))', 900913 ) , 3310 )) 
> GROUP BY s.legcode, s.strahler 
> ORDER BY s.legcode, s.strahler; 
> 
> 
> 
> The explain analyze output: 
> 
> http://explain.depesz.com/s/PNZ 
> 
> The line table has 254833 records. It has a spatial index and the optimizer 
> is using it. The index is clustered. And I have run vacuum analyze on it. 
> 
> So it takes about 3.5 seconds to return 27 rows (an aggregation of 28863 
> rows). Is this about as good as can be expected? Ideally, we'd love to see 
> this return in about 1 second or less. Is that unreasonable? 
> 
> Version info: 
> PostgreSQL 9.1.2 on x86_64-pc-linux-gnu, compiled by gcc-4.6.real 
> (Ubuntu/Linaro 4.6.1-9ubuntu3) 4.6.1, 64-bit 
> POSTGIS="1.5.3" GEOS="3.2.2-CAPI-1.6.2" PROJ="Rel. 4.7.1, 23 September 2009" 
> LIBXML="2.7.8" USE_STATS 
> 
> Thanks for any insight you can provide. 
> 
> Shira 
> 
> _______________________________________________ 
> postgis-users mailing list 
> postgis-users at postgis.refractions.net 
> http://postgis.refractions.net/mailman/listinfo/postgis-users 
> 
_______________________________________________ 
postgis-users mailing list 
postgis-users at postgis.refractions.net 
http://postgis.refractions.net/mailman/listinfo/postgis-users 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120918/19d8acfa/attachment.html>