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

Luca Bertoncello lucabert at lucabert.de
Mon Jan 18 04:34:57 PST 2021


Am 18.01.2021 12:48, schrieb Tomas Straupis:

Hi again

>   Here is a working forest aggregation routine.

I'm feeling really dumb, but I can't get your example working...
Here my code:

CREATE SEQUENCE city_boundaries_seq START 1;
ALTER SEQUENCE city_boundaries_seq OWNER TO _renderd;

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, 500)), 
3)::geometry(MultiPolygon, 3857) as way
     FROM planet_osm_polygon
    WHERE landuse IN ('residential', 'retail', 'retail;residential', 
'commercial', 'school', 'university');
ALTER TABLE city_boundaries OWNER TO _renderd;

DELETE FROM city_boundaries WHERE ST_Area(ST_Buffer(way, -10)) < 10 and 
res = 10;

UPDATE city_boundaries SET way = 
ST_Makevalid(ST_Multi(ST_SimplifyPreserveTopology(ST_Buffer(ST_Buffer(way, 
10, 'quad_segs=1'), -10, 'quad_segs=1'), 10))) WHERE res = 10;

UPDATE city_boundaries SET way_area = st_area(way) WHERE res = 10;

So, if I undestand correctly I create a new table city_boundaries with 
all buildings of the given types in a radius of 10 meters, is if 
correct?
Then I try to display it on the map. Same result as previous: many 
little areas...

Maybe should I enhance the distance between the buildings? I'd like to 
have a radius maybe about 500 meter or one kilometer...
But I don't understand what "res" means...
Could you help me again?

Really many thanks!!
Luca Bertoncello
(lucabert at lucabert.de)


More information about the postgis-users mailing list