Mercurial > gemma
annotate schema/isrs_functions.sql @ 1664:819f67c31dfb
Imports: Stripped schema prefixes from table dependencies.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Sun, 23 Dec 2018 12:57:28 +0100 |
parents | 9d51f022b8ee |
children | 6646ba22c94a |
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, |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
50 (SELECT ST_Collect(from_point.geom, to_point.geom) AS pts |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
51 FROM from_point, to_point) AS points |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
52 WHERE ST_Covers(lines.line, points.pts)), |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
53 axis_substring AS ( |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
54 -- 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
|
55 SELECT ST_LineSubstring( |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
56 axis_segment.line, |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
57 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
|
58 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
|
59 ) AS line |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
60 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
|
61 range_area AS ( |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
62 -- 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
|
63 -- 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
|
64 -- 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
|
65 -- 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
|
66 -- be a multipolygon. |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
67 SELECT (ST_Dump(ST_Intersection( |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
68 ST_Buffer( |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
69 axis_substring.line, |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
70 ST_MaxDistance( |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
71 axis_substring.line, |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
72 ST_Transform(area, z)), |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
73 'endcap=flat'), |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
74 ST_Transform(area, z)))).geom |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
75 FROM axis_substring, utm_zone) |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
76 -- 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
|
77 -- around the clipped axis |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
78 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
|
79 FROM axis_substring, range_area |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
80 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
|
81 $$ |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
82 LANGUAGE sql; |