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

[strk at refractions.net]

On Tue, Aug 30, 2005 at 01:52:06PM -0600, Ethan Alpert wrote:
> 
> 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.

Did you check explain output ? How long does the && part take ?
Is your query using (did you define) a spatial index ?

--strk;

> 
> 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/
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users