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

[Marcin Mionskowski]

And you can:
create index buildings_centroid_idx on buildings using gist
(ST_Centroid(buildings.geom))

In my test case it yields another 10x improvement.

pon., 27 mar 2023 o 08:33 Marcin Mionskowski <mionskowskimarcin at gmail.com>
napisał(a):

> In my "explain tests", made on much smaller tables, I get 500 times
> smaller costs only by changing LEFT JOIN to "regular" JOIN.
>
> pon., 27 mar 2023 o 01:07 Raj Talati <rajtalati at gmail.com> napisał(a):
>
>> 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
>>>
>> _______________________________________________
>> 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/20230327/ba440918/attachment.htm>