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

[Stefan Keller]

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
>