Mercurial > gemma

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