pggeodb-preprod.nancy.inra.fr/db_onf_gde - db_onf_gde on pggeodb-preprod.nancy.inra.fr

Function: validatetopology

Schema

topology

Owner

postgres

Descriptions

args: topology_schema_name - Returns a set of validatetopology_returntype objects detailing issues with topology

Options

Definition

CREATE OR REPLACE FUNCTION topology.validatetopology (
 toponame varchar
)
RETURNS SETOF topology.validatetopology_returntype AS
$span$
DECLARE
 retrec topology.ValidateTopology_ReturnType;
 rec RECORD;
 rec2 RECORD;
 i integer;
 invalid_edges integer[];
 invalid_faces integer[];
 sql text;
BEGIN

 -- Check for coincident nodes
 FOR rec IN EXECUTE 'SELECT a.node_id as id1, b.node_id as id2 FROM '
   || quote_ident(toponame) || '.node a, '
   || quote_ident(toponame) || '.node b '
      'WHERE a.node_id < b.node_id '
      ' AND ST_DWithin(a.geom, b.geom, 0)' -- NOTE: see #1625 and #1789
 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
 FOR rec IN EXECUTE 'SELECT n.node_id as nid, e.edge_id as eid FROM '
   || quote_ident(toponame) || '.node n, '
   || quote_ident(toponame) || '.edge e '
      'WHERE e.start_node != n.node_id '
      'AND e.end_node != n.node_id '
      'AND ST_Within(n.geom, e.geom)'
 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
 FOR rec IN EXECUTE 'SELECT e.geom, e.edge_id as id1, e.left_face, e.right_face FROM '
   || quote_ident(toponame) || '.edge e 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;

   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
 sql := '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 '
   || quote_ident(toponame) || '.edge e1, '
   || quote_ident(toponame) || '.edge e2 '
      'WHERE e1.edge_id < e2.edge_id '
      ' AND e1.geom && e2.geom ';
 IF invalid_edges IS NOT NULL THEN
   sql := sql || ' AND NOT e1.edge_id = ANY ('
              || quote_literal(invalid_edges) || ')'
              || ' AND NOT e2.edge_id = ANY ('
              || quote_literal(invalid_edges) || ')';
 END IF;

 FOR rec IN EXECUTE sql
 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
 FOR rec IN EXECUTE 'SELECT e.edge_id as id1, n.node_id as id2 FROM '
   || quote_ident(toponame) || '.edge e, '
   || quote_ident(toponame) || '.node n '
      'WHERE e.start_node = n.node_id '
      'AND NOT ST_Equals(ST_StartPoint(e.geom), n.geom)'
 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
 FOR rec IN EXECUTE 'SELECT e.edge_id as id1, n.node_id as id2 FROM '
   || quote_ident(toponame) || '.edge e, '
   || quote_ident(toponame) || '.node n '
      'WHERE e.end_node = n.node_id '
      'AND NOT ST_Equals(ST_EndPoint(e.geom), n.geom)'
 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
 FOR rec IN EXECUTE 'SELECT face_id as id1 FROM '
   || quote_ident(toponame) || '.face '
   || 'WHERE face_id > 0 EXCEPT ( SELECT left_face FROM '
   || quote_ident(toponame) || '.edge '
   || ' UNION SELECT right_face FROM '
   || quote_ident(toponame) || '.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

 sql := 'CREATE TEMP TABLE face_check ON COMMIT DROP AS '
      'SELECT face_id, topology.ST_GetFaceGeometry('
   || quote_literal(toponame) || ', face_id) as geom, mbr FROM '
   || quote_ident(toponame) || '.face WHERE face_id > 0';
 IF invalid_faces IS NOT NULL THEN
   sql := sql || ' AND NOT face_id = ANY ('
              || quote_literal(invalid_faces) || ')';
 END IF;
 EXECUTE sql;

 -- Build a gist index on geom
 EXECUTE 'CREATE INDEX "face_check_gist" ON '
      'face_check USING gist (geom);';

 -- Build a btree index on id
 EXECUTE 'CREATE INDEX "face_check_bt" ON '
      'face_check (face_id);';

 -- Scan the table looking for NULL geometries
 FOR rec IN EXECUTE
   'SELECT f1.face_id FROM '
      'face_check f1 WHERE f1.geom IS NULL OR ST_IsEmpty(f1.geom)'
 LOOP
   -- Face missing !
   retrec.error := 'face has no rings';
   retrec.id1 := rec.face_id;
   retrec.id2 := NULL;
   RETURN NEXT retrec;
 END LOOP;


 -- Scan the table looking for overlap or containment
 -- TODO: also check for MBR consistency
 FOR rec IN EXECUTE
   'SELECT f1.geom, f1.face_id as id1, f2.face_id as id2, '
      ' ST_Relate(f1.geom, f2.geom) as im'
      ' FROM '
      'face_check f1, '
      'face_check f2 '
      'WHERE f1.face_id < f2.face_id'
      ' AND f1.geom && f2.geom'
 LOOP

   -- Face overlap
   IF ST_RelateMatch(rec.im, 'T*T***T**') THEN
   retrec.error = 'face overlaps face';
   retrec.id1 = rec.id1;
   retrec.id2 = rec.id2;
   RETURN NEXT retrec;
   END IF;

   -- Face 1 is within face 2
   IF ST_RelateMatch(rec.im, 'T*F**F***') THEN
   retrec.error = 'face within face';
   retrec.id1 = rec.id1;
   retrec.id2 = rec.id2;
   RETURN NEXT retrec;
   END IF;

   -- Face 1 contains face 2
   IF ST_RelateMatch(rec.im, 'T*****FF*') THEN
   retrec.error = 'face within face';
   retrec.id1 = rec.id2;
   retrec.id2 = rec.id1;
   RETURN NEXT retrec;
   END IF;

 END LOOP;


 DROP TABLE face_check;

 RETURN;
END
$span$
LANGUAGE 'plpgsql'
VOLATILE
RETURNS NULL ON NULL INPUT
SECURITY INVOKER
COST 100 ROWS 1000;

COMMENT ON FUNCTION topology.validatetopology(toponame varchar)
IS 'args: topology_schema_name - Returns a set of validatetopology_returntype objects detailing issues with topology';

This file was generated with SQL Manager for PostgreSQL (www.pgsqlmanager.com) at 07/12/2018 13:23