Mercurial > gemma

annotate schema/updates/1466/01.fix_ISRSrange_axis.sql @ 5736:55892008ec96 default tip

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Fixed a bunch of corner cases in WG import.
author Sascha Wilde <wilde@sha-bang.de>
date Wed, 29 May 2024 19:02:42 +0200
parents 68358e4603c8
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
5513
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1 CREATE OR REPLACE FUNCTION ISRSrange_axis(
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
2 stretch isrsrange,
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
3 tolerance float
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
4 -- in m, up to which linestrings will be connected at their boundary
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
5 ) RETURNS geometry
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
6 AS $$
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
7 DECLARE z int;
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
8 DECLARE result_geom geometry;
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
9 BEGIN
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
10 -- Find best matchting UTM zone
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
11 z = best_utm(stretch);
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
12
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
13 CREATE TEMP TABLE axis AS
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
14 SELECT row_number() OVER () AS id,
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
15 geom AS wtwaxs,
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
16 ST_Boundary(geom) AS bdr
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
17 FROM waterway.waterway_axis,
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
18 ST_Dump(ST_Transform(wtwaxs::geometry, z))
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
19 WHERE validity @> current_timestamp;
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
20 CREATE INDEX axs_bdr ON axis USING GiST (bdr);
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
21 ANALYZE axis;
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
22
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
23 WITH RECURSIVE
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
24 -- In order to guarantee the following ST_Covers to work,
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
25 -- snap distance mark coordinates to axis
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
26 points0 AS (
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
27 SELECT ST_ClosestPoint(
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
28 wtwaxs,
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
29 ST_Transform(geom, z)) AS geom
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
30 FROM ST_Dump(ISRSrange_points(stretch)), (
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
31 SELECT ST_Collect(wtwaxs) AS wtwaxs
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
32 FROM axis) AS ax),
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
33 -- Ensure two distinct points on axis have been found
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
34 points AS (
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
35 SELECT geom
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
36 FROM points0
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
37 WHERE 2 = (SELECT count(DISTINCT geom) FROM points0)),
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
38 axis_snapped AS (
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
39 -- Iteratively connect non-contiguous axis chunks
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
40 -- to find the contiguous axis on which given distance marks lie
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
41 (SELECT ARRAY[id] AS ids, wtwaxs
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
42 FROM axis, points
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
43 WHERE ST_Intersects(
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
44 ST_Buffer(axis.wtwaxs, 0.0001), points.geom)
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
45 FETCH FIRST ROW ONLY)
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
46 UNION
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
47 -- Connect endpoint of next linestring with closest
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
48 -- endpoint of merged linestring until a contiguous
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
49 -- linestring connecting both distance marks is build up
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
50 (SELECT refids || id,
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
51 ST_LineMerge(ST_Collect(ARRAY(
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
52 -- Linestring build up so far
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
53 SELECT refgeom
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
54 UNION
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
55 -- Fill eventual gap
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
56 SELECT ST_ShortestLine(
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
57 ST_Boundary(refgeom), bdr)
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
58 UNION
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
59 -- Linestring to be added
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
60 SELECT geom)))
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
61 FROM axis_snapped AS axis_snapped (refids, refgeom),
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
62 axis AS axis (id, geom, bdr),
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
63 (SELECT ST_Collect(points.geom) AS pts
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
64 FROM points) AS points
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
65 WHERE id <> ALL(refids)
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
66 AND ST_DWithin(
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
67 ST_Boundary(refgeom), bdr, tolerance)
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
68 AND NOT ST_Covers(ST_Buffer(refgeom, 0.0001), points.pts)
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
69 ORDER BY ST_Boundary(refgeom) <-> bdr
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
70 FETCH FIRST ROW ONLY)),
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
71 axis_segment AS (
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
72 -- Fetch end result from snapping
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
73 SELECT wtwaxs AS line
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
74 FROM axis_snapped,
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
75 (SELECT ST_Collect(points.geom) AS pts
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
76 FROM points) AS points
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
77 -- Return end result only if both distance marks were connected
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
78 WHERE ST_Covers(ST_Buffer(wtwaxs, 0.0001), points.pts))
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
79 -- Use linear referencing to clip axis between distance marks.
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
80 -- Simplification is used to work-around the problem, that
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
81 -- ST_LineSubstring might generate very small line segments at an
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
82 -- end of the resulting linestring, that significantly differ from
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
83 -- the direction of the input linestring due to finite precision
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
84 -- of the calculation. The generated small segment of the
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
85 -- resulting line would lead e.g. to unexpected results in an area
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
86 -- generated by ISRSrange_area().
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
87 SELECT ST_SimplifyPreserveTopology(ST_LineSubstring(
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
88 axis_segment.line, min(fractions.f), max(fractions.f)),
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
89 0.0001) AS line
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
90 INTO STRICT result_geom
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
91 FROM axis_segment, LATERAL (
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
92 SELECT ST_LineLocatePoint(axis_segment.line, points.geom) AS f
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
93 FROM points) AS fractions
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
94 GROUP BY axis_segment.line;
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
95
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
96 -- Drop temporary table to avoid side effects on PostgreSQL's MVCC,
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
97 -- because otherwise subsequent invocations of the function will not see
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
98 -- changes on the underlying waterway.waterway_axis that might have
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
99 -- occured.
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
100 DROP TABLE axis;
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
101
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
102 RETURN result_geom;
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
103 END;
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
104 $$
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
105 LANGUAGE plpgsql
68358e4603c8 Use current axis only for calculating bottleneck areas
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
106 PARALLEL RESTRICTED;