Mercurial > gemma

annotate schema/isrs_functions.sql @ 5666:37c2354a6024 clickable-links

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Render links only to known bottlenecks
author Thomas Junk <thomas.junk@intevation.de>
date Tue, 05 Dec 2023 15:34:31 +0100
parents ce9a9a1bf92f
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
1720
29a0fe218af9 Add missing license header
Tom Gottfried <tom@intevation.de>
parents: 1717
diff changeset
1 -- This is Free Software under GNU Affero General Public License v >= 3.0
29a0fe218af9 Add missing license header
Tom Gottfried <tom@intevation.de>
parents: 1717
diff changeset
2 -- without warranty, see README.md and license for details.
29a0fe218af9 Add missing license header
Tom Gottfried <tom@intevation.de>
parents: 1717
diff changeset
3
29a0fe218af9 Add missing license header
Tom Gottfried <tom@intevation.de>
parents: 1717
diff changeset
4 -- SPDX-License-Identifier: AGPL-3.0-or-later
29a0fe218af9 Add missing license header
Tom Gottfried <tom@intevation.de>
parents: 1717
diff changeset
5 -- License-Filename: LICENSES/AGPL-3.0.txt
29a0fe218af9 Add missing license header
Tom Gottfried <tom@intevation.de>
parents: 1717
diff changeset
6
2373
4aabbf324e55 Fix cutting of axis segment between two distance marks
Tom Gottfried <tom@intevation.de>
parents: 2232
diff changeset
7 -- Copyright (C) 2018, 2019 by via donau
1720
29a0fe218af9 Add missing license header
Tom Gottfried <tom@intevation.de>
parents: 1717
diff changeset
8 -- – Österreichische Wasserstraßen-Gesellschaft mbH
29a0fe218af9 Add missing license header
Tom Gottfried <tom@intevation.de>
parents: 1717
diff changeset
9 -- Software engineering by Intevation GmbH
29a0fe218af9 Add missing license header
Tom Gottfried <tom@intevation.de>
parents: 1717
diff changeset
10
29a0fe218af9 Add missing license header
Tom Gottfried <tom@intevation.de>
parents: 1717
diff changeset
11 -- Author(s):
29a0fe218af9 Add missing license header
Tom Gottfried <tom@intevation.de>
parents: 1717
diff changeset
12 -- * Tom Gottfried <tom@intevation.de>
29a0fe218af9 Add missing license header
Tom Gottfried <tom@intevation.de>
parents: 1717
diff changeset
13 -- * Sascha Wilde <wilde@intevation.de>
29a0fe218af9 Add missing license header
Tom Gottfried <tom@intevation.de>
parents: 1717
diff changeset
14
2455
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
15 CREATE OR REPLACE FUNCTION best_utm(stretch isrsrange) RETURNS integer
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
16 AS $$
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
17 SELECT best_utm(ST_Collect(geom::geometry))
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
18 FROM waterway.distance_marks_virtual
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
19 WHERE location_code IN (lower(stretch), upper(stretch))
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
20 $$
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
21 LANGUAGE sql
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
22 STABLE PARALLEL SAFE;
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
23
2535
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
24 -- Return a multipoint with coordinates of stretch end points
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
25 CREATE OR REPLACE FUNCTION ISRSrange_points(
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
26 stretch isrsrange
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
27 ) RETURNS geometry
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
28 AS $$
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
29 DECLARE result_geom geometry;
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
30 BEGIN
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
31 SELECT geom
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
32 INTO STRICT result_geom
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
33 FROM (
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
34 SELECT ST_Collect(CAST(geom AS geometry)) AS geom
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
35 FROM waterway.distance_marks_virtual
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
36 WHERE location_code IN(lower(stretch), upper(stretch))) AS pts
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
37 -- Ensure both have been found
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
38 WHERE ST_NumGeometries(geom) = 2;
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
39
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
40 RETURN result_geom;
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
41 END;
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
42 $$
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
43 LANGUAGE plpgsql
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
44 STABLE PARALLEL SAFE;
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
45
2455
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
46 -- Clip waterway axis to a stretch given by a pair of ISRS location codes.
1629
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
47 -- Uses the table waterway.distance_marks_virtual to map ISRS location codes
2455
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
48 -- to their geo-location.
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
49 -- Distance marks are assumed to be near the axis.
2563
dc4fae4bdb8f Expose axis snapping tolerance to users
Tom Gottfried <tom@intevation.de>
parents: 2535
diff changeset
50 -- Returns the axis geometry transformed to the best matching UTM zone.
2514
79f4a20e31c2 Introduce distance limit up to which axis linestrings are connected
Tom Gottfried <tom@intevation.de>
parents: 2455
diff changeset
51 CREATE OR REPLACE FUNCTION ISRSrange_axis(
79f4a20e31c2 Introduce distance limit up to which axis linestrings are connected
Tom Gottfried <tom@intevation.de>
parents: 2455
diff changeset
52 stretch isrsrange,
79f4a20e31c2 Introduce distance limit up to which axis linestrings are connected
Tom Gottfried <tom@intevation.de>
parents: 2455
diff changeset
53 tolerance float
79f4a20e31c2 Introduce distance limit up to which axis linestrings are connected
Tom Gottfried <tom@intevation.de>
parents: 2455
diff changeset
54 -- in m, up to which linestrings will be connected at their boundary
79f4a20e31c2 Introduce distance limit up to which axis linestrings are connected
Tom Gottfried <tom@intevation.de>
parents: 2455
diff changeset
55 ) RETURNS geometry
1629
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
56 AS $$
2987
524f1382558a Make snapping of many axis segments faster
Tom Gottfried <tom@intevation.de>
parents: 2956
diff changeset
57 DECLARE z int;
2535
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
58 DECLARE result_geom geometry;
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
59 BEGIN
2987
524f1382558a Make snapping of many axis segments faster
Tom Gottfried <tom@intevation.de>
parents: 2956
diff changeset
60 -- Find best matchting UTM zone
524f1382558a Make snapping of many axis segments faster
Tom Gottfried <tom@intevation.de>
parents: 2956
diff changeset
61 z = best_utm(stretch);
524f1382558a Make snapping of many axis segments faster
Tom Gottfried <tom@intevation.de>
parents: 2956
diff changeset
62
524f1382558a Make snapping of many axis segments faster
Tom Gottfried <tom@intevation.de>
parents: 2956
diff changeset
63 CREATE TEMP TABLE axis AS
5009
e8b2dc771f9e Store axis as MultiLinestring
Tom Gottfried <tom@intevation.de>
parents: 3731
diff changeset
64 SELECT row_number() OVER () AS id,
e8b2dc771f9e Store axis as MultiLinestring
Tom Gottfried <tom@intevation.de>
parents: 3731
diff changeset
65 geom AS wtwaxs,
e8b2dc771f9e Store axis as MultiLinestring
Tom Gottfried <tom@intevation.de>
parents: 3731
diff changeset
66 ST_Boundary(geom) AS bdr
e8b2dc771f9e Store axis as MultiLinestring
Tom Gottfried <tom@intevation.de>
parents: 3731
diff changeset
67 FROM waterway.waterway_axis,
5513
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents: 5009
diff changeset
68 ST_Dump(ST_Transform(wtwaxs::geometry, z))
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents: 5009
diff changeset
69 WHERE validity @> current_timestamp;
2987
524f1382558a Make snapping of many axis segments faster
Tom Gottfried <tom@intevation.de>
parents: 2956
diff changeset
70 CREATE INDEX axs_bdr ON axis USING GiST (bdr);
524f1382558a Make snapping of many axis segments faster
Tom Gottfried <tom@intevation.de>
parents: 2956
diff changeset
71 ANALYZE axis;
524f1382558a Make snapping of many axis segments faster
Tom Gottfried <tom@intevation.de>
parents: 2956
diff changeset
72
2431
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
73 WITH RECURSIVE
1629
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
74 -- In order to guarantee the following ST_Covers to work,
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
75 -- snap distance mark coordinates to axis
2956
974122125a76 Let it be an error if closest points of DISMARs on axis are equal
Tom Gottfried <tom@intevation.de>
parents: 2563
diff changeset
76 points0 AS (
1629
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
77 SELECT ST_ClosestPoint(
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
78 wtwaxs,
2535
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
79 ST_Transform(geom, z)) AS geom
2987
524f1382558a Make snapping of many axis segments faster
Tom Gottfried <tom@intevation.de>
parents: 2956
diff changeset
80 FROM ST_Dump(ISRSrange_points(stretch)), (
2431
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
81 SELECT ST_Collect(wtwaxs) AS wtwaxs
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
82 FROM axis) AS ax),
2956
974122125a76 Let it be an error if closest points of DISMARs on axis are equal
Tom Gottfried <tom@intevation.de>
parents: 2563
diff changeset
83 -- Ensure two distinct points on axis have been found
974122125a76 Let it be an error if closest points of DISMARs on axis are equal
Tom Gottfried <tom@intevation.de>
parents: 2563
diff changeset
84 points AS (
974122125a76 Let it be an error if closest points of DISMARs on axis are equal
Tom Gottfried <tom@intevation.de>
parents: 2563
diff changeset
85 SELECT geom
974122125a76 Let it be an error if closest points of DISMARs on axis are equal
Tom Gottfried <tom@intevation.de>
parents: 2563
diff changeset
86 FROM points0
974122125a76 Let it be an error if closest points of DISMARs on axis are equal
Tom Gottfried <tom@intevation.de>
parents: 2563
diff changeset
87 WHERE 2 = (SELECT count(DISTINCT geom) FROM points0)),
2431
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
88 axis_snapped AS (
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
89 -- Iteratively connect non-contiguous axis chunks
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
90 -- to find the contiguous axis on which given distance marks lie
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
91 (SELECT ARRAY[id] AS ids, wtwaxs
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
92 FROM axis, points
5520
05db984d3db1 Improve performance of bottleneck area calculation
Tom Gottfried <tom@intevation.de>
parents: 5513
diff changeset
93 WHERE ST_DWithin(axis.wtwaxs, points.geom, 0.0001)
2431
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
94 FETCH FIRST ROW ONLY)
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
95 UNION
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
96 -- Connect endpoint of next linestring with closest
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
97 -- endpoint of merged linestring until a contiguous
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
98 -- linestring connecting both distance marks is build up
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
99 (SELECT refids || id,
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
100 ST_LineMerge(ST_Collect(ARRAY(
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
101 -- Linestring build up so far
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
102 SELECT refgeom
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
103 UNION
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
104 -- Fill eventual gap
5520
05db984d3db1 Improve performance of bottleneck area calculation
Tom Gottfried <tom@intevation.de>
parents: 5513
diff changeset
105 SELECT ST_ShortestLine(refbdr, bdr)
2431
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
106 UNION
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
107 -- Linestring to be added
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
108 SELECT geom)))
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
109 FROM axis_snapped AS axis_snapped (refids, refgeom),
2987
524f1382558a Make snapping of many axis segments faster
Tom Gottfried <tom@intevation.de>
parents: 2956
diff changeset
110 axis AS axis (id, geom, bdr),
5520
05db984d3db1 Improve performance of bottleneck area calculation
Tom Gottfried <tom@intevation.de>
parents: 5513
diff changeset
111 ST_Boundary(refgeom) AS refbdr (refbdr)
2431
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
112 WHERE id <> ALL(refids)
5520
05db984d3db1 Improve performance of bottleneck area calculation
Tom Gottfried <tom@intevation.de>
parents: 5513
diff changeset
113 AND ST_DWithin(refbdr, bdr, tolerance)
05db984d3db1 Improve performance of bottleneck area calculation
Tom Gottfried <tom@intevation.de>
parents: 5513
diff changeset
114 -- Stop if refgeom goes through both distance marks
05db984d3db1 Improve performance of bottleneck area calculation
Tom Gottfried <tom@intevation.de>
parents: 5513
diff changeset
115 AND NOT 0.0001 >= ALL(SELECT refgeom <-> geom FROM points)
05db984d3db1 Improve performance of bottleneck area calculation
Tom Gottfried <tom@intevation.de>
parents: 5513
diff changeset
116 ORDER BY refbdr <-> bdr
2431
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
117 FETCH FIRST ROW ONLY)),
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
118 axis_segment AS (
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
119 -- Fetch end result from snapping
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
120 SELECT wtwaxs AS line
5520
05db984d3db1 Improve performance of bottleneck area calculation
Tom Gottfried <tom@intevation.de>
parents: 5513
diff changeset
121 FROM axis_snapped
2535
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
122 -- Return end result only if both distance marks were connected
5520
05db984d3db1 Improve performance of bottleneck area calculation
Tom Gottfried <tom@intevation.de>
parents: 5513
diff changeset
123 WHERE 0.0001 >= ALL(SELECT wtwaxs <-> geom FROM points))
2455
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
124 -- Use linear referencing to clip axis between distance marks.
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
125 -- Simplification is used to work-around the problem, that
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
126 -- ST_LineSubstring might generate very small line segments at an
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
127 -- end of the resulting linestring, that significantly differ from
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
128 -- the direction of the input linestring due to finite precision
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
129 -- of the calculation. The generated small segment of the
2956
974122125a76 Let it be an error if closest points of DISMARs on axis are equal
Tom Gottfried <tom@intevation.de>
parents: 2563
diff changeset
130 -- resulting line would lead e.g. to unexpected results in an area
974122125a76 Let it be an error if closest points of DISMARs on axis are equal
Tom Gottfried <tom@intevation.de>
parents: 2563
diff changeset
131 -- generated by ISRSrange_area().
2455
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
132 SELECT ST_SimplifyPreserveTopology(ST_LineSubstring(
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
133 axis_segment.line, min(fractions.f), max(fractions.f)),
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
134 0.0001) AS line
2535
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
135 INTO STRICT result_geom
2455
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
136 FROM axis_segment, LATERAL (
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
137 SELECT ST_LineLocatePoint(axis_segment.line, points.geom) AS f
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
138 FROM points) AS fractions
2535
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
139 GROUP BY axis_segment.line;
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
140
2987
524f1382558a Make snapping of many axis segments faster
Tom Gottfried <tom@intevation.de>
parents: 2956
diff changeset
141 -- Drop temporary table to avoid side effects on PostgreSQL's MVCC,
524f1382558a Make snapping of many axis segments faster
Tom Gottfried <tom@intevation.de>
parents: 2956
diff changeset
142 -- because otherwise subsequent invocations of the function will not see
524f1382558a Make snapping of many axis segments faster
Tom Gottfried <tom@intevation.de>
parents: 2956
diff changeset
143 -- changes on the underlying waterway.waterway_axis that might have
524f1382558a Make snapping of many axis segments faster
Tom Gottfried <tom@intevation.de>
parents: 2956
diff changeset
144 -- occured.
524f1382558a Make snapping of many axis segments faster
Tom Gottfried <tom@intevation.de>
parents: 2956
diff changeset
145 DROP TABLE axis;
524f1382558a Make snapping of many axis segments faster
Tom Gottfried <tom@intevation.de>
parents: 2956
diff changeset
146
2535
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
147 RETURN result_geom;
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
148 END;
2455
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
149 $$
2535
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
150 LANGUAGE plpgsql
2987
524f1382558a Make snapping of many axis segments faster
Tom Gottfried <tom@intevation.de>
parents: 2956
diff changeset
151 PARALLEL RESTRICTED;
2455
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
152
2563
dc4fae4bdb8f Expose axis snapping tolerance to users
Tom Gottfried <tom@intevation.de>
parents: 2535
diff changeset
153 -- Clip an area to a stretch given by a geometry representing an axis (e.g.
dc4fae4bdb8f Expose axis snapping tolerance to users
Tom Gottfried <tom@intevation.de>
parents: 2535
diff changeset
154 -- the output of ISRSrange_axis()).
dc4fae4bdb8f Expose axis snapping tolerance to users
Tom Gottfried <tom@intevation.de>
parents: 2535
diff changeset
155 -- Clipping is done by cutting the area in perpendicular direction at
dc4fae4bdb8f Expose axis snapping tolerance to users
Tom Gottfried <tom@intevation.de>
parents: 2535
diff changeset
156 -- the ends of the given axis.
2455
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
157 -- The area passed as argument is assumed to intersect with the axis
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
158 -- (use e.g. waterway area or fairway dimensions).
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
159 -- If a multipolygon is passed, the union of the polygons intersecting with the
2563
dc4fae4bdb8f Expose axis snapping tolerance to users
Tom Gottfried <tom@intevation.de>
parents: 2535
diff changeset
160 -- axis is used for clipping.
2455
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
161 CREATE OR REPLACE FUNCTION ISRSrange_area(
2563
dc4fae4bdb8f Expose axis snapping tolerance to users
Tom Gottfried <tom@intevation.de>
parents: 2535
diff changeset
162 axis geometry,
2455
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
163 area geometry
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
164 ) RETURNS geometry
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
165 AS $$
3561
453f15ba8030 Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents: 2987
diff changeset
166 DECLARE
453f15ba8030 Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents: 2987
diff changeset
167 area_subset geometry;
453f15ba8030 Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents: 2987
diff changeset
168 result_geom geometry;
453f15ba8030 Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents: 2987
diff changeset
169 BEGIN
453f15ba8030 Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents: 2987
diff changeset
170 -- In case area is a multipolygon, process the union of those
453f15ba8030 Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents: 2987
diff changeset
171 -- polygons, which intersect with the axis. The union is to avoid
453f15ba8030 Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents: 2987
diff changeset
172 -- problems with invalid/self-intersecting multipolygons
453f15ba8030 Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents: 2987
diff changeset
173 SELECT ST_Union(a_dmp.geom)
453f15ba8030 Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents: 2987
diff changeset
174 INTO STRICT area_subset
453f15ba8030 Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents: 2987
diff changeset
175 FROM (SELECT ST_MakeValid(ST_Transform(geom, ST_SRID(axis)))
453f15ba8030 Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents: 2987
diff changeset
176 FROM ST_Dump(area)) AS a_dmp (geom)
453f15ba8030 Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents: 2987
diff changeset
177 WHERE ST_Intersects(a_dmp.geom, axis)
453f15ba8030 Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents: 2987
diff changeset
178 HAVING ST_Union(a_dmp.geom) IS NOT NULL;
453f15ba8030 Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents: 2987
diff changeset
179
2455
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
180 WITH
2398
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2373
diff changeset
181 rotated_ends AS (
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2373
diff changeset
182 SELECT ST_Collect(ST_Scale(
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2373
diff changeset
183 ST_Translate(e,
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2373
diff changeset
184 (ST_X(p1) - ST_X(p2)) / 2,
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2373
diff changeset
185 (ST_Y(p1) - ST_Y(p2)) / 2),
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2373
diff changeset
186 ST_Point(d, d), p1)) AS blade
2563
dc4fae4bdb8f Expose axis snapping tolerance to users
Tom Gottfried <tom@intevation.de>
parents: 2535
diff changeset
187 FROM (SELECT i, ST_PointN(axis, i) AS p1
2398
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2373
diff changeset
188 FROM (VALUES (1), (-1)) AS idx (i)) AS ep,
2563
dc4fae4bdb8f Expose axis snapping tolerance to users
Tom Gottfried <tom@intevation.de>
parents: 2535
diff changeset
189 ST_Rotate(ST_PointN(axis, i*2), pi()/2, p1) AS ep2 (p2),
2398
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2373
diff changeset
190 ST_Makeline(p1, p2) AS e (e),
3561
453f15ba8030 Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents: 2987
diff changeset
191 LATERAL (
453f15ba8030 Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents: 2987
diff changeset
192 SELECT (ST_MaxDistance(p1, area_subset) / ST_Length(e))
2398
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2373
diff changeset
193 * 2) AS d (d)),
1629
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
194 range_area AS (
2398
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2373
diff changeset
195 -- Split area by orthogonal lines at the ends of the clipped axis
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2373
diff changeset
196 SELECT (ST_Dump(ST_CollectionExtract(
3561
453f15ba8030 Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents: 2987
diff changeset
197 ST_Split(area_subset, blade), 3))).geom
453f15ba8030 Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents: 2987
diff changeset
198 FROM rotated_ends)
1983
f9f1babe52ae Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents: 1811
diff changeset
199 -- From the polygons returned by the last CTE, select only those
1629
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
200 -- around the clipped axis
5559
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents: 5521
diff changeset
201 SELECT ST_Multi(ST_CollectionExtract(ST_MakeValid(ST_Transform(
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents: 5521
diff changeset
202 ST_Union(range_area.geom), ST_SRID(area))), 3))
3561
453f15ba8030 Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents: 2987
diff changeset
203 INTO result_geom
2563
dc4fae4bdb8f Expose axis snapping tolerance to users
Tom Gottfried <tom@intevation.de>
parents: 2535
diff changeset
204 FROM range_area
3561
453f15ba8030 Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents: 2987
diff changeset
205 WHERE ST_Intersects(ST_Buffer(range_area.geom, -0.0001), axis);
453f15ba8030 Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents: 2987
diff changeset
206
453f15ba8030 Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents: 2987
diff changeset
207 RETURN result_geom;
453f15ba8030 Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents: 2987
diff changeset
208 END;
1629
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
209 $$
3561
453f15ba8030 Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents: 2987
diff changeset
210 LANGUAGE plpgsql
453f15ba8030 Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents: 2987
diff changeset
211 STABLE PARALLEL SAFE;
3731
0d45a3c34900 schema: Added function to detect string representation of ISRS codes.
Sascha Wilde <wilde@intevation.de>
parents: 3561
diff changeset
212
0d45a3c34900 schema: Added function to detect string representation of ISRS codes.
Sascha Wilde <wilde@intevation.de>
parents: 3561
diff changeset
213
0d45a3c34900 schema: Added function to detect string representation of ISRS codes.
Sascha Wilde <wilde@intevation.de>
parents: 3561
diff changeset
214 -- Check if a given string looks like an ISRS code
0d45a3c34900 schema: Added function to detect string representation of ISRS codes.
Sascha Wilde <wilde@intevation.de>
parents: 3561
diff changeset
215 CREATE OR REPLACE FUNCTION is_ISRSstring(str text) RETURNS boolean
0d45a3c34900 schema: Added function to detect string representation of ISRS codes.
Sascha Wilde <wilde@intevation.de>
parents: 3561
diff changeset
216 AS $$
0d45a3c34900 schema: Added function to detect string representation of ISRS codes.
Sascha Wilde <wilde@intevation.de>
parents: 3561
diff changeset
217 BEGIN
0d45a3c34900 schema: Added function to detect string representation of ISRS codes.
Sascha Wilde <wilde@intevation.de>
parents: 3561
diff changeset
218 str = upper(str);
0d45a3c34900 schema: Added function to detect string representation of ISRS codes.
Sascha Wilde <wilde@intevation.de>
parents: 3561
diff changeset
219 RETURN (SELECT str SIMILAR TO '[A-Z]{2}[A-Z0-9]{13}[0-9]{5}')
0d45a3c34900 schema: Added function to detect string representation of ISRS codes.
Sascha Wilde <wilde@intevation.de>
parents: 3561
diff changeset
220 AND is_country(substring(str from 1 for 2));
0d45a3c34900 schema: Added function to detect string representation of ISRS codes.
Sascha Wilde <wilde@intevation.de>
parents: 3561
diff changeset
221 END;
0d45a3c34900 schema: Added function to detect string representation of ISRS codes.
Sascha Wilde <wilde@intevation.de>
parents: 3561
diff changeset
222 $$
0d45a3c34900 schema: Added function to detect string representation of ISRS codes.
Sascha Wilde <wilde@intevation.de>
parents: 3561
diff changeset
223 LANGUAGE plpgsql
0d45a3c34900 schema: Added function to detect string representation of ISRS codes.
Sascha Wilde <wilde@intevation.de>
parents: 3561
diff changeset
224 IMMUTABLE PARALLEL SAFE;