[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgis-users
Subject:    Re: [postgis-users] St_makevalid issue
From:       Paul van der Linden <paul.doskabouter () gmail ! com>
Date:       2020-04-01 15:17:52
Message-ID: CAEC-EqD65UTnPhLPO1CNHXjvkzrKnsbFiLTzGfTxgFERdCMzJg () mail ! gmail ! com
[Download RAW message or body]

[Attachment #2 (multipart/alternative)]


Noticed that the message containing the query wasn't coupled to my latest
post, so here it is again:

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 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 ne_10m_admin_1_states_provinces_lakes
          ) natearth
     ) foo
   ) foo2
) as fff
WHERE NOT ST_isvalid(simplifiedgeo2)


P.S. when replying, could you also include my mail address?

[Attachment #5 (text/html)]

<div dir="ltr"><div style="font-size:small" class="gmail_default">Noticed that the \
message containing the query wasn&#39;t coupled to my latest post, so here it is \
again:<br></div><div style="font-size:small" class="gmail_default"><br></div><div \
style="font-size:small" class="gmail_default">SELECT ST_isvalid(simplifiedgeo2),* \
FROM (<br>SELECT   <br>      foo2.ne_id,<br>      <a \
href="http://foo2.name">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">foo.name</a>,<br>           \
foo.geom<br>        FROM (<br>            SELECT<br>               \
natearth.ne_id,<br>               natearth.geom,<br>               <a \
href="http://natearth.name">natearth.name</a>,<br>               CASE<br>             \
WHEN (<br>                        ST_xmax(ST_shiftlongitude(natearth.geom)::box3d) - \
ST_xmin(ST_shiftlongitude(natearth.geom)::box3d))<br>                        &lt;<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">ne_10m_admin_0_countries_lakes.name</a><br> \
FROM 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">ne_10m_admin_1_states_provinces_lakes.name</a><br> \
FROM ne_10m_admin_1_states_provinces_lakes<br>               ) natearth<br>        ) \
foo<br>     ) foo2<br>) as fff<br>WHERE NOT ST_isvalid(simplifiedgeo2)</div><div \
style="font-size:small" class="gmail_default"><br></div><div style="font-size:small" \
class="gmail_default"><br></div><div style="font-size:small" \
class="gmail_default">P.S. when replying, could you also include my mail \
address?<br></div></div>


[Attachment #6 (text/plain)]

_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

[prev in list] [next in list] [prev in thread] [next in thread]