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
