<div dir="ltr">Hi,<div><br></div><div>These are all hacks with different meanings. ST_CollectionExtract removes sliver parts that became lines, and ST_Multi just transforms a GEOMETRYCOLLECTION into MULTI*. Basically ST_Multi is most of time used with badly-written software that does not expect GEOMETRYCOLLECTION at all, or wants everything to be of the same type (so, a MULTIPOLYGON). ST_CollectionExtract performs one kind of clean up. For other clean ups, like dissolving overlapping polygons, you may need different hacks - maybe ST_UnaryUnion. If your data does not contain that kind of problems or your further processing is robust to them, you can just omit it.</div><br><div class="gmail_quote"><div dir="ltr">On Thu, Dec 13, 2018 at 6:38 PM David Haynes <<a href="mailto:haynesd2@gmail.com" target="_blank">haynesd2@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr">Question about the solution that was posted below. I have always used ST_CollectionExtract( ST_MakeValid(geom), 3 ) and in the post it uses <span class="m_5185723267929227157gmail-m_-8064163730979279118gmail-pln m_5185723267929227157gmail-m_-8064163730979279118cye-lm-tag" style="font-family:inherit;font-style:inherit;font-variant:inherit;font-weight:inherit;white-space:inherit;background-color:rgb(239,240,241);font-size:13px;margin:0px;padding:0px;border:0px;font-stretch:inherit;line-height:inherit;vertical-align:baseline;box-sizing:inherit;color:rgb(48,51,54)">st_multi</span><span class="m_5185723267929227157gmail-m_-8064163730979279118gmail-pun m_5185723267929227157gmail-m_-8064163730979279118cye-lm-tag" style="font-family:inherit;font-style:inherit;font-variant:inherit;font-weight:inherit;white-space:inherit;background-color:rgb(239,240,241);font-size:13px;margin:0px;padding:0px;border:0px;font-stretch:inherit;line-height:inherit;vertical-align:baseline;box-sizing:inherit;color:rgb(48,51,54)">(</span><span class="m_5185723267929227157gmail-m_-8064163730979279118gmail-pln m_5185723267929227157gmail-m_-8064163730979279118cye-lm-tag" style="font-family:inherit;font-style:inherit;font-variant:inherit;font-weight:inherit;white-space:inherit;background-color:rgb(239,240,241);font-size:13px;margin:0px;padding:0px;border:0px;font-stretch:inherit;line-height:inherit;vertical-align:baseline;box-sizing:inherit;color:rgb(48,51,54)">st_makevalid</span><span class="m_5185723267929227157gmail-m_-8064163730979279118gmail-pun m_5185723267929227157gmail-m_-8064163730979279118cye-lm-tag" style="font-family:inherit;font-style:inherit;font-variant:inherit;font-weight:inherit;white-space:inherit;background-color:rgb(239,240,241);font-size:13px;margin:0px;padding:0px;border:0px;font-stretch:inherit;line-height:inherit;vertical-align:baseline;box-sizing:inherit;color:rgb(48,51,54)">(</span><span class="m_5185723267929227157gmail-m_-8064163730979279118gmail-pln m_5185723267929227157gmail-m_-8064163730979279118cye-lm-tag" style="font-family:inherit;font-style:inherit;font-variant:inherit;font-weight:inherit;white-space:inherit;background-color:rgb(239,240,241);font-size:13px;margin:0px;padding:0px;border:0px;font-stretch:inherit;line-height:inherit;vertical-align:baseline;box-sizing:inherit;color:rgb(48,51,54)">geom</span><span class="m_5185723267929227157gmail-m_-8064163730979279118gmail-pun m_5185723267929227157gmail-m_-8064163730979279118cye-lm-tag" style="font-family:inherit;font-style:inherit;font-variant:inherit;font-weight:inherit;white-space:inherit;background-color:rgb(239,240,241);font-size:13px;margin:0px;padding:0px;border:0px;font-stretch:inherit;line-height:inherit;vertical-align:baseline;box-sizing:inherit;color:rgb(48,51,54)">))</span><div><font color="#303336"><br></font></div><div class="m_5185723267929227157gmail-m_-8064163730979279118cye-lm-tag"><font color="#303336" class="m_5185723267929227157gmail-m_-8064163730979279118cye-lm-tag">Which is preferred. On the website for ST_CollectionExtract() there is a new warning.</font></div><div><span style="color:rgb(46,46,46);font-family:"Lucida Grande",Verdana,Geneva,Arial,Helvetica,sans-serif;font-size:14.4px;background-color:rgb(255,241,241)" class="m_5185723267929227157gmail-m_-8064163730979279118cye-lm-tag">When specifying 3 == POLYGON a multipolygon is returned even when the edges are shared. This results in an invalid multipolygon for many cases such as applying this function on an </span><a class="m_5185723267929227157gmail-m_-8064163730979279118gmail-xref" href="https://postgis.net/docs/ST_Split.html" title="ST_Split" style="text-decoration-line:none;color:rgb(85,85,221);font-family:"Lucida Grande",Verdana,Geneva,Arial,Helvetica,sans-serif;font-size:14.4px;background-color:rgb(255,241,241)" target="_blank">ST_Split</a><span style="color:rgb(46,46,46);font-family:"Lucida Grande",Verdana,Geneva,Arial,Helvetica,sans-serif;font-size:14.4px;background-color:rgb(255,241,241)" class="m_5185723267929227157gmail-m_-8064163730979279118cye-lm-tag"> result.</span> <font color="#303336"><br></font><span class="m_5185723267929227157gmail-m_-8064163730979279118gmail-pln m_5185723267929227157gmail-m_-8064163730979279118cye-lm-tag" style="font-family:inherit;font-style:inherit;font-variant:inherit;font-weight:inherit;white-space:inherit;background-color:rgb(239,240,241);font-size:13px;margin:0px;padding:0px;border:0px;font-stretch:inherit;line-height:inherit;vertical-align:baseline;box-sizing:inherit;color:rgb(48,51,54)"></span><div><br></div><div>Try the solution outlined here:<br></div><div><div><a href="https://gis.stackexchange.com/questions/31310/acquiring-arcgis-like-speed-in-postgis" target="_blank">https://gis.stackexchange.com/questions/31310/acquiring-arcgis-like-speed-in-postgis</a><br></div><div class="m_5185723267929227157gmail-m_-8064163730979279118gmail-cye-lm-tag"><div class="m_5185723267929227157gmail-m_-8064163730979279118gmail-yj6qo m_5185723267929227157gmail-m_-8064163730979279118gmail-ajU" style="outline:none;padding:10px 0px;width:22px;margin:2px 0px 0px;color:rgb(0,0,0)"><br class="m_5185723267929227157gmail-m_-8064163730979279118gmail-Apple-interchange-newline"></div></div></div></div></div><br><div class="gmail_quote"><div dir="ltr">On Sun, Dec 2, 2018 at 3:40 PM Paul Ramsey <<a href="mailto:pramsey@cleverelephant.ca" target="_blank">pramsey@cleverelephant.ca</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div dir="ltr">Try the solution outlined here:<div><br></div><div><a href="https://gis.stackexchange.com/questions/31310/acquiring-arcgis-like-speed-in-postgis" target="_blank">https://gis.stackexchange.com/questions/31310/acquiring-arcgis-like-speed-in-postgis</a><br></div><div><br></div></div></div><br><div class="gmail_quote"><div dir="ltr">On Sun, Dec 2, 2018 at 10:44 AM Paul van der Linden <<a href="mailto:paul.doskabouter@gmail.com" target="_blank">paul.doskabouter@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div class="gmail_default" style="font-size:small">As I am working with large polygons, I'm always struggling with performance, and trying to find ways to improve them.</div><div class="gmail_default" style="font-size:small">F.e. I have lots of queries like:</div><div class="gmail_default" style="font-size:small">SELECT ST_Intersection(table1.geom,table2.geom) <br></div><div class="gmail_default" style="font-size:small">FROM table1</div><div class="gmail_default" style="font-size:small">JOIN table2 on ST_Intersects(table1.geom,table2.geom)</div><div class="gmail_default" style="font-size:small"><br></div><div class="gmail_default" style="font-size:small">In case of large polygons this is sometimes a bottleneck, and I have the following suggestion:</div><div class="gmail_default" style="font-size:small">Create a function which returns the relation between 2 polygons (within, intersects or disjunct) so that I can do the following:</div><div class="gmail_default" style="font-size:small"><br></div><div class="gmail_default" style="font-size:small">
<div class="gmail_default" style="font-size:small">SELECT <br></div><div class="gmail_default" style="font-size:small"> CASE</div><div class="gmail_default" style="font-size:small"> WHEN
ST_Relate(table1.geom,table2.geom)=intersects THEN ST_Intersection(table1.geom,table2.geom) <br></div><div class="gmail_default" style="font-size:small"> ELSE table1.geom</div><div class="gmail_default" style="font-size:small"> END<br></div><div class="gmail_default" style="font-size:small">FROM table1</div><div class="gmail_default" style="font-size:small">JOIN table2 on ST_Relate(table1.geom,table2.geom) IN (intersects,within)<br></div><div class="gmail_default" style="font-size:small"><br></div><div class="gmail_default" style="font-size:small">or (because
ST_Relate is calculated twice in previous query):</div><div class="gmail_default" style="font-size:small"><br></div><div class="gmail_default" style="font-size:small">
<div class="gmail_default" style="font-size:small">SELECT <br></div><div class="gmail_default" style="font-size:small"> CASE</div><div class="gmail_default" style="font-size:small"> WHEN relate=intersects THEN ST_Intersection(t1geom,t2geom) <br></div><div class="gmail_default" style="font-size:small"> ELSE t1geom</div><div class="gmail_default" style="font-size:small"> END</div><div class="gmail_default" style="font-size:small">FROM (<br></div><div class="gmail_default" style="font-size:small"> SELECT ST_Relate(table1.geom,table2.geom) as relate,table1.geom AS t1geom,table2.geom AS t2geom FROM table1</div><div class="gmail_default" style="font-size:small"> JOIN table2 on table1.geom && table2.geom</div><div class="gmail_default" style="font-size:small">) AS allpolies</div><div class="gmail_default" style="font-size:small">WHERE relate IN
(intersects,within)
</div><div class="gmail_default" style="font-size:small"><br></div>
</div>
</div></div>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></blockquote></div>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></blockquote></div>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></blockquote></div><br clear="all"><div><br></div>-- <br><div dir="ltr" class="m_5185723267929227157gmail_signature"><div dir="ltr"><div><div>Darafei Praliaskouski</div><div>Support me: <a href="http://patreon.com/komzpa" target="_blank">http://patreon.com/komzpa</a></div></div></div></div></div>-- <br><div dir="ltr" class="gmail_signature" data-smartmail="gmail_signature"><div dir="ltr">Darafei Praliaskouski<br>Support me: <a href="http://patreon.com/komzpa">http://patreon.com/komzpa</a></div></div>