[postgis-users] touches function problem

[Obe, Regina]

Your query looks wrong and hard to follow to me.  

1) You seem to have a precedence of operation problem as far as I can
tell.

For example this part 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 )

Do you realize your && will be ignored  except for the case where level
= 1.  I'm assuming that was not your intention.

try changing the above part 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 ))

or your query to something like the below

SELECT astext(qf.wkb_geometry) as wkt_geom, qf.ogc_field, qf.ischanged, 
qf.prechanged,nrings(qf.wkb_geometry) as
nrings,numgeometries(qf.wkb_geometry) as numgeometries
FROM  quanbj1_10000_6_9_fullscreen qf INNER JOIN
quanbj1_10000_6_9_fullscreen qref
	ON (qf.wkb_geometry && qref.wkb_geometry AND qref.ogc_fid=149648
AND touches(qf.wkb_geometry, qref.wkb_geometry))
WHERE qf.level = 1 or (qf.level > 1 and qf.isleaf = 1) or (level > 1 and
child_level < 1 )


If its still slow, then run and explain analyze on it and send us the
explain plan.

Hope that helps,
Regina





-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
caij at lreis.ac.cn
Sent: Tuesday, November 13, 2007 6: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

-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.