[PostGIS] #6095: add fixVertexSegmentPairsBelowDistance (was: add fixVertexSegmentPairsBelowDistanc)

[PostGIS]

#6095: add fixVertexSegmentPairsBelowDistance
--------------------------------+-----------------------------
  Reporter:  Lars Aksel Opsahl  |      Owner:  strk
      Type:  enhancement        |     Status:  new
  Priority:  medium             |  Milestone:  PostGIS Fund Me
 Component:  topology           |    Version:
Resolution:                     |   Keywords:
--------------------------------+-----------------------------
Changes (by strk):

 * milestone:  PostGIS 3.6.5 => PostGIS Fund Me
 * summary:  add fixVertexSegmentPairsBelowDistanc => add
     fixVertexSegmentPairsBelowDistance
 * version:  3.6.x =>


Old description:

> In the attached dump we this case
>

> {{{
> [local] lop at t1=# SELECT * FROM
> topology.FindVertexSegmentPairsBelowDistance('grunnkart_037',1e-15);
>  seg_edge | segno | vert_edge | vertno |                     vert_geom
> ----------+-------+-----------+--------+----------------------------------------------------
>         7 |    59 |        31 |      2 |
> 0101000020A2100000767C04140DD7164009093C79AE774D40
>
> }}}
>
> the problem here that when covert this topology to simple feature we get
> this error
>
> {{{
>
> SELECT
> ST_ISvalid(ST_GeomFromEWKT(ST_AsEWKT(topology.ST_GetFaceGeometry('grunnkart_037',20),8)));
> NOTICE:  00000: Self-intersection at or near point 5.7100108290768832
> 58.935012010161067
> LOCATION:  pg_notice, lwgeom_pg.c:389
>  st_isvalid
> ------------
>  f
> (1 row)
>
> }}}
>
> Then we run this code
>

> {{{
> DO $$
> DECLARE
>                 r RECORD;
> BEGIN
>                 FOR r IN
>                                 SELECT DISTINCT ON (vs.vert_geom,
> e.edge_id)
>                                                         e.geom,
>                                                         vs.vert_geom
>                                 FROM
> topology.FindVertexSegmentPairsBelowDistance(
> 'grunnkart_037',
>                                                                 1e-15
>                                                 ) AS vs
>                                 JOIN grunnkart_037.edge e
>                                         ON e.edge_id = vs.seg_edge
>                                 ORDER BY vs.vert_geom, e.edge_id
>                 LOOP
>                                 BEGIN
>                                                 PERFORM
> topology.TopoGeo_addLinestring( 'grunnkart_037', ST_ShortestLine(r.geom,
> r.vert_geom), -1 );
>                                 EXCEPTION
>                                                 WHEN OTHERS THEN
>                                                                 RAISE
> WARNING 'Failed: %, error: %',
> ST_AsText(r.vert_geom),
> SQLERRM;
>                                 END;
>                 END LOOP;
> END;
> $$;
>

> }}}
>
> and the face geom is valid
>

> {{{
> SELECT
> ST_ISvalid(ST_GeomFromEWKT(ST_AsEWKT(topology.ST_GetFaceGeometry('grunnkart_037',20),8)));
>  st_isvalid
> ------------
>  t
> (1 row)
>
> }}}
>
> tested on
>
> {{{
>
> POSTGIS="3.7.0dev 3.6.0rc2-473-g260127869" [EXTENSION] PGSQL="160"
> GEOS="3.14.1-CAPI-1.20.5" PROJ="9.8.1 NETWORK_ENABLED=OFF
> URL_ENDPOINT=https://cdn.proj.org
> USER_WRITABLE_DIRECTORY=/Users/lop/Library/Application Support/proj
> DATABASE_PATH=/opt/homebrew/Cellar/proj/9.8.1/share/proj/proj.db"
> (compiled against PROJ 9.8.1) LIBXML="2.9.13" LIBJSON="0.18"
> LIBPROTOBUF="1.5.2" WAGYU="0.5.0 (Internal)" TOPOLOGY
> }}}

New description:

 In the attached dump we have this case


 {{{
 [local] lop at t1=# SELECT * FROM
 topology.FindVertexSegmentPairsBelowDistance('grunnkart_037',1e-15);
  seg_edge | segno | vert_edge | vertno |                     vert_geom
 ----------+-------+-----------+--------+----------------------------------------------------
         7 |    59 |        31 |      2 |
 0101000020A2100000767C04140DD7164009093C79AE774D40

 }}}

 the problem here that when covert this topology to simple feature we get
 this error

 {{{

 SELECT
 ST_ISvalid(ST_GeomFromEWKT(ST_AsEWKT(topology.ST_GetFaceGeometry('grunnkart_037',20),8)));
 NOTICE:  00000: Self-intersection at or near point 5.7100108290768832
 58.935012010161067
 LOCATION:  pg_notice, lwgeom_pg.c:389
  st_isvalid
 ------------
  f
 (1 row)

 }}}

 Then we run this code


 {{{
 DO $$
 DECLARE
                 r RECORD;
 BEGIN
                 FOR r IN
                                 SELECT DISTINCT ON (vs.vert_geom,
 e.edge_id)
                                                         e.geom,
                                                         vs.vert_geom
                                 FROM
 topology.FindVertexSegmentPairsBelowDistance(
 'grunnkart_037',
                                                                 1e-15
                                                 ) AS vs
                                 JOIN grunnkart_037.edge e
                                         ON e.edge_id = vs.seg_edge
                                 ORDER BY vs.vert_geom, e.edge_id
                 LOOP
                                 BEGIN
                                                 PERFORM
 topology.TopoGeo_addLinestring( 'grunnkart_037', ST_ShortestLine(r.geom,
 r.vert_geom), -1 );
                                 EXCEPTION
                                                 WHEN OTHERS THEN
                                                                 RAISE
 WARNING 'Failed: %, error: %',
 ST_AsText(r.vert_geom),
 SQLERRM;
                                 END;
                 END LOOP;
 END;
 $$;


 }}}

 and the face geom is valid


 {{{
 SELECT
 ST_ISvalid(ST_GeomFromEWKT(ST_AsEWKT(topology.ST_GetFaceGeometry('grunnkart_037',20),8)));
  st_isvalid
 ------------
  t
 (1 row)

 }}}

 tested on

 {{{

 POSTGIS="3.7.0dev 3.6.0rc2-473-g260127869" [EXTENSION] PGSQL="160"
 GEOS="3.14.1-CAPI-1.20.5" PROJ="9.8.1 NETWORK_ENABLED=OFF
 URL_ENDPOINT=https://cdn.proj.org
 USER_WRITABLE_DIRECTORY=/Users/lop/Library/Application Support/proj
 DATABASE_PATH=/opt/homebrew/Cellar/proj/9.8.1/share/proj/proj.db"
 (compiled against PROJ 9.8.1) LIBXML="2.9.13" LIBJSON="0.18"
 LIBPROTOBUF="1.5.2" WAGYU="0.5.0 (Internal)" TOPOLOGY
 }}}

--
Comment:

 Any new function will have to wait for 3.8.0

 May I recommend to NOT use WKT in your excercise ? The loss of precision
 you get is the reason why your simple feature constructed from face
 geometry becomes invalid. Verify by just calling
 `ST_IsValid(topology.ST_GetFaceGeometry(...))`
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/6095#comment:2>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.