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

[Tomas Straupis]

I've tested on Vilnius and get fine results with coefficients given
below (but for you numbers could be different depending on which is
your target scale).

Here ClusterWithin is reduced to 200 - group polygons which are closer
than 200 meters. This also means that buffering should be at least
200/2=100 but practically 120-150 to aggregate polygons.

Then delete polygons which after reducing 50 meters are less than
50m2. Tune these numbers to your fitting.

Then add buffer 120 and remove buffer 120 (note I've changed join to
miter as that gives better result for non natural features).

Also I've added industrial landuse. Think about allotments, cemetery.

-- This groups all polygons closer than 200 meters
CREATE TABLE city_boundaries AS
  SELECT NEXTVAL('city_boundaries_seq') AS id
        ,0::bigint AS way_area
        ,10 AS res
        ,ST_CollectionExtract(unnest(ST_ClusterWithin(way,
200)),3)::geometry(MultiPolygon, 3857) as way
    FROM planet_osm_polygon
   WHERE landuse IN ('residential', 'retail', 'retail;residential',
'commercial', 'school', 'university', 'industrial');

-- This deletes all polygons which after removing 50 meters from all
sides have area less than 50
DELETE FROM city_boundaries WHERE ST_Area(ST_Buffer(way, -50)) < 50
and res = 10;

-- Aggregates all clustered polygons by adding 120m and then removing
120m from all sides
UPDATE city_boundaries SET way =
ST_Makevalid(ST_Multi(ST_Buffer(ST_Buffer(way, 120, 'join=miter'),
-120, 'join=miter'))) WHERE res = 10;