[postgis-users] Spatial join not finishing

[David Quinn]

Just a thought (from a novice postgis user) - are there spatial indexes on
both tables?
I've forgotten to create these a couple of times and the time difference is
enormous.

-David

On Wed, Feb 29, 2012 at 6:15 PM, Paul Ramsey <pramsey at opengeo.org> wrote:

> Without a query plan (explain ... ) there's not much people can do but
> scratch their chins sagely.
>
> P.
>
> On Wed, Feb 29, 2012 at 3:11 PM, Jorge Gil <j.a.lopesgil at tudelft.nl>
> wrote:
> > Hi everyone,
> >
> > I've been trying to do a spatial join for a couple of days in different
> ways
> > and it never seems to finish. I've done something identical before in a
> much
> > larger set of the same data and it worked after a few hours.
> >
> > I have a polygons (buildings) table with 150,000 records and a points
> (land
> > use) table with 170,000 records. I want to calculate how many points are
> > within each building and add that total to the column 'units' in the
> > buildings table. The query I run is this:
> >
> > UPDATE buildings as aa SET units=bb.count FROM (SELECT cc.gid , count(*)
> > from buildings as cc, landuse as dd
> > where ST_Within(dd.the_geom, cc.the_geom) group by cc.gid) as bb WHERE
> > aa.gid=bb.gid;
> >
> > I also try to create a separate table with the results of the join before
> > doing the update, but the first step also fails.
> >
> > Am I doing something wrong? Is there a simple trick that makes this more
> > efficient?
> >
> > Thank you,
> > Jorge
> >
> > --
> > Jorge Gil
> > PhD Candidate
> >
> > TU Delft / Faculty of Architecture
> > Department of Urbanism
> > Chair of Spatial Planning and Strategy
> >
> > Julianalaan 134
> > 2628 BL Delft
> > P.O. Box 5043
> > 2600 GA Delft
> > The Netherlands
> >
> > www.tudelft.nl
> >
> > _______________________________________________
> > 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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120229/0f99e421/attachment.html>