<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=utf-8"><meta name=Generator content="Microsoft Word 15 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Aptos;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
font-size:12.0pt;
font-family:"Aptos",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
p.MsoListParagraph, li.MsoListParagraph, div.MsoListParagraph
{mso-style-priority:34;
margin-top:0in;
margin-right:0in;
margin-bottom:0in;
margin-left:.5in;
font-size:12.0pt;
font-family:"Aptos",sans-serif;}
span.EmailStyle18
{mso-style-type:personal-reply;
font-family:"Aptos",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:11.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
/* List Definitions */
@list l0
{mso-list-id:409272888;
mso-list-type:hybrid;
mso-list-template-ids:257331908 67698705 67698713 67698715 67698703 67698713 67698715 67698703 67698713 67698715;}
@list l0:level1
{mso-level-text:"%1\)";
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level2
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level3
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l0:level4
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level5
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level6
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l0:level7
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level8
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level9
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l1
{mso-list-id:777722009;
mso-list-template-ids:-1701002272;}
ol
{margin-bottom:0in;}
ul
{margin-bottom:0in;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-US link=blue vlink=purple style='word-wrap:break-word'><div class=WordSection1><p class=MsoNormal><span style='font-size:11.0pt'>That behavior has always been like that from the beginning of time. I forget the reasons. <o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'>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.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'>To be honest, I haven’t tried it with millions of features. But my assumption based on how it works<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt'><o:p> </o:p></span></p><ol style='margin-top:0in' start=1 type=1><li class=MsoListParagraph style='margin-left:0in;mso-list:l0 level1 lfo2'><span style='font-size:11.0pt'> 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 <o:p></o:p></span></li><li class=MsoListParagraph style='margin-left:0in;mso-list:l0 level1 lfo2'><span style='font-size:11.0pt'>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<o:p></o:p></span></li><li class=MsoListParagraph style='margin-left:0in;mso-list:l0 level1 lfo2'><span style='font-size:11.0pt'>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 <o:p></o:p></span></li></ol><p class=MsoNormal><span style='font-size:11.0pt'><o:p> </o:p></span></p><div style='border:none;border-left:solid blue 1.5pt;padding:0in 0in 0in 4.0pt'><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal><b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>From:</span></b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'> Antonio Valanzano <anvalanz@gmail.com> <br><b>Sent:</b> Monday, December 23, 2024 11:55 PM<br><b>To:</b> postgis-users@lists.osgeo.org<br><b>Subject:</b> Re: ST_Union behaviour<o:p></o:p></span></p></div></div><p class=MsoNormal><o:p> </o:p></p><div><p class=MsoNormal>Regina I have already followed what you have suggested after reading this post <o:p></o:p></p><div><p class=MsoNormal><a href="https://blog.cleverelephant.ca/2019/07/postgis-overlays.html" target="_blank">https://blog.cleverelephant.ca/2019/07/postgis-overlays.html</a> (the attached jpeg new_polygons was realized using such multistep procedure).<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>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.<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>Probably there is an historical reason for this behaviour that I don't know.<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>The multistep procedure requires to use St_Polygonize and, based on your experience, is it able to correctly manage tables with millions of features?<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>Would it be possible to use some functions provided by the topology extensions?<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>The topological model does not allow overlaps between features within the same layer.<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>Thanks for your patience.<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>Antonio<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><div><p class=MsoNormal><o:p> </o:p></p><div><div><p class=MsoNormal>Il Lun 23 Dic 2024, 23:52 Regina Obe <<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>> ha scritto:<o:p></o:p></p></div><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-right:0in'><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt'>Best to keep conversation on the list. Added back postgis-users.</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt'>I think what might do what you want here is combination of ST_Boundary, ST_Union, and ST_Polygonize</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt'> </span><o:p></o:p></p><ol start=1 type=1><li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;mso-list:l1 level1 lfo1'><span style='font-size:11.0pt'>Get the linework of the polygons using ST_Boundary – <a href="https://postgis.net/docs/ST_Boundary.html" target="_blank">https://postgis.net/docs/ST_Boundary.html</a></span><o:p></o:p></li><li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;mso-list:l1 level1 lfo1'><span style='font-size:11.0pt'>Union the linework – as you noted ST_Union when fed linestrings will create a multilinestring with splits at the junctions <a href="https://postgis.net/docs/ST_Union.html" target="_blank">https://postgis.net/docs/ST_Union.html</a></span><o:p></o:p></li><li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;mso-list:l1 level1 lfo1'><span style='font-size:11.0pt'>Then polygonise the linework - <a href="https://postgis.net/docs/ST_Polygonize.html" target="_blank">https://postgis.net/docs/ST_Polygonize.html</a> (this is an aggregate that returns a geometry collection of all the polygons formed from the linework</span><o:p></o:p></li><li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;mso-list:l1 level1 lfo1'><span style='font-size:11.0pt'>Use ST_Dump to dump out the individual polygons from the polygonize operation <a href="https://postgis.net/docs/ST_Dump.html" target="_blank">https://postgis.net/docs/ST_Dump.html</a></span><o:p></o:p></li></ol><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt'>WITH a(name, geom) AS ( VALUES ( 'A', ST_GeomFromText('POLYGON((0 0, 0 4, 4 4, 4 0, 0 0))') )</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt'> , ( 'B', ST_GeomFromText('POLYGON((3 1, 3 3, 6 3, 6 1, 3 1))') )</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt'> , ( 'C', ST_GeomFromText('POLYGON((3 -1, 3 2, 8 2, 8 -1, 3 -1))') )</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt'> )</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt'>, b(geom) AS (SELECT ST_Union(ST_Boundary(a.geom)) AS geom FROM a)</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt'>SELECT (ST_Dump(ST_Polygonize( b.geom) )).geom</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt'>FROM b;</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt'> </span><o:p></o:p></p><div style='border:none;border-left:solid blue 1.5pt;padding:0in 0in 0in 4.0pt'><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>From:</span></b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'> Antonio Valanzano <<a href="mailto:anvalanz@gmail.com" target="_blank">anvalanz@gmail.com</a>> <br><b>Sent:</b> Monday, December 23, 2024 6:37 AM<br><b>To:</b> Regina Obe <<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>><br><b>Subject:</b> Re: ST_Union behaviour</span><o:p></o:p></p></div></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Regina, thanks for the fast reply.<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>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)<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>The four initial polygons are all valid as you can see from the results of the following query<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>SELECT <br> id, <br> ST_isValid(geom)<br>FROM chp02.prova_overlap;<br>-- 4 rows<br>"id" "st_isvalid"<br>1 true<br>2 true<br>3 true<br>4 true<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>I have also tried, as you suggested, the ST_UnaryUnion but the result is the same as ST_Union.<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Antonio<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Il giorno lun 23 dic 2024 alle ore 10:46 Regina Obe <<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>> ha scritto:<o:p></o:p></p></div><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-top:5.0pt;margin-right:0in;margin-bottom:5.0pt'><div><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt'>Are you unioning one geometry or many?</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt'>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.</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt'>In these cases you should be using ST_UnaryUnion <a href="https://postgis.net/docs/ST_UnaryUnion.html" target="_blank">https://postgis.net/docs/ST_UnaryUnion.html</a></span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt'> </span><o:p></o:p></p><div style='border:none;border-left:solid windowtext 1.5pt;padding:0in 0in 0in 4.0pt;border-color:currentcolor currentcolor currentcolor blue'><div><div style='border:none;border-top:solid windowtext 1.0pt;padding:3.0pt 0in 0in 0in;border-color:currentcolor currentcolor'><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>From:</span></b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'> Antonio Valanzano <<a href="mailto:anvalanz@gmail.com" target="_blank">anvalanz@gmail.com</a>> <br><b>Sent:</b> Monday, December 23, 2024 3:47 AM<br><b>To:</b> <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br><b>Subject:</b> ST_Union behaviour</span><o:p></o:p></p></div></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Does someone know why ST_Union behaves differently with linestrings and polygons?<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>If a table contains linestrings that overlap at some points then ST_Union creates a collection of linestrings that are splitted at intersections.<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>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).<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Thanks in advance.<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Antonio<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div></div></div></div></div></div></blockquote></div></div></div></div></blockquote></div></div></div></div></div></div></body></html>