Mercurial > gemma

diff schema/isrs_functions.sql @ 2514:79f4a20e31c2

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Introduce distance limit up to which axis linestrings are connected Before, e.g. a linestring representing a tributary with its mouth in the middle of another linestring, had been connected to the end of the latter.
author Tom Gottfried <tom@intevation.de>
date Tue, 05 Mar 2019 15:44:05 +0100
parents 54c9fe587fe6
children 73c8762cee60
line wrap: on
line diff
--- a/schema/isrs_functions.sql	Tue Mar 05 09:59:06 2019 +0100
+++ b/schema/isrs_functions.sql	Tue Mar 05 15:44:05 2019 +0100
@@ -25,7 +25,11 @@
 -- Uses the table waterway.distance_marks_virtual to map ISRS location codes
 -- to their geo-location.
 -- Distance marks are assumed to be near the axis.
-CREATE OR REPLACE FUNCTION ISRSrange_axis(stretch isrsrange) RETURNS geometry
+CREATE OR REPLACE FUNCTION ISRSrange_axis(
+    stretch isrsrange,
+    tolerance float
+    -- in m, up to which linestrings will be connected at their boundary
+) RETURNS geometry
 AS $$
     WITH RECURSIVE
         -- Get coordinates of location codes
@@ -76,6 +80,8 @@
                     (SELECT ST_Collect(points.geom) AS pts
                         FROM points) AS points
                 WHERE id <> ALL(refids)
+                    AND ST_DWithin(
+                        ST_Boundary(refgeom), ST_Boundary(geom), tolerance)
                     AND NOT ST_Covers(ST_Buffer(refgeom, 0.0001), points.pts)
                 ORDER BY ST_Distance(ST_Boundary(refgeom), ST_Boundary(geom))
                 FETCH FIRST ROW ONLY)),
@@ -118,7 +124,7 @@
 AS $$
     WITH
         axis_substring AS (
-            SELECT ISRSrange_axis(stretch) AS line),
+            SELECT ISRSrange_axis(stretch, 5) AS line),
         utm_zone AS (
             SELECT best_utm(stretch) AS z),
         area_subset AS (