<div dir="ltr"><div class="gmail_default" style="font-size:small">I have a query that gives inconsistent results.</div><div class="gmail_default" style="font-size:small">It almost seems like st_makevalid doesn't always produce the same results given the same input, and sometimes even returns invalid geometries...</div><div class="gmail_default" style="font-size:small"><br></div><div class="gmail_default" style="font-size:small">Results are</div><div class="gmail_default" style="font-size:small">f;1159320407;"Bangladesh"<br>f;1159321073;"Mozambique"<br>f;1159316737;"Nord"<br>f;1159314827;"Uusimaa"</div><div class="gmail_default" style="font-size:small"><br></div><div class="gmail_default" style="font-size:small">second run:</div><div class="gmail_default" style="font-size:small">f;1159321073;"Mozambique"<br>t;1159315455;"Syddanmark"<br>t;1159314827;"Uusimaa"<br>t;1159317795;"Gerona"</div><div class="gmail_default" style="font-size:small"><br></div><div class="gmail_default" style="font-size:small">third run:</div><div class="gmail_default" style="font-size:small">f;1159321073;"Mozambique"<br>t;1159315455;"Syddanmark"</div><div class="gmail_default" style="font-size:small"><br></div><div class="gmail_default" style="font-size:small">note the 't' in the second and third run...</div><div class="gmail_default" style="font-size:small"><br></div><div class="gmail_default" style="font-size:small">Query:<br></div><div class="gmail_default" style="font-size:small"><br></div><div class="gmail_default" style="font-size:small">SELECT ST_isvalid(simplifiedgeo2),* FROM (<br>SELECT  <br>    foo2.ne_id,<br>    <a href="http://foo2.name" target="_blank">foo2.name</a>,<br>    CASE<br>        WHEN ST_isvalid(foo2.simplifiedgeo) THEN foo2.simplifiedgeo<br>        ELSE ST_makevalid(foo2.simplifiedgeo)<br>    END AS simplifiedgeo2<br>   FROM ( <br>     SELECT <br>       ST_simplifypreservetopology(foo.geom, <br>         LEAST(<br>           ST_xmax(foo.shiftedgeo::box3d) - ST_xmin(foo.shiftedgeo::box3d), ST_ymax(foo.shiftedgeo::box3d) - ST_ymin(foo.shiftedgeo::box3d))<br>         ) AS simplifiedgeo,<br>       foo.ne_id,<br>       <a href="http://foo.name" target="_blank">foo.name</a>,<br>       foo.geom<br>     FROM ( <br>        SELECT <br>          natearth.ne_id,<br>          natearth.geom,<br>          <a href="http://natearth.name" target="_blank">natearth.name</a>,<br>          CASE<br>              WHEN (<br>                ST_xmax(ST_shiftlongitude(natearth.geom)::box3d) - ST_xmin(ST_shiftlongitude(natearth.geom)::box3d)) <br>                < <br>                (ST_xmax(natearth.geom::box3d) - ST_xmin(natearth.geom::box3d)<br>              ) THEN ST_shiftlongitude(natearth.geom)<br>              ELSE natearth.geom<br>          END AS shiftedgeo<br>          FROM ( <br>             SELECT <br>                ne_10m_admin_0_countries_lakes.ne_id,<br>                ne_10m_admin_0_countries_lakes.geom,<br>                <a href="http://ne_10m_admin_0_countries_lakes.name" target="_blank">ne_10m_admin_0_countries_lakes.name</a><br>              FROM natural_earth.ne_10m_admin_0_countries_lakes<br>          UNION ALL<br>             SELECT <br>                ne_10m_admin_1_states_provinces_lakes.ne_id,<br>                ne_10m_admin_1_states_provinces_lakes.geom,<br>                <a href="http://ne_10m_admin_1_states_provinces_lakes.name" target="_blank">ne_10m_admin_1_states_provinces_lakes.name</a><br>             FROM natural_earth.ne_10m_admin_1_states_provinces_lakes<br>          ) natearth<br>     ) foo<br>   ) foo2<br>) as fff<br>WHERE NOT ST_isvalid(simplifiedgeo2)</div><div class="gmail_default" style="font-size:small"><br></div><div class="gmail_default" style="font-size:small">Any advice on how to further debug this?<br></div><div class="gmail_default" style="font-size:small"><br></div><div class="gmail_default" style="font-size:small">
"POSTGIS="2.5.1 r17027" [EXTENSION] PGSQL="110" GEOS="3.7.0-CAPI-1.11.0 
3.7.1" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.2.4, released 
2018/03/19 GDAL_DATA not found" LIBXML="2.7.8" LIBJSON="0.12" 
LIBPROTOBUF="1.2.1" (core procs from "2.5.0 r16836" nee (...)"<br>"PostgreSQL 11.2, compiled by Visual C++ build 1914, 64-bit" <br></div><div class="gmail_default" style="font-size:small"><br></div><div class="gmail_default" style="font-size:small">Running on windows 64-bit.<br></div></div>