# HG changeset patch
# User Tom Gottfried <tom@intevation.de>
# Date 1551957600 -3600
# Node ID 73c8762cee603a3093408a0f744d40200a37dd53
# Parent  bb5286acfee2b1a06472da08159d22dd128c8edb
Stretch area generation: Throw errors instead of returning NULL

It is now an error if not both distance marks for the given stretch
can be found or no contiguous axis can be constructed between them.

diff -r bb5286acfee2 -r 73c8762cee60 schema/isrs_functions.sql
--- a/schema/isrs_functions.sql	Thu Mar 07 12:15:14 2019 +0100
+++ b/schema/isrs_functions.sql	Thu Mar 07 12:20:00 2019 +0100
@@ -21,6 +21,28 @@
     LANGUAGE sql
     STABLE PARALLEL SAFE;
 
+-- Return a multipoint with coordinates of stretch end points
+CREATE OR REPLACE FUNCTION ISRSrange_points(
+    stretch isrsrange
+) RETURNS geometry
+AS $$
+DECLARE result_geom geometry;
+BEGIN
+    SELECT geom
+        INTO STRICT result_geom
+        FROM (
+            SELECT ST_Collect(CAST(geom AS geometry)) AS geom
+                FROM waterway.distance_marks_virtual
+                WHERE location_code IN(lower(stretch), upper(stretch))) AS pts
+        -- Ensure both have been found
+        WHERE ST_NumGeometries(geom) = 2;
+
+    RETURN result_geom;
+END;
+    $$
+    LANGUAGE plpgsql
+    STABLE PARALLEL SAFE;
+
 -- Clip waterway axis to a stretch given by a pair of ISRS location codes.
 -- Uses the table waterway.distance_marks_virtual to map ISRS location codes
 -- to their geo-location.
@@ -31,12 +53,9 @@
     -- in m, up to which linestrings will be connected at their boundary
 ) RETURNS geometry
 AS $$
+DECLARE result_geom geometry;
+BEGIN
     WITH RECURSIVE
-        -- Get coordinates of location codes
-        points_geog AS (
-            SELECT geom FROM waterway.distance_marks_virtual
-                WHERE location_code = lower(stretch)
-                    OR location_code = upper(stretch)),
         utm_zone AS (
             -- Find best matchting UTM zone
             SELECT best_utm(stretch) AS z),
@@ -48,8 +67,8 @@
         points AS (
             SELECT ST_ClosestPoint(
                     wtwaxs,
-                    ST_Transform(points_geog.geom::geometry, z)) AS geom
-                FROM points_geog, utm_zone, (
+                    ST_Transform(geom, z)) AS geom
+                FROM ST_Dump(ISRSrange_points(stretch)), utm_zone, (
                     SELECT ST_Collect(wtwaxs) AS wtwaxs
                         FROM axis) AS ax),
         axis_snapped AS (
@@ -88,9 +107,11 @@
         axis_segment AS (
             -- Fetch end result from snapping
             SELECT wtwaxs AS line
-                FROM axis_snapped
-                WHERE array_length(ids, 1) = (
-                    SELECT max(array_length(ids, 1)) FROM axis_snapped))
+                FROM axis_snapped,
+                    (SELECT ST_Collect(points.geom) AS pts
+                        FROM points) AS points
+                -- Return end result only if both distance marks were connected
+                WHERE ST_Covers(ST_Buffer(wtwaxs, 0.0001), points.pts))
         -- Use linear referencing to clip axis between distance marks.
         -- Simplification is used to work-around the problem, that
         -- ST_LineSubstring might generate very small line segments at an
@@ -102,12 +123,16 @@
         SELECT ST_SimplifyPreserveTopology(ST_LineSubstring(
                     axis_segment.line, min(fractions.f), max(fractions.f)),
                 0.0001) AS line
+        INTO STRICT result_geom
         FROM axis_segment, LATERAL (
             SELECT ST_LineLocatePoint(axis_segment.line, points.geom) AS f
                 FROM points) AS fractions
-        GROUP BY axis_segment.line
+        GROUP BY axis_segment.line;
+
+    RETURN result_geom;
+END;
     $$
-    LANGUAGE sql
+    LANGUAGE plpgsql
     STABLE PARALLEL SAFE;
 
 -- Clip an area to a stretch given by a pair of ISRS location codes.
diff -r bb5286acfee2 -r 73c8762cee60 schema/isrs_tests.sql
--- a/schema/isrs_tests.sql	Thu Mar 07 12:15:14 2019 +0100
+++ b/schema/isrs_tests.sql	Thu Mar 07 12:20:00 2019 +0100
@@ -34,6 +34,23 @@
     ,
     'isrs_asText() is the inverse of isrs_fromText()');
 
+SELECT throws_ok($$
+    SELECT ISRSrange_points(isrsrange(
+            ('AT', 'XXX', '00001', '00000', 0)::isrs,
+            ('AT', 'XXX', '00001', '00000', 99999)::isrs))
+    $$,
+    'P0002', NULL,
+    'ISRSrange_points fails if not both distance marks can be found');
+
+SELECT throws_ok($$
+    SELECT ISRSrange_axis(isrsrange(
+            ('AT', 'XXX', '00001', '00000', 0)::isrs,
+            ('AT', 'XXX', '00001', '00000', 2)::isrs),
+        0)
+    $$,
+    'P0002', NULL,
+    'ISRSrange_axis fails if no contiguous axis can be constructed');
+
 SELECT ok(
     ISRSrange_area(isrsrange(
             ('AT', 'XXX', '00001', '00000', 0)::isrs,
diff -r bb5286acfee2 -r 73c8762cee60 schema/run_tests.sh
--- a/schema/run_tests.sh	Thu Mar 07 12:15:14 2019 +0100
+++ b/schema/run_tests.sh	Thu Mar 07 12:20:00 2019 +0100
@@ -28,7 +28,7 @@
     -c 'SET client_min_messages TO WARNING' \
     -c "DROP ROLE IF EXISTS $TEST_ROLES" \
     -f tap_tests_data.sql \
-    -c 'SELECT plan(58)' \
+    -c 'SELECT plan(60)' \
     -f isrs_tests.sql \
     -f auth_tests.sql \
     -f manage_users_tests.sql \