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

[Luca Bertoncello]

Hi list!

I'm always trying to create a style for a map in ICAO style using data
from OpenStreetMaps...
Now I'm trying to display the city boundaries.
Problem: in the database I just have the political (administrative)
boundaries, and I need the physical boundaries.
With "physical boundaries" I mean: I just need to display the part of
the city with buildings ignoring unused areas and this is mostly littler
than the political boundaries.

I tried to get them with this query:

SELECT way
FROM planet_osm_polygon
WHERE landuse IN ('residential', 'retail', 'retail;residential')

I think I need many other landuse types, but this is not the problem.
The problem is, that I have many little areas, and I want to group them,
so that I can select only the "grouped areas" greater than X.
I found the function ST_ClusterDBSCAN that maybe help me, so I tried the
query:

SELECT way, ST_ClusterDBSCAN(way, eps := 50, minpoints := 2) OVER
() AS cid
FROM planet_osm_polygon
WHERE landuse IN ('residential', 'retail', 'retail;residential')

Now I have an "ID" to group the nearing buildings, but I always don't
have an idea, how to create a boundary over this aggregates...

Can someone help me?

Thanks a lot
Luca Bertoncello
(luca...@lucabert.de)
_______________________________________________
postgis-users mailing list
postgi...@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

[Tomas Straupis]

2021-01-18, pr, 09:26 Luca Bertoncello rašė:
> Now I have an "ID" to group the nearing buildings, but I always don't
> have an idea, how to create a boundary over this aggregates...

In order to do aggregation/dissolving, you can try using st_buffer
on grouped (clustered) geometries.
Something like this:

st_buffer(st_buffer(your_clustered_aggregated_geometry, 1000), -1000)

Depending on details you can add an option to use quad_segs=1 to
avoid having round corners when doing positive buffer.
Something like this:

st_buffer(st_buffer(you_clustered_aggregated_geometry, 1000,
'quad_segs=1'), -1000, 'quad_segs=1')

--
Tomas

[Luca Bertoncello]

Am 18.01.2021 10:21, schrieb Tomas Straupis:

Hi Tomas,

> In order to do aggregation/dissolving, you can try using st_buffer
> on grouped (clustered) geometries.
> Something like this:
>
> st_buffer(st_buffer(your_clustered_aggregated_geometry, 1000), -1000)
>
> Depending on details you can add an option to use quad_segs=1 to
> avoid having round corners when doing positive buffer.
> Something like this:
>
> st_buffer(st_buffer(you_clustered_aggregated_geometry, 1000,
> 'quad_segs=1'), -1000, 'quad_segs=1')

Do you mean something like that?

SELECT ST_Buffer(ST_Buffer(way, 1000'quad_segs=1'), -1000,
'quad_segs=1')
FROM
(SELECT way, ST_ClusterDBSCAN(way, eps := 50, minpoints := 2)

OVER () AS cid
FROM planet_osm_polygon
WHERE landuse IN ('residential', 'retail',
'retail;residential')

) a

Thanks
Luca Bertoncello
(luca...@lucabert.de)

[Tomas Straupis]

2021-01-18, pr, 11:26 Luca Bertoncello rašė:
> Do you mean something like that?
>
> SELECT ST_Buffer(ST_Buffer(way, 1000'quad_segs=1'), -1000,
> 'quad_segs=1')
> FROM
> (SELECT way, ST_ClusterDBSCAN(way, eps := 50, minpoints := 2)
> OVER () AS cid
> FROM planet_osm_polygon
> WHERE landuse IN ('residential', 'retail',
> 'retail;residential')
> ) a

Have not tested your statement, but besides missing comma between
1000 and 'quad_segs=1' looks fine.
I use such buffering for landuse (forest, water) aggregation for
different scales.
There are other options like doing +50 -100 +50, or -50 +100 -50
buffer. Depending on which works better. One way is better at
eradicating small inner gaps, other method is better at removing small
external details.

P.S. There is also convex function, but in my experience buffers give
better result.

--
Tomas

[Luca Bertoncello]

Am 18.01.2021 10:51, schrieb Tomas Straupis:

Hi Tomas

> Have not tested your statement, but besides missing comma between
> 1000 and 'quad_segs=1' looks fine.
> I use such buffering for landuse (forest, water) aggregation for
> different scales.

So this is my query:

(SELECT ST_Buffer(ST_Buffer(way, 1000, 'quad_segs=1'), -1000,
'quad_segs=1') as way
FROM
(SELECT way, ST_ClusterDBSCAN(way, eps := 1000, minpoints

:= 2) OVER () AS cid
FROM planet_osm_polygon
WHERE landuse IN ('residential', 'retail',

'retail;residential', 'commercial', 'school')
) a
) AS test

unfortunately it does not work as expected, since I have always many
many many little areas...

Any other idea?

Thanks
Luca Bertoncello
(luca...@lucabert.de)

[Tomas Straupis]

> unfortunately it does not work as expected, since I have always many
> many many little areas...
>
> Any other idea?

Are you sure you're getting a combined multigeometry before applying
buffer+-? All small polygons which you expect to be aggregated into
one should be in one geometry (one row) before doing buffering.

1. Increase buffer size.
2. ST_ConvexHull, ST_ConcaveHull.

Here is a working forest aggregation routine.

create table gen_forest as
select nextval('gen_forest_seq') AS id
,0::bigint AS way_area
,10 AS res
,ST_CollectionExtract(unnest(ST_ClusterWithin(way, 10)),
3)::geometry(MultiPolygon, 3857) as way
from planet_osm_polygon
where landuse = 'forest';

delete from gen_forest where st_area(st_buffer(way, -10)) < 10 and res = 10;

update gen_forest 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 gen_forest set way_area = st_area(way) where res = 10;

--
Tomas

[Luca Bertoncello]

Am 18.01.2021 12:48, schrieb Tomas Straupis:

Hi Tomas,

> Are you sure you're getting a combined multigeometry before applying
> buffer+-? All small polygons which you expect to be aggregated into
> one should be in one geometry (one row) before doing buffering.

No, I'm not... :) Sorry, I'm really a beginner with PostGIS and
OpenStreetMaps...

> 1. Increase buffer size.
> 2. ST_ConvexHull, ST_ConcaveHull.
>
> Here is a working forest aggregation routine.
>
> create table gen_forest as
> select nextval('gen_forest_seq') AS id
> ,0::bigint AS way_area
> ,10 AS res
> ,ST_CollectionExtract(unnest(ST_ClusterWithin(way, 10)),
> 3)::geometry(MultiPolygon, 3857) as way
> from planet_osm_polygon
> where landuse = 'forest';
>
> delete from gen_forest where st_area(st_buffer(way, -10)) < 10 and res
> = 10;
>
> update gen_forest 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 gen_forest set way_area = st_area(way) where res = 10;

I'll try to create a table with landuse = 'residential' (and the other),
then use this table to generate the boundaries...
Do I understand correctly, you create areas with "forests" and distance
not greater than 10 meters?

I'll report to the list the result.

Thanks
Luca Bertoncello
(luca...@lucabert.de)

[Luca Bertoncello]

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
(luca...@lucabert.de)

[Tomas Straupis]

2021-01-18, pr, 14:35 Luca Bertoncello rašė:
> 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?

Yes (just not buildings, but landuses).
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.

> 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.

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.

--
Tomas

[Luca Bertoncello]

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
(luca...@lucabert.de)

[Tomas Straupis]

2021-01-18, pr, 15:05 Luca Bertoncello rašė:
> 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;

This statement has problems.
You're adding a buffer of 10 then removing a buffer of 500 (so
basically leaving only very large polygons and they are eroded by
500meters). Then you're simplifying the result with a 500m value (a
lot).

Try this:

UPDATE city_boundaries
SET way = ST_Makevalid(ST_Multi(ST_Buffer(ST_Buffer(way, 400,
'quad_segs=1'), -400, 'quad_segs=1')));

[Luca Bertoncello]

Am 18.01.2021 14:38, schrieb Tomas Straupis:

Hi

> This statement has problems.
> You're adding a buffer of 10 then removing a buffer of 500 (so
> basically leaving only very large polygons and they are eroded by
> 500meters). Then you're simplifying the result with a 500m value (a
> lot).
>
> Try this:
>
> UPDATE city_boundaries
> SET way = ST_Makevalid(ST_Multi(ST_Buffer(ST_Buffer(way, 400,
> 'quad_segs=1'), -400, 'quad_segs=1')));

I tried this:

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, 400)),

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

UPDATE city_boundaries SET way =
ST_Makevalid(ST_Multi(ST_Buffer(ST_Buffer(way, 400, 'quad_segs=1'),
-400, 'quad_segs=1')));

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

This will be better, but:
1) I have always many little areas
2) I miss some "medium areas"
3) the displayed areas have many "holes"

A possible solution to 2) could be to reduce the distance (let's say 200
meter instead of 400?) but so I'll increase the number of little areas.

I think a right way could be to reduce the distance (see previous) and
generate an area grouping all near little areas, filling the "inclosed"
holes in the areas. Then I can search for areas greater than for example
20 Km².
But I don't know to do that...

Thanks
Luca Bertoncello
(luca...@lucabert.de)

[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;

[Luca Bertoncello]

Am 18.01.2021 15:09, schrieb Tomas Straupis:

Hi Tomas,

> 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).

It may be a right way, but I'd like to have a boundary of all extracted
buildings...
Do you think, it should be possible?
If I have that, I can filter the areas by size and just display what I
want...

Thanks
Luca Bertoncello
(luca...@lucabert.de)

[Luca Bertoncello]

Am 18.01.2021 um 15:09 schrieb 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).

Hi again,

I think I'm on the right way...
I can select the boundaries by size, so I can display almost what I need.
The very problem is that I have many "holes"...

You can see the example: in Dresden there are many holes in the
building, but they are "little", and I want to fill them, leaving the
holes greater than X...

Do you have an idea how to do that?

Thanks
Luca Bertoncello
(luca...@lucabert.de)

[Hugues François]

Hi,

Some time ago, I wrote a PLPGSQL function that creates urban areas from a buildings table.

The attached function could help you to solve your issues and test several thresholds when adapted to your own database. I'm sorry it's written in a weird French-English style but the queries should help you to understand the variable names meaning. Of course, feel free to ask any question.

The hole issue is directly adressed line 55 to line 85. It uses st_dumprings along with st_exterrioring and st_buildarea and compares hole surface to the "surf_trou" threshold.

HTH,
Hug


--




----- Mail original -----
De: "Luca Bertoncello" <luca...@lucabert.de>
À: "PostGIS Users Discussion" <postgi...@lists.osgeo.org>
Envoyé: Lundi 18 Janvier 2021 21:37:44
Objet: Re: [postgis-users] Creating a boundary of an aggregate of buildings

[Fichier texte:ATT00001]

[Luca Bertoncello]

Am 19.01.2021 09:01, schrieb Hugues François:

Hi Hugues

> The attached function could help you to solve your issues and test
> several thresholds when adapted to your own database. I'm sorry it's
> written in a weird French-English style but the queries should help
> you to understand the variable names meaning. Of course, feel free to
> ask any question.
>
> The hole issue is directly adressed line 55 to line 85. It uses
> st_dumprings along with st_exterrioring and st_buildarea and compares
> hole surface to the "surf_trou" threshold.

Thank you very much. I'll try to adapt your functions to the
OpenStreetMaps' data.

Now I'm trying to reduce the "little spots" on the map joining the table
with the "city's building boundaries" with a table with the "city's
administrative boundaries" and select only the building in a city, but
it does not work...

My query:

SELECT
city_boundaries.way
FROM city_boundaries, city_admin_boundaries
WHERE ST_Within(city_boundaries.way,
city_admin_boundaries.way) AND
(city_admin_boundaries.population >= 18000 OR
city_admin_boundaries.km2 >= 50);

Unfortunately I get many little spots outsides the cities and inside the
cities I have few spots with buildings.
If I just use:

SELECT
city_boundaries.way
FROM city_boundaries
WHERE (ST_Area(ST_Transform(way, 4326)::geography) / 1000000)
> 3.5;

I get many spots outside the city and great building's spots inside the
cities.

city_admin_boundaries was created as:

CREATE TABLE city_admin_boundaries AS
SELECT
planet_osm_polygon.way,
planet_osm_polygon.admin_level,
planet_osm_point.name,
planet_osm_point.place,
CASE
WHEN (planet_osm_point.tags->'population' ~ '^[0-9]{1,8}$') THEN
(planet_osm_point.tags->'population')::INTEGER ELSE 0
END as population,
(ST_Area(ST_Transform(planet_osm_polygon.way, 4326)::geography) /
1000000) AS km2
FROM planet_osm_polygon
JOIN (
SELECT name, MAX(admin_level) AS al
FROM planet_osm_polygon
WHERE boundary = 'administrative' AND admin_level IN ('4', '6', '8')
AND osm_id < 0 GROUP BY name
) size USING(name)
JOIN planet_osm_point USING (name)
WHERE planet_osm_polygon.boundary = 'administrative' AND
planet_osm_polygon.admin_level = size.al AND
(
(
planet_osm_polygon.admin_level IN ('6', '8') AND
planet_osm_point.place IN ('city', 'town')
) OR
(
planet_osm_polygon.admin_level = '4' AND
planet_osm_point.place = 'city'
)
) AND
planet_osm_polygon.osm_id < 0;

and contains correct data, since I display the administrative boundaries
on the map, too and they are correct.

Could someone explain me where is my error?

Thanks
Luca Bertoncello
(luca...@lucabert.de)

[Luca Bertoncello]

Am 18.01.2021 um 15:09 schrieb Tomas Straupis:

Hi Tomas and all,

I finally imported all Europe data from OSM in my PostGIS and I created
the table city_boundary as you suggested, with JOIN to another help
table in order to restrict the search.

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')));

derived from what you suggested

> -- 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;

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.

Thanks a lot
Luca Bertoncello
(luca...@lucabert.de)

[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

[Luca Bertoncello]

Am 28.01.2021 16:21, schrieb Tomas Straupis:

Hi Tomas,

> If 300m buffer +- is good for your scale, then you can reduce the

Yes, 300m is good for what I need, I tested it on a small region.

> 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.

OK, I'll try in the test environment how it looks like, then try on the
production server.

Thanks
Luca Bertoncello
(luca...@lucabert.de)

[Luca Bertoncello]

Am 28.01.2021 16:21, schrieb Tomas Straupis:

> Something like:
>
> UPDATE city_boundaries SET way = ST_SimplifyPreserveTopology(way,
> 100);
>
> before running running buffer +- update.

I get the error:

FEHLER: Geometry type (Polygon) does not match column type
(MultiPolygon)

Could you say me how to correct the query?

Thanks
Luca Bertoncello
(luca...@lucabert.de)

[Tomas Straupis]

2021-01-28, kt, 17:35 Luca Bertoncello rašė:
> I get the error:
>
> FEHLER: Geometry type (Polygon) does not match column type
> (MultiPolygon)
>
> Could you say me how to correct the query?

Add ST_Multi like this:

UPDATE city_boundaries SET way =

ST_Multi(ST_SimplifyPreserveTopology(way, 100));

[Luca Bertoncello]

Am 28.01.2021 16:45, schrieb Tomas Straupis:
> 2021-01-28, kt, 17:35 Luca Bertoncello rašė:
>> I get the error:
>>
>> FEHLER: Geometry type (Polygon) does not match column type
>> (MultiPolygon)
>>
>> Could you say me how to correct the query?
>
> Add ST_Multi like this:
>
> UPDATE city_boundaries SET way =
> ST_Multi(ST_SimplifyPreserveTopology(way, 100));

OK, thank you!

A test looks like very good, pretty the same of the previous version, so
I'll use it.

Regards
Luca Bertoncello
(luca...@lucabert.de)

[Stephen Woodbridge]

On 1/28/2021 10:34 AM, Luca Bertoncello wrote:
> Am 28.01.2021 16:21, schrieb Tomas Straupis:
>
>>   Something like:
>>
>>   UPDATE city_boundaries SET way = ST_SimplifyPreserveTopology(way,
>> 100);
>>
>>   before running running buffer +- update.
>
> I get the error:
>
> FEHLER:  Geometry type (Polygon) does not match column type
> (MultiPolygon)
>
> Could you say me how to correct the query?

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

>

[Luca Bertoncello]

Am 27.01.2021 22:07, schrieb Luca Bertoncello:

Hi list

> derived from what you suggested
>
>> -- 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;
>
> 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.

I found what uses the huge amount of RAM:

gis=# select char_length(way) from city_boundaries where id = 25822;
char_length
-------------
2252882

So a very huge polygon...
And if I check the other polygons for Europe, I see:

gis=# select count(*) from city_boundaries where char_length(way) >
100000;
count
-------
675
(1 Zeile)

gis=# select count(*) from city_boundaries where char_length(way) >
1000000;
count
-------
45
(1 Zeile)

Maybe is there any other possibility to aggregate the huge polygons
reducing the used RAM?

Thanks