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

[Ethan Alpert]

>From my original email:

"I have two shapefiles I want to interesect. I shp2pgsql'ed them, loaded
them into the database and created spatial indexes for them (vacuum
analyze'ed for good measure):"

-e

-----Original Message-----
From: strk at refractions.net [mailto:strk at refractions.net] 
Sent: Thursday, September 08, 2005 11:37 AM
To: PostGIS Users Discussion
Cc: Ethan Alpert
Subject: Re: [postgis-users] Why does this take almost 3 hours?


On Wed, Aug 31, 2005 at 01:21:21PM -0600, Ethan Alpert wrote:
> 
> 
> I'll look at the explain again. In my first email I mentioned I have 
> created spatial indexes for both my tables.

Ethan, would you run VACUUM ANALYZE and send explain output again ?

--strk;

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