Mercurial > gemma
annotate schema/isrs_functions.sql @ 2367:0aedae39726e
Print templates: Removed primary key from templates table and add a unique constraint to template name and country instead to make possible that different countries can use templates with same name.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Thu, 21 Feb 2019 10:46:38 +0100 |
parents | 7936b46a88d4 |
children | 4aabbf324e55 |
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 |
29a0fe218af9
Add missing license header
Tom Gottfried <tom@intevation.de>
parents:
1717
diff
changeset
|
7 -- Copyright (C) 2018 by via donau |
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 $$ |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
29 WITH |
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 |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
31 from_geog AS ( |
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 |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
33 WHERE location_code = lower(stretch)), |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
34 to_geog AS ( |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
35 SELECT geom FROM waterway.distance_marks_virtual |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
36 WHERE location_code = upper(stretch)), |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
37 utm_zone AS ( |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
38 -- Find best matchting UTM zone |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
39 SELECT best_utm(ST_Collect( |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
40 from_geog.geom::geometry, |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
41 to_geog.geom::geometry)) AS z |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
42 FROM from_geog, to_geog), |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
43 axis AS ( |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
44 -- Transform and sew together contiguous axis chunks |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
45 SELECT ST_LineMerge(ST_Collect(ST_Transform( |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
46 wtwaxs::geometry, z))) AS wtwaxs |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
47 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
|
48 -- 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
|
49 -- snap distance mark coordinates to axis |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
50 from_point AS ( |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
51 SELECT ST_ClosestPoint( |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
52 wtwaxs, |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
53 ST_Transform(from_geog.geom::geometry, z)) AS geom |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
54 FROM axis, from_geog, utm_zone), |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
55 to_point AS ( |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
56 SELECT ST_ClosestPoint( |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
57 wtwaxs, |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
58 ST_Transform(to_geog.geom::geometry, z)) AS geom |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
59 FROM axis, to_geog, utm_zone), |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
60 axis_segment AS ( |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
61 -- select the contiguous axis on which distance marks lie |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
62 SELECT line |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
63 FROM ( |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
64 SELECT (ST_Dump(wtwaxs)).geom AS line |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
65 FROM axis) AS lines, |
1721
837d12b49dfd
Fixed fix for ISRSrange_area() function.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1717
diff
changeset
|
66 (SELECT ST_Collect(from_point.geom, to_point.geom) AS pts |
1629
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
67 FROM from_point, to_point) AS points |
1723
50548a6df009
Fixed fix of fix.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1722
diff
changeset
|
68 WHERE ST_Covers(ST_Buffer(lines.line, 0.0001), points.pts)), |
1629
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
69 axis_substring AS ( |
1811
c21f72775f6b
Fix numerical problems in stretch area generation
Tom Gottfried <tom@intevation.de>
parents:
1723
diff
changeset
|
70 -- 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
|
71 -- 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
|
72 -- 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
|
73 -- 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
|
74 -- 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
|
75 -- 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
|
76 -- 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
|
77 -- endcap=flat in the CTE below. |
c21f72775f6b
Fix numerical problems in stretch area generation
Tom Gottfried <tom@intevation.de>
parents:
1723
diff
changeset
|
78 SELECT ST_SimplifyPreserveTopology(ST_LineSubstring( |
1629
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
79 axis_segment.line, |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
80 ST_LineLocatePoint(axis_segment.line, from_point.geom), |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
81 ST_LineLocatePoint(axis_segment.line, to_point.geom) |
1811
c21f72775f6b
Fix numerical problems in stretch area generation
Tom Gottfried <tom@intevation.de>
parents:
1723
diff
changeset
|
82 ), 0.0001) AS line |
1629
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
83 FROM axis_segment, from_point, to_point), |
2232
7936b46a88d4
Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
84 area_subset AS ( |
7936b46a88d4
Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
85 -- 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
|
86 -- 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
|
87 -- problems with invalid/self-intersecting multipolygons |
7936b46a88d4
Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
88 SELECT ST_Union(a_dmp.geom) AS area |
7936b46a88d4
Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
89 FROM axis_substring, utm_zone, |
7936b46a88d4
Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
90 ST_Dump(ST_Transform(area, z)) AS a_dmp |
7936b46a88d4
Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
91 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
|
92 ), |
1629
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
93 range_area AS ( |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
94 -- Create a buffer around the clipped axis, as large as it could |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
95 -- potentially be intersecting with the area polygon that |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
96 -- intersects with the clipped axis. Get the intersection of that |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
97 -- buffer with the area polygon, which can potentially |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
98 -- be a multipolygon. |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
99 SELECT (ST_Dump(ST_Intersection( |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
100 ST_Buffer( |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
101 axis_substring.line, |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
102 ST_MaxDistance( |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
103 axis_substring.line, |
2232
7936b46a88d4
Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
104 area_subset.area), |
1629
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
105 'endcap=flat'), |
2232
7936b46a88d4
Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
106 area_subset.area))).geom |
7936b46a88d4
Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
107 FROM axis_substring, area_subset) |
1983
f9f1babe52ae
Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents:
1811
diff
changeset
|
108 -- 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
|
109 -- around the clipped axis |
1983
f9f1babe52ae
Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents:
1811
diff
changeset
|
110 SELECT ST_Collect(ST_Transform(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
|
111 FROM axis_substring, range_area |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
112 WHERE ST_Intersects(range_area.geom, axis_substring.line) |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
113 $$ |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
114 LANGUAGE sql; |