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

[Raj Talati]

I prefer doing insert instead of update if we doing all rows update :) so
create New table with new table and copy from old and later drop it you can
go without index and do index later after copy. Postgres update is delete
and insert internally and direct big update will bloat table.
Other thing you can do multi thread if you have 16 core with nothing else
can go 12-14 threads updates which can do portion of your big table easy if
no good criteria can be mod function on PK

Hope this helps .

Raj T

On Sun, Mar 26, 2023 at 6:54 PM karsten <karsten at terragis.net> wrote:

> 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
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20230326/76dca63a/attachment.htm>