[postgis-users] Re:touches problem

[Gregory Williamson]

As suggested earlier by both myself and the most excellent "Regina, Obe", post the results of running the queries with "EXPLAIN ANALYZE" and show us the DDL of the tables in question (including indexes and row counts). And I agree with her assessment of your SQL as being hard to read. Of course, as the old adage puts it, "If it was hard to write it should be hard to read." ;-)

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 10:33 PM
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] Re:touches problem
 
Hi all,
   Thanks for ur answers!
   I've taken the advice from Regina to change my query sentence to: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 )),while my sentence is: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 )
   And I've improved the speed.And I want to know the reason about why it is too slow with my sentence while in both ways I can get the same query result?
   Good Luck!

-----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/20071114/9d2b096e/attachment.html>