<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 6.5.7652.24">
<TITLE>RE: [postgis-users] Re:touches problem</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->
<P><FONT SIZE=2>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." ;-)<BR>
<BR>
Greg Williamson<BR>
Senior DBA<BR>
GlobeXplorer LLC, a DigitalGlobe company<BR>
<BR>
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.<BR>
<BR>
(My corporate masters made me say this.)<BR>
<BR>
<BR>
<BR>
-----Original Message-----<BR>
From: postgis-users-bounces@postgis.refractions.net on behalf of caij@lreis.ac.cn<BR>
Sent: Tue 11/13/2007 10:33 PM<BR>
To: postgis-users@postgis.refractions.net<BR>
Subject: [postgis-users] Re:touches problem<BR>
<BR>
Hi all,<BR>
Thanks for ur answers!<BR>
I've taken the advice from Regina to change my query sentence to:wkb_geometry && (select wkb_geometry from quanbj1_10000_6_9_fullscreen<BR>
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 )<BR>
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?<BR>
Good Luck!<BR>
<BR>
-----Original Message-----<BR>
From: postgis-users-bounces@postgis.refractions.net on behalf of caij@lreis.ac.cn<BR>
Sent: Tue 11/13/2007 4:42 AM<BR>
To: postgis-users@postgis.refractions.net<BR>
Subject: [postgis-users] touches function problem<BR>
<BR>
Hi all,<BR>
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?<BR>
Wait for ur answers!<BR>
Good Luck!<BR>
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))<BR>
<BR>
<BR>
_______________________________________________<BR>
postgis-users mailing list<BR>
postgis-users@postgis.refractions.net<BR>
<A HREF="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>
<BR>
</FONT>
</P>
</BODY>
</HTML>