Mercurial > gemma

annotate schema/updates/1423/02.adapt_func.sql @ 5666:37c2354a6024 clickable-links

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