[postgis-tickets] [SCM] PostGIS branch master updated. 3.3.0beta1-21-g2f915e1e9
git at osgeo.org
git at osgeo.org
Mon Jul 11 09:22:23 PDT 2022
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, master has been updated
via 2f915e1e9813789062d4d87c7a080986514815b7 (commit)
from 1db19735711247aa69b74e75add07d47d3ea35ab (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 2f915e1e9813789062d4d87c7a080986514815b7
Author: Sandro Santilli <strk at kbt.io>
Date: Fri Jul 8 13:04:50 2022 +0200
Implement topology.RemoveUnusedPrimitives
Closes #5183
Includes tests, docs and NEWS entry
diff --git a/.editorconfig b/.editorconfig
index 8c724db4d..e8c970e3e 100644
--- a/.editorconfig
+++ b/.editorconfig
@@ -55,6 +55,10 @@ indent_style = space
[topology/**.{sql,sql.in}]
indent_style = space
+# topology/clean SQL files want 2-space indentation
+[topology/sql/cleanup/**.{sql,sql.in}]
+indent_size = 2
+
# postgis/sfcgal.sql.in wants space indentation
[postgis/sfcgal.sql.in]
indent_style = space
diff --git a/NEWS b/NEWS
index b5f1a2174..0e04e26c0 100644
--- a/NEWS
+++ b/NEWS
@@ -12,6 +12,8 @@ Changes since PostGIS 3.3.0beta1
* Bug fixes *
- #5179, pgsql2shp syntax error on big-endian (Bas Couwenberg)
+* New features *
+ - #5183, topology.RemoveUnusedPrimitives (Sandro Santilli)
PostGIS 3.3.0beta1
2022/07/03
diff --git a/doc/extras_topology.xml b/doc/extras_topology.xml
index 3f7c3897c..dcbf4a425 100644
--- a/doc/extras_topology.xml
+++ b/doc/extras_topology.xml
@@ -3157,6 +3157,49 @@ faceid
</refsection>
</refentry>
+ <refentry id="TP_RemoveUnusedPrimitives">
+ <refnamediv>
+ <refname>RemoveUnusedPrimitives</refname>
+ <refpurpose>Removes topology primitives which not needed to define existing TopoGeometry objects.</refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+ <funcsynopsis>
+ <funcprototype>
+ <funcdef>int <function>RemoveUnusedPrimitives</function></funcdef>
+ <paramdef><type>text</type> <parameter>topology_name</parameter></paramdef>
+ <paramdef><type>geometry</type> <parameter>bbox</parameter></paramdef>
+ </funcprototype>
+ </funcsynopsis>
+ </refsynopsisdiv>
+
+ <refsection>
+ <title>Description</title>
+ <para>
+Finds all primitives (nodes, edges, faces) that are not
+strictly needed to represent existing TopoGeometry objects and removes
+them, maintaining topology validity (edge linking, face labeling)
+and TopoGeometry space occupation.
+ </para>
+
+ <para>
+No new primitive identifiers are created, but rather existing primitives
+are expanded to include merged faces (upon removing edges) or healed edges
+(upon removing nodes).
+ </para>
+
+ <para>Availability: 3.3.0</para>
+ </refsection>
+
+ <refsection>
+ <title>See Also</title>
+ <para>
+<xref linkend="ST_ModEdgeHeal" />,
+<xref linkend="ST_RemEdgeModFace" />
+ </para>
+ </refsection>
+ </refentry>
+
</sect1>
diff --git a/topology/Makefile.in b/topology/Makefile.in
index 041855dcb..9b53bf98f 100644
--- a/topology/Makefile.in
+++ b/topology/Makefile.in
@@ -115,6 +115,7 @@ topology_upgrade.sql: topology_before_upgrade.sql topology_upgrade.sql.in topolo
cat $^ > $@
topology.sql: \
+ sql/cleanup/RemoveUnusedPrimitives.sql.in \
sql/sqlmm.sql.in \
sql/populate.sql.in \
sql/polygonize.sql.in \
diff --git a/topology/sql/cleanup/RemoveUnusedPrimitives.sql.in b/topology/sql/cleanup/RemoveUnusedPrimitives.sql.in
new file mode 100644
index 000000000..fba82fbdc
--- /dev/null
+++ b/topology/sql/cleanup/RemoveUnusedPrimitives.sql.in
@@ -0,0 +1,674 @@
+-- This function finds primitives intersecting a bounding box
+-- that are not used in the definition of TopoGeometry objects
+-- and remove them all.
+--
+-- Returns number of deleted primitives
+--
+CREATE OR REPLACE FUNCTION topology.RemoveUnusedPrimitives(
+ atopology text,
+ bbox GEOMETRY DEFAULT NULL
+)
+RETURNS INT AS
+$BODY$
+DECLARE
+ topo topology.topology;
+ deletedNodes INT := 0;
+ deletedEdges INT := 0;
+ deletedNodesDeg2 INT := 0;
+ sql TEXT;
+ rec RECORD;
+ edgeMap JSONB := '{}';
+ edge1 INT;
+ edge2 INT;
+ removedNode INT;
+ ok BOOLEAN;
+ fixedLinks INT := 0;
+ mergedFaces INT[];
+ moreMergedFaces INT[];
+BEGIN
+
+ topo := findTopology(atopology);
+ IF topo.id IS NULL THEN
+ RAISE EXCEPTION 'Could not find topology "%"', atopology;
+ END IF;
+
+ RAISE NOTICE 'Removing unused edges';
+
+ RAISE DEBUG 'Determining edges not referenced by linear TopoGeoms';
+ -- Delete edges not used in non-hierarchical TopoGeometry
+ -- from linear typed layers
+ sql := format(
+ $$
+ CREATE TEMPORARY TABLE deleted_edges AS
+ SELECT
+ edge_id,
+ next_right_edge,
+ next_left_edge,
+ left_face,
+ right_face,
+ start_node,
+ end_node
+ FROM %1$I.edge_data e
+ WHERE ( $1 IS NULL OR ST_Intersects(geom, $1) )
+ AND NOT EXISTS (
+ SELECT 1
+ FROM %1$I.relation r, topology.layer l
+ WHERE r.layer_id = l.layer_id
+ AND l.topology_id = $2
+ AND l.child_id IS NULL
+ AND l.feature_type IN (2, 4)
+ AND r.element_id in ( e.edge_id, -e.edge_id )
+ )
+ $$,
+ topo.name
+ );
+ --RAISE DEBUG 'SQL: %', sql;
+ EXECUTE sql USING bbox, topo.id;
+ GET DIAGNOSTICS fixedLinks = ROW_COUNT;
+ RAISE DEBUG 'Found % edges not referenced by linear TopoGeoms', fixedLinks;
+
+ -- remove from deleted_edges the edges binding
+ -- faces that individually (not both) take part
+ -- of the definition of an areal TopoGeometry
+ RAISE DEBUG 'Determining edges not binding areal TopoGeoms';
+ sql := format(
+ $$
+ WITH breaking_merges AS (
+ SELECT
+ DISTINCT
+ de.edge_id
+ --, ARRAY[r.layer_id, r.topogeo_id] topogeo
+ --, array_agg(r.element_id) faces
+ FROM
+ topology.layer l,
+ %1$I.relation r,
+ pg_temp.deleted_edges de
+ WHERE l.topology_id = %2$L
+ AND l.child_id IS NULL -- non-hierarchical layer
+ AND l.feature_type IN (3, 4) -- areal or mixed layer
+ AND r.layer_id = l.layer_id
+ AND r.element_type = 3 -- face primitive
+ AND de.left_face != de.right_face -- non-dangling edges
+ AND ( r.element_id = de.left_face OR r.element_id = de.right_face )
+ GROUP BY de.edge_id, r.layer_id, r.topogeo_id
+ HAVING count(DISTINCT r.element_id) != 2
+ )
+ --SELECT * FROM breaking_merges
+ DELETE FROM pg_temp.deleted_edges de
+ WHERE edge_id IN (
+ SELECT edge_id FROM breaking_merges
+ )
+ $$,
+ topo.name,
+ topo.id
+ );
+ --RAISE DEBUG 'SQL: %', sql;
+ EXECUTE sql;
+ GET DIAGNOSTICS fixedLinks = ROW_COUNT;
+ RAISE DEBUG 'Retained % edges binding areal TopoGeoms', fixedLinks;
+ --FOR rec IN EXECUTE sql LOOP
+ --RAISE NOTICE 'Should retain edges % binding areal TopoGeom % in layer %', rec.edge_id, rec.topogeo_id, rec.layer_id;
+ --END LOOP;
+
+
+ RAISE DEBUG 'Deleting unused edges';
+ sql := format(
+ $$
+ DELETE FROM %1$I.edge_data e
+ WHERE e.edge_id IN (
+ SELECT edge_id FROM pg_temp.deleted_edges
+ )
+ $$,
+ topo.name
+ );
+ EXECUTE sql;
+ GET DIAGNOSTICS deletedEdges = ROW_COUNT;
+ RAISE DEBUG 'Deleted % unused edges', deletedEdges;
+
+
+ RAISE DEBUG 'Fixing broken next_right_edge links';
+ sql := format(
+ $$
+ UPDATE %1$I.edge_data e
+ SET
+ next_right_edge =
+ CASE
+ WHEN e.next_right_edge = ne.edge_id THEN
+ ne.next_right_edge
+ ELSE
+ ne.next_left_edge
+ END,
+ abs_next_right_edge =
+ CASE
+ WHEN e.next_right_edge = ne.edge_id THEN
+ abs(ne.next_right_edge)
+ ELSE
+ abs(ne.next_left_edge)
+ END
+ FROM pg_temp.deleted_edges ne
+ WHERE e.abs_next_right_edge = ne.edge_id
+ AND e.next_right_edge !=
+ CASE
+ WHEN e.next_right_edge = ne.edge_id THEN
+ ne.next_right_edge
+ ELSE
+ ne.next_left_edge
+ END
+ RETURNING e.*
+ $$,
+ topo.name
+ );
+ --RAISE DEBUG 'SQL: %', sql;
+ LOOP
+ EXECUTE sql;
+ GET DIAGNOSTICS fixedLinks = ROW_COUNT;
+-- fixedLinks := 0;
+-- FOR rec IN EXECUTE sql LOOP
+-- fixedLinks := fixedLinks + 1;
+-- RAISE DEBUG 'Updated next_right_edge link for edge %, now having next_right_edge=% and abs_next_right_edge=%', rec.edge_id, rec.next_right_edge, rec.abs_next_right_edge;
+-- END LOOP;
+ IF fixedLinks = 0 THEN
+ RAISE DEBUG 'No (more) broken next_right_edge links';
+ EXIT;
+ END IF;
+ RAISE DEBUG 'Updated % broken next_right_edge links', fixedLinks;
+ END LOOP;
+
+ RAISE DEBUG 'Fixing broken next_left_edge links';
+ sql := format(
+ $$
+ UPDATE %1$I.edge_data e
+ SET
+ next_left_edge =
+ CASE
+ WHEN e.next_left_edge = ne.edge_id THEN
+ ne.next_right_edge
+ ELSE
+ ne.next_left_edge
+ END,
+ abs_next_left_edge =
+ CASE
+ WHEN e.next_left_edge = ne.edge_id THEN
+ abs(ne.next_right_edge)
+ ELSE
+ abs(ne.next_left_edge)
+ END
+ FROM pg_temp.deleted_edges ne
+ WHERE e.abs_next_left_edge = ne.edge_id
+ -- Avoid updating records which do not need
+ -- to be updated (alternatively we could DELETE
+ -- those records from deleted_edges before next iteration)
+ AND e.next_left_edge !=
+ CASE
+ WHEN e.next_left_edge = ne.edge_id THEN
+ ne.next_right_edge
+ ELSE
+ ne.next_left_edge
+ END
+ RETURNING e.*
+ $$,
+ topo.name
+ );
+ --RAISE DEBUG 'SQL: %', sql;
+ LOOP
+ EXECUTE sql;
+ GET DIAGNOSTICS fixedLinks = ROW_COUNT;
+-- fixedLinks := 0;
+-- FOR rec IN EXECUTE sql LOOP
+-- fixedLinks := fixedLinks + 1;
+-- RAISE DEBUG 'Updated next_left_edge link for edge %, now having next_left_edge=% and abs_next_left_edge=%', rec.edge_id, rec.next_left_edge, rec.abs_next_left_edge;
+-- END LOOP;
+ IF fixedLinks = 0 THEN
+ RAISE DEBUG 'No (more) broken next_left_edge links found';
+ EXIT;
+ END IF;
+ RAISE DEBUG 'Updated % broken next_left_edge links', fixedLinks;
+ END LOOP;
+
+ --
+ -- Build arrays of faces to be merged
+ --
+
+ RAISE DEBUG 'Building face merge sets';
+
+ CREATE TEMPORARY TABLE mergeable_faces AS
+ WITH merges AS (
+ SELECT
+ DISTINCT
+ ARRAY[
+ LEAST(left_face, right_face),
+ GREATEST(left_face, right_face)
+ ] faceset
+ FROM deleted_edges
+ WHERE left_face != right_face
+ )
+ SELECT faceset
+ FROM merges;
+
+ CREATE TEMPORARY TABLE merged_faces (keep INT, merge INT[]);
+
+ LOOP -- {
+
+ -- Fetch next merge
+ DELETE FROM mergeable_faces
+ WHERE ctid = (SELECT ctid FROM mergeable_faces LIMIT 1)
+ RETURNING faceset
+ INTO mergedFaces;
+ IF mergedFaces IS NULL THEN
+ EXIT;
+ END IF;
+
+ RAISE DEBUG 'Next merged faces start with: %', mergedFaces;
+
+ LOOP --{
+ WITH deleted AS (
+ DELETE FROM mergeable_faces
+ WHERE faceset && mergedFaces
+ RETURNING faceset
+ ), flood_faces AS (
+ SELECT DISTINCT unnest(faceset) merged
+ FROM deleted
+ )
+ SELECT array_agg(merged)
+ FROM flood_faces
+ INTO moreMergedFaces;
+
+ IF moreMergedFaces IS NULL THEN
+ EXIT;
+ END IF;
+
+ RAISE DEBUG 'There is more merged faces: %', moreMergedFaces;
+ SELECT array_agg(x) FROM (
+ SELECT unnest(mergedFaces) x
+ UNION
+ SELECT unnest(moreMergedFaces)
+ ) foo
+ INTO mergedFaces;
+ RAISE DEBUG 'Merged faces grows to: %', mergedFaces;
+
+ END LOOP; --}
+
+ mergedFaces := array_agg(distinct x ORDER BY x) FROM unnest(mergedFaces) x;
+ RAISE DEBUG 'Storing merged faceset: %', mergedFaces;
+
+ INSERT INTO pg_temp.merged_faces VALUES (
+ mergedFaces[1],
+ array_remove(mergedFaces, mergedFaces[1])
+ );
+
+ END LOOP; --}
+
+ DROP TABLE pg_temp.mergeable_faces;
+
+ --
+ -- Fix face labels
+ --
+
+ RAISE DEBUG 'Fixing broken left_face labels';
+ sql := format(
+ $$
+ UPDATE %1$I.edge_data e
+ SET left_face = mf.keep
+ FROM pg_temp.merged_faces mf
+ WHERE e.left_face != mf.keep
+ AND e.left_face = ANY(mf.merge)
+ $$,
+ topo.name
+ );
+ --RAISE DEBUG 'SQL: %', sql;
+ EXECUTE sql;
+ GET DIAGNOSTICS fixedLinks = ROW_COUNT;
+ RAISE DEBUG 'Updated % broken left_face links', fixedLinks;
+
+ RAISE DEBUG 'Fixing broken right_face labels';
+ sql := format(
+ $$
+ UPDATE %1$I.edge_data e
+ SET right_face = mf.keep
+ FROM pg_temp.merged_faces mf
+ WHERE e.right_face != mf.keep
+ AND e.right_face = ANY(mf.merge)
+ $$,
+ topo.name
+ );
+ --RAISE DEBUG 'SQL: %', sql;
+ EXECUTE sql;
+ GET DIAGNOSTICS fixedLinks = ROW_COUNT;
+ RAISE DEBUG 'Updated % broken right_face links', fixedLinks;
+
+
+ RAISE DEBUG 'Updating containing_face labels for merged faces';
+ sql := format(
+ $$
+ UPDATE %1$I.node n
+ SET containing_face = mf.keep
+ FROM pg_temp.merged_faces mf
+ WHERE n.containing_face != mf.keep
+ AND n.containing_face = ANY(mf.merge)
+ $$,
+ topo.name
+ );
+ --RAISE DEBUG 'SQL: %', sql;
+ EXECUTE sql;
+ GET DIAGNOSTICS fixedLinks = ROW_COUNT;
+ RAISE DEBUG 'Updated % containing_face labels for nodes', fixedLinks;
+
+ --
+ -- Fix face table (delete/update mbr)
+ --
+
+ RAISE DEBUG 'Updating merged faces MBR';
+ sql := format(
+ $$
+ WITH merged_mbr AS (
+ SELECT
+ mf.keep,
+ ST_Envelope(
+ ST_Collect(
+ f.mbr
+ )
+ ) mbr
+ FROM pg_temp.merged_faces mf
+ JOIN %1$I.face f ON (
+ f.face_id = mf.keep OR
+ f.face_id = ANY( mf.merge )
+ )
+ WHERE mf.keep != 0
+ GROUP by mf.keep
+ )
+ UPDATE %1$I.face f
+ SET mbr = m.mbr
+ FROM merged_mbr m
+ WHERE f.face_id = m.keep
+ $$,
+ topo.name
+ );
+ EXECUTE sql;
+ GET DIAGNOSTICS fixedLinks = ROW_COUNT;
+ RAISE DEBUG 'Updated % merged faces MBR', fixedLinks;
+
+ RAISE DEBUG 'Deleting removed faces';
+ sql := format(
+ $$
+ DELETE FROM %1$I.face
+ USING pg_temp.merged_faces mf
+ WHERE face_id = ANY (mf.merge)
+ $$,
+ topo.name
+ );
+ EXECUTE sql;
+ GET DIAGNOSTICS fixedLinks = ROW_COUNT;
+ RAISE DEBUG 'Deleted % merged faces', fixedLinks;
+
+ --
+ -- Fix TopoGeometry
+ --
+
+ RAISE DEBUG 'Updating areal TopoGeometry definitions';
+ -- We remove the merged faces from the definition
+ -- of areal TopoGeometry objects
+ sql := format(
+ $$
+ WITH deleted AS (
+ DELETE FROM %1$I.relation r
+ USING topology.layer l, pg_temp.merged_faces mf
+ WHERE l.topology_id = %2$L
+ AND l.feature_type IN (3, 4)
+ AND l.child_id IS NULL
+ AND r.layer_id = l.layer_id
+ AND r.element_id = ANY (mf.merge)
+ RETURNING
+ r.topogeo_id,
+ r.layer_id,
+ l.schema_name,
+ l.table_name,
+ l.feature_column,
+ mf.merge,
+ mf.keep,
+ r.element_id
+ )
+ SELECT
+ topogeo_id,
+ layer_id,
+ schema_name,
+ table_name,
+ feature_column,
+ merge,
+ keep,
+ array_agg(element_id) lost_faces
+ FROM deleted
+ GROUP BY 1,2,3,4,5,6,7
+ $$,
+ topo.name,
+ topo.id
+ );
+ --RAISE NOTICE 'SQL: %', sql;
+ FOR rec IN EXECUTE sql
+ LOOP
+ RAISE DEBUG 'Areal TopoGeometry % in layer %.%.% '
+ 'lost faces % (kept %) in its composition',
+ rec.topogeo_id, rec.schema_name,
+ rec.table_name, rec.feature_column,
+ rec.lost_faces, rec.keep
+ ;
+ END LOOP;
+
+ --
+ -- Mark newly isolated nodes as such
+ --
+
+ RAISE DEBUG 'Determining newly isolated nodes';
+ sql := format(
+ $$
+ WITH unlinked_nodes AS (
+ SELECT start_node node_id FROM pg_temp.deleted_edges
+ UNION
+ SELECT end_node FROM pg_temp.deleted_edges
+ ), isolated AS (
+ SELECT node_id FROM unlinked_nodes
+ EXCEPT SELECT start_node FROM %1$I.edge_data
+ EXCEPT SELECT end_node FROM %1$I.edge_data
+ ), incident_faces AS (
+ SELECT
+ node_id,
+ array_agg(DISTINCT face_id) incident_faces
+ FROM (
+ SELECT DISTINCT node_id, unnest(face_id) face_id
+ FROM (
+ SELECT
+ i.node_id,
+ ARRAY[e.left_face, e.right_face] face_id
+ FROM isolated i, deleted_edges e
+ WHERE e.start_node = i.node_id
+ UNION
+ SELECT
+ i.node_id,
+ ARRAY[e.left_face, e.right_face] face_id
+ FROM isolated i, deleted_edges e
+ WHERE e.end_node = i.node_id
+ ) foo
+ ) bar
+ GROUP BY node_id
+ ), containing_faces AS (
+ SELECT
+ inc.node_id,
+ COALESCE(mf.keep, incident_faces[1]) face_id
+ FROM incident_faces inc
+ LEFT JOIN pg_temp.merged_faces mf
+ ON ( inc.incident_faces && mf.merge )
+ )
+ UPDATE %1$I.node n
+ SET containing_face = cf.face_id
+ FROM containing_faces cf
+ WHERE n.node_id = cf.node_id
+ AND n.containing_face IS DISTINCT FROM cf.face_id
+ $$,
+ topo.name
+ );
+ --RAISE DEBUG 'SQL: %', sql;
+ EXECUTE sql;
+ GET DIAGNOSTICS fixedLinks = ROW_COUNT;
+ RAISE DEBUG 'Isolated % nodes', fixedLinks;
+
+ RAISE NOTICE 'Removed % unused edges', deletedEdges;
+
+
+ --
+ -- Clean isolated nodes
+ --
+
+ -- Cleanup isolated nodes
+ -- (non-isolated ones would have become isolated by now)
+ sql := format(
+ $$
+ SELECT
+ n.node_id
+ FROM
+ %1$I.node n
+ WHERE ( $1 IS NULL OR ST_Intersects(n.geom, $1) )
+ AND n.containing_face IS NOT NULL
+ AND NOT EXISTS (
+ SELECT 1
+ FROM %1$I.relation r, topology.layer l
+ WHERE r.layer_id = l.layer_id
+ AND l.topology_id = $2
+ AND l.child_id IS NULL
+ AND l.feature_type = 1
+ AND r.element_id = n.node_id
+ )
+ $$,
+ topo.name
+ );
+ RAISE NOTICE 'Removing isolated nodes';
+ FOR rec in EXECUTE sql USING bbox, topo.id
+ LOOP --{
+ BEGIN
+ PERFORM topology.ST_RemIsoNode(topo.name, rec.node_id);
+ RAISE DEBUG 'Removed isolated node %', rec.node_id;
+ deletedNodes := deletedNodes + 1;
+ EXCEPTION WHEN OTHERS
+ THEN
+ RAISE WARNING 'Isolated node % could not be removed: %', rec.node_id, SQLERRM;
+ END;
+ END LOOP; --}
+ RAISE NOTICE 'Removed % isolated nodes', deletedNodes;
+
+ -- Remove nodes connecting only 2 edges if
+ -- no lineal TopoGeometry exists that is defined
+ -- by only one of them
+ sql := format(
+ $$
+ WITH
+ unused_connected_nodes_in_bbox AS (
+ SELECT
+ n.node_id
+ FROM %1$I.node n
+ WHERE
+ ( $1 IS NULL OR ST_Intersects(n.geom, $1) )
+ AND n.containing_face IS NULL
+
+ EXCEPT
+
+ SELECT r.element_id
+ FROM %1$I.relation r
+ JOIN topology.layer l ON ( r.layer_id = l.layer_id )
+ WHERE l.child_id IS NULL
+ AND l.topology_id = $2
+ AND l.feature_type IN ( 1, 4 ) -- puntual or mixed layer
+ AND r.element_type = 1 -- node primitive
+ ),
+ removable_nodes_of_degree_2_in_bbox AS (
+ SELECT
+ n.node_id,
+ array_agg(e.edge_id) edges
+ FROM
+ unused_connected_nodes_in_bbox n,
+ %1$I.edge e
+ WHERE (
+ n.node_id = e.start_node
+ OR n.node_id = e.end_node
+ )
+ GROUP BY n.node_id
+ HAVING count(e.edge_id) = 2
+ ),
+ breaking_heals AS (
+ SELECT
+ DISTINCT
+ n.node_id
+ -- , ARRAY[r.layer_id, r.topogeo_id]
+ -- , array_agg(r.element_id) edges
+ FROM
+ removable_nodes_of_degree_2_in_bbox n,
+ %1$I.relation r,
+ topology.layer l
+ WHERE l.topology_id = $2
+ AND l.child_id IS NULL
+ AND l.feature_type IN ( 2, 4 ) -- lineal or mixed layer
+ AND r.layer_id = l.layer_id
+ AND r.element_type = 2 -- edge primitive
+ AND r.element_id IN (
+ n.edges[1], -n.edges[1],
+ n.edges[2], -n.edges[2]
+ )
+ GROUP BY n.node_id, r.layer_id, r.topogeo_id
+ HAVING count(DISTINCT abs(r.element_id)) != 2
+ )
+ SELECT
+ node_id,
+ edges[1] edge1,
+ edges[2] edge2
+ FROM removable_nodes_of_degree_2_in_bbox
+ WHERE node_id NOT IN (
+ SELECT node_id FROM breaking_heals
+ )
+ $$,
+ topo.name
+ );
+ --RAISE DEBUG 'SQL: %', sql;
+ RAISE NOTICE 'Removing unneeded nodes of degree 2';
+ EXECUTE sql USING bbox, topo.id;
+ FOR rec in EXECUTE sql USING bbox, topo.id
+ LOOP --{
+ RAISE DEBUG 'edgeMap: %', edgeMap;
+ -- Edges may have changed name
+ edge1 := COALESCE( (edgeMap -> rec.edge1::text)::int, rec.edge1);
+ edge2 := COALESCE( (edgeMap -> rec.edge2::text)::int, rec.edge2);
+
+ RAISE DEBUG 'Should heal edges % (now %) and % (now %) bound by node %',
+ rec.edge1, edge1, rec.edge2, edge2, rec.node_id;
+
+ IF edge1 = edge2 THEN
+ -- Nothing to merge here, continue
+ CONTINUE;
+ END IF;
+ ok := false;
+
+ BEGIN
+ -- TODO: replace ST_ModEdgeHeal loop with a faster direct deletion and healing
+ removedNode := topology.ST_ModEdgeHeal(topo.name, edge1, edge2);
+ IF rec.node_id != removedNode THEN
+ RAISE EXCEPTION 'Healing of edges % and % was reported '
+ 'to remove node % while we expected % instead',
+ edge1, edge2, removedNode, rec.node_id;
+ END IF;
+ RAISE DEBUG 'Edge % merged into %, dropping node %', edge2, edge1, removedNode;
+ ok := 1;
+ EXCEPTION WHEN OTHERS
+ THEN
+ RAISE WARNING 'Edges % and % joined by node % could not be healed: %', edge1, edge2, rec.node_id, SQLERRM;
+ END;
+ IF ok THEN
+ -- edge2 was now renamed to edge1, update map
+ edgeMap := jsonb_set(edgeMap, ARRAY[edge2::text], to_jsonb(edge1));
+ deletedNodesDeg2 := deletedNodesDeg2 + 1;
+ END IF;
+ END LOOP; --}
+ RAISE NOTICE 'Removed % unneeded nodes of degree 2', deletedNodesDeg2;
+
+ DROP TABLE pg_temp.deleted_edges;
+ DROP TABLE pg_temp.merged_faces;
+
+ RETURN deletedEdges + deletedNodes + deletedNodesDeg2;
+END;
+$BODY$ LANGUAGE 'plpgsql' VOLATILE;
+
diff --git a/topology/test/regress/removeunusedprimitives.sql b/topology/test/regress/removeunusedprimitives.sql
new file mode 100644
index 000000000..cd966cd4d
--- /dev/null
+++ b/topology/test/regress/removeunusedprimitives.sql
@@ -0,0 +1,210 @@
+set client_min_messages to WARNING;
+
+\i :top_builddir/topology/test/load_topology.sql
+\i ../load_features.sql
+
+ALTER TABLE features.land_parcels ADD geom geometry;
+UPDATE features.land_parcels SET geom = feature::geometry;
+
+ALTER TABLE features.city_streets ADD geom geometry;
+UPDATE features.city_streets SET geom = feature::geometry;
+
+ALTER TABLE features.traffic_signs ADD geom geometry;
+UPDATE features.traffic_signs SET geom = feature::geometry;
+
+
+CREATE FUNCTION features.check_changed_features()
+RETURNS TABLE(typ text, nam text, exp text, obt text, symdiff text) AS
+$$
+
+ SELECT 'areal', feature_name,
+ ST_AsText(geom),
+ ST_AsText(feature::geometry),
+ ST_AsText(ST_SymDifference(feature::geometry, geom))
+ FROM features.land_parcels
+ WHERE NOT ST_Equals(feature::geometry, geom)
+
+ UNION ALL
+
+ SELECT 'lineal', feature_name,
+ ST_AsText(geom),
+ ST_AsText(feature::geometry),
+ ST_AsText(ST_SymDifference(feature::geometry, geom))
+ FROM features.city_streets
+ WHERE NOT ST_Equals(feature::geometry, geom)
+
+ UNION ALL
+
+ SELECT 'puntual', feature_name,
+ ST_AsText(geom),
+ ST_AsText(feature::geometry),
+ ST_AsText(ST_SymDifference(feature::geometry, geom))
+ FROM features.traffic_signs
+ WHERE NOT ST_Equals(feature::geometry, geom);
+
+$$ LANGUAGE 'sql';
+
+-- Error calls
+
+SELECT 'e1', topology.RemoveUnusedPrimitives('non-existent'::text);
+SELECT 'e2', topology.RemoveUnusedPrimitives(NULL::text);
+
+-- Valid calls
+
+-- T0 -- bbox limited
+
+--set client_min_messages to DEBUG;
+SELECT 't0', 'clean', topology.RemoveUnusedPrimitives('city_data',
+ ST_MakeEnvelope(8,5,10,23)
+);
+SELECT 't0', 'changed', * FROM features.check_changed_features();
+SELECT 't0', 'invalidity', * FROM topology.ValidateTopology('city_data');
+
+-- T1
+
+--set client_min_messages to DEBUG;
+SELECT 't1', 'clean', topology.RemoveUnusedPrimitives('city_data');
+SELECT 't1', 'changed', * FROM features.check_changed_features();
+SELECT 't1', 'invalidity', * FROM topology.ValidateTopology('city_data');
+
+
+-- T2
+
+WITH deleted AS (
+ DELETE FROM features.city_streets WHERE feature_name IN ( 'R1' )
+ RETURNING feature
+), clear AS (
+ SELECT clearTopoGeom(feature) FROM deleted
+) SELECT NULL FROM clear;
+
+--set client_min_messages to DEBUG;
+SELECT 't2', 'clean', topology.RemoveUnusedPrimitives('city_data');
+SELECT 't2', 'changed', * FROM features.check_changed_features();
+SELECT 't2', 'invalidity', * FROM topology.ValidateTopology('city_data');
+
+-- T3
+
+WITH deleted AS (
+ DELETE FROM features.traffic_signs WHERE feature_name IN ( 'S3' )
+ RETURNING feature
+), clear AS (
+ SELECT clearTopoGeom(feature) FROM deleted
+) SELECT NULL FROM clear;
+
+--set client_min_messages to DEBUG;
+SELECT 't3', 'clean', topology.RemoveUnusedPrimitives('city_data');
+SELECT 't3', 'changed', * FROM features.check_changed_features();
+SELECT 't3', 'invalidity', * FROM topology.ValidateTopology('city_data');
+
+-- T4 -- test removal of isolated node
+
+WITH deleted AS (
+ DELETE FROM features.traffic_signs WHERE feature_name IN ( 'S4' )
+ RETURNING feature
+), clear AS (
+ SELECT clearTopoGeom(feature) FROM deleted
+) SELECT NULL FROM clear;
+
+--set client_min_messages to DEBUG;
+SELECT 't4', 'clean', topology.RemoveUnusedPrimitives('city_data');
+SELECT 't4', 'changed', * FROM features.check_changed_features();
+SELECT 't4', 'invalidity', * FROM topology.ValidateTopology('city_data');
+
+-- T5 -- test removal of isolated edge leaving a used and an unused endpoint
+
+-- Drop lineal feature R3 turning isolated edge (25) unused
+-- Add a puntual feature TS1 using one (21) of the two unlinked
+-- nodes (21 and 22)
+WITH deleted AS (
+ DELETE FROM features.city_streets WHERE feature_name IN ( 'R3' )
+ RETURNING feature
+),
+start_point AS (
+ SELECT ST_StartPoint(feature::geometry) sp, feature
+ FROM deleted
+),
+clear AS (
+ SELECT sp, clearTopoGeom(feature)
+ FROM start_point
+)
+INSERT INTO features.traffic_signs ( feature_name, feature )
+SELECT
+ 'TS1',
+ toTopoGeom(
+ sp,
+ 'city_data',
+ layer_id(findLayer('features', 'traffic_signs', 'feature')),
+ 0
+ )
+FROM clear;
+UPDATE features.traffic_signs SET geom = feature::geometry
+WHERE feature_name = 'TS1';
+
+
+--set client_min_messages to DEBUG;
+SELECT 't5', 'clean', topology.RemoveUnusedPrimitives('city_data');
+SELECT 't5', 'changed', * FROM features.check_changed_features();
+SELECT 't5', 'invalidity', * FROM topology.ValidateTopology('city_data');
+
+-- T6 -- test removal in presence of node incident to 2 closed edges
+-- and 2 non-closed edges
+
+-- Add closed edge starting and ending on the node used by S1
+
+CREATE FUNCTION features.triangle(point geometry, offx float8, offy float8)
+RETURNS geometry AS $xx$
+ WITH point AS (
+ SELECT ST_X($1) x, ST_Y($1) y
+ )
+ SELECT ST_MakeLine(p order by id) l FROM (
+ SELECT
+ s,
+ ST_MakePoint(
+ x + CASE WHEN s > 1 AND s < 4 THEN $2 ELSE 0 END,
+ y + CASE WHEN s > 2 AND s < 4 THEN $3 ELSE 0 END
+ )
+ FROM
+ point,
+ generate_series(1,4) s
+ ) foo(id,p)
+$xx$ LANGUAGE 'sql';
+
+WITH node AS (
+ SELECT ST_GeometryN(feature::geometry, 1) g
+ FROM features.traffic_signs WHERE feature_name = 'S1'
+),
+triangle_right AS (
+ SELECT TopoGeo_addLineString( 'city_data',
+ features.triangle(g, 4, 4)
+ ) FROM node
+),
+triangle_left AS (
+ SELECT TopoGeo_addLineString( 'city_data',
+ features.triangle(g, -4, 4)
+ ) FROM node
+)
+SELECT NULL FROM triangle_right UNION
+SELECT NULL FROM triangle_left;
+
+
+-- Drop puntual feature S1 turning node 14 unused
+WITH deleted AS (
+ DELETE FROM features.traffic_signs
+ WHERE feature_name = 'S1'
+ RETURNING feature
+),
+clear AS (
+ SELECT clearTopoGeom(feature)
+ FROM deleted
+)
+SELECT NULL FROM clear;
+
+--set client_min_messages to DEBUG;
+SELECT 't6', 'clean', topology.RemoveUnusedPrimitives('city_data');
+SELECT 't6', 'changed', * FROM features.check_changed_features();
+SELECT 't6', 'invalidity', * FROM topology.ValidateTopology('city_data');
+
+-- Cleanup
+SELECT NULL FROM DropTopology('city_data');
+DROP SCHEMA features CASCADE;
+
diff --git a/topology/test/regress/removeunusedprimitives_expected b/topology/test/regress/removeunusedprimitives_expected
new file mode 100644
index 000000000..afcbe4895
--- /dev/null
+++ b/topology/test/regress/removeunusedprimitives_expected
@@ -0,0 +1,9 @@
+ERROR: Could not find topology "non-existent"
+ERROR: Could not find topology "<NULL>"
+t0|clean|2
+t1|clean|4
+t2|clean|3
+t3|clean|1
+t4|clean|1
+t5|clean|2
+t6|clean|3
diff --git a/topology/test/tests.mk b/topology/test/tests.mk
index 2fbb08541..af09adccd 100644
--- a/topology/test/tests.mk
+++ b/topology/test/tests.mk
@@ -46,6 +46,7 @@ TESTS += \
$(top_srcdir)/topology/test/regress/legacy_query.sql \
$(top_srcdir)/topology/test/regress/legacy_validate.sql \
$(top_srcdir)/topology/test/regress/polygonize.sql \
+ $(top_srcdir)/topology/test/regress/removeunusedprimitives.sql \
$(top_srcdir)/topology/test/regress/sqlmm.sql \
$(top_srcdir)/topology/test/regress/st_addedgemodface.sql \
$(top_srcdir)/topology/test/regress/st_addedgenewfaces.sql \
diff --git a/topology/topology.sql.in b/topology/topology.sql.in
index a73391687..27ae06595 100644
--- a/topology/topology.sql.in
+++ b/topology/topology.sql.in
@@ -1418,6 +1418,9 @@ LANGUAGE 'plpgsql' VOLATILE STRICT;
#include "sql/manage/ValidateTopology.sql.in"
#include "sql/manage/ValidateTopologyRelation.sql.in"
+-- Cleanup functions
+#include "sql/cleanup/RemoveUnusedPrimitives.sql.in"
+
CREATE OR REPLACE FUNCTION topology.postgis_topology_scripts_installed() RETURNS text
AS _POSTGIS_SQL_SELECT_POSTGIS_SCRIPTS_VERSION
-----------------------------------------------------------------------
Summary of changes:
.editorconfig | 4 +
NEWS | 2 +
doc/extras_topology.xml | 43 ++
topology/Makefile.in | 1 +
topology/sql/cleanup/RemoveUnusedPrimitives.sql.in | 674 +++++++++++++++++++++
topology/test/regress/removeunusedprimitives.sql | 210 +++++++
.../test/regress/removeunusedprimitives_expected | 9 +
topology/test/tests.mk | 1 +
topology/topology.sql.in | 3 +
9 files changed, 947 insertions(+)
create mode 100644 topology/sql/cleanup/RemoveUnusedPrimitives.sql.in
create mode 100644 topology/test/regress/removeunusedprimitives.sql
create mode 100644 topology/test/regress/removeunusedprimitives_expected
hooks/post-receive
--
PostGIS
More information about the postgis-tickets
mailing list