Mercurial > gemma
annotate schema/updates/1467/01.improve_ISRSrange_axis.sql @ 5736:55892008ec96 default tip
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 |
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; |