Mercurial > gemma

annotate schema/isrs_functions.sql @ 5513:68358e4603c8

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Use current axis only for calculating bottleneck areas This is a fixup of rev. cf25b23e3eec, which introduced historic data for the waterway axis but missed to take this into account in the calculation of bottleneck areas, leading to sometimes excessive runtime and bad results due to multiple (almost) equal axis geometries being considered as candidates in the bottleneck stretch. The database migration tries to recalculate all bottleneck areas, while some might fail that did not fail on import. A warning message is emitted for these and the area is left untouched.
author Tom Gottfried <tom@intevation.de>
date Tue, 19 Oct 2021 13:12:39 +0200
parents e8b2dc771f9e
children 05db984d3db1
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
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
93 WHERE ST_Intersects(
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
94 ST_Buffer(axis.wtwaxs, 0.0001), points.geom)
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
95 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
96 UNION
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
97 -- 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
98 -- 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
99 -- 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
100 (SELECT refids || id,
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
101 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
102 -- 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
103 SELECT refgeom
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
104 UNION
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
105 -- Fill eventual gap
2450
7677a2850a2d Substitute unnecessarily home-made shortest line
Tom Gottfried <tom@intevation.de>
parents: 2431
diff changeset
106 SELECT ST_ShortestLine(
2987
524f1382558a Make snapping of many axis segments faster
Tom Gottfried <tom@intevation.de>
parents: 2956
diff changeset
107 ST_Boundary(refgeom), bdr)
2431
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
108 UNION
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
109 -- 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
110 SELECT geom)))
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
111 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
112 axis AS axis (id, geom, bdr),
2373
4aabbf324e55 Fix cutting of axis segment between two distance marks
Tom Gottfried <tom@intevation.de>
parents: 2232
diff changeset
113 (SELECT ST_Collect(points.geom) AS pts
4aabbf324e55 Fix cutting of axis segment between two distance marks
Tom Gottfried <tom@intevation.de>
parents: 2232
diff changeset
114 FROM points) AS points
2431
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
115 WHERE id <> ALL(refids)
2514
79f4a20e31c2 Introduce distance limit up to which axis linestrings are connected
Tom Gottfried <tom@intevation.de>
parents: 2455
diff changeset
116 AND ST_DWithin(
2987
524f1382558a Make snapping of many axis segments faster
Tom Gottfried <tom@intevation.de>
parents: 2956
diff changeset
117 ST_Boundary(refgeom), bdr, tolerance)
2431
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
118 AND NOT ST_Covers(ST_Buffer(refgeom, 0.0001), points.pts)
2987
524f1382558a Make snapping of many axis segments faster
Tom Gottfried <tom@intevation.de>
parents: 2956
diff changeset
119 ORDER BY ST_Boundary(refgeom) <-> bdr
2431
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
120 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
121 axis_segment AS (
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
122 -- 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
123 SELECT wtwaxs AS line
2535
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
124 FROM axis_snapped,
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
125 (SELECT ST_Collect(points.geom) AS pts
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
126 FROM points) AS points
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
127 -- Return end result only if both distance marks were connected
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
128 WHERE ST_Covers(ST_Buffer(wtwaxs, 0.0001), points.pts))
2455
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
129 -- 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
130 -- 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
131 -- 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
132 -- 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
133 -- 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
134 -- 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
135 -- 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
136 -- generated by ISRSrange_area().
2455
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
137 SELECT ST_SimplifyPreserveTopology(ST_LineSubstring(
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
138 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
139 0.0001) AS line
2535
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
140 INTO STRICT result_geom
2455
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
141 FROM axis_segment, LATERAL (
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
142 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
143 FROM points) AS fractions
2535
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
144 GROUP BY axis_segment.line;
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
145
2987
524f1382558a Make snapping of many axis segments faster
Tom Gottfried <tom@intevation.de>
parents: 2956
diff changeset
146 -- 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
147 -- 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
148 -- 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
149 -- occured.
524f1382558a Make snapping of many axis segments faster
Tom Gottfried <tom@intevation.de>
parents: 2956
diff changeset
150 DROP TABLE axis;
524f1382558a Make snapping of many axis segments faster
Tom Gottfried <tom@intevation.de>
parents: 2956
diff changeset
151
2535
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
152 RETURN result_geom;
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
153 END;
2455
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
154 $$
2535
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
155 LANGUAGE plpgsql
2987
524f1382558a Make snapping of many axis segments faster
Tom Gottfried <tom@intevation.de>
parents: 2956
diff changeset
156 PARALLEL RESTRICTED;
2455
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
157
2563
dc4fae4bdb8f Expose axis snapping tolerance to users
Tom Gottfried <tom@intevation.de>
parents: 2535
diff changeset
158 -- 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
159 -- the output of ISRSrange_axis()).
dc4fae4bdb8f Expose axis snapping tolerance to users
Tom Gottfried <tom@intevation.de>
parents: 2535
diff changeset
160 -- 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
161 -- 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
162 -- 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
163 -- (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
164 -- 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
165 -- 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
166 CREATE OR REPLACE FUNCTION ISRSrange_area(
2563
dc4fae4bdb8f Expose axis snapping tolerance to users
Tom Gottfried <tom@intevation.de>
parents: 2535
diff changeset
167 axis geometry,
2455
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
168 area geometry
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
169 ) RETURNS geometry
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
170 AS $$
3561
453f15ba8030 Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents: 2987
diff changeset
171 DECLARE
453f15ba8030 Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents: 2987
diff changeset
172 area_subset geometry;
453f15ba8030 Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents: 2987
diff changeset
173 result_geom geometry;
453f15ba8030 Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents: 2987
diff changeset
174 BEGIN
453f15ba8030 Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents: 2987
diff changeset
175 -- 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
176 -- 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
177 -- problems with invalid/self-intersecting multipolygons
453f15ba8030 Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents: 2987
diff changeset
178 SELECT ST_Union(a_dmp.geom)
453f15ba8030 Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents: 2987
diff changeset
179 INTO STRICT area_subset
453f15ba8030 Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents: 2987
diff changeset
180 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
181 FROM ST_Dump(area)) AS a_dmp (geom)
453f15ba8030 Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents: 2987
diff changeset
182 WHERE ST_Intersects(a_dmp.geom, axis)
453f15ba8030 Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents: 2987
diff changeset
183 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
184
2455
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
185 WITH
2398
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2373
diff changeset
186 rotated_ends AS (
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2373
diff changeset
187 SELECT ST_Collect(ST_Scale(
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2373
diff changeset
188 ST_Translate(e,
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2373
diff changeset
189 (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
190 (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
191 ST_Point(d, d), p1)) AS blade
2563
dc4fae4bdb8f Expose axis snapping tolerance to users
Tom Gottfried <tom@intevation.de>
parents: 2535
diff changeset
192 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
193 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
194 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
195 ST_Makeline(p1, p2) AS e (e),
3561
453f15ba8030 Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents: 2987
diff changeset
196 LATERAL (
453f15ba8030 Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents: 2987
diff changeset
197 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
198 * 2) AS d (d)),
1629
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
199 range_area AS (
2398
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2373
diff changeset
200 -- 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
201 SELECT (ST_Dump(ST_CollectionExtract(
3561
453f15ba8030 Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents: 2987
diff changeset
202 ST_Split(area_subset, blade), 3))).geom
453f15ba8030 Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents: 2987
diff changeset
203 FROM rotated_ends)
1983
f9f1babe52ae Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents: 1811
diff changeset
204 -- 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
205 -- around the clipped axis
2425
4c16f5ad1905 Make area generation more robust
Tom Gottfried <tom@intevation.de>
parents: 2398
diff changeset
206 SELECT ST_Multi(ST_Transform(ST_Union(range_area.geom), ST_SRID(area)))
3561
453f15ba8030 Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents: 2987
diff changeset
207 INTO result_geom
2563
dc4fae4bdb8f Expose axis snapping tolerance to users
Tom Gottfried <tom@intevation.de>
parents: 2535
diff changeset
208 FROM range_area
3561
453f15ba8030 Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents: 2987
diff changeset
209 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
210
453f15ba8030 Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents: 2987
diff changeset
211 RETURN result_geom;
453f15ba8030 Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents: 2987
diff changeset
212 END;
1629
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
213 $$
3561
453f15ba8030 Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents: 2987
diff changeset
214 LANGUAGE plpgsql
453f15ba8030 Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents: 2987
diff changeset
215 STABLE PARALLEL SAFE;
3731
0d45a3c34900 schema: Added function to detect string representation of ISRS codes.
Sascha Wilde <wilde@intevation.de>
parents: 3561
diff changeset
216
0d45a3c34900 schema: Added function to detect string representation of ISRS codes.
Sascha Wilde <wilde@intevation.de>
parents: 3561
diff changeset
217
0d45a3c34900 schema: Added function to detect string representation of ISRS codes.
Sascha Wilde <wilde@intevation.de>
parents: 3561
diff changeset
218 -- 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
219 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
220 AS $$
0d45a3c34900 schema: Added function to detect string representation of ISRS codes.
Sascha Wilde <wilde@intevation.de>
parents: 3561
diff changeset
221 BEGIN
0d45a3c34900 schema: Added function to detect string representation of ISRS codes.
Sascha Wilde <wilde@intevation.de>
parents: 3561
diff changeset
222 str = upper(str);
0d45a3c34900 schema: Added function to detect string representation of ISRS codes.
Sascha Wilde <wilde@intevation.de>
parents: 3561
diff changeset
223 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
224 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
225 END;
0d45a3c34900 schema: Added function to detect string representation of ISRS codes.
Sascha Wilde <wilde@intevation.de>
parents: 3561
diff changeset
226 $$
0d45a3c34900 schema: Added function to detect string representation of ISRS codes.
Sascha Wilde <wilde@intevation.de>
parents: 3561
diff changeset
227 LANGUAGE plpgsql
0d45a3c34900 schema: Added function to detect string representation of ISRS codes.
Sascha Wilde <wilde@intevation.de>
parents: 3561
diff changeset
228 IMMUTABLE PARALLEL SAFE;