ST_Union behaviour

[Antonio Valanzano]

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> 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>
> *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> 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>
> *Sent:* Monday, December 23, 2024 3:47 AM
> *To:* 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/77d632b6/attachment.htm>