[postgis-tickets] [SCM] PostGIS branch master updated. 3.1.0alpha2-156-g52a6210

[git at osgeo.org]

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  52a62103c129e413a3384fa61f1e5f8c06493c23 (commit)
      from  a55328fe58bfec202f12cfa0e831dc3cf5b8e665 (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 52a62103c129e413a3384fa61f1e5f8c06493c23
Author: Sandro Santilli <strk at kbt.io>
Date:   Thu Nov 12 23:09:46 2020 +0100

    Use a temporary table to store edges bounding areal TopoGeometrys
    
    Greatly speeds up rendering polygons with many holes.
    
    References #4789 in master branch (3.1.0dev)

diff --git a/NEWS b/NEWS
index 4ef2903..5de2963 100644
--- a/NEWS
+++ b/NEWS
@@ -14,6 +14,8 @@ Only tickets not included in 3.1.0alpha2
 
 * Enhancements *
 
+  - #4789, Speed up TopoJSON output for areal TopoGeometry with
+           many holes (Sandro Santilli)
   - #4758, Improve topology noding robustness (Sandro Santilli)
   - Make ST_Subdivide interruptable (Sandro Santilli)
   - #4660, Changes in double / coordinate printing (Raúl Marín)
diff --git a/topology/sql/export/TopoJSON.sql.in b/topology/sql/export/TopoJSON.sql.in
index e8b9637..0418d75 100644
--- a/topology/sql/export/TopoJSON.sql.in
+++ b/topology/sql/export/TopoJSON.sql.in
@@ -3,7 +3,7 @@
 -- PostGIS - Spatial Types for PostgreSQL
 -- http://postgis.net
 --
--- Copyright (C) 2013 Sandro Santilli <strk at kbt.io>
+-- Copyright (C) 2013-2020 Sandro Santilli <strk at kbt.io>
 --
 -- This is free software; you can redistribute and/or modify it under
 -- the terms of the GNU General Public Licence. See the COPYING file.
@@ -33,7 +33,6 @@ DECLARE
   toponame text;
   json text;
   sql text;
-  bounds GEOMETRY;
   rec RECORD;
   rec2 RECORD;
   side int;
@@ -45,8 +44,6 @@ DECLARE
   old_search_path TEXT;
   all_faces int[];
   faces int[];
-  bounding_edges int[];
-  visited_face int;
   shell_faces int[];
   visited_edges int[];
   looking_for_holes BOOLEAN;
@@ -132,14 +129,29 @@ BEGIN
     looking_for_holes := false;
     shell_faces := ARRAY[]::int[];
 
-    SELECT array_agg(edge_id)
-    FROM edge_data e
+    CREATE TEMP TABLE _postgis_topology_astopojson_tmp_edges
+    ON COMMIT DROP
+    AS
+    SELECT
+         ROW_NUMBER() OVER (
+            ORDER BY
+              ST_XMin(e.geom),
+              ST_YMin(e.geom),
+              edge_id
+         ) leftmost_index,
+         e.edge_id,
+         e.left_face,
+         e.right_face,
+         e.next_right_edge,
+         e.next_left_edge
+    FROM edge e
     WHERE
-         ( e.left_face = ANY ( faces ) OR
-           e.right_face = ANY ( faces ) )
-    INTO bounding_edges;
+         ( e.left_face = ANY ( all_faces ) OR
+           e.right_face = ANY ( all_faces ) )
+    ;
+    CREATE INDEX on _postgis_topology_astopojson_tmp_edges (edge_id);
 
-    LOOP -- {
+    LOOP -- { until all edges were visited
 
       arcs := NULL;
       edges_found := false;
@@ -152,16 +164,19 @@ BEGIN
       FOR rec in -- {
 WITH RECURSIVE
 _edges AS (
-  SELECT e.*,
-         e.left_face = ANY ( faces ) as lf,
-         e.right_face = ANY ( faces ) as rf
-  FROM edge e
-  WHERE edge_id = ANY (bounding_edges)
-          AND NOT e.edge_id = ANY ( visited_edges )
+  SELECT
+     *,
+     left_face = ANY ( faces ) as lf,
+     right_face = ANY ( faces ) as rf
+  FROM
+    _postgis_topology_astopojson_tmp_edges
 ),
 _leftmost_non_dangling_edge AS (
-  SELECT e.* FROM _edges e WHERE e.lf != e.rf
-  ORDER BY ST_XMin(geom), ST_YMin(geom) LIMIT 1
+  SELECT e.edge_id
+    FROM _edges e WHERE e.lf != e.rf
+  ORDER BY
+    leftmost_index
+  LIMIT 1
 ),
 _edgepath AS (
   SELECT
@@ -206,7 +221,9 @@ _edgepath AS (
 SELECT abs(signed_edge_id) as edge_id, signed_edge_id, dangling,
         lf, rf, left_face, right_face
 FROM _edgepath
-      LOOP  -- }{
+      -- }
+
+      LOOP  -- { over recursive query
 
 #ifdef POSTGIS_TOPOLOGY_DEBUG
         RAISE DEBUG ' edge % lf:%(%) rf:%(%)' , rec.signed_edge_id, rec.lf, rec.left_face, rec.rf, rec.right_face;
@@ -256,13 +273,18 @@ FROM _edgepath
 
         arcs := arcid || arcs;
 
-      END LOOP; -- }
+      END LOOP; -- } over recursive query
+
+      DELETE from _postgis_topology_astopojson_tmp_edges
+      WHERE edge_id = ANY (visited_edges);
+      visited_edges := ARRAY[]::int[];
 
 #ifdef POSTGIS_TOPOLOGY_DEBUG
       --RAISE DEBUG 'Edges found:%, visited faces: %, ARCS: %' , edges_found, shell_faces, arcs;
 #endif
 
-      IF NOT edges_found THEN
+      IF NOT edges_found THEN -- {
+
         IF looking_for_holes THEN
           looking_for_holes := false;
 #ifdef POSTGIS_TOPOLOGY_DEBUG
@@ -275,7 +297,9 @@ FROM _edgepath
         ELSE
           EXIT; -- end of loop
         END IF;
-      ELSE
+
+      ELSE -- } edges found {
+
         faces := shell_faces;
         IF arcs IS NOT NULL THEN
 #ifdef POSTGIS_TOPOLOGY_DEBUG
@@ -284,10 +308,13 @@ FROM _edgepath
           ringtxt := ringtxt || ( '[' || array_to_string(arcs,',') || ']' );
         END IF;
         looking_for_holes := true;
-      END IF;
+
+      END IF; -- }
 
     END LOOP; -- }
 
+    DROP TABLE _postgis_topology_astopojson_tmp_edges;
+
     json := json || array_to_string(comptxt, ',') || ']}';
 
     EXECUTE 'SET search_path TO ' || old_search_path;

-----------------------------------------------------------------------

Summary of changes:
 NEWS                                |  2 +
 topology/sql/export/TopoJSON.sql.in | 73 +++++++++++++++++++++++++------------
 2 files changed, 52 insertions(+), 23 deletions(-)


hooks/post-receive
-- 
PostGIS