[PostGIS] #6095: add fixVertexSegmentPairsBelowDistanc

[PostGIS]

#6095: add fixVertexSegmentPairsBelowDistanc
-------------------------------+---------------------------
 Reporter:  Lars Aksel Opsahl  |      Owner:  strk
     Type:  enhancement        |     Status:  new
 Priority:  medium             |  Milestone:  PostGIS 3.6.5
Component:  topology           |    Version:  3.6.x
 Keywords:                     |
-------------------------------+---------------------------
 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
 }}}
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/6095>
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.