Mercurial > gemma
annotate schema/updates/1423/02.adapt_func.sql @ 5454:b4216db975e3 uiimprovements
fix layout for contextbox content.
author | Thomas Junk <thomas.junk@intevation.de> |
---|---|
date | Wed, 14 Jul 2021 16:21:27 +0200 |
parents | e8b2dc771f9e |
children |
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; |