[postgis-users] St_makevalid issue

Thu Mar 26 09:28:25 PDT 2020

>That's a pretty complicated query to try and understand, let alone
>reproduce and debug.
>
>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.
>
>
>
>On Thu, Mar 26, 2020 at 7:55 AM Paul van der Linden <
>paul.doskabouter at gmail.com> wrote:
>
>> Doesn't anybody have an idea? Or is at least curious?
>>
>> Especially the select st_isvalid(geo) where not st_valid returning a
>> "true" row?
>>
>> >I have a query that gives inconsistent results.
>> >It almost seems like st_makevalid doesn't always produce the same
results
>> >given the same input, and sometimes even returns invalid geometries...
>> >
>> >Results are
>> >f;1159320407;"Bangladesh"
>> >f;1159321073;"Mozambique"
>> >f;1159316737;"Nord"
>> >f;1159314827;"Uusimaa"
>> >
>> >second run:
>> >f;1159321073;"Mozambique"
>> >t;1159315455;"Syddanmark"
>> >t;1159314827;"Uusimaa"
>> >t;1159317795;"Gerona"
>> >
>> >third run:
>> >f;1159321073;"Mozambique"
>> >t;1159315455;"Syddanmark"
>> >
>> >note the 't' in the second and third run...
>> >
>> >Query:
>> >
>> >SELECT ST_isvalid(simplifiedgeo2),* FROM (
>> >SELECT
>> >    foo2.ne_id,
>> >    foo2.name,
>> >    CASE
>> >        WHEN ST_isvalid(foo2.simplifiedgeo) THEN foo2.simplifiedgeo
>> >        ELSE ST_makevalid(foo2.simplifiedgeo)
>> >    END AS simplifiedgeo2
>> >   FROM (
>> >     SELECT
>> >       ST_simplifypreservetopology(foo.geom,
>> >         LEAST(
>> >           ST_xmax(foo.shiftedgeo::box3d) -
>> ST_xmin(foo.shiftedgeo::box3d),
>> >ST_ymax(foo.shiftedgeo::box3d) - ST_ymin(foo.shiftedgeo::box3d))
>> >         ) AS simplifiedgeo,
>> >       foo.ne_id,
>> >       foo.name,
>> >       foo.geom
>> >     FROM (
>> >        SELECT
>> >          natearth.ne_id,
>> >          natearth.geom,
>> >          natearth.name,
>> >          CASE
>> >              WHEN (
>> >                ST_xmax(ST_shiftlongitude(natearth.geom)::box3d) -
>> >ST_xmin(ST_shiftlongitude(natearth.geom)::box3d))
>> >                <
>> >                (ST_xmax(natearth.geom::box3d) -
>> >ST_xmin(natearth.geom::box3d)
>> >              ) THEN ST_shiftlongitude(natearth.geom)
>> >              ELSE natearth.geom
>> >          END AS shiftedgeo
>> >          FROM (
>> >             SELECT
>> >                ne_10m_admin_0_countries_lakes.ne_id,
>> >                ne_10m_admin_0_countries_lakes.geom,
>> >                ne_10m_admin_0_countries_lakes.name
>> >              FROM natural_earth.ne_10m_admin_0_countries_lakes
>> >          UNION ALL
>> >             SELECT
>> >                ne_10m_admin_1_states_provinces_lakes.ne_id,
>> >                ne_10m_admin_1_states_provinces_lakes.geom,
>> >                ne_10m_admin_1_states_provinces_lakes.name
>> >             FROM natural_earth.ne_10m_admin_1_states_provinces_lakes
>> >          ) natearth
>> >     ) foo
>> >   ) foo2
>> >) as fff
>> >WHERE NOT ST_isvalid(simplifiedgeo2)
>> >
>> >Any advice on how to further debug this?
>> >
>> >"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 (...)"
>> >"PostgreSQL 11.2, compiled by Visual C++ build 1914, 64-bit"
>> >
>> >Running on windows 64-bit.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200326/7ccd3dd4/attachment.html>
-------------- next part --------------
"st_isvalid","ne_id","name","simplifiedgeo2"
"f","1159321073","Mozambique","0106000020E6100000050000000103000000010000000600000038ABE6C2930E40400C5B132E0BD73AC0E028E333E7363E40283E922582F62DC0388307595841444060D6BF2897F424C050700659784A444040E9C028F7FB2EC010B50659D04641403081B6E9109533C038ABE6C2930E40400C5B132E0BD73AC00103000000010000000500000068B809C2F75A41404066D287E02128C070FEF6CA4D5D41404068D96A7D0F28C01834F722E2604140C0ED043B6C1628C0308742AFB25E4140C077D8697B2D28C068B809C2F75A41404066D287E02128C00103000000010000000500000010B506591074404030EB5F94CB0E3AC0C844B2038B754040808761945B003AC0C0AE5BAEC57D40403081B6E910F839C0C844B2030B7B404030EB5F944B0B3AC010B506591074404030EB5F94CB0E3AC00103000000010000000500000058BBB1031BBC41403081B6E9908C35C010B50659D0BE4140705BB4E950A435C0E831B103ABB7414050D85E946BC835C0803E075900B84140F07A0B3F46AC35C058BBB1031BBC41403081B6E9908C35C001030000000100000005000000186687B66BF443401078709B9C4630C0602D39B922F943400035CF795C4D30C090DB24E371F04340A0C8BA48B96A30C0106D339421E9434060E298F0415130C0186687B66BF443401078709B9C4630C0"
"t","1159315455","Syddanmark","0106000020E610000005000000010300000002000000050000001032926500E022402A36448DBB674B4020C92731F67C2240C284087AA0664B404007253951522140B607EF52BA724B401E898F2BE9592040AF0697C796E64B401032926500E022402A36448DBB674B4005000000E0A1C20EECF3204060BF248B37AC4B4040D41A6441ED204050A3CF35B6B64B40A0C7C40EACBF2040F8CB7AE04CBB4B4000FA1C6401CE2040B0107BE0A4B04B40E0A1C20EECF3204060BF248B37AC4B4001030000000100000005000000C01F1F64C1B5254088427AE09C854B40C0E070B9D6E9254018B979E0AC934B4040D41A6441632540985ECF351E5E4B40C01F1F64C133254008E8CF354E6B4B40C01F1F64C1B5254088427AE09C854B4001030000000100000005000000C0E070B9D6052440985ECF35DE7C4B4000FA1C6401212440D0FD79E0C4704B4040D41A6441E82340F880CF356A6E4B40C01F1F64C141234028D5CE356E874B40C0E070B9D6052440985ECF35DE7C4B400103000000010000000500000080AE186481E6244018B979E02C734B4080AE18648109254008E8CF350E6D4B40402C75B956C4244030B6D035566A4B4060C11964E1602440985ECF351E7D4B4080AE186481E6244018B979E02C734B4001030000000100000005000000A03220642155254088F7CE353A884B40C01F1F64C121244008E8CF35CE8B4B4040D41A64415A2340204FD03572BF4B40402C75B956412540D893D0358ACE4B40A03220642155254088F7CE353A884B40"
"t","1159311757","Barisal","0106000020E61000000F00000001030000000100000007000000BCF92DD7CE9C56406040F4C0A10C3740F8E3832CD8975640A0469F6BECC2364060D72DD742A7564010D09F6B9C5036402838832C3C9956406021F6C0C94936406422D981858A5640409EA06BE4CD35405C27AA6B997856407011C1F0B5B43640BCF92DD7CE9C56406040F4C0A10C3740010300000001000000050000002838832CFCA55640E06B48168FFC3540D0602ED7D2A65640F0019F6B54F035402838832C9CA2564030BD9E6B3CE03540181C2ED73AA056406021F6C0C9DC35402838832CFCA55640E06B48168FFC35400103000000010000000500000060D72DD742955640F0019F6B54F035402838832C3C995640E06B48160F00364088A52ED7AA945640A0FBF3C009D835405406842C64945640E06B48168FE4354060D72DD742955640F0019F6B54F035400103000000010000000500000098C1832CCC9856408059A06B4C0C36405406842C64985640800EF5C0E90536402838832CBC955640800EF5C0E9F83540E07C832CF4965640A0914A16CF05364098C1832CCC9856408059A06B4C0C364001030000000100000005000000181C2ED7DA9B5640C0339E6B8C0A36403C54D8811D9F5640E06B48160FFA3540F8E3832C989A5640A0FBF3C089E635402838832C3C995640F0019F6B54F03540181C2ED7DA9B5640C0339E6B8C0A364001030000000100000005000000F8E3832CD8A256406021F6C0C90F3640E07C832CF49E564010EF9D6BF408364060D72DD742A75640201B4B16FF293640E07C832C94A056403072F3C0D9FF3540F8E3832CD8A256406021F6C0C90F3640010300000001000000050000006422D98145BC5640F0E2A06B7C343640181C2ED73ABD5640B027A16B14253640181C2ED77ABB5640A0469F6BEC1D36406422D981E5BA5640D0AAF6C0F92836406422D98145BC5640F0E2A06B7C343640010300000001000000050000003C54D8817DBE5640E06B48168F2F3640845A832C88BE564030BD9E6B3C3D3640845A832CE8BE56403072F3C0594236402838832CDCBF564090A44B162F3C36403C54D8817DBE5640E06B48168F2F3640010300000001000000050000002838832CFCBC5640C0339E6B0C3A3640181C2ED7FABB5640B027A16B943B36403C54D8817DBE5640608B9F6B045D36405406842CA4BF5640F0019F6B544D36402838832CFCBC5640C0339E6B0C3A364001030000000100000005000000845A832CA8A7564030BD9E6BBC6C3640845A832C28AA5640C0E8F2C029613640F8E3832CF8A85640A0FBF3C0094F3640F8E3832CF8A85640608B9F6B84593640845A832CA8A7564030BD9E6BBC6C364001030000000100000004000000CC15832C70A9564030BD9E6BBC6C3640D0602ED7D2A95640A0659D6BC47B3640F498D881B5AA5640409EA06B64703640CC15832C70A9564030BD9E6BBC6C3640010300000001000000050000000CB8976998B25640006A1E25CE7E36405406842C84B5564010D09F6B9C2D3640CC15832C70A95640D0AAF6C079FE3540F8E3832C78A5564030084A169FC436400CB8976998B25640006A1E25CE7E364001030000000100000005000000F04059D54BA95640E0CBDA066BD9364060804EF257A1564010E8EA4036B6364088A52ED72A9D564010EF9D6B74C4364098C1832C4C9E5640D0AAF6C079DD3640F04059D54BA95640E0CBDA066BD9364001030000000100000005000000F498D881B5AA5640D0AAF6C0F9F33640F8E3832CB8A8564050AA9D6BDCEB36405406842C84A056408059A06BCCF936405406842C84A55640C07E4916EF083740F498D881B5AA5640D0AAF6C0F9F336400103000000010000000500000088A52ED7AAA05640606CA16BAC1137403C9F832C00A156408059A06B4C0D374098C1832C0C9E564010EF9D6BF401374088A52ED72A9D5640B027A16B1410374088A52ED7AAA05640606CA16BAC113740"