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