[postgis-users] touches function problem

[Gregory Williamson]

Make sure you have indexes created on the geometry, and have run ANALYZE ... if that;s done and doesn't show any difference, perhaps using a "distance(x,y) = 0" construct might be faster. In some cases it is faster and in some not.

You might also post the table definitions (including indexes), table sizes (# of rows) and the results of your query when run under "EXPLAIN ANALYZE <...>;" ... the explain analyze tells a lot about the strategy that PostgreSQL is using. It is possible that the slow part is not the spatial query.

HTH,

Greg Williamson
Senior DBA
GlobeXplorer LLC, a DigitalGlobe company

Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.

(My corporate masters made me say this.)



-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net on behalf of caij at lreis.ac.cn
Sent: Tue 11/13/2007 4:42 AM
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] touches function problem
 
Hi all,
  I have a table which has 279,370 records.And the geometry type is polygon and multipolygon.When I used the touches function to get the neighbor polygons of one polygon,it takes me 7-10 seconds to process it.And to my program,it is unbearable. (the processing sentence is below!) Who can tell me how to improve the speed?
   Wait for ur answers!
   Good Luck!

select astext(wkb_geometry),ogc_fid,ischanged,prechanged,nrings(wkb_geometry) as nrings,numgeometries(wkb_geometry) as numgeometries from (select ogc_fid,wkb_geometry,ischanged,prechanged from quanbj1_10000_6_9_fullscreen where wkb_geometry && (select wkb_geometry from quanbj1_10000_6_9_fullscreen where ogc_fid=149648 ) and (level=1) or (level>1 and isleaf=1) or (level>1 and child_level<1 )) as temprec where touches(wkb_geometry,(select wkb_geometry from quanbj1_10000_6_9_fullscreen where ogc_fid=149648))

_______________________________________________
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/20071113/cd5733fa/attachment.html>