[postgis-users] improve query - summarize building count and area on each parcel

[karsten]

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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20230326/ba3def2d/attachment.htm>