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

[Ethan Alpert]

I'll look at the explain again. In my first email I mentioned I have
created spatial indexes for both my tables.

-e

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
strk at refractions.net
Sent: Wednesday, August 31, 2005 10:31 AM
To: postgis-users at postgis.refractions.net
Subject: Re: [postgis-users] Why does this take almost 3 hours?


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