Mercurial > gemma
annotate schema/isrs_functions.sql @ 2431:48495bd3081d
Construct stretch area between distance marks also from non-contiguous axis
Before, both distance marks were required to be next to the same linestring
of the waterway axis.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Thu, 28 Feb 2019 19:44:38 +0100 |
parents | 4c16f5ad1905 |
children | 7677a2850a2d |
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 |
1629
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
15 -- Clip an area to a stretch given by a pair of ISRS location codes. |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
16 -- Uses the table waterway.distance_marks_virtual to map ISRS location codes |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
17 -- to their geo-location and the table waterway.waterway_axis to retrieve |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
18 -- perpendicular direction at these geo-locations. |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
19 -- Distance marks are assumed to be near the axis and the area passed as |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
20 -- argument is assumed to intersect with the axis |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
21 -- (use e.g. waterway area or fairway dimensions). |
2232
7936b46a88d4
Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
22 -- If a multipolygon is passed, the union of the polygons intersecting with the |
7936b46a88d4
Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
23 -- relevant part of the axis is used for clipping. |
1629
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
24 CREATE OR REPLACE FUNCTION ISRSrange_area( |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
25 stretch isrsrange, |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
26 area geometry |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
27 ) RETURNS geometry |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
28 AS $$ |
2431
48495bd3081d
Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents:
2425
diff
changeset
|
29 WITH RECURSIVE |
1629
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
30 -- 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
|
31 points_geog AS ( |
1629
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
32 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
|
33 WHERE location_code = lower(stretch) |
4aabbf324e55
Fix cutting of axis segment between two distance marks
Tom Gottfried <tom@intevation.de>
parents:
2232
diff
changeset
|
34 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
|
35 utm_zone AS ( |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
36 -- Find best matchting UTM zone |
2373
4aabbf324e55
Fix cutting of axis segment between two distance marks
Tom Gottfried <tom@intevation.de>
parents:
2232
diff
changeset
|
37 SELECT best_utm(ST_Collect(geom::geometry)) AS z |
4aabbf324e55
Fix cutting of axis segment between two distance marks
Tom Gottfried <tom@intevation.de>
parents:
2232
diff
changeset
|
38 FROM points_geog), |
1629
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
39 axis AS ( |
2431
48495bd3081d
Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents:
2425
diff
changeset
|
40 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
|
41 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
|
42 -- 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
|
43 -- 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
|
44 points AS ( |
1629
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
45 SELECT ST_ClosestPoint( |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
46 wtwaxs, |
2373
4aabbf324e55
Fix cutting of axis segment between two distance marks
Tom Gottfried <tom@intevation.de>
parents:
2232
diff
changeset
|
47 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
|
48 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
|
49 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
|
50 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
|
51 axis_snapped AS ( |
48495bd3081d
Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents:
2425
diff
changeset
|
52 -- 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
|
53 -- 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
|
54 (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
|
55 FROM axis, points |
48495bd3081d
Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents:
2425
diff
changeset
|
56 WHERE ST_Intersects( |
48495bd3081d
Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents:
2425
diff
changeset
|
57 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
|
58 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
|
59 UNION |
48495bd3081d
Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents:
2425
diff
changeset
|
60 -- 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
|
61 -- 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
|
62 -- 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
|
63 (SELECT refids || id, |
48495bd3081d
Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents:
2425
diff
changeset
|
64 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
|
65 -- 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
|
66 SELECT refgeom |
48495bd3081d
Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents:
2425
diff
changeset
|
67 UNION |
48495bd3081d
Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents:
2425
diff
changeset
|
68 -- Fill eventual gap |
48495bd3081d
Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents:
2425
diff
changeset
|
69 SELECT ST_MakeLine( |
48495bd3081d
Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents:
2425
diff
changeset
|
70 ST_ClosestPoint( |
48495bd3081d
Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents:
2425
diff
changeset
|
71 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
|
72 ST_ClosestPoint( |
48495bd3081d
Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents:
2425
diff
changeset
|
73 ST_Boundary(geom), ST_Boundary(refgeom))) |
48495bd3081d
Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents:
2425
diff
changeset
|
74 UNION |
48495bd3081d
Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents:
2425
diff
changeset
|
75 -- 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
|
76 SELECT geom))) |
48495bd3081d
Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents:
2425
diff
changeset
|
77 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
|
78 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
|
79 (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
|
80 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
|
81 WHERE id <> ALL(refids) |
48495bd3081d
Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents:
2425
diff
changeset
|
82 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
|
83 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
|
84 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
|
85 axis_segment AS ( |
48495bd3081d
Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents:
2425
diff
changeset
|
86 -- 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
|
87 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
|
88 FROM axis_snapped |
48495bd3081d
Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents:
2425
diff
changeset
|
89 WHERE array_length(ids, 1) = ( |
48495bd3081d
Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents:
2425
diff
changeset
|
90 SELECT max(array_length(ids, 1)) FROM axis_snapped)), |
1629
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
91 axis_substring AS ( |
1811
c21f72775f6b
Fix numerical problems in stretch area generation
Tom Gottfried <tom@intevation.de>
parents:
1723
diff
changeset
|
92 -- Use linear referencing to clip axis between distance marks. |
c21f72775f6b
Fix numerical problems in stretch area generation
Tom Gottfried <tom@intevation.de>
parents:
1723
diff
changeset
|
93 -- Simplification is used to work-around the problem, that |
c21f72775f6b
Fix numerical problems in stretch area generation
Tom Gottfried <tom@intevation.de>
parents:
1723
diff
changeset
|
94 -- ST_LineSubstring might generate very small line segments at an |
c21f72775f6b
Fix numerical problems in stretch area generation
Tom Gottfried <tom@intevation.de>
parents:
1723
diff
changeset
|
95 -- end of the resulting linestring, that significantly differ from |
c21f72775f6b
Fix numerical problems in stretch area generation
Tom Gottfried <tom@intevation.de>
parents:
1723
diff
changeset
|
96 -- the direction of the input linestring due to finite precision |
c21f72775f6b
Fix numerical problems in stretch area generation
Tom Gottfried <tom@intevation.de>
parents:
1723
diff
changeset
|
97 -- of the calculation. The generated small segment of the |
c21f72775f6b
Fix numerical problems in stretch area generation
Tom Gottfried <tom@intevation.de>
parents:
1723
diff
changeset
|
98 -- resulting line leads to unexpected results of the buffer with |
c21f72775f6b
Fix numerical problems in stretch area generation
Tom Gottfried <tom@intevation.de>
parents:
1723
diff
changeset
|
99 -- endcap=flat in the CTE below. |
c21f72775f6b
Fix numerical problems in stretch area generation
Tom Gottfried <tom@intevation.de>
parents:
1723
diff
changeset
|
100 SELECT ST_SimplifyPreserveTopology(ST_LineSubstring( |
2373
4aabbf324e55
Fix cutting of axis segment between two distance marks
Tom Gottfried <tom@intevation.de>
parents:
2232
diff
changeset
|
101 axis_segment.line, min(fractions.f), max(fractions.f)), |
4aabbf324e55
Fix cutting of axis segment between two distance marks
Tom Gottfried <tom@intevation.de>
parents:
2232
diff
changeset
|
102 0.0001) AS line |
4aabbf324e55
Fix cutting of axis segment between two distance marks
Tom Gottfried <tom@intevation.de>
parents:
2232
diff
changeset
|
103 FROM axis_segment, LATERAL ( |
4aabbf324e55
Fix cutting of axis segment between two distance marks
Tom Gottfried <tom@intevation.de>
parents:
2232
diff
changeset
|
104 SELECT ST_LineLocatePoint(axis_segment.line, points.geom) AS f |
4aabbf324e55
Fix cutting of axis segment between two distance marks
Tom Gottfried <tom@intevation.de>
parents:
2232
diff
changeset
|
105 FROM points) AS fractions |
4aabbf324e55
Fix cutting of axis segment between two distance marks
Tom Gottfried <tom@intevation.de>
parents:
2232
diff
changeset
|
106 GROUP BY axis_segment.line), |
2232
7936b46a88d4
Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
107 area_subset AS ( |
7936b46a88d4
Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
108 -- 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
|
109 -- 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
|
110 -- problems with invalid/self-intersecting multipolygons |
7936b46a88d4
Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
111 SELECT ST_Union(a_dmp.geom) AS area |
2425
4c16f5ad1905
Make area generation more robust
Tom Gottfried <tom@intevation.de>
parents:
2398
diff
changeset
|
112 FROM axis_substring, utm_zone, LATERAL ( |
4c16f5ad1905
Make area generation more robust
Tom Gottfried <tom@intevation.de>
parents:
2398
diff
changeset
|
113 SELECT ST_MakeValid(ST_Transform(geom, z)) AS geom |
4c16f5ad1905
Make area generation more robust
Tom Gottfried <tom@intevation.de>
parents:
2398
diff
changeset
|
114 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
|
115 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
|
116 ), |
2398
8481e6266691
Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
2373
diff
changeset
|
117 rotated_ends AS ( |
8481e6266691
Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
2373
diff
changeset
|
118 SELECT ST_Collect(ST_Scale( |
8481e6266691
Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
2373
diff
changeset
|
119 ST_Translate(e, |
8481e6266691
Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
2373
diff
changeset
|
120 (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
|
121 (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
|
122 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
|
123 FROM axis_substring, area_subset, |
8481e6266691
Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
2373
diff
changeset
|
124 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
|
125 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
|
126 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
|
127 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
|
128 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
|
129 * 2) AS d (d)), |
1629
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
130 range_area AS ( |
2398
8481e6266691
Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
2373
diff
changeset
|
131 -- 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
|
132 SELECT (ST_Dump(ST_CollectionExtract( |
8481e6266691
Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
2373
diff
changeset
|
133 ST_Split(area, blade), 3))).geom |
8481e6266691
Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
2373
diff
changeset
|
134 FROM area_subset, rotated_ends) |
1983
f9f1babe52ae
Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents:
1811
diff
changeset
|
135 -- 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
|
136 -- around the clipped axis |
2425
4c16f5ad1905
Make area generation more robust
Tom Gottfried <tom@intevation.de>
parents:
2398
diff
changeset
|
137 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
|
138 FROM axis_substring, range_area |
2398
8481e6266691
Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
2373
diff
changeset
|
139 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
|
140 axis_substring.line) |
1629
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
141 $$ |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
142 LANGUAGE sql; |