[postgis-users] Creating a boundary of an aggregate of buildings

[Tomas Straupis]

2021-01-27, tr, 23:08 Luca Bertoncello rašė:
> My Table city_boundary has about 108.000 rows (and only two fields: id
> and way) and the aggretation query:
>
> UPDATE city_boundaries SET way =
> ST_Makevalid(ST_Multi(ST_Buffer(ST_Buffer(way, 300, 'join=miter'), -300,
> 'join=miter')));
>
> <...>
>
> runs since today ~13:30 (8,5 hours).
> Do you/do someone have an idea how to speed it up? The PC has 4 cores
> and 16 GB RAM and currently I cannot add more RAM.

  If 300m buffer +- is good for your scale, then you can reduce the
number of vertexes thus speeding up buffer operations considerably.
For example you could dp simplify geometries beforehand.

  Something like:

  UPDATE city_boundaries SET way = ST_SimplifyPreserveTopology(way, 100);

  before running running buffer +- update.

  (or you could put simplify inside your current update statement as a
first action then you will only have one update instead of two for
each geometry)

-- 
Tomas