[postgis-users] Spatial join not finishing

[Paul Ramsey]

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