[postgis-tickets] [SCM] PostGIS branch main updated. 3.1.0rc1-259-g424ce73
git at osgeo.org
git at osgeo.org
Thu Jul 1 11:36:54 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 424ce7343f5165fa7cb1391ded0b3e376b9cbc15 (commit)
via 9b44f27f8393d723aed5e1f5db8defacd36f8659 (commit)
via e229449130861affa0952380bb9bc4de03056610 (commit)
from ba050a61b052dc3f559b6aace630dad191eabb27 (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 424ce7343f5165fa7cb1391ded0b3e376b9cbc15
Author: Sandro Santilli <strk at kbt.io>
Date: Thu Jul 1 18:36:19 2021 +0200
Add optional bbox parameter to ValidateTopology
Closes #4936
Includes test and documentation.
diff --git a/NEWS b/NEWS
index 592b97f..e87d347 100644
--- a/NEWS
+++ b/NEWS
@@ -6,6 +6,7 @@ PostGIS 3.2.0
- #4933, topology.GetFaceByPoint will not work with topologies having invalid edge linking.
* Enhancements *
+ - #4936, Bounding box limited ValidateTopology (Sandro Santilli)
- #4933, Speed up topology building in presence of big faces (Sandro Santilli)
- #3233, ValidateTopology check for node's containing_face (Sandro Santilli)
- #4830, ValidateTopology check for edges side face containment
diff --git a/doc/extras_topology.xml b/doc/extras_topology.xml
index c918e8b..952b5fb 100644
--- a/doc/extras_topology.xml
+++ b/doc/extras_topology.xml
@@ -558,7 +558,8 @@ FROM topology.layer;
<funcsynopsis>
<funcprototype>
<funcdef>setof validatetopology_returntype <function>ValidateTopology</function></funcdef>
- <paramdef><type>varchar </type> <parameter>topology_schema_name</parameter></paramdef>
+ <paramdef><type>varchar </type> <parameter>toponame</parameter></paramdef>
+ <paramdef choice="opt"><type>geometry</type> <parameter>bbox</parameter></paramdef>
</funcprototype>
</funcsynopsis>
</refsynopsisdiv>
@@ -566,7 +567,13 @@ FROM topology.layer;
<refsection>
<title>Description</title>
- <para>Returns a set of <xref linkend="validatetopology_returntype"/> objects detailing issues with topology. List of possible errors and what the returned ids represent are displayed below:</para>
+ <para>
+Returns a set of <xref linkend="validatetopology_returntype"/> objects
+detailing issues with topology, optionally limiting the check to the
+area specified by the <varname>bbox</varname> parameter.
+ </para>
+
+ <para>List of possible errors and what the returned ids represent are displayed below:</para>
<informaltable rowsep="1" frame="all">
<tgroup cols="3">
@@ -631,6 +638,7 @@ FROM topology.layer;
<!-- use this format if not a new function but functionality enhanced -->
<para>Enhanced: 2.0.0 more efficient edge crossing detection and fixes for false positives that were existent in prior versions.</para>
<para>Changed: 2.2.0 values for id1 and id2 were swapped for 'edge crosses node' to be consistent with error description.</para>
+ <para>Changed: 3.2.0 added optional bbox parameter.</para>
</refsection>
diff --git a/topology/test/invalid_topology.sql b/topology/test/invalid_topology.sql
index c38072c..1bcd260 100644
--- a/topology/test/invalid_topology.sql
+++ b/topology/test/invalid_topology.sql
@@ -150,7 +150,19 @@ INSERT INTO invalid_topology.edge VALUES(32, 4, 4, 31, -31, 12, 12,
INSERT INTO invalid_topology.edge VALUES(33, 3, 3, 28, 28, 13, 13,
'01020000000100000000000000000039400000000000804140');
--- Validate topology
-SELECT * from topology.validatetopology('invalid_topology');
+-- Set face mbr based on referencing edges mbr
+UPDATE invalid_topology.face f
+SET mbr = (
+ WITH env AS (
+ SELECT ST_Envelope(ST_Union(ST_Envelope(e.geom))) env
+ FROM invalid_topology.edge e
+ WHERE
+ ( e.left_face = f.face_id OR e.right_face = f.face_id )
+ )
+ SELECT ST_MakeEnvelope(st_xmin(env), st_ymin(env), st_xmax(env), st_ymax(env))
+ FROM env
+)
+WHERE face_id > 0;
END;
+
diff --git a/topology/test/regress/legacy_invalid.sql b/topology/test/regress/legacy_invalid.sql
index bb2f916..9afad61 100644
--- a/topology/test/regress/legacy_invalid.sql
+++ b/topology/test/regress/legacy_invalid.sql
@@ -2,5 +2,58 @@ set client_min_messages to WARNING;
\i ../invalid_topology.sql
+-- Validate full topology, store invalidities in a table
+CREATE TABLE invalid_topology.invalidities AS
+SELECT * from topology.validatetopology('invalid_topology');
+SELECT * FROM invalid_topology.invalidities;
+
+-- Test bbox-limited checking
+-- See https://trac.osgeo.org/postgis/ticket/4936
+CREATE TABLE invalid_topology.grid_invalidities AS
+WITH
+extents AS (
+ SELECT ST_Envelope(ST_Extent(geom)) env
+ FROM invalid_topology.edge
+ UNION
+ SELECT ST_Envelope(ST_Extent(geom))
+ FROM invalid_topology.node
+ UNION
+ SELECT ST_Envelope(ST_Extent(mbr))
+ FROM invalid_topology.face
+),
+topo_envelope AS (
+ SELECT ST_Envelope(ST_Union(env)) env
+ FROM extents
+),
+-- roughly a 8x5 grid on whole topology extent
+grid AS (
+ SELECT *
+ FROM ST_SquareGrid(
+ ST_Length(
+ ST_BoundingDiagonal(
+ ( SELECT env FROM topo_envelope )
+ )
+ )/8.0,
+ ( SELECT env FROM topo_envelope )
+ )
+)
+SELECT g.i, g.j, vt.*
+FROM grid g,
+LATERAL topology.validatetopology('invalid_topology', g.geom) vt ;
+
+-- Check that all errors found by the catch-all validator
+-- are also cought by the per-cell validator
+CREATE TABLE invalid_topology.missing_invalidities AS
+ SELECT error, id1, id2 FROM invalid_topology.invalidities
+ EXCEPT
+ SELECT error, id1, id2 FROM invalid_topology.grid_invalidities
+;
+
+SELECT '#4936', 'missing_count', count(*)
+FROM invalid_topology.missing_invalidities ;
+
+SELECT '#4936', 'missing', *
+FROM invalid_topology.missing_invalidities ;
+
-- clean up
SELECT topology.DropTopology('invalid_topology');
diff --git a/topology/test/regress/legacy_invalid_expected b/topology/test/regress/legacy_invalid_expected
index bc2cd65..ffcf2b0 100644
--- a/topology/test/regress/legacy_invalid_expected
+++ b/topology/test/regress/legacy_invalid_expected
@@ -22,4 +22,7 @@ not-isolated node has not-null containing_face|4|
edge not covered by both its side faces|27|
edge not covered by both its side faces|28|
edge not covered by both its side faces|30|
+#4936|missing_count|2
+#4936|missing|face has no rings|10|
+#4936|missing|face without edges|10|
Topology 'invalid_topology' dropped
diff --git a/topology/topology.sql.in b/topology/topology.sql.in
index fa672ba..fcff96b 100644
--- a/topology/topology.sql.in
+++ b/topology/topology.sql.in
@@ -1438,12 +1438,15 @@ LANGUAGE 'plpgsql' VOLATILE STRICT;
CREATE CAST (topology.TopoGeometry AS Geometry) WITH FUNCTION topology.Geometry(topology.TopoGeometry) AS IMPLICIT;
--{
--- ValidateTopology(toponame)
+-- ValidateTopology(toponame, [bbox])
--
-- Return a Set of ValidateTopology_ReturnType containing
-- informations on all topology inconsistencies
--
-CREATE OR REPLACE FUNCTION topology.ValidateTopology(toponame varchar)
+-- 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
$$
@@ -1489,6 +1492,14 @@ BEGIN
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;
@@ -1507,6 +1518,14 @@ BEGIN
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
@@ -1518,7 +1537,12 @@ BEGIN
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 ORDER BY edge_id
+ 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
@@ -1577,6 +1601,14 @@ BEGIN
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
@@ -1642,6 +1674,10 @@ BEGIN
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;
@@ -1659,6 +1695,10 @@ BEGIN
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;
@@ -1671,7 +1711,12 @@ BEGIN
FOR rec IN
SELECT face_id as id1
FROM face
- WHERE face_id > 0 EXCEPT (
+ WHERE face_id > 0
+ AND (
+ bbox IS NULL
+ OR mbr && bbox
+ )
+ EXCEPT (
SELECT left_face FROM edge
UNION
SELECT right_face FROM edge
@@ -1704,6 +1749,10 @@ BEGIN
TRUE
END
)
+ AND (
+ bbox IS NULL
+ OR mbr && bbox
+ )
;
-- Build a gist index on geom
@@ -1783,13 +1832,16 @@ BEGIN
ST_Equals(ST_EndPoint(e.geom), n.geom)
)
WHERE
- (
- 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 )
+ ( 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
@@ -1816,7 +1868,14 @@ BEGIN
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)
+ 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
)
@@ -1859,8 +1918,8 @@ BEGIN
RETURN;
END
$$
-LANGUAGE 'plpgsql' VOLATILE STRICT;
--- } ValidateTopology(toponame)
+LANGUAGE 'plpgsql' VOLATILE; -- NOTE: we need VOLATILE to use SHOW
+--} ValidateTopology(toponame, bbox)
--{
-- CreateTopology(name, SRID, precision, hasZ)
commit 9b44f27f8393d723aed5e1f5db8defacd36f8659
Author: Sandro Santilli <strk at kbt.io>
Date: Thu Jul 1 17:56:39 2021 +0200
ValidateTopology: error out if topology schema is unexistent
... and warn if it's not registered in topology.topology
diff --git a/topology/topology.sql.in b/topology/topology.sql.in
index 67da649..fa672ba 100644
--- a/topology/topology.sql.in
+++ b/topology/topology.sql.in
@@ -1457,6 +1457,24 @@ DECLARE
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;
commit e229449130861affa0952380bb9bc4de03056610
Author: Sandro Santilli <strk at kbt.io>
Date: Thu Jul 1 17:46:03 2021 +0200
Add DEBUG output in ValidateTopology
Since it's an expensive operation the messages can give an idea
of what's happening, during the operations
diff --git a/topology/topology.sql.in b/topology/topology.sql.in
index 8b83e38..67da649 100644
--- a/topology/topology.sql.in
+++ b/topology/topology.sql.in
@@ -1463,6 +1463,7 @@ BEGIN
-- 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
@@ -1479,6 +1480,7 @@ BEGIN
-- 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
@@ -1495,10 +1497,11 @@ BEGIN
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 ORDER BY edge_id
- LOOP
+ LOOP --{
-- Any invalid edge becomes a cancer for higher level complexes
IF NOT ST_IsValid(rec.geom) THEN
@@ -1532,9 +1535,10 @@ BEGIN
RETURN NEXT retrec;
END IF;
- END LOOP;
+ END LOOP; --}
-- Check for edge crossing
+ RAISE DEBUG 'Checking for crossing edges';
FOR rec IN
SELECT
e1.edge_id as id1,
@@ -1555,7 +1559,7 @@ BEGIN
NOT e2.edge_id = ANY (invalid_edges)
)
)
- LOOP
+ LOOP --{
IF ST_RelateMatch(rec.im, 'FF1F**1*2') THEN
CONTINUE; -- no interior intersection
@@ -1608,10 +1612,11 @@ BEGIN
retrec.id1 = rec.id1;
retrec.id2 = rec.id2;
RETURN NEXT retrec;
- END LOOP;
+ 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
@@ -1619,15 +1624,16 @@ BEGIN
node n
WHERE e.start_node = n.node_id
AND NOT ST_Equals(ST_StartPoint(e.geom), n.geom)
- LOOP
+ LOOP --{
retrec.error = 'edge start node geometry mis-match';
retrec.id1 = rec.id1;
retrec.id2 = rec.id2;
RETURN NEXT retrec;
- END LOOP;
+ 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
@@ -1635,14 +1641,15 @@ BEGIN
node n
WHERE e.end_node = n.node_id
AND NOT ST_Equals(ST_EndPoint(e.geom), n.geom)
- LOOP
+ LOOP --{
retrec.error = 'edge end node geometry mis-match';
retrec.id1 = rec.id1;
retrec.id2 = rec.id2;
RETURN NEXT retrec;
- END LOOP;
+ 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
@@ -1651,16 +1658,18 @@ BEGIN
UNION
SELECT right_face FROM edge
)
- LOOP
+ LOOP --{
retrec.error = 'face without edges';
retrec.id1 = rec.id1;
retrec.id2 = NULL;
RETURN NEXT retrec;
- END LOOP;
+ 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,
@@ -1686,19 +1695,21 @@ BEGIN
CREATE INDEX "face_check_bt" ON face_check (face_id);
-- Scan the table looking for NULL geometries
+ RAISE DEBUG 'Checking for faces with no rings';
FOR rec IN
SELECT face_id FROM face_check
WHERE geom IS NULL OR ST_IsEmpty(geom)
- LOOP
+ LOOP --{
-- Face missing !
retrec.error := 'face has no rings';
retrec.id1 := rec.face_id;
retrec.id2 := NULL;
RETURN NEXT retrec;
- END LOOP;
+ END LOOP; --}
-- Scan the table looking for overlap or containment
-- TODO: also check for MBR consistency
+ RAISE DEBUG 'Checking for face-in-face';
FOR rec IN
SELECT
f1.geom,
@@ -1710,7 +1721,7 @@ BEGIN
face_check f2
WHERE f1.face_id < f2.face_id
AND f1.geom && f2.geom
- LOOP
+ LOOP --{
-- Face overlap
IF ST_RelateMatch(rec.im, 'T*T***T**') THEN
@@ -1736,9 +1747,10 @@ BEGIN
RETURN NEXT retrec;
END IF;
- END LOOP;
+ END LOOP; --}
-- Check nodes have correct containing_face (#3233)
+ RAISE DEBUG 'Checking for node containing_face correctness';
FOR rec IN
SELECT
n.node_id,
@@ -1761,7 +1773,7 @@ BEGIN
)
OR ( n.containing_face IS NOT NULL AND e.edge_id IS NOT NULL )
GROUP BY n.node_id, n.containing_face
- LOOP
+ LOOP --{
IF rec.edge_id IS NOT NULL THEN
-- node is not really isolated
retrec.error := 'not-isolated node has not-null containing_face';
@@ -1771,9 +1783,10 @@ BEGIN
retrec.id1 := rec.node_id;
retrec.id2 := NULL; -- TODO: write expected containing_face here ?
RETURN NEXT retrec;
- END LOOP;
+ 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
@@ -1801,7 +1814,7 @@ BEGIN
OR
( right_face = 0 AND left_face = 0 AND covered_by[1] != 0 )
ORDER BY edge_id
- LOOP
+ 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
@@ -1819,7 +1832,7 @@ BEGIN
-- RETURN NEXT retrec;
-- END IF;
END IF;
- END LOOP;
+ END LOOP; --}
DROP TABLE face_check;
-----------------------------------------------------------------------
Summary of changes:
NEWS | 1 +
doc/extras_topology.xml | 12 +-
topology/test/invalid_topology.sql | 16 ++-
topology/test/regress/legacy_invalid.sql | 53 +++++++++
topology/test/regress/legacy_invalid_expected | 3 +
topology/topology.sql.in | 154 ++++++++++++++++++++------
6 files changed, 203 insertions(+), 36 deletions(-)
hooks/post-receive
--
PostGIS
More information about the postgis-tickets
mailing list