RE: ST_Union behaviour

Best to keep conversation on the list.  Added back postgis-users.

 

I think what might do what you want here is combination of ST_Boundary, ST_Union, and ST_Polygonize

 

1.	Get the linework of the polygons using ST_Boundary –  https://postgis.net/docs/ST_Boundary.html
2.	Union the linework – as you noted ST_Union when fed linestrings will create a multilinestring with splits at the junctions https://postgis.net/docs/ST_Union.html
3.	Then polygonise the linework -  https://postgis.net/docs/ST_Polygonize.html  (this is an aggregate that returns a geometry collection of all the polygons formed from the linework
4.	Use ST_Dump to dump out the individual polygons from the polygonize operation  https://postgis.net/docs/ST_Dump.html

 

 

WITH a(name, geom) AS ( VALUES ( 'A', ST_GeomFromText('POLYGON((0 0, 0 4, 4 4, 4 0, 0 0))') )

      , ( 'B', ST_GeomFromText('POLYGON((3 1, 3 3, 6 3, 6 1, 3 1))') )

      , ( 'C', ST_GeomFromText('POLYGON((3 -1, 3 2, 8 2, 8 -1, 3 -1))') )

    )

, b(geom) AS (SELECT ST_Union(ST_Boundary(a.geom)) AS geom FROM a)

SELECT (ST_Dump(ST_Polygonize( b.geom) )).geom

FROM b;

 

 

 

From: Antonio Valanzano <[email protected]> 
Sent: Monday, December 23, 2024 6:37 AM
To: Regina Obe <[email protected]>
Subject: Re: ST_Union behaviour

 

Regina, thanks for the fast reply.

 

I am unioning 4 distinct polygons(see attached polygon_overlapping.jpeg) which present some overlapping (some areas with 2 overlapping polygons, 1 area with 3 overlapping polygons) and I would like to create a new table comprising all these polygons but splitted where they overlap. (see attached new_polygons.jpeg)

 

The four initial polygons are all valid as you can see from the results of the following query

SELECT 
  id, 
  ST_isValid(geom)
FROM chp02.prova_overlap;
-- 4 rows
"id" "st_isvalid"
1 true
2 true
3 true
4 true

 

I have also tried, as you suggested, the ST_UnaryUnion but the result is the same as ST_Union.

 

Antonio

 

 

Il giorno lun 23 dic 2024 alle ore 10:46 Regina Obe <[email protected] <mailto:[email protected]> > ha scritto:

Are you unioning one geometry or many?

 

The only reason I can think of why ST_Union would return unchanged overlapping polygons is if you fed it a geometry collection or invalid multipolygon with overlapping polygons.

 

In these cases you should be using ST_UnaryUnion  https://postgis.net/docs/ST_UnaryUnion.html

 

 

 

From: Antonio Valanzano <[email protected] <mailto:[email protected]> > 
Sent: Monday, December 23, 2024 3:47 AM
To: [email protected] <mailto:[email protected]> 
Subject: ST_Union behaviour

 

Does someone know why ST_Union behaves differently with linestrings and polygons?

 

If a table contains linestrings that overlap at some points then ST_Union creates a collection of linestrings  that are splitted at intersections.

 

If a table contains polygons with overlaps then ST_Union creates a collection of polygons that are still overlapped and are not splitted (the result contains only the original polygons).

 

Thanks in advance.

 

Antonio