[postgis-tickets] [SCM] PostGIS branch main updated. 3.1.0rc1-273-g02ae48a
git at osgeo.org
git at osgeo.org
Tue Jul 6 08:24:23 PDT 2021
This is an automated email from the git hooks/post-receive script. It was
generated because a ref change was pushed to the repository containing
the project "PostGIS".
The branch, main has been updated
via 02ae48afb536bd302734fe8b342e312527f2d2f3 (commit)
from 4c84d496cd2d545582cdb9c005c734c720c8b58e (commit)
Those revisions listed above that are new to this repository have
not appeared on any other notification email; so we list those
revisions in full, below.
- Log -----------------------------------------------------------------
commit 02ae48afb536bd302734fe8b342e312527f2d2f3
Author: Sandro Santilli <strk at kbt.io>
Date: Tue Jul 6 17:22:20 2021 +0200
Put ValidateTopology code in its own file
diff --git a/topology/Makefile.in b/topology/Makefile.in
index 2d134ff..d6d5fea 100644
--- a/topology/Makefile.in
+++ b/topology/Makefile.in
@@ -125,6 +125,7 @@ topology.sql: \
sql/manage/FindLayer.sql.in \
sql/manage/FindTopology.sql.in \
sql/manage/ManageHelper.sql.in \
+ sql/manage/ValidateTopology.sql.in \
sql/topoelement/topoelement_agg.sql.in \
sql/topogeometry/type.sql.in \
sql/topogeometry/totopogeom.sql.in \
diff --git a/topology/sql/manage/ValidateTopology.sql.in b/topology/sql/manage/ValidateTopology.sql.in
new file mode 100644
index 0000000..5bc19cb
--- /dev/null
+++ b/topology/sql/manage/ValidateTopology.sql.in
@@ -0,0 +1,528 @@
+-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
+--
+--
+-- PostGIS - Spatial Types for PostgreSQL
+-- http://postgis.net
+--
+-- Copyright (C) 2010, 2021 Sandro Santilli <strk at kbt.io>
+-- Copyright (C) 2005 Refractions Research Inc.
+--
+-- This is free software; you can redistribute and/or modify it under
+-- the terms of the GNU General Public Licence. See the COPYING file.
+--
+-- Author: Sandro Santilli <strk at kbt.io>
+--
+-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
+
+--
+-- Type returned by ValidateTopology
+--
+CREATE TYPE topology.ValidateTopology_ReturnType AS (
+ error varchar,
+ id1 integer,
+ id2 integer
+);
+
+--{
+-- ValidateTopology(toponame, [bbox])
+--
+-- Return a Set of ValidateTopology_ReturnType containing
+-- informations on all topology inconsistencies
+--
+-- Availability: 1.0.0
+-- Changed: 3.2.0 - add bbox optional parameter
+--
+CREATE OR REPLACE FUNCTION topology.ValidateTopology(toponame varchar, bbox geometry DEFAULT NULL)
+ RETURNS setof topology.ValidateTopology_ReturnType
+AS
+$$
+DECLARE
+ retrec topology.ValidateTopology_ReturnType;
+ rec RECORD;
+ rec2 RECORD;
+ affected_rows integer;
+ invalid_edges integer[];
+ invalid_faces integer[];
+ search_path_backup text;
+BEGIN
+
+ IF NOT EXISTS (
+ SELECT oid
+ FROM pg_catalog.pg_namespace
+ WHERE nspname = toponame
+ )
+ THEN
+ RAISE EXCEPTION 'Topology schema % does not exist', toponame;
+ END IF;
+
+ IF NOT EXISTS (
+ SELECT id
+ FROM topology.topology
+ WHERE name = toponame
+ )
+ THEN
+ RAISE WARNING 'Topology % is not registered in topology.topology', toponame;
+ END IF;
+
+ EXECUTE 'SHOW search_path' INTO search_path_backup;
+ EXECUTE 'SET search_PATH TO ' || quote_ident(toponame) || ','
+ || search_path_backup;
+
+ IF bbox IS NOT NULL THEN
+ RAISE DEBUG 'Limiting topology checking to bbox %', ST_AsEWKT(ST_Envelope(bbox));
+ END IF;
+
+
+ -- Check for coincident nodes
+ RAISE DEBUG 'Checking for coincident nodes';
+ FOR rec IN
+ SELECT a.node_id as id1, b.node_id as id2
+ FROM
+ node a,
+ node b
+ WHERE a.node_id < b.node_id
+ AND ST_DWithin(a.geom, b.geom, 0) -- NOTE: see #1625 and #1789
+ AND (
+ bbox IS NULL
+ OR (
+ a.geom && bbox
+ AND
+ b.geom && bbox
+ )
+ )
+ LOOP
+ retrec.error = 'coincident nodes';
+ retrec.id1 = rec.id1;
+ retrec.id2 = rec.id2;
+ RETURN NEXT retrec;
+ END LOOP;
+
+ -- Check for edge crossed nodes
+ -- TODO: do this in the single edge loop
+ RAISE DEBUG 'Checking for edges crossing nodes';
+ FOR rec IN
+ SELECT n.node_id as nid, e.edge_id as eid
+ FROM
+ node n,
+ edge e
+ WHERE e.start_node != n.node_id
+ AND e.end_node != n.node_id
+ AND ST_Within(n.geom, e.geom)
+ AND (
+ bbox IS NULL
+ OR (
+ n.geom && bbox
+ AND
+ e.geom && bbox
+ )
+ )
+ LOOP
+ retrec.error = 'edge crosses node';
+ retrec.id1 = rec.eid; -- edge_id
+ retrec.id2 = rec.nid; -- node_id
+ RETURN NEXT retrec;
+ END LOOP;
+
+ -- Scan all edges
+ RAISE DEBUG 'Checking for invalid or not-simple edges';
+ FOR rec IN
+ SELECT e.geom, e.edge_id as id1, e.left_face, e.right_face
+ FROM edge e
+ WHERE (
+ bbox IS NULL
+ OR e.geom && bbox
+ )
+ ORDER BY edge_id
+ LOOP --{
+
+ -- Any invalid edge becomes a cancer for higher level complexes
+ IF NOT ST_IsValid(rec.geom) THEN
+
+ retrec.error = 'invalid edge';
+ retrec.id1 = rec.id1;
+ retrec.id2 = NULL;
+ RETURN NEXT retrec;
+ invalid_edges := array_append(invalid_edges, rec.id1);
+
+ IF invalid_faces IS NULL OR NOT rec.left_face = ANY ( invalid_faces )
+ THEN
+ invalid_faces := array_append(invalid_faces, rec.left_face);
+ END IF;
+
+ IF rec.right_face != rec.left_face AND ( invalid_faces IS NULL OR
+ NOT rec.right_face = ANY ( invalid_faces ) )
+ THEN
+ invalid_faces := array_append(invalid_faces, rec.right_face);
+ END IF;
+
+ CONTINUE;
+
+ END IF;
+
+ -- Check edge being simple (ie: not self-intersecting)
+ IF NOT ST_IsSimple(rec.geom) THEN
+ retrec.error = 'edge not simple';
+ retrec.id1 = rec.id1;
+ retrec.id2 = NULL;
+ RETURN NEXT retrec;
+ END IF;
+
+ END LOOP; --}
+
+ -- Check for edge crossing
+ RAISE DEBUG 'Checking for crossing edges';
+ FOR rec IN
+ SELECT
+ e1.edge_id as id1,
+ e2.edge_id as id2,
+ e1.geom as g1,
+ e2.geom as g2,
+ ST_Relate(e1.geom, e2.geom) as im
+ FROM
+ edge e1,
+ edge e2
+ WHERE
+ e1.edge_id < e2.edge_id
+ AND e1.geom && e2.geom
+ AND (
+ invalid_edges IS NULL OR (
+ NOT e1.edge_id = ANY (invalid_edges)
+ AND
+ NOT e2.edge_id = ANY (invalid_edges)
+ )
+ )
+ AND (
+ bbox IS NULL
+ OR (
+ e1.geom && bbox
+ AND
+ e2.geom && bbox
+ )
+ )
+ LOOP --{
+
+ IF ST_RelateMatch(rec.im, 'FF1F**1*2') THEN
+ CONTINUE; -- no interior intersection
+
+ --
+ -- Closed lines have no boundary, so endpoint
+ -- intersection would be considered interior
+ -- See http://trac.osgeo.org/postgis/ticket/770
+ -- See also full explanation in topology.AddEdge
+ --
+
+ ELSIF ST_RelateMatch(rec.im, 'FF10F01F2') THEN
+ -- first line (g1) is open, second (g2) is closed
+ -- first boundary has puntual intersection with second interior
+ --
+ -- compute intersection, check it equals second endpoint
+ IF ST_Equals(ST_Intersection(rec.g2, rec.g1),
+ ST_StartPoint(rec.g2))
+ THEN
+ CONTINUE;
+ END IF;
+
+ ELSIF ST_RelateMatch(rec.im, 'F01FFF102') THEN
+ -- second line (g2) is open, first (g1) is closed
+ -- second boundary has puntual intersection with first interior
+ --
+ -- compute intersection, check it equals first endpoint
+ IF ST_Equals(ST_Intersection(rec.g2, rec.g1),
+ ST_StartPoint(rec.g1))
+ THEN
+ CONTINUE;
+ END IF;
+
+ ELSIF ST_RelateMatch(rec.im, '0F1FFF1F2') THEN
+ -- both lines are closed (boundary intersects nothing)
+ -- they have puntual intersection between interiors
+ --
+ -- compute intersection, check it's a single point
+ -- and equals first StartPoint _and_ second StartPoint
+ IF ST_Equals(ST_Intersection(rec.g1, rec.g2),
+ ST_StartPoint(rec.g1)) AND
+ ST_Equals(ST_StartPoint(rec.g1), ST_StartPoint(rec.g2))
+ THEN
+ CONTINUE;
+ END IF;
+
+ END IF;
+
+ retrec.error = 'edge crosses edge';
+ retrec.id1 = rec.id1;
+ retrec.id2 = rec.id2;
+ RETURN NEXT retrec;
+ END LOOP; --}
+
+ -- Check for edge start_node geometry mis-match
+ -- TODO: move this in the first edge table scan
+ RAISE DEBUG 'Checking for edges start_node mismatch';
+ FOR rec IN
+ SELECT e.edge_id as id1, n.node_id as id2
+ FROM
+ edge e,
+ node n
+ WHERE e.start_node = n.node_id
+ AND NOT ST_Equals(ST_StartPoint(e.geom), n.geom)
+ AND (
+ bbox IS NULL
+ OR e.geom && bbox
+ )
+ LOOP --{
+ retrec.error = 'edge start node geometry mis-match';
+ retrec.id1 = rec.id1;
+ retrec.id2 = rec.id2;
+ RETURN NEXT retrec;
+ END LOOP; --}
+
+ -- Check for edge end_node geometry mis-match
+ -- TODO: move this in the first edge table scan
+ RAISE DEBUG 'Checking for edges end_node mismatch';
+ FOR rec IN
+ SELECT e.edge_id as id1, n.node_id as id2
+ FROM
+ edge e,
+ node n
+ WHERE e.end_node = n.node_id
+ AND NOT ST_Equals(ST_EndPoint(e.geom), n.geom)
+ AND (
+ bbox IS NULL
+ OR e.geom && bbox
+ )
+ LOOP --{
+ retrec.error = 'edge end node geometry mis-match';
+ retrec.id1 = rec.id1;
+ retrec.id2 = rec.id2;
+ RETURN NEXT retrec;
+ END LOOP; --}
+
+ -- Check for faces w/out edges
+ RAISE DEBUG 'Checking for faces without edges';
+ FOR rec IN
+ SELECT face_id as id1
+ FROM face
+ WHERE face_id > 0
+ AND (
+ bbox IS NULL
+ OR mbr && bbox
+ )
+ EXCEPT (
+ SELECT left_face FROM edge
+ UNION
+ SELECT right_face FROM edge
+ )
+ LOOP --{
+ retrec.error = 'face without edges';
+ retrec.id1 = rec.id1;
+ retrec.id2 = NULL;
+ RETURN NEXT retrec;
+ END LOOP; --}
+
+ -- Now create a temporary table to construct all face geometries
+ -- for checking their consistency
+
+ RAISE DEBUG 'Constructing geometry of all faces';
+
+ CREATE TEMP TABLE face_check ON COMMIT DROP AS
+ SELECT
+ face_id,
+ topology.ST_GetFaceGeometry(toponame, face_id) AS geom,
+ mbr
+ FROM
+ face
+ WHERE
+ face_id > 0
+ AND (
+ CASE WHEN invalid_faces IS NOT NULL THEN
+ NOT face_id = ANY(invalid_faces)
+ ELSE
+ TRUE
+ END
+ )
+ AND (
+ bbox IS NULL
+ OR mbr && bbox
+ )
+ ;
+
+ -- Build a gist index on geom
+ CREATE INDEX "face_check_gist" ON face_check USING gist (geom);
+
+ -- Build a btree index on id
+ CREATE INDEX "face_check_bt" ON face_check (face_id);
+
+ -- Scan the table looking for NULL geometries
+ -- or geometries with wrong MBR consistency
+ RAISE DEBUG 'Checking faces';
+ affected_rows := 0;
+ FOR rec IN
+ SELECT * FROM face_check
+ LOOP --{
+
+ affected_rows := affected_rows + 1;
+
+ IF rec.geom IS NULL OR ST_IsEmpty(rec.geom)
+ THEN
+ -- Face missing !
+ retrec.error := 'face has no rings';
+ retrec.id1 := rec.face_id;
+ retrec.id2 := NULL;
+ RETURN NEXT retrec;
+ END IF;
+
+ IF NOT ST_Equals(rec.mbr, ST_Envelope(rec.geom))
+ THEN
+ RAISE DEBUG 'MBR expected:% obtained:%', ST_AsEWKT(ST_Envelope(rec.geom)), ST_AsEWKT(ST_Envelope(rec.mbr));
+ -- Inconsistent MBR!
+ retrec.error := 'face has wrong mbr';
+ retrec.id1 := rec.face_id;
+ retrec.id2 := NULL;
+ RETURN NEXT retrec;
+ END IF;
+
+ FOR rec2 IN
+ SELECT
+ geom,
+ face_id,
+ ST_Relate(rec.geom, geom) as im
+ FROM
+ face_check
+ WHERE face_id > rec.face_id
+ AND geom && rec.geom
+ LOOP --{
+
+ -- Face overlap
+ IF ST_RelateMatch(rec2.im, 'T*T***T**') THEN
+ retrec.error = 'face overlaps face';
+ retrec.id1 = rec.face_id;
+ retrec.id2 = rec2.face_id;
+ RETURN NEXT retrec;
+ END IF;
+
+ -- Face 1 is within face 2
+ IF ST_RelateMatch(rec2.im, 'T*F**F***') THEN
+ retrec.error = 'face within face';
+ retrec.id1 = rec.face_id;
+ retrec.id2 = rec2.face_id;
+ RETURN NEXT retrec;
+ END IF;
+
+ -- Face 1 contains face 2
+ IF ST_RelateMatch(rec2.im, 'T*****FF*') THEN
+ retrec.error = 'face within face';
+ retrec.id1 = rec.face_id;
+ retrec.id2 = rec2.face_id;
+ RETURN NEXT retrec;
+ END IF;
+
+ END LOOP; --}
+
+ END LOOP; --}
+
+ RAISE DEBUG 'Checked % faces', affected_rows;
+
+ -- Check nodes have correct containing_face (#3233)
+ RAISE DEBUG 'Checking for node containing_face correctness';
+ FOR rec IN
+ SELECT
+ n.node_id,
+ -- in a corrupted topology multiple faces may contain the node
+ min(f.face_id) face_id,
+ -- multiple edges may contain the node
+ min(e.edge_id) edge_id
+ FROM node n
+ LEFT JOIN face_check f ON ( ST_Contains(f.geom, n.geom) )
+ LEFT JOIN edge e ON (
+ e.start_node = n.node_id OR
+ e.end_node = n.node_id
+ )
+ WHERE
+ ( bbox IS NULL OR n.geom && bbox )
+ AND (
+ (
+ e.edge_id IS NULL AND (
+ n.containing_face != f.face_id
+ OR ( n.containing_face IS NULL AND f.face_id IS NOT NULL )
+ )
+ )
+ OR ( n.containing_face IS NOT NULL AND e.edge_id IS NOT NULL)
+ )
+ GROUP BY n.node_id, n.containing_face
+ LOOP --{
+ IF rec.edge_id IS NOT NULL THEN
+ -- node is not really isolated
+ retrec.error := 'not-isolated node has not-null containing_face';
+ ELSE
+ retrec.error := 'isolated node has wrong containing_face';
+ END IF;
+ retrec.id1 := rec.node_id;
+ retrec.id2 := NULL; -- TODO: write expected containing_face here ?
+ RETURN NEXT retrec;
+ END LOOP; --}
+
+ -- Check edges are covered by their left-right faces (#4831)
+ RAISE DEBUG 'Checking for edges coverage';
+ FOR rec IN
+ WITH edge_coverage AS (
+ SELECT
+ e.edge_id,
+ e.left_face,
+ e.right_face,
+ array_agg(COALESCE(f.face_id, 0)) covered_by
+ FROM
+ edge_data e
+ LEFT JOIN face_check f ON ( ST_Covers(f.geom, e.geom) )
+ -- skip invalid edges (toxic, and will be already reported previously)
+ WHERE
+ (
+ invalid_edges IS NULL OR NOT e.edge_id = ANY(invalid_edges)
+ )
+ AND
+ (
+ bbox IS NULL OR e.geom && bbox
+ )
+ GROUP BY
+ e.edge_id, e.left_face, e.right_face
+ )
+ SELECT
+ *,
+ left_face = ANY(covered_by) left_face_covered,
+ right_face = ANY(covered_by) right_face_covered
+ from edge_coverage
+ WHERE
+ ( left_face != 0 AND NOT left_face = ANY(covered_by) )
+ OR
+ ( right_face != 0 AND NOT right_face = ANY(covered_by) )
+ OR
+ ( right_face = 0 AND left_face = 0 AND covered_by[1] != 0 )
+ ORDER BY edge_id
+ LOOP --{
+ retrec.id1 := rec.edge_id;
+ retrec.id2 := NULL; -- TODO: write expected containing_face here ?
+ IF rec.left_face = 0 AND rec.right_face = 0 THEN
+ retrec.error := 'edge covered by some face has universal face on both sides';
+ RETURN NEXT retrec;
+ ELSE
+ retrec.error := 'edge not covered by both its side faces';
+ RETURN NEXT retrec;
+-- IF rec.left_face != 0 AND NOT rec.left_face_covered THEN
+-- retrec.error := 'edge not covered by its left face';
+-- RETURN NEXT retrec;
+-- END IF;
+-- IF rec.right_face != 0 AND NOT rec.right_face_covered THEN
+-- retrec.error := 'edge not covered by its right face';
+-- RETURN NEXT retrec;
+-- END IF;
+ END IF;
+ END LOOP; --}
+
+ DROP TABLE face_check;
+
+ EXECUTE 'SET search_PATH TO ' || search_path_backup;
+
+ RETURN;
+END
+$$
+LANGUAGE 'plpgsql' VOLATILE; -- NOTE: we need VOLATILE to use SHOW
+--} ValidateTopology(toponame, bbox)
+
diff --git a/topology/topology.sql.in b/topology/topology.sql.in
index e427691..d23f1dc 100644
--- a/topology/topology.sql.in
+++ b/topology/topology.sql.in
@@ -320,15 +320,6 @@ ON topology.layer FOR EACH ROW EXECUTE PROCEDURE topology.LayerTrigger();
--} Layer table.
--
--- Type returned by ValidateTopology
---
-CREATE TYPE topology.ValidateTopology_ReturnType AS (
- error varchar,
- id1 integer,
- id2 integer
-);
-
---
-- TopoGeometry type
--
CREATE TYPE topology.TopoGeometry AS (
@@ -1438,509 +1429,6 @@ LANGUAGE 'plpgsql' VOLATILE STRICT;
CREATE CAST (topology.TopoGeometry AS Geometry) WITH FUNCTION topology.Geometry(topology.TopoGeometry) AS IMPLICIT;
--{
--- ValidateTopology(toponame, [bbox])
---
--- Return a Set of ValidateTopology_ReturnType containing
--- informations on all topology inconsistencies
---
--- Availability: 1.0.0
--- Changed: 3.2.0 - add bbox optional parameter
---
-CREATE OR REPLACE FUNCTION topology.ValidateTopology(toponame varchar, bbox geometry DEFAULT NULL)
- RETURNS setof topology.ValidateTopology_ReturnType
-AS
-$$
-DECLARE
- retrec topology.ValidateTopology_ReturnType;
- rec RECORD;
- rec2 RECORD;
- affected_rows integer;
- invalid_edges integer[];
- invalid_faces integer[];
- search_path_backup text;
-BEGIN
-
- IF NOT EXISTS (
- SELECT oid
- FROM pg_catalog.pg_namespace
- WHERE nspname = toponame
- )
- THEN
- RAISE EXCEPTION 'Topology schema % does not exist', toponame;
- END IF;
-
- IF NOT EXISTS (
- SELECT id
- FROM topology.topology
- WHERE name = toponame
- )
- THEN
- RAISE WARNING 'Topology % is not registered in topology.topology', toponame;
- END IF;
-
- EXECUTE 'SHOW search_path' INTO search_path_backup;
- EXECUTE 'SET search_PATH TO ' || quote_ident(toponame) || ','
- || search_path_backup;
-
- IF bbox IS NOT NULL THEN
- RAISE DEBUG 'Limiting topology checking to bbox %', ST_AsEWKT(ST_Envelope(bbox));
- END IF;
-
-
- -- Check for coincident nodes
- RAISE DEBUG 'Checking for coincident nodes';
- FOR rec IN
- SELECT a.node_id as id1, b.node_id as id2
- FROM
- node a,
- node b
- WHERE a.node_id < b.node_id
- AND ST_DWithin(a.geom, b.geom, 0) -- NOTE: see #1625 and #1789
- AND (
- bbox IS NULL
- OR (
- a.geom && bbox
- AND
- b.geom && bbox
- )
- )
- LOOP
- retrec.error = 'coincident nodes';
- retrec.id1 = rec.id1;
- retrec.id2 = rec.id2;
- RETURN NEXT retrec;
- END LOOP;
-
- -- Check for edge crossed nodes
- -- TODO: do this in the single edge loop
- RAISE DEBUG 'Checking for edges crossing nodes';
- FOR rec IN
- SELECT n.node_id as nid, e.edge_id as eid
- FROM
- node n,
- edge e
- WHERE e.start_node != n.node_id
- AND e.end_node != n.node_id
- AND ST_Within(n.geom, e.geom)
- AND (
- bbox IS NULL
- OR (
- n.geom && bbox
- AND
- e.geom && bbox
- )
- )
- LOOP
- retrec.error = 'edge crosses node';
- retrec.id1 = rec.eid; -- edge_id
- retrec.id2 = rec.nid; -- node_id
- RETURN NEXT retrec;
- END LOOP;
-
- -- Scan all edges
- RAISE DEBUG 'Checking for invalid or not-simple edges';
- FOR rec IN
- SELECT e.geom, e.edge_id as id1, e.left_face, e.right_face
- FROM edge e
- WHERE (
- bbox IS NULL
- OR e.geom && bbox
- )
- ORDER BY edge_id
- LOOP --{
-
- -- Any invalid edge becomes a cancer for higher level complexes
- IF NOT ST_IsValid(rec.geom) THEN
-
- retrec.error = 'invalid edge';
- retrec.id1 = rec.id1;
- retrec.id2 = NULL;
- RETURN NEXT retrec;
- invalid_edges := array_append(invalid_edges, rec.id1);
-
- IF invalid_faces IS NULL OR NOT rec.left_face = ANY ( invalid_faces )
- THEN
- invalid_faces := array_append(invalid_faces, rec.left_face);
- END IF;
-
- IF rec.right_face != rec.left_face AND ( invalid_faces IS NULL OR
- NOT rec.right_face = ANY ( invalid_faces ) )
- THEN
- invalid_faces := array_append(invalid_faces, rec.right_face);
- END IF;
-
- CONTINUE;
-
- END IF;
-
- -- Check edge being simple (ie: not self-intersecting)
- IF NOT ST_IsSimple(rec.geom) THEN
- retrec.error = 'edge not simple';
- retrec.id1 = rec.id1;
- retrec.id2 = NULL;
- RETURN NEXT retrec;
- END IF;
-
- END LOOP; --}
-
- -- Check for edge crossing
- RAISE DEBUG 'Checking for crossing edges';
- FOR rec IN
- SELECT
- e1.edge_id as id1,
- e2.edge_id as id2,
- e1.geom as g1,
- e2.geom as g2,
- ST_Relate(e1.geom, e2.geom) as im
- FROM
- edge e1,
- edge e2
- WHERE
- e1.edge_id < e2.edge_id
- AND e1.geom && e2.geom
- AND (
- invalid_edges IS NULL OR (
- NOT e1.edge_id = ANY (invalid_edges)
- AND
- NOT e2.edge_id = ANY (invalid_edges)
- )
- )
- AND (
- bbox IS NULL
- OR (
- e1.geom && bbox
- AND
- e2.geom && bbox
- )
- )
- LOOP --{
-
- IF ST_RelateMatch(rec.im, 'FF1F**1*2') THEN
- CONTINUE; -- no interior intersection
-
- --
- -- Closed lines have no boundary, so endpoint
- -- intersection would be considered interior
- -- See http://trac.osgeo.org/postgis/ticket/770
- -- See also full explanation in topology.AddEdge
- --
-
- ELSIF ST_RelateMatch(rec.im, 'FF10F01F2') THEN
- -- first line (g1) is open, second (g2) is closed
- -- first boundary has puntual intersection with second interior
- --
- -- compute intersection, check it equals second endpoint
- IF ST_Equals(ST_Intersection(rec.g2, rec.g1),
- ST_StartPoint(rec.g2))
- THEN
- CONTINUE;
- END IF;
-
- ELSIF ST_RelateMatch(rec.im, 'F01FFF102') THEN
- -- second line (g2) is open, first (g1) is closed
- -- second boundary has puntual intersection with first interior
- --
- -- compute intersection, check it equals first endpoint
- IF ST_Equals(ST_Intersection(rec.g2, rec.g1),
- ST_StartPoint(rec.g1))
- THEN
- CONTINUE;
- END IF;
-
- ELSIF ST_RelateMatch(rec.im, '0F1FFF1F2') THEN
- -- both lines are closed (boundary intersects nothing)
- -- they have puntual intersection between interiors
- --
- -- compute intersection, check it's a single point
- -- and equals first StartPoint _and_ second StartPoint
- IF ST_Equals(ST_Intersection(rec.g1, rec.g2),
- ST_StartPoint(rec.g1)) AND
- ST_Equals(ST_StartPoint(rec.g1), ST_StartPoint(rec.g2))
- THEN
- CONTINUE;
- END IF;
-
- END IF;
-
- retrec.error = 'edge crosses edge';
- retrec.id1 = rec.id1;
- retrec.id2 = rec.id2;
- RETURN NEXT retrec;
- END LOOP; --}
-
- -- Check for edge start_node geometry mis-match
- -- TODO: move this in the first edge table scan
- RAISE DEBUG 'Checking for edges start_node mismatch';
- FOR rec IN
- SELECT e.edge_id as id1, n.node_id as id2
- FROM
- edge e,
- node n
- WHERE e.start_node = n.node_id
- AND NOT ST_Equals(ST_StartPoint(e.geom), n.geom)
- AND (
- bbox IS NULL
- OR e.geom && bbox
- )
- LOOP --{
- retrec.error = 'edge start node geometry mis-match';
- retrec.id1 = rec.id1;
- retrec.id2 = rec.id2;
- RETURN NEXT retrec;
- END LOOP; --}
-
- -- Check for edge end_node geometry mis-match
- -- TODO: move this in the first edge table scan
- RAISE DEBUG 'Checking for edges end_node mismatch';
- FOR rec IN
- SELECT e.edge_id as id1, n.node_id as id2
- FROM
- edge e,
- node n
- WHERE e.end_node = n.node_id
- AND NOT ST_Equals(ST_EndPoint(e.geom), n.geom)
- AND (
- bbox IS NULL
- OR e.geom && bbox
- )
- LOOP --{
- retrec.error = 'edge end node geometry mis-match';
- retrec.id1 = rec.id1;
- retrec.id2 = rec.id2;
- RETURN NEXT retrec;
- END LOOP; --}
-
- -- Check for faces w/out edges
- RAISE DEBUG 'Checking for faces without edges';
- FOR rec IN
- SELECT face_id as id1
- FROM face
- WHERE face_id > 0
- AND (
- bbox IS NULL
- OR mbr && bbox
- )
- EXCEPT (
- SELECT left_face FROM edge
- UNION
- SELECT right_face FROM edge
- )
- LOOP --{
- retrec.error = 'face without edges';
- retrec.id1 = rec.id1;
- retrec.id2 = NULL;
- RETURN NEXT retrec;
- END LOOP; --}
-
- -- Now create a temporary table to construct all face geometries
- -- for checking their consistency
-
- RAISE DEBUG 'Constructing geometry of all faces';
-
- CREATE TEMP TABLE face_check ON COMMIT DROP AS
- SELECT
- face_id,
- topology.ST_GetFaceGeometry(toponame, face_id) AS geom,
- mbr
- FROM
- face
- WHERE
- face_id > 0
- AND (
- CASE WHEN invalid_faces IS NOT NULL THEN
- NOT face_id = ANY(invalid_faces)
- ELSE
- TRUE
- END
- )
- AND (
- bbox IS NULL
- OR mbr && bbox
- )
- ;
-
- -- Build a gist index on geom
- CREATE INDEX "face_check_gist" ON face_check USING gist (geom);
-
- -- Build a btree index on id
- CREATE INDEX "face_check_bt" ON face_check (face_id);
-
- -- Scan the table looking for NULL geometries
- -- or geometries with wrong MBR consistency
- RAISE DEBUG 'Checking faces';
- affected_rows := 0;
- FOR rec IN
- SELECT * FROM face_check
- LOOP --{
-
- affected_rows := affected_rows + 1;
-
- IF rec.geom IS NULL OR ST_IsEmpty(rec.geom)
- THEN
- -- Face missing !
- retrec.error := 'face has no rings';
- retrec.id1 := rec.face_id;
- retrec.id2 := NULL;
- RETURN NEXT retrec;
- END IF;
-
- IF NOT ST_Equals(rec.mbr, ST_Envelope(rec.geom))
- THEN
- RAISE DEBUG 'MBR expected:% obtained:%', ST_AsEWKT(ST_Envelope(rec.geom)), ST_AsEWKT(ST_Envelope(rec.mbr));
- -- Inconsistent MBR!
- retrec.error := 'face has wrong mbr';
- retrec.id1 := rec.face_id;
- retrec.id2 := NULL;
- RETURN NEXT retrec;
- END IF;
-
- FOR rec2 IN
- SELECT
- geom,
- face_id,
- ST_Relate(rec.geom, geom) as im
- FROM
- face_check
- WHERE face_id > rec.face_id
- AND geom && rec.geom
- LOOP --{
-
- -- Face overlap
- IF ST_RelateMatch(rec2.im, 'T*T***T**') THEN
- retrec.error = 'face overlaps face';
- retrec.id1 = rec.face_id;
- retrec.id2 = rec2.face_id;
- RETURN NEXT retrec;
- END IF;
-
- -- Face 1 is within face 2
- IF ST_RelateMatch(rec2.im, 'T*F**F***') THEN
- retrec.error = 'face within face';
- retrec.id1 = rec.face_id;
- retrec.id2 = rec2.face_id;
- RETURN NEXT retrec;
- END IF;
-
- -- Face 1 contains face 2
- IF ST_RelateMatch(rec2.im, 'T*****FF*') THEN
- retrec.error = 'face within face';
- retrec.id1 = rec.face_id;
- retrec.id2 = rec2.face_id;
- RETURN NEXT retrec;
- END IF;
-
- END LOOP; --}
-
- END LOOP; --}
-
- RAISE DEBUG 'Checked % faces', affected_rows;
-
- -- Check nodes have correct containing_face (#3233)
- RAISE DEBUG 'Checking for node containing_face correctness';
- FOR rec IN
- SELECT
- n.node_id,
- -- in a corrupted topology multiple faces may contain the node
- min(f.face_id) face_id,
- -- multiple edges may contain the node
- min(e.edge_id) edge_id
- FROM node n
- LEFT JOIN face_check f ON ( ST_Contains(f.geom, n.geom) )
- LEFT JOIN edge e ON (
- e.start_node = n.node_id OR
- e.end_node = n.node_id
- )
- WHERE
- ( bbox IS NULL OR n.geom && bbox )
- AND (
- (
- e.edge_id IS NULL AND (
- n.containing_face != f.face_id
- OR ( n.containing_face IS NULL AND f.face_id IS NOT NULL )
- )
- )
- OR ( n.containing_face IS NOT NULL AND e.edge_id IS NOT NULL)
- )
- GROUP BY n.node_id, n.containing_face
- LOOP --{
- IF rec.edge_id IS NOT NULL THEN
- -- node is not really isolated
- retrec.error := 'not-isolated node has not-null containing_face';
- ELSE
- retrec.error := 'isolated node has wrong containing_face';
- END IF;
- retrec.id1 := rec.node_id;
- retrec.id2 := NULL; -- TODO: write expected containing_face here ?
- RETURN NEXT retrec;
- END LOOP; --}
-
- -- Check edges are covered by their left-right faces (#4831)
- RAISE DEBUG 'Checking for edges coverage';
- FOR rec IN
- WITH edge_coverage AS (
- SELECT
- e.edge_id,
- e.left_face,
- e.right_face,
- array_agg(COALESCE(f.face_id, 0)) covered_by
- FROM
- edge_data e
- LEFT JOIN face_check f ON ( ST_Covers(f.geom, e.geom) )
- -- skip invalid edges (toxic, and will be already reported previously)
- WHERE
- (
- invalid_edges IS NULL OR NOT e.edge_id = ANY(invalid_edges)
- )
- AND
- (
- bbox IS NULL OR e.geom && bbox
- )
- GROUP BY
- e.edge_id, e.left_face, e.right_face
- )
- SELECT
- *,
- left_face = ANY(covered_by) left_face_covered,
- right_face = ANY(covered_by) right_face_covered
- from edge_coverage
- WHERE
- ( left_face != 0 AND NOT left_face = ANY(covered_by) )
- OR
- ( right_face != 0 AND NOT right_face = ANY(covered_by) )
- OR
- ( right_face = 0 AND left_face = 0 AND covered_by[1] != 0 )
- ORDER BY edge_id
- LOOP --{
- retrec.id1 := rec.edge_id;
- retrec.id2 := NULL; -- TODO: write expected containing_face here ?
- IF rec.left_face = 0 AND rec.right_face = 0 THEN
- retrec.error := 'edge covered by some face has universal face on both sides';
- RETURN NEXT retrec;
- ELSE
- retrec.error := 'edge not covered by both its side faces';
- RETURN NEXT retrec;
--- IF rec.left_face != 0 AND NOT rec.left_face_covered THEN
--- retrec.error := 'edge not covered by its left face';
--- RETURN NEXT retrec;
--- END IF;
--- IF rec.right_face != 0 AND NOT rec.right_face_covered THEN
--- retrec.error := 'edge not covered by its right face';
--- RETURN NEXT retrec;
--- END IF;
- END IF;
- END LOOP; --}
-
- DROP TABLE face_check;
-
- EXECUTE 'SET search_PATH TO ' || search_path_backup;
-
- RETURN;
-END
-$$
-LANGUAGE 'plpgsql' VOLATILE; -- NOTE: we need VOLATILE to use SHOW
---} ValidateTopology(toponame, bbox)
-
---{
-- CreateTopology(name, SRID, precision, hasZ)
--
-- Create a topology schema, add a topology info record
@@ -2323,6 +1811,7 @@ LANGUAGE 'plpgsql' VOLATILE STRICT;
#include "sql/manage/CopyTopology.sql.in"
#include "sql/manage/FindTopology.sql.in"
#include "sql/manage/FindLayer.sql.in"
+#include "sql/manage/ValidateTopology.sql.in"
CREATE OR REPLACE FUNCTION topology.postgis_topology_scripts_installed() RETURNS text
-----------------------------------------------------------------------
Summary of changes:
topology/Makefile.in | 1 +
topology/sql/manage/ValidateTopology.sql.in | 528 ++++++++++++++++++++++++++++
topology/topology.sql.in | 513 +--------------------------
3 files changed, 530 insertions(+), 512 deletions(-)
create mode 100644 topology/sql/manage/ValidateTopology.sql.in
hooks/post-receive
--
PostGIS
More information about the postgis-tickets
mailing list