<div dir="ltr">That's a pretty complicated query to try and understand, let alone reproduce and debug.<div><br></div><div>You could capture the full output from a few runs and provide that (with geometry as WKB). That might allow seeing if the discrepancy is real and why it is happening.</div><div><br></div><div><br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Thu, Mar 26, 2020 at 7:55 AM Paul van der Linden <<a href="mailto:paul.doskabouter@gmail.com">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><div style="font-size:small" class="gmail_default">Doesn't anybody have an idea? Or is at least curious?</div><div style="font-size:small" class="gmail_default"><br></div><div style="font-size:small" class="gmail_default">Especially the select st_isvalid(geo) where not st_valid returning a "true" row?<br></div><br></div><div>>I have a query that gives inconsistent results.</div>>It almost seems like st_makevalid doesn't always produce the same results<br>>given the same input, and sometimes even returns invalid geometries...<br>><br>>Results are<br>>f;1159320407;"Bangladesh"<br>>f;1159321073;"Mozambique"<br>>f;1159316737;"Nord"<br>>f;1159314827;"Uusimaa"<br>><br>>second run:<br>>f;1159321073;"Mozambique"<br>>t;1159315455;"Syddanmark"<br>>t;1159314827;"Uusimaa"<br>>t;1159317795;"Gerona"<br>><br>>third run:<br>>f;1159321073;"Mozambique"<br>>t;1159315455;"Syddanmark"<br>><br>>note the 't' in the second and third run...<br>><br>>Query:<br>><br>>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),<br>>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) -<br>>ST_xmin(ST_shiftlongitude(natearth.geom)::box3d))<br>> <<br>> (ST_xmax(natearth.geom::box3d) -<br>>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)<br>><br>>Any advice on how to further debug this?<br>><br>>"POSTGIS="2.5.1 r17027" [EXTENSION] PGSQL="110" GEOS="3.7.0-CAPI-1.11.0<br>>3.7.1" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.2.4, released<br>>2018/03/19 GDAL_DATA not found" LIBXML="2.7.8" LIBJSON="0.12"<br>>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>><br>>Running on windows 64-bit.</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>