<html><head><style type='text/css'>p { margin: 0; }</style></head><body><div style='font-family: Arial; font-size: 10pt; color: #000000'>Hi Stefan. Thanks for your response. Can you clarify: Where would we put the functional index? The polygon is being drawn dynamically. <br><div><span name="x"></span><br></div>Shira<div><br><hr id="zwchr"><div style="color:#000;font-weight:normal;font-style:normal;text-decoration:none;font-family:Helvetica,Arial,sans-serif;font-size:12pt;"><b>From: </b>"Stefan Keller" <sfkeller@gmail.com><br><b>To: </b>"PostGIS Users Discussion" <postgis-users@postgis.refractions.net><br><b>Cc: </b>"Patty Frontiera" <pattyf@sfei.org><br><b>Sent: </b>Monday, September 17, 2012 11:23:13 PM<br><b>Subject: </b>Re: [postgis-users] How to speed up a spatial intersection where the intersecting polygon is drawn on-the-fly?<br><br>Hi,<br><br>I'm unsure how good ST_Transform performs. Did you consider adding a<br>partial functional index like described in [1] ?<br><br>-S.<br><br>[1] http://postgis.refractions.net/documentation/manual-1.5/ST_Transform.html<br><br>2012/9/18 Shira Bezalel <shira@sfei.org>:<br>><br>> I'm trying to determine if the response time we're seeing on a query is<br>> reasonable or if there's anything we can do to speed it up. Looking for<br>> advice and/or a reality check.<br>><br>> In general, we benefit from dicing our large polygon layers to speed up<br>> intersection queries (a big thanks to Paul Ramsey for that pointer), but the<br>> catch with this particular query is that it is issued from a web-based GIS<br>> application where a user draws a (potentially very large) polygon on-the-fly<br>> and then total length calculations are made for intersecting line features<br>> within this dynamically drawn polygon. I've even tried dicing dynamically,<br>> but it seems to only add more overall time.<br>><br>> Here is the SQL for one particular polygon I drew while testing:<br>><br>> SELECT s.legcode, s.strahler, sum ( st_length ( st_intersection ( the_geom,<br>> st_transform ( ST_GeomFromText ( 'POLYGON((-13648936.363952<br>> 4623692.0844833,-13611023.597928 4607181.686376,-13648324.867726<br>> 4592505.7769473,-13590844.222464 4581498.8448758,-13665446.76206<br>> 4572326.4014828,-13674007.709226 4607181.686376,-13653828.333762<br>> 4591282.7844949,-13648936.363952 4623692.0844833))', 900913 ) , 3310 ) ) ) )<br>> * 0.00062137119AS miles<br>> FROM baari_streams s<br>> WHERE ST_Intersects ( the_geom, st_transform ( ST_GeomFromText (<br>> 'POLYGON((-13648936.363952 4623692.0844833,-13611023.597928<br>> 4607181.686376,-13648324.867726 4592505.7769473,-13590844.222464<br>> 4581498.8448758,-13665446.76206 4572326.4014828,-13674007.709226<br>> 4607181.686376,-13653828.333762 4591282.7844949,-13648936.363952<br>> 4623692.0844833))', 900913 ) , 3310 ))<br>> GROUP BY s.legcode, s.strahler<br>> ORDER BY s.legcode, s.strahler;<br>><br>><br>><br>> The explain analyze output:<br>><br>> http://explain.depesz.com/s/PNZ<br>><br>> The line table has 254833 records. It has a spatial index and the optimizer<br>> is using it. The index is clustered. And I have run vacuum analyze on it.<br>><br>> So it takes about 3.5 seconds to return 27 rows (an aggregation of 28863<br>> rows). Is this about as good as can be expected? Ideally, we'd love to see<br>> this return in about 1 second or less. Is that unreasonable?<br>><br>> Version info:<br>> PostgreSQL 9.1.2 on x86_64-pc-linux-gnu, compiled by gcc-4.6.real<br>> (Ubuntu/Linaro 4.6.1-9ubuntu3) 4.6.1, 64-bit<br>> POSTGIS="1.5.3" GEOS="3.2.2-CAPI-1.6.2" PROJ="Rel. 4.7.1, 23 September 2009"<br>> LIBXML="2.7.8" USE_STATS<br>><br>> Thanks for any insight you can provide.<br>><br>> Shira<br>><br>> _______________________________________________<br>> postgis-users mailing list<br>> postgis-users@postgis.refractions.net<br>> http://postgis.refractions.net/mailman/listinfo/postgis-users<br>><br>_______________________________________________<br>postgis-users mailing list<br>postgis-users@postgis.refractions.net<br>http://postgis.refractions.net/mailman/listinfo/postgis-users<br></div><br></div></div></body></html>