Mercurial > gemma

annotate schema/isrs_functions.sql @ 1717:6646ba22c94a

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Made ISRSrange_area a bit more tolerant to work with reql world data. Kudos to SLT for implementing the fix.
author Sascha Wilde <wilde@intevation.de>
date Tue, 08 Jan 2019 17:29:53 +0100
parents 9d51f022b8ee
children 29a0fe218af9 837d12b49dfd
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
1629
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1 -- 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
2 -- 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
3 -- 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
4 -- 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
5 -- 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
6 -- 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
7 -- (use e.g. waterway area or fairway dimensions).
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
8 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
9 stretch isrsrange,
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
10 area geometry
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
11 ) RETURNS geometry
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
12 AS $$
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
13 WITH
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
14 -- Get coordinates of location codes
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
15 from_geog AS (
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
16 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
17 WHERE location_code = lower(stretch)),
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
18 to_geog AS (
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
19 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
20 WHERE location_code = upper(stretch)),
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
21 utm_zone AS (
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
22 -- Find best matchting UTM zone
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
23 SELECT best_utm(ST_Collect(
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
24 from_geog.geom::geometry,
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
25 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
26 FROM from_geog, to_geog),
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
27 axis AS (
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
28 -- 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
29 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
30 wtwaxs::geometry, z))) AS wtwaxs
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
31 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
32 -- 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
33 -- 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
34 from_point AS (
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
35 SELECT ST_ClosestPoint(
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
36 wtwaxs,
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
37 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
38 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
39 to_point AS (
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
40 SELECT ST_ClosestPoint(
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
41 wtwaxs,
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
42 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
43 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
44 axis_segment AS (
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
45 -- 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
46 SELECT line
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
47 FROM (
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
48 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
49 FROM axis) AS lines,
1717
6646ba22c94a Made ISRSrange_area a bit more tolerant to work with reql world data.
Sascha Wilde <wilde@intevation.de>
parents: 1629
diff changeset
50 (SELECT ST_Collect(st_buffer(from_point.geom, 0.0001),
6646ba22c94a Made ISRSrange_area a bit more tolerant to work with reql world data.
Sascha Wilde <wilde@intevation.de>
parents: 1629
diff changeset
51 st_buffer(to_point.geom, 0.0001)) AS pts
1629
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
52 FROM from_point, to_point) AS points
1717
6646ba22c94a Made ISRSrange_area a bit more tolerant to work with reql world data.
Sascha Wilde <wilde@intevation.de>
parents: 1629
diff changeset
53 WHERE ST_intersects(lines.line, points.pts)),
1629
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
54 axis_substring AS (
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
55 -- Use linear referencing to clip axis between distance marks
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
56 SELECT ST_LineSubstring(
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
57 axis_segment.line,
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
58 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
59 ST_LineLocatePoint(axis_segment.line, to_point.geom)
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
60 ) AS line
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
61 FROM axis_segment, from_point, to_point),
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
62 range_area AS (
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
63 -- 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
64 -- 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
65 -- 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
66 -- 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
67 -- be a multipolygon.
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
68 SELECT (ST_Dump(ST_Intersection(
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
69 ST_Buffer(
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
70 axis_substring.line,
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
71 ST_MaxDistance(
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
72 axis_substring.line,
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
73 ST_Transform(area, z)),
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
74 'endcap=flat'),
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
75 ST_Transform(area, z)))).geom
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
76 FROM axis_substring, utm_zone)
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
77 -- From the polygons returned by the last CTE, select only the one
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
78 -- around the clipped axis
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
79 SELECT ST_Transform(range_area.geom, ST_SRID(area))
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
80 FROM axis_substring, range_area
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
81 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
82 $$
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
83 LANGUAGE sql;