Mercurial > gemma
annotate schema/isrs_functions.sql @ 4214:49564382ffff
Added a import queue job to recalculate the contour lines of the sounding results if the heights have changed.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Fri, 16 Aug 2019 13:15:34 +0200 |
parents | 0d45a3c34900 |
children | e8b2dc771f9e |
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 |
524f1382558a
Make snapping of many axis segments faster
Tom Gottfried <tom@intevation.de>
parents:
2956
diff
changeset
|
64 SELECT id, wtwaxs, ST_Boundary(wtwaxs) AS bdr |
524f1382558a
Make snapping of many axis segments faster
Tom Gottfried <tom@intevation.de>
parents:
2956
diff
changeset
|
65 FROM (SELECT id, ST_Transform(wtwaxs::geometry, z) AS wtwaxs |
524f1382558a
Make snapping of many axis segments faster
Tom Gottfried <tom@intevation.de>
parents:
2956
diff
changeset
|
66 FROM waterway.waterway_axis) AS axs; |
524f1382558a
Make snapping of many axis segments faster
Tom Gottfried <tom@intevation.de>
parents:
2956
diff
changeset
|
67 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
|
68 ANALYZE axis; |
524f1382558a
Make snapping of many axis segments faster
Tom Gottfried <tom@intevation.de>
parents:
2956
diff
changeset
|
69 |
2431
48495bd3081d
Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents:
2425
diff
changeset
|
70 WITH RECURSIVE |
1629
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
71 -- 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
|
72 -- 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
|
73 points0 AS ( |
1629
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
74 SELECT ST_ClosestPoint( |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
75 wtwaxs, |
2535
73c8762cee60
Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents:
2514
diff
changeset
|
76 ST_Transform(geom, z)) AS geom |
2987
524f1382558a
Make snapping of many axis segments faster
Tom Gottfried <tom@intevation.de>
parents:
2956
diff
changeset
|
77 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
|
78 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
|
79 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
|
80 -- 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
|
81 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
|
82 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
|
83 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
|
84 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
|
85 axis_snapped AS ( |
48495bd3081d
Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents:
2425
diff
changeset
|
86 -- 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
|
87 -- 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
|
88 (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
|
89 FROM axis, points |
48495bd3081d
Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents:
2425
diff
changeset
|
90 WHERE ST_Intersects( |
48495bd3081d
Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents:
2425
diff
changeset
|
91 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
|
92 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
|
93 UNION |
48495bd3081d
Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents:
2425
diff
changeset
|
94 -- 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
|
95 -- 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
|
96 -- 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
|
97 (SELECT refids || id, |
48495bd3081d
Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents:
2425
diff
changeset
|
98 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
|
99 -- 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
|
100 SELECT refgeom |
48495bd3081d
Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents:
2425
diff
changeset
|
101 UNION |
48495bd3081d
Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents:
2425
diff
changeset
|
102 -- Fill eventual gap |
2450
7677a2850a2d
Substitute unnecessarily home-made shortest line
Tom Gottfried <tom@intevation.de>
parents:
2431
diff
changeset
|
103 SELECT ST_ShortestLine( |
2987
524f1382558a
Make snapping of many axis segments faster
Tom Gottfried <tom@intevation.de>
parents:
2956
diff
changeset
|
104 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
|
105 UNION |
48495bd3081d
Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents:
2425
diff
changeset
|
106 -- 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
|
107 SELECT geom))) |
48495bd3081d
Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents:
2425
diff
changeset
|
108 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
|
109 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
|
110 (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
|
111 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
|
112 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
|
113 AND ST_DWithin( |
2987
524f1382558a
Make snapping of many axis segments faster
Tom Gottfried <tom@intevation.de>
parents:
2956
diff
changeset
|
114 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
|
115 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
|
116 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
|
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 |
2535
73c8762cee60
Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents:
2514
diff
changeset
|
121 FROM axis_snapped, |
73c8762cee60
Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents:
2514
diff
changeset
|
122 (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
|
123 FROM points) AS points |
73c8762cee60
Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents:
2514
diff
changeset
|
124 -- 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
|
125 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
|
126 -- 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
|
127 -- 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
|
128 -- 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
|
129 -- 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
|
130 -- 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
|
131 -- 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
|
132 -- 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
|
133 -- generated by ISRSrange_area(). |
2455
54c9fe587fe6
Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents:
2450
diff
changeset
|
134 SELECT ST_SimplifyPreserveTopology(ST_LineSubstring( |
54c9fe587fe6
Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents:
2450
diff
changeset
|
135 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
|
136 0.0001) AS line |
2535
73c8762cee60
Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents:
2514
diff
changeset
|
137 INTO STRICT result_geom |
2455
54c9fe587fe6
Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents:
2450
diff
changeset
|
138 FROM axis_segment, LATERAL ( |
54c9fe587fe6
Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents:
2450
diff
changeset
|
139 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
|
140 FROM points) AS fractions |
2535
73c8762cee60
Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents:
2514
diff
changeset
|
141 GROUP BY axis_segment.line; |
73c8762cee60
Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents:
2514
diff
changeset
|
142 |
2987
524f1382558a
Make snapping of many axis segments faster
Tom Gottfried <tom@intevation.de>
parents:
2956
diff
changeset
|
143 -- 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
|
144 -- 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
|
145 -- 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
|
146 -- occured. |
524f1382558a
Make snapping of many axis segments faster
Tom Gottfried <tom@intevation.de>
parents:
2956
diff
changeset
|
147 DROP TABLE axis; |
524f1382558a
Make snapping of many axis segments faster
Tom Gottfried <tom@intevation.de>
parents:
2956
diff
changeset
|
148 |
2535
73c8762cee60
Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents:
2514
diff
changeset
|
149 RETURN result_geom; |
73c8762cee60
Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents:
2514
diff
changeset
|
150 END; |
2455
54c9fe587fe6
Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents:
2450
diff
changeset
|
151 $$ |
2535
73c8762cee60
Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents:
2514
diff
changeset
|
152 LANGUAGE plpgsql |
2987
524f1382558a
Make snapping of many axis segments faster
Tom Gottfried <tom@intevation.de>
parents:
2956
diff
changeset
|
153 PARALLEL RESTRICTED; |
2455
54c9fe587fe6
Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents:
2450
diff
changeset
|
154 |
2563
dc4fae4bdb8f
Expose axis snapping tolerance to users
Tom Gottfried <tom@intevation.de>
parents:
2535
diff
changeset
|
155 -- 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
|
156 -- the output of ISRSrange_axis()). |
dc4fae4bdb8f
Expose axis snapping tolerance to users
Tom Gottfried <tom@intevation.de>
parents:
2535
diff
changeset
|
157 -- 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
|
158 -- 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
|
159 -- 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
|
160 -- (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
|
161 -- 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
|
162 -- 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
|
163 CREATE OR REPLACE FUNCTION ISRSrange_area( |
2563
dc4fae4bdb8f
Expose axis snapping tolerance to users
Tom Gottfried <tom@intevation.de>
parents:
2535
diff
changeset
|
164 axis geometry, |
2455
54c9fe587fe6
Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents:
2450
diff
changeset
|
165 area geometry |
54c9fe587fe6
Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents:
2450
diff
changeset
|
166 ) RETURNS geometry |
54c9fe587fe6
Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents:
2450
diff
changeset
|
167 AS $$ |
3561
453f15ba8030
Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents:
2987
diff
changeset
|
168 DECLARE |
453f15ba8030
Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents:
2987
diff
changeset
|
169 area_subset geometry; |
453f15ba8030
Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents:
2987
diff
changeset
|
170 result_geom geometry; |
453f15ba8030
Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents:
2987
diff
changeset
|
171 BEGIN |
453f15ba8030
Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents:
2987
diff
changeset
|
172 -- 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
|
173 -- 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
|
174 -- problems with invalid/self-intersecting multipolygons |
453f15ba8030
Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents:
2987
diff
changeset
|
175 SELECT ST_Union(a_dmp.geom) |
453f15ba8030
Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents:
2987
diff
changeset
|
176 INTO STRICT area_subset |
453f15ba8030
Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents:
2987
diff
changeset
|
177 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
|
178 FROM ST_Dump(area)) AS a_dmp (geom) |
453f15ba8030
Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents:
2987
diff
changeset
|
179 WHERE ST_Intersects(a_dmp.geom, axis) |
453f15ba8030
Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents:
2987
diff
changeset
|
180 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
|
181 |
2455
54c9fe587fe6
Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents:
2450
diff
changeset
|
182 WITH |
2398
8481e6266691
Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
2373
diff
changeset
|
183 rotated_ends AS ( |
8481e6266691
Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
2373
diff
changeset
|
184 SELECT ST_Collect(ST_Scale( |
8481e6266691
Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
2373
diff
changeset
|
185 ST_Translate(e, |
8481e6266691
Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
2373
diff
changeset
|
186 (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
|
187 (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
|
188 ST_Point(d, d), p1)) AS blade |
2563
dc4fae4bdb8f
Expose axis snapping tolerance to users
Tom Gottfried <tom@intevation.de>
parents:
2535
diff
changeset
|
189 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
|
190 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
|
191 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
|
192 ST_Makeline(p1, p2) AS e (e), |
3561
453f15ba8030
Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents:
2987
diff
changeset
|
193 LATERAL ( |
453f15ba8030
Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents:
2987
diff
changeset
|
194 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
|
195 * 2) AS d (d)), |
1629
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
196 range_area AS ( |
2398
8481e6266691
Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
2373
diff
changeset
|
197 -- 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
|
198 SELECT (ST_Dump(ST_CollectionExtract( |
3561
453f15ba8030
Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents:
2987
diff
changeset
|
199 ST_Split(area_subset, blade), 3))).geom |
453f15ba8030
Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents:
2987
diff
changeset
|
200 FROM rotated_ends) |
1983
f9f1babe52ae
Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents:
1811
diff
changeset
|
201 -- 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
|
202 -- around the clipped axis |
2425
4c16f5ad1905
Make area generation more robust
Tom Gottfried <tom@intevation.de>
parents:
2398
diff
changeset
|
203 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
|
204 INTO result_geom |
2563
dc4fae4bdb8f
Expose axis snapping tolerance to users
Tom Gottfried <tom@intevation.de>
parents:
2535
diff
changeset
|
205 FROM range_area |
3561
453f15ba8030
Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents:
2987
diff
changeset
|
206 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
|
207 |
453f15ba8030
Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents:
2987
diff
changeset
|
208 RETURN result_geom; |
453f15ba8030
Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents:
2987
diff
changeset
|
209 END; |
1629
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
210 $$ |
3561
453f15ba8030
Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents:
2987
diff
changeset
|
211 LANGUAGE plpgsql |
453f15ba8030
Improve error handling in area generation
Tom Gottfried <tom@intevation.de>
parents:
2987
diff
changeset
|
212 STABLE PARALLEL SAFE; |
3731
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 |
0d45a3c34900
schema: Added function to detect string representation of ISRS codes.
Sascha Wilde <wilde@intevation.de>
parents:
3561
diff
changeset
|
215 -- 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
|
216 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
|
217 AS $$ |
0d45a3c34900
schema: Added function to detect string representation of ISRS codes.
Sascha Wilde <wilde@intevation.de>
parents:
3561
diff
changeset
|
218 BEGIN |
0d45a3c34900
schema: Added function to detect string representation of ISRS codes.
Sascha Wilde <wilde@intevation.de>
parents:
3561
diff
changeset
|
219 str = upper(str); |
0d45a3c34900
schema: Added function to detect string representation of ISRS codes.
Sascha Wilde <wilde@intevation.de>
parents:
3561
diff
changeset
|
220 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
|
221 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
|
222 END; |
0d45a3c34900
schema: Added function to detect string representation of ISRS codes.
Sascha Wilde <wilde@intevation.de>
parents:
3561
diff
changeset
|
223 $$ |
0d45a3c34900
schema: Added function to detect string representation of ISRS codes.
Sascha Wilde <wilde@intevation.de>
parents:
3561
diff
changeset
|
224 LANGUAGE plpgsql |
0d45a3c34900
schema: Added function to detect string representation of ISRS codes.
Sascha Wilde <wilde@intevation.de>
parents:
3561
diff
changeset
|
225 IMMUTABLE PARALLEL SAFE; |