Hi All, I have two tables, a parcel (polygon) table and a building (polygon) table in a PostGIS db. There are about 1 million parcels and over a million buildings in these tables ... I have set gist indexes on both and clustered each on a geohash, pkey is set on objectid.
I would like to fill 2 additional columns for the parcels layer to have the number of building on each parcel (building_count) and the summed up area of all those building footprints (building_fp). Below is an update query but it is extremely lengthy and inefficient (on a beefy 16 core Ubuntu server with 128 GB tons of RAM) : I had to stop an after running 3 days when it was not finished ... I am wondering if anyone has an idea how to make it more efficient ? This is the query: UPDATE parcels p SET building_count = subquery.num, building_fp = subquery.sumarea from (select objectid, count(*) as num, sum(st_area(b.geom)) as sumarea FROM parcels p left join buildings b on st_intersects(p.geom,st_centroid(b.geom)) group by objectid ) subquery where p.objectid = subquery.objectid ; Any suggestions ? Cheers Karsten Vennemann
_______________________________________________ postgis-users mailing list [email protected] https://lists.osgeo.org/mailman/listinfo/postgis-users
