Re: Help with a query

You might try adding a step to get the MultiPolygons from your Geometrycollection, see

https://postgis.net/docs/ST_CollectionExtract.html

Use type = 3 for polygons

UPDATE city_boundaries SET wayn3 = ST_Multi(ST_CollectionExtract(ST_Buffer(wayn2, -50, 'join=miter'),3));


Brent Wood

Principal Technician, Fisheries
NIWA
DDI:  +64 (4) 3860529

________________________________
From: Luca Bertoncello <[email protected]>
Sent: Monday, December 23, 2024 03:40
To: [email protected] <[email protected]>
Subject: Help with a query

Hi list!

I have a query that worked some years with my "old" Debian 10 and
Postgres 11.
Now with Debian 12 and Postgres 15 throws an error...

So, my problem: I need to create a table with the _physical_ boundaries
of the cities.
I already asked you some years ago and now I have my query.

So I create a Table `city_boundaries`. Now I must "compact" it, in order
to have only the outer polygon.
The table has a column `way` defined as geometry(MultiPolygon,3857).

As suggested 2021, I aggregate the building using 4 temporary columns,
wayn1, wayn2, wayn3 and wayn4. They are defined too as
geometry(MultiPolygon,3857).

Then I run my queries:

UPDATE city_boundaries SET wayn1 =
ST_Multi(ST_SimplifyPreserveTopology(way, 100));
UPDATE city_boundaries SET wayn2 = ST_Multi(ST_Buffer(wayn1, 50,
'join=miter'));
UPDATE city_boundaries SET wayn3 = ST_Multi(ST_Buffer(wayn2, -50,
'join=miter'));
UPDATE city_boundaries SET wayn4 = ST_Makevalid(wayn3);

The last query throws the error:

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

I suppose, in the last versions of PostGIS the Function ST_Makevalid
will return something other than in the previous one.
Can someone suggest me how to "convert" it as needed?

Thanks a lot
Luca Bertoncello
([email protected])
[https://www.niwa.co.nz/static/niwa-2018-horizontal-180.png] <https://www.niwa.co.nz>
Brent Wood
Principal Technician - GIS and Spatial Data Management
Programme Leader - Environmental Information Delivery
+64-4-386-0529

National Institute of Water & Atmospheric Research Ltd (NIWA)
301 Evans Bay Parade Hataitai Wellington New Zealand
Connect with NIWA: niwa.co.nz<https://www.niwa.co.nz> Facebook<https://www.facebook.com/nzniwa> LinkedIn<https://www.linkedin.com/company/niwa> Twitter<https://twitter.com/niwa_nz> Instagram<https://www.instagram.com/niwa_science> YouTube<https://www.youtube.com/channel/UCJ-j3MLMg1H59Ak2UaNLL3A>
To ensure compliance with legal requirements and to maintain cyber security standards, NIWA's IT systems are subject to ongoing monitoring, activity logging and auditing. This monitoring and auditing service may be provided by third parties. Such third parties can access information transmitted to, processed by and stored on NIWA's IT systems.
Note: This email is intended solely for the use of the addressee and may contain information that is confidential or subject to legal professional privilege. If you receive this email in error please immediately notify the sender and delete the email.