[PostGIS] #6095: add fixVertexSegmentPairsBelowDistanc
PostGIS
trac at osgeo.org
Fri Jul 10 03:17:11 PDT 2026
#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
Resolution: | Keywords:
--------------------------------+---------------------------
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 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 (by Lars Aksel Opsahl):
Did another test on bigger dataset t2.
We find a lot this FindVertexSegmentPairsBelowDistance we also two self
intersection in simple feature as we see.
{{{
SELECT 'FindVertexSegmentPairsBelowDistance before :' || count(*) FROM
topology.FindVertexSegmentPairsBelowDistance(
'grunnkart_037',
1e-7
);
SELECT 'invalid face before fixVertexSegmentPairsBelowDistance:'||
count(*)
FROM (
SELECT
ST_ISvalid(ST_GeomFromEWKT(ST_AsEWKT(topology.ST_GetFaceGeometry('grunnkart_037',face_id),8)))
isvalid
FROM grunnkart_037.face WHERE face_id > 0 ORDER by
ST_area(topology.ST_GetFaceGeometry('grunnkart_037',face_id),true)
) r WHERE not isvalid;
?column?
--------------------------------------------------
FindVertexSegmentPairsBelowDistance before :1037
(1 row)
NOTICE: 00000: Self-intersection at or near point 5.7028636310767338
58.933701939524774
LOCATION: pg_notice, lwgeom_pg.c:389
NOTICE: 00000: Self-intersection at or near point 5.7028636310767338
58.933701939524774
LOCATION: pg_notice, lwgeom_pg.c:389
?column?
----------------------------------------------------------
invalid face before fixVertexSegmentPairsBelowDistance:2
}}}
We run fix
{{{
DO $$
DECLARE
r RECORD;
BEGIN
FOR r IN
SELECT DISTINCT ON (e.edge_id)
e.geom,
vs.vert_geom
FROM
topology.FindVertexSegmentPairsBelowDistance(
'grunnkart_037',
1e-7
) AS vs
JOIN grunnkart_037.edge e
ON e.edge_id = vs.seg_edge
ORDER BY e.edge_id, vs.vert_geom
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;
$$;
NOTICE: 00000: Empty component of noded line
LOCATION: pg_notice, lwgeom_pg.c:389
NOTICE: 00000: Empty component of noded line
LOCATION: pg_notice, lwgeom_pg.c:389
NOTICE: 00000: Empty component of noded line
LOCATION: pg_notice, lwgeom_pg.c:389
NOTICE: 00000: Empty component of noded line
LOCATION: pg_notice, lwgeom_pg.c:389
NOTICE: 00000: Empty component of noded line
LOCATION: pg_notice, lwgeom_pg.c:389
NOTICE: 00000: Empty component of noded line
LOCATION: pg_notice, lwgeom_pg.c:389
DO
}}}
But now we seem to have more problems than before the fix
{{{
SELECT 'FindVertexSegmentPairsBelowDistance after:' || count(*) FROM
topology.FindVertexSegmentPairsBelowDistance(
'grunnkart_037',
1e-7
);
SELECT 'invalid face after fixVertexSegmentPairsBelowDistance:'||count(*)
FROM (
SELECT
ST_ISvalid(ST_GeomFromEWKT(ST_AsEWKT(topology.ST_GetFaceGeometry('grunnkart_037',face_id),8)))
isvalid
FROM grunnkart_037.face WHERE face_id > 0 ORDER by
ST_area(topology.ST_GetFaceGeometry('grunnkart_037',face_id),true)
) r WHERE not isvalid;
SELECT * from topology.ValidateTopology('grunnkart_037');
NOTICE: 00000: Too few points in geometry component at or near point
5.7221960200000002 58.942031450000002
LOCATION: pg_notice, lwgeom_pg.c:389
NOTICE: 00000: Too few points in geometry component at or near point
5.7085718600000002 58.93178168
LOCATION: pg_notice, lwgeom_pg.c:389
NOTICE: 00000: Too few points in geometry component at or near point
5.7239140600000002 58.925350950000002
LOCATION: pg_notice, lwgeom_pg.c:389
NOTICE: 00000: Too few points in geometry component at or near point
5.7239140600000002 58.925350940000001
LOCATION: pg_notice, lwgeom_pg.c:389
NOTICE: 00000: Too few points in geometry component at or near point
5.7239140900000001 58.925350950000002
LOCATION: pg_notice, lwgeom_pg.c:389
NOTICE: 00000: Too few points in geometry component at or near point
5.7238169699999997 58.928892070000003
LOCATION: pg_notice, lwgeom_pg.c:389
NOTICE: 00000: Too few points in geometry component at or near point
5.71480999 58.936201019999999
LOCATION: pg_notice, lwgeom_pg.c:389
NOTICE: 00000: Too few points in geometry component at or near point
5.7142168199999999 58.935326719999999
LOCATION: pg_notice, lwgeom_pg.c:389
NOTICE: 00000: Too few points in geometry component at or near point
5.7236422300000003 58.93658525
LOCATION: pg_notice, lwgeom_pg.c:389
NOTICE: 00000: Too few points in geometry component at or near point
5.7087765800000003 58.922366529999998
LOCATION: pg_notice, lwgeom_pg.c:389
NOTICE: 00000: Too few points in geometry component at or near point
5.7044360200000002 58.922831590000001
LOCATION: pg_notice, lwgeom_pg.c:389
NOTICE: 00000: Too few points in geometry component at or near point
5.7019316699999996 58.933653839999998
LOCATION: pg_notice, lwgeom_pg.c:389
NOTICE: 00000: Too few points in geometry component at or near point
5.7028636400000003 58.933701939999999
LOCATION: pg_notice, lwgeom_pg.c:389
NOTICE: 00000: Too few points in geometry component at or near point
5.7029897099999998 58.93366056
LOCATION: pg_notice, lwgeom_pg.c:389
NOTICE: 00000: Too few points in geometry component at or near point
5.7018358600000001 58.933649299999999
LOCATION: pg_notice, lwgeom_pg.c:389
NOTICE: 00000: Self-intersection at or near point 5.7019283657189828
58.932679461437964
LOCATION: pg_notice, lwgeom_pg.c:389
NOTICE: 00000: Too few points in geometry component at or near point
5.7014842100000003 58.932243669999998
LOCATION: pg_notice, lwgeom_pg.c:389
NOTICE: 00000: Too few points in geometry component at or near point
5.7014842100000003 58.932243669999998
LOCATION: pg_notice, lwgeom_pg.c:389
NOTICE: 00000: Too few points in geometry component at or near point
5.7013110999999999 58.932082100000002
LOCATION: pg_notice, lwgeom_pg.c:389
NOTICE: 00000: Too few points in geometry component at or near point
5.7012686600000002 58.932041769999998
LOCATION: pg_notice, lwgeom_pg.c:389
NOTICE: 00000: Too few points in geometry component at or near point
5.7012686600000002 58.93362501
LOCATION: pg_notice, lwgeom_pg.c:389
NOTICE: 00000: Self-intersection at or near point 5.7028636400000003
58.933701939999999
LOCATION: pg_notice, lwgeom_pg.c:389
NOTICE: 00000: Too few points in geometry component at or near point
5.7013111099999998 58.932082100000002
LOCATION: pg_notice, lwgeom_pg.c:389
NOTICE: 00000: Too few points in geometry component at or near point
5.7013111099999998 58.932082100000002
LOCATION: pg_notice, lwgeom_pg.c:389
NOTICE: 00000: Too few points in geometry component at or near point
5.7012860300000003 58.93205871
LOCATION: pg_notice, lwgeom_pg.c:389
NOTICE: 00000: Too few points in geometry component at or near point
5.7012859899999997 58.932058689999998
LOCATION: pg_notice, lwgeom_pg.c:389
NOTICE: 00000: Too few points in geometry component at or near point
5.70526351 58.936307399999997
LOCATION: pg_notice, lwgeom_pg.c:389
NOTICE: 00000: Too few points in geometry component at or near point
5.7133967700000001 58.934488729999998
LOCATION: pg_notice, lwgeom_pg.c:389
NOTICE: 00000: Too few points in geometry component at or near point
5.7029828299999998 58.933654189999999
LOCATION: pg_notice, lwgeom_pg.c:389
NOTICE: 00000: Too few points in geometry component at or near point
5.7029828299999998 58.933654189999999
LOCATION: pg_notice, lwgeom_pg.c:389
NOTICE: 00000: Ring Self-intersection at or near point 5.7029828299999998
58.933654189999999
LOCATION: pg_notice, lwgeom_pg.c:389
NOTICE: 00000: Too few points in geometry component at or near point
5.7027127999999996 58.933404099999997
LOCATION: pg_notice, lwgeom_pg.c:389
NOTICE: 00000: Self-intersection at or near point 5.70266684
58.933361529999999
LOCATION: pg_notice, lwgeom_pg.c:389
NOTICE: 00000: Too few points in geometry component at or near point
5.7026668300000001 58.933361529999999
LOCATION: pg_notice, lwgeom_pg.c:389
NOTICE: 00000: Too few points in geometry component at or near point
5.7027127999999996 58.933404099999997
LOCATION: pg_notice, lwgeom_pg.c:389
NOTICE: 00000: Self-intersection at or near point 5.7015160700000003
58.933634069999997
LOCATION: pg_notice, lwgeom_pg.c:389
?column?
----------------------------------------------------------
invalid face after fixVertexSegmentPairsBelowDistance:36
(1 row)
NOTICE: 00000: Checking for coincident nodes
LOCATION: exec_stmt_raise, pl_exec.c:3911
NOTICE: 00000: Checking for edges crossing nodes
LOCATION: exec_stmt_raise, pl_exec.c:3911
NOTICE: 00000: Checking for invalid or not-simple edges
LOCATION: exec_stmt_raise, pl_exec.c:3911
NOTICE: 00000: Checking for crossing edges
LOCATION: exec_stmt_raise, pl_exec.c:3911
NOTICE: 00000: Checking for edges start_node mismatch
LOCATION: exec_stmt_raise, pl_exec.c:3911
NOTICE: 00000: Checking for edges end_node mismatch
LOCATION: exec_stmt_raise, pl_exec.c:3911
NOTICE: 00000: Checking for faces without edges
LOCATION: exec_stmt_raise, pl_exec.c:3911
NOTICE: 00000: Checking edge linking
LOCATION: exec_stmt_raise, pl_exec.c:3911
NOTICE: 00000: Building edge rings
LOCATION: exec_stmt_raise, pl_exec.c:3911
NOTICE: 00000: Found 1609 rings, 1494 valid shells, 115 valid holes
LOCATION: exec_stmt_raise, pl_exec.c:3911
NOTICE: 00000: Constructing geometry of all faces
LOCATION: exec_stmt_raise, pl_exec.c:3911
NOTICE: 00000: Checking faces
LOCATION: exec_stmt_raise, pl_exec.c:3911
NOTICE: 00000: Checked 1495 faces
LOCATION: exec_stmt_raise, pl_exec.c:3911
NOTICE: 00000: Checking for holes coverage
LOCATION: exec_stmt_raise, pl_exec.c:3911
NOTICE: 00000: Finished checking for coverage of 115 holes
LOCATION: exec_stmt_raise, pl_exec.c:3911
NOTICE: 00000: Checking for node containing_face correctness
LOCATION: exec_stmt_raise, pl_exec.c:3911
error | id1 | id2
-------+-----+-----
(0 rows)
}}}
--
Ticket URL: <https://trac.osgeo.org/postgis/ticket/6095#comment:1>
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.
More information about the postgis-tickets
mailing list