Mercurial > gemma

annotate schema/isrs_functions.sql @ 2232:7936b46a88d4

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Handle invalid multipolygons in area generation from stretch
author Tom Gottfried <tom@intevation.de>
date Wed, 13 Feb 2019 11:44:27 +0100
parents f9f1babe52ae
children 4aabbf324e55
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
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;