Mercurial > gemma

annotate schema/isrs_functions.sql @ 2514:79f4a20e31c2

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Introduce distance limit up to which axis linestrings are connected Before, e.g. a linestring representing a tributary with its mouth in the middle of another linestring, had been connected to the end of the latter.
author Tom Gottfried <tom@intevation.de>
date Tue, 05 Mar 2019 15:44:05 +0100
parents 54c9fe587fe6
children 73c8762cee60
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
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
24 -- 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
25 -- 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
26 -- to their geo-location.
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
27 -- Distance marks are assumed to be near the axis.
2514
79f4a20e31c2 Introduce distance limit up to which axis linestrings are connected
Tom Gottfried <tom@intevation.de>
parents: 2455
diff changeset
28 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
29 stretch isrsrange,
79f4a20e31c2 Introduce distance limit up to which axis linestrings are connected
Tom Gottfried <tom@intevation.de>
parents: 2455
diff changeset
30 tolerance float
79f4a20e31c2 Introduce distance limit up to which axis linestrings are connected
Tom Gottfried <tom@intevation.de>
parents: 2455
diff changeset
31 -- 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
32 ) RETURNS geometry
1629
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
33 AS $$
2431
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
34 WITH RECURSIVE
1629
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
35 -- Get coordinates of location codes
2373
4aabbf324e55 Fix cutting of axis segment between two distance marks
Tom Gottfried <tom@intevation.de>
parents: 2232
diff changeset
36 points_geog AS (
1629
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
37 SELECT geom FROM waterway.distance_marks_virtual
2373
4aabbf324e55 Fix cutting of axis segment between two distance marks
Tom Gottfried <tom@intevation.de>
parents: 2232
diff changeset
38 WHERE location_code = lower(stretch)
4aabbf324e55 Fix cutting of axis segment between two distance marks
Tom Gottfried <tom@intevation.de>
parents: 2232
diff changeset
39 OR location_code = upper(stretch)),
1629
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
40 utm_zone AS (
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
41 -- Find best matchting UTM zone
2455
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
42 SELECT best_utm(stretch) AS z),
1629
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
43 axis AS (
2431
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
44 SELECT id, ST_Transform(wtwaxs::geometry, z) AS wtwaxs
1629
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
45 FROM waterway.waterway_axis, utm_zone),
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
46 -- 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
47 -- snap distance mark coordinates to axis
2373
4aabbf324e55 Fix cutting of axis segment between two distance marks
Tom Gottfried <tom@intevation.de>
parents: 2232
diff changeset
48 points AS (
1629
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
49 SELECT ST_ClosestPoint(
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
50 wtwaxs,
2373
4aabbf324e55 Fix cutting of axis segment between two distance marks
Tom Gottfried <tom@intevation.de>
parents: 2232
diff changeset
51 ST_Transform(points_geog.geom::geometry, z)) AS geom
2431
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
52 FROM points_geog, utm_zone, (
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
53 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
54 FROM axis) AS ax),
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
55 axis_snapped AS (
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
56 -- 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
57 -- 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
58 (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
59 FROM axis, points
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
60 WHERE ST_Intersects(
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
61 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
62 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
63 UNION
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
64 -- 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
65 -- 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
66 -- 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
67 (SELECT refids || id,
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
68 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
69 -- 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
70 SELECT refgeom
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
71 UNION
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
72 -- Fill eventual gap
2450
7677a2850a2d Substitute unnecessarily home-made shortest line
Tom Gottfried <tom@intevation.de>
parents: 2431
diff changeset
73 SELECT ST_ShortestLine(
7677a2850a2d Substitute unnecessarily home-made shortest line
Tom Gottfried <tom@intevation.de>
parents: 2431
diff changeset
74 ST_Boundary(refgeom), ST_Boundary(geom))
2431
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
75 UNION
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
76 -- 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
77 SELECT geom)))
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
78 FROM axis_snapped AS axis_snapped (refids, refgeom),
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
79 axis AS axis (id, geom),
2373
4aabbf324e55 Fix cutting of axis segment between two distance marks
Tom Gottfried <tom@intevation.de>
parents: 2232
diff changeset
80 (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
81 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
82 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
83 AND ST_DWithin(
79f4a20e31c2 Introduce distance limit up to which axis linestrings are connected
Tom Gottfried <tom@intevation.de>
parents: 2455
diff changeset
84 ST_Boundary(refgeom), ST_Boundary(geom), tolerance)
2431
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
85 AND NOT ST_Covers(ST_Buffer(refgeom, 0.0001), points.pts)
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
86 ORDER BY ST_Distance(ST_Boundary(refgeom), ST_Boundary(geom))
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
87 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
88 axis_segment AS (
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
89 -- 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
90 SELECT wtwaxs AS line
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
91 FROM axis_snapped
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
92 WHERE array_length(ids, 1) = (
2455
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
93 SELECT max(array_length(ids, 1)) FROM axis_snapped))
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
94 -- 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
95 -- 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
96 -- 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
97 -- 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
98 -- 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
99 -- of the calculation. The generated small segment of the
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
100 -- resulting line leads to unexpected results of the buffer with
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
101 -- endcap=flat in the CTE below.
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
102 SELECT ST_SimplifyPreserveTopology(ST_LineSubstring(
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
103 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
104 0.0001) AS line
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
105 FROM axis_segment, LATERAL (
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
106 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
107 FROM points) AS fractions
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
108 GROUP BY axis_segment.line
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
109 $$
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
110 LANGUAGE sql
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
111 STABLE PARALLEL SAFE;
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
112
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
113 -- Clip an area to a stretch given by a pair of ISRS location codes.
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
114 -- Uses ISRSrange_axis() to retrieve the respective clipped axis used to find
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
115 -- perpendicular direction at geo-locations of ISRS codes.
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
116 -- 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
117 -- (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
118 -- If a multipolygon is passed, the union of the polygons intersecting with the
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
119 -- relevant part of the axis is used for clipping.
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
120 CREATE OR REPLACE FUNCTION ISRSrange_area(
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
121 stretch isrsrange,
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
122 area geometry
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
123 ) RETURNS geometry
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
124 AS $$
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
125 WITH
1629
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
126 axis_substring AS (
2514
79f4a20e31c2 Introduce distance limit up to which axis linestrings are connected
Tom Gottfried <tom@intevation.de>
parents: 2455
diff changeset
127 SELECT ISRSrange_axis(stretch, 5) AS line),
2455
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
128 utm_zone AS (
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling
Tom Gottfried <tom@intevation.de>
parents: 2450
diff changeset
129 SELECT best_utm(stretch) AS z),
2232
7936b46a88d4 Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
130 area_subset AS (
7936b46a88d4 Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
131 -- In case area is a multipolygon, process the union of those
7936b46a88d4 Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
132 -- polygons, which intersect with the axis. The union is to avoid
7936b46a88d4 Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
133 -- problems with invalid/self-intersecting multipolygons
7936b46a88d4 Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
134 SELECT ST_Union(a_dmp.geom) AS area
2425
4c16f5ad1905 Make area generation more robust
Tom Gottfried <tom@intevation.de>
parents: 2398
diff changeset
135 FROM axis_substring, utm_zone, LATERAL (
4c16f5ad1905 Make area generation more robust
Tom Gottfried <tom@intevation.de>
parents: 2398
diff changeset
136 SELECT ST_MakeValid(ST_Transform(geom, z)) AS geom
4c16f5ad1905 Make area generation more robust
Tom Gottfried <tom@intevation.de>
parents: 2398
diff changeset
137 FROM ST_Dump(area)) AS a_dmp
2232
7936b46a88d4 Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
138 WHERE ST_Intersects(a_dmp.geom, axis_substring.line)
7936b46a88d4 Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
139 ),
2398
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2373
diff changeset
140 rotated_ends AS (
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2373
diff changeset
141 SELECT ST_Collect(ST_Scale(
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2373
diff changeset
142 ST_Translate(e,
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2373
diff changeset
143 (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
144 (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
145 ST_Point(d, d), p1)) AS blade
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2373
diff changeset
146 FROM axis_substring, area_subset,
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2373
diff changeset
147 LATERAL (SELECT i, ST_PointN(line, i) AS p1
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2373
diff changeset
148 FROM (VALUES (1), (-1)) AS idx (i)) AS ep,
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2373
diff changeset
149 ST_Rotate(ST_PointN(line, i*2), pi()/2, p1) AS ep2 (p2),
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2373
diff changeset
150 ST_Makeline(p1, p2) AS e (e),
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2373
diff changeset
151 LATERAL (SELECT (ST_MaxDistance(p1, area) / ST_Length(e))
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2373
diff changeset
152 * 2) AS d (d)),
1629
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
153 range_area AS (
2398
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2373
diff changeset
154 -- 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
155 SELECT (ST_Dump(ST_CollectionExtract(
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2373
diff changeset
156 ST_Split(area, blade), 3))).geom
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2373
diff changeset
157 FROM area_subset, rotated_ends)
1983
f9f1babe52ae Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents: 1811
diff changeset
158 -- 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
159 -- around the clipped axis
2425
4c16f5ad1905 Make area generation more robust
Tom Gottfried <tom@intevation.de>
parents: 2398
diff changeset
160 SELECT ST_Multi(ST_Transform(ST_Union(range_area.geom), ST_SRID(area)))
1629
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
161 FROM axis_substring, range_area
2398
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2373
diff changeset
162 WHERE ST_Intersects(ST_Buffer(range_area.geom, -0.0001),
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2373
diff changeset
163 axis_substring.line)
1629
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
164 $$
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
165 LANGUAGE sql;