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

Luca Bertoncello lucabert at lucabert.de
Mon Jan 18 05:04:48 PST 2021


Am 18.01.2021 13:55, schrieb Tomas Straupis:

Hi,

>   ST_ClusterWithin(way, 500)) <- this combines all polygons which are
> less than 500 meters apart.
>   For landuses 10 meters for ST_Buffer not enough. Try increasing 10
> to say a 400(m) or even more (especially given you're clustering
> polygons with a distance of 500 meters). If a distance between
> different parts (polygons) of a geometry is 500 meters, you need a
> buffer of at least 250 meters for them to "touch", if different parts
> of geometries will not "touch" with positive buffer - they will not
> combine - will not form one aggregated geometry out of a lot of small
> ones.

Sorry, I can't understand what you mean...
Could you give me an example?

I tried:

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, -500)) < 500 
and res = 10;

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

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

and almost all little plots are disappeared, but many little cities, 
too...
And the boundaries are to "irregular", so I think, I get "single 
buildings" too...

>> But I don't understand what "res" means...
> 
>   In my case the same table holds information for different scales of
> a map (multi-sclae). res=10 are values for 10 scale, then there are
> values for 40, 120 etc. You can ignore them.

OK

> P.S. Hint: do one step at a time and then use QGIS to see how those
> intermediate geometries look like. It will make it easier to
> understand what this code does.

After every try I generate a new map with Mapnik, to see what happens.

Thanks
Luca Bertoncello
(lucabert at lucabert.de)


More information about the postgis-users mailing list