Mercurial > gemma

annotate schema/updates/1466/01.fix_ISRSrange_axis.sql @ 5513:68358e4603c8

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Use current axis only for calculating bottleneck areas This is a fixup of rev. cf25b23e3eec, which introduced historic data for the waterway axis but missed to take this into account in the calculation of bottleneck areas, leading to sometimes excessive runtime and bad results due to multiple (almost) equal axis geometries being considered as candidates in the bottleneck stretch. The database migration tries to recalculate all bottleneck areas, while some might fail that did not fail on import. A warning message is emitted for these and the area is left untouched.
author Tom Gottfried <tom@intevation.de>
date Tue, 19 Oct 2021 13:12:39 +0200
parents
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;