<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="gmail-pln cye-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="gmail-pun cye-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="gmail-pln cye-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="gmail-pun cye-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="gmail-pln cye-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="gmail-pun cye-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="cye-lm-tag"><font color="#303336" class="cye-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="cye-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="gmail-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)">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="cye-lm-tag"> result.</span>  <font color="#303336"><br></font><span class="gmail-pln cye-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="gmail-cye-lm-tag"><div class="gmail-yj6qo gmail-ajU" style="outline:none;padding:10px 0px;width:22px;margin:2px 0px 0px;color:rgb(0,0,0)"><br class="gmail-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">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>