<div dir="ltr">Hi,<div><br></div><div>923 is still pretty much to review. I hope this snipped can help you isolate just a really breaking couple, run it in a loop while it still deletes something:<br><div><br></div><div><pre style="background-color:rgb(43,43,43);color:rgb(169,183,198);font-family:"DejaVu Sans Mono""><span style="color:rgb(204,120,50);font-weight:bold">delete<br></span><span style="color:rgb(204,120,50);font-weight:bold">from </span>bad_input<br><span style="color:rgb(204,120,50);font-weight:bold">where </span><span style="color:rgb(255,198,109)">ctid </span>= (<br> <span style="color:rgb(204,120,50);font-weight:bold">select </span><span style="color:rgb(255,198,109)">ctid<br></span><span style="color:rgb(255,198,109)"> </span><span style="color:rgb(204,120,50);font-weight:bold">from </span>bad_input a<br> <span style="color:rgb(204,120,50);font-weight:bold">where </span>(<br> <span style="color:rgb(204,120,50);font-weight:bold">select </span><span style="color:rgb(255,198,109);font-style:italic">st_union_fails</span>(<span style="color:rgb(255,198,109);font-style:italic">array_agg</span>(geom)) <span style="color:rgb(204,120,50);font-weight:bold">from </span>bad_input b <span style="color:rgb(204,120,50);font-weight:bold">where </span>a.<span style="color:rgb(255,198,109)">ctid </span>!= b.<span style="color:rgb(255,198,109)">ctid<br></span><span style="color:rgb(255,198,109)"> </span>)<br> <span style="color:rgb(204,120,50);font-weight:bold">limit </span><span style="color:rgb(104,151,187)">1<br></span><span style="color:rgb(104,151,187)"> </span>)<span style="color:rgb(204,120,50)">;</span></pre></div></div></div><br><div class="gmail_quote"><div dir="ltr">ср, 19 сент. 2018 г. в 10:47, Birgit Laggner <<a href="mailto:birgit.laggner@thuenen.de">birgit.laggner@thuenen.de</a>>:<br></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div style="font-family:arial,helvetica,sans-serif;font-size:12pt;color:#000000"><div>Dear Darafei,<br></div><div><br></div><div>good idea to write a wrapper to ferret out the offending rows. I had to change it a bit because I don't use ST_Union(geometry, geometry) but the aggregate ST_Union(geometry). Since I am not very experienced in writing aggregates, I modified the wrapper that it fits for ST_Union(geometry[]) and used it in my query with ST_Union_Fails(array_agg(geom)).</div><div><br></div><div>I have found 3 groups with a total of 923 geometries which returned an error with ST_Union.<br></div><div><br></div><div>As soon as I get access to my old userID, I will file the them as a ticket.<br></div><div><br></div><div>In the meantime I will try to union the problematic geometries in a loop one at a time with st_makevalid after each loop - maybe that will work as a workaround, now I could reduce the number of geometries dramatically.<br></div><div><br></div><div><br></div><div>Thank you very much for your help!<br></div><div><br></div><div>Kind regards,<br></div><div>Birgit<br></div><div><br></div><div><br></div><div><br></div><hr id="m_6602761528276242407zwchr"><div><b>Von: </b>"Darafei \"Komяpa\" Praliaskouski" <<a href="mailto:me@komzpa.net" target="_blank">me@komzpa.net</a>><br><b>An: </b>"PostGIS Users Discussion" <<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>><br><b>Gesendet: </b>Mittwoch, 19. September 2018 02:46:42<br><b>Betreff: </b>Re: [postgis-users] GEOSUnaryUnion: TopologyException: found non-noded intersection BUT geom is valid<br></div></div></div><div><div style="font-family:arial,helvetica,sans-serif;font-size:12pt;color:#000000"><br><div><div dir="ltr">Hi,<br><div>This is not supposed to happen. </div><br><div>Can you please isolate the offending rows unioning them pairwise and file them as a ticket? Here's a handy helper function that returns True if Union failed:</div><br><div><pre style="background-color:rgb(43,43,43);color:rgb(169,183,198);font-family:"DejaVu Sans Mono""><span style="color:rgb(204,120,50);font-weight:bold">create or replace function </span><span style="color:rgb(255,198,109);font-style:italic">st_union_fails</span>(geom1 <span style="color:rgb(204,120,50);font-weight:bold">geometry</span><span style="color:rgb(204,120,50)">, </span>geom2 <span style="color:rgb(204,120,50);font-weight:bold">geometry</span>)<br> <span style="color:rgb(204,120,50);font-weight:bold">returns boolean<br></span><span style="color:rgb(204,120,50);font-weight:bold">as </span><span style="color:rgb(106,135,89)">$$<br></span><span style="color:rgb(204,120,50);font-weight:bold">begin<br></span><span style="color:rgb(204,120,50);font-weight:bold"> begin<br></span><span style="color:rgb(204,120,50);font-weight:bold"> </span>geom1 = <span style="color:rgb(255,198,109);font-style:italic">ST_Union</span>(Geom1<span style="color:rgb(204,120,50)">, </span>geom2)<span style="color:rgb(204,120,50)">;<br></span><span style="color:rgb(204,120,50)"> </span><span style="color:rgb(204,120,50);font-weight:bold">return false</span><span style="color:rgb(204,120,50)">;<br></span><span style="color:rgb(204,120,50)"> </span><span style="color:rgb(204,120,50);font-weight:bold">exception when others<br></span><span style="color:rgb(204,120,50);font-weight:bold"> then<br></span><span style="color:rgb(204,120,50);font-weight:bold"> return true</span><span style="color:rgb(204,120,50)">;<br></span><span style="color:rgb(204,120,50)"> </span><span style="color:rgb(204,120,50);font-weight:bold">end</span><span style="color:rgb(204,120,50)">;<br></span><span style="color:rgb(204,120,50);font-weight:bold">end</span><span style="color:rgb(204,120,50)">;<br></span><span style="color:rgb(106,135,89)">$$<br></span><span style="color:rgb(204,120,50);font-weight:bold">language </span>plpgsql<span style="color:rgb(204,120,50)">;</span></pre></div><br><br></div><br><div class="gmail_quote"><div dir="ltr">вт, 18 сент. 2018 г. в 16:25, Birgit Laggner <<a href="mailto:birgit.laggner@thuenen.de" target="_blank">birgit.laggner@thuenen.de</a>>:<br></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div id="m_6602761528276242407m_2151994923472200391zimbraEditorContainer" style="font-family:arial,helvetica,sans-serif;font-size:12pt;color:#000000" class="m_6602761528276242407m_21519949234722003912"><div>Dear discussion group,<br></div><br><div>I am trying to ST_Union several polygons and get a TopologyException (GEOSUnaryUnion: TopologyException: found non-noded intersection between LINESTRING (3.56442e+06 5.42679e+06, 3.56442e+06 5.42679e+06) and LINESTRING (3.56442e+06 5.42679e+06, 3.56442e+06 5.42679e+06) at 3564420.7944701263 5426786.9800475985).<br></div><div>I have tested with ST_Valid and all polygons in the datasets are valid.<br></div><br><div>The dataset is pretty large (53.5 Mio. polygons) and will probably get grouped into around 8.7 Mio resulting geometries during ST_Union.<br><br></div><div>If I try to ST_Union the geometries near the point ST_Union mentions in the error message (ST_Buffer with up to 100 metres), everything works fine. I am out of ideas how to find the problematic geometry or at least how to work around the TopologyException.<br></div><br><div>My PostGIS version is: "POSTGIS="2.3.3 r15473" GEOS="3.5.1-CAPI-1.9.1 r4246" SFCGAL="1.2.2" PROJ="Rel. 4.9.2, 08 September 2015" GDAL="GDAL 1.11.3, released 2015/09/16" LIBXML="2.9.3" LIBJSON="0.11.99" TOPOLOGY RASTER"<br></div><br><br><div>I would be very glad if anyone would come up with ideas how to solve my problem.<br></div><br><div>Thanks a lot in advance!<br></div><br><div>Regards,<br></div><div>Birgit<br></div><br><br></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><br></blockquote></div>-- <br><div dir="ltr" class="m_6602761528276242407gmail_signature"><div dir="ltr">Darafei Praliaskouski<br>Support me: <a href="http://patreon.com/komzpa" target="_blank">http://patreon.com/komzpa</a><br></div></div>
<br>_______________________________________________<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" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br></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><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>