ST_Union behaviour

[Regina Obe]

That behavior has always been like that from the beginning of time.  I forget the reasons.  

I think for one, if you were to return what you describe, it would be an invalid multipolygon so you’d always have to return a geometry collection in that case.

 

To be honest, I haven’t tried it with millions of features.  But my assumption based on how it works

 

1.	Since ST_Polygonize is a aggregate function, it could create a geometry collection that is too big for PostgreSQL 2GB toast limit which easily eat up your ram,  so I would be more concerned with you hitting a hard limit 
2.	If you have a GROUP BY in there, which I imagine is the more common case, then it should be able to handle millions of features fine for most cases, but would still be dependent on the number of vertices in each of your polygons and number of groupings you have
3.	You can’t use the topology functions unless you decide to manage your dataset with topology or create a temporary topology.  Which is something you should consider doing, if you always require this kind of splitting 

 

From: Antonio Valanzano <anvalanz at gmail.com> 
Sent: Monday, December 23, 2024 11:55 PM
To: postgis-users at lists.osgeo.org
Subject: Re: ST_Union behaviour

 

Regina I have already followed what you have suggested after reading  this post 

https://blog.cleverelephant.ca/2019/07/postgis-overlays.html (the attached jpeg new_polygons was realized using such multistep procedure).

 

 

 

My question is  about the different behaviour of ST_Union with lines and polygons and I am wondering if exists a more direct way of obtaining the final result.

 

Probably there is an historical reason for this behaviour that I don't know.

 

The multistep procedure requires to use St_Polygonize and, based on your experience,  is it able to correctly manage tables with millions of features?

 

Would it be possible to use some functions provided by the topology extensions?

 

The topological model does not allow overlaps between features within the same layer.

 

Thanks for your patience.

 

Antonio

 

 

 

 

 

Il Lun 23 Dic 2024, 23:52 Regina Obe <lr at pcorp.us <mailto:lr at pcorp.us> > ha scritto:

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 <anvalanz at gmail.com> 
Sent: Monday, December 23, 2024 6:37 AM
To: Regina Obe <lr at pcorp.us <mailto:lr at pcorp.us> >
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 <lr at pcorp.us <mailto:lr at pcorp.us> > 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 <anvalanz at gmail.com <mailto:anvalanz at gmail.com> > 
Sent: Monday, December 23, 2024 3:47 AM
To: postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org> 
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

 

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20241224/9f2d1f0c/attachment.htm>