[postgis-users] Why does this take almost 3 hours?

[Ethan Alpert]

Well using distance cut the query down to only 30 minutes (my mapscript
queryByShape() takes 2 minutes).

I also wrote a script that selects the catalogid's out of the table then
loops and forms the following query:

select catalogid,cii_region from new_snaps a, nga_reg b where
distance(a.the_geom,b.the_geom) <= 0 and catalogid = ?

This took 16 minutes.

What I don't understand is why 

 SELECT a.catalogid, b.cii_region, a.the_geom  FROM new_snaps a, nga_reg
b WHERE a.the_geom && b.the_geom AND distance(a.the_geom, b.the_geom) <=
0;


Would be twice as long. Seems to me that looping through one table and
calling && and distance for each element would be what the query does
but clearly it doesn't do it.

Anyhow mapscript's queryByShape() 0wnz in this particular case.

-e

-----Original Message-----
From: dblasby at openplans.org [mailto:dblasby at openplans.org] 
Sent: Tuesday, August 30, 2005 11:39 AM
To: postgis-users at postgis.refractions.net
Cc: Ethan Alpert
Subject: [postgis-users] Why does this take almost 3 hours?


"intersects()" is very computationally expensive.  You might want to try
"distance() <=0" which can often short-circuit computation.

 SELECT a.catalogid, b.cii_region, a.the_geom
   FROM new_snaps a, nga_reg b
  WHERE a.the_geom && b.the_geom AND distance(a.the_geom,
b.the_geom)<=0;

dave



----------------------------------------------------------
This mail sent through IMP: https://webmail.limegroup.com/