Mercurial > gemma
comparison schema/updates/1423/02.adapt_func.sql @ 5009:e8b2dc771f9e
Store axis as MultiLinestring
MultiLinestrings could already be imported but we stored them as
multiple Linestrings with identical attributes and even stored
Linestrings with self-intersections as multiple single Linestrings
with identical attributes. Avoid both by storing as MultiLinestring.
In passing, removed unnecessary processing steps in the INSERT
statemet for the sys_admin case and ensured that attempts to
convert to valid simple features are made after transformation,
which might lead to invalid features.
Since isrsrange_axis() relies on single Linestrings for linear
referencing, add an extra ST_Dump().
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Wed, 11 Mar 2020 17:11:23 +0100 |
parents | |
children |
comparison
equal
deleted
inserted
replaced
5008:0b97f5301a17 | 5009:e8b2dc771f9e |
---|---|
1 CREATE OR REPLACE FUNCTION ISRSrange_axis( | |
2 stretch isrsrange, | |
3 tolerance float | |
4 -- in m, up to which linestrings will be connected at their boundary | |
5 ) RETURNS geometry | |
6 AS $$ | |
7 DECLARE z int; | |
8 DECLARE result_geom geometry; | |
9 BEGIN | |
10 -- Find best matchting UTM zone | |
11 z = best_utm(stretch); | |
12 | |
13 CREATE TEMP TABLE axis AS | |
14 SELECT row_number() OVER () AS id, | |
15 geom AS wtwaxs, | |
16 ST_Boundary(geom) AS bdr | |
17 FROM waterway.waterway_axis, | |
18 ST_Dump(ST_Transform(wtwaxs::geometry, z)); | |
19 CREATE INDEX axs_bdr ON axis USING GiST (bdr); | |
20 ANALYZE axis; | |
21 | |
22 WITH RECURSIVE | |
23 -- In order to guarantee the following ST_Covers to work, | |
24 -- snap distance mark coordinates to axis | |
25 points0 AS ( | |
26 SELECT ST_ClosestPoint( | |
27 wtwaxs, | |
28 ST_Transform(geom, z)) AS geom | |
29 FROM ST_Dump(ISRSrange_points(stretch)), ( | |
30 SELECT ST_Collect(wtwaxs) AS wtwaxs | |
31 FROM axis) AS ax), | |
32 -- Ensure two distinct points on axis have been found | |
33 points AS ( | |
34 SELECT geom | |
35 FROM points0 | |
36 WHERE 2 = (SELECT count(DISTINCT geom) FROM points0)), | |
37 axis_snapped AS ( | |
38 -- Iteratively connect non-contiguous axis chunks | |
39 -- to find the contiguous axis on which given distance marks lie | |
40 (SELECT ARRAY[id] AS ids, wtwaxs | |
41 FROM axis, points | |
42 WHERE ST_Intersects( | |
43 ST_Buffer(axis.wtwaxs, 0.0001), points.geom) | |
44 FETCH FIRST ROW ONLY) | |
45 UNION | |
46 -- Connect endpoint of next linestring with closest | |
47 -- endpoint of merged linestring until a contiguous | |
48 -- linestring connecting both distance marks is build up | |
49 (SELECT refids || id, | |
50 ST_LineMerge(ST_Collect(ARRAY( | |
51 -- Linestring build up so far | |
52 SELECT refgeom | |
53 UNION | |
54 -- Fill eventual gap | |
55 SELECT ST_ShortestLine( | |
56 ST_Boundary(refgeom), bdr) | |
57 UNION | |
58 -- Linestring to be added | |
59 SELECT geom))) | |
60 FROM axis_snapped AS axis_snapped (refids, refgeom), | |
61 axis AS axis (id, geom, bdr), | |
62 (SELECT ST_Collect(points.geom) AS pts | |
63 FROM points) AS points | |
64 WHERE id <> ALL(refids) | |
65 AND ST_DWithin( | |
66 ST_Boundary(refgeom), bdr, tolerance) | |
67 AND NOT ST_Covers(ST_Buffer(refgeom, 0.0001), points.pts) | |
68 ORDER BY ST_Boundary(refgeom) <-> bdr | |
69 FETCH FIRST ROW ONLY)), | |
70 axis_segment AS ( | |
71 -- Fetch end result from snapping | |
72 SELECT wtwaxs AS line | |
73 FROM axis_snapped, | |
74 (SELECT ST_Collect(points.geom) AS pts | |
75 FROM points) AS points | |
76 -- Return end result only if both distance marks were connected | |
77 WHERE ST_Covers(ST_Buffer(wtwaxs, 0.0001), points.pts)) | |
78 -- Use linear referencing to clip axis between distance marks. | |
79 -- Simplification is used to work-around the problem, that | |
80 -- ST_LineSubstring might generate very small line segments at an | |
81 -- end of the resulting linestring, that significantly differ from | |
82 -- the direction of the input linestring due to finite precision | |
83 -- of the calculation. The generated small segment of the | |
84 -- resulting line would lead e.g. to unexpected results in an area | |
85 -- generated by ISRSrange_area(). | |
86 SELECT ST_SimplifyPreserveTopology(ST_LineSubstring( | |
87 axis_segment.line, min(fractions.f), max(fractions.f)), | |
88 0.0001) AS line | |
89 INTO STRICT result_geom | |
90 FROM axis_segment, LATERAL ( | |
91 SELECT ST_LineLocatePoint(axis_segment.line, points.geom) AS f | |
92 FROM points) AS fractions | |
93 GROUP BY axis_segment.line; | |
94 | |
95 -- Drop temporary table to avoid side effects on PostgreSQL's MVCC, | |
96 -- because otherwise subsequent invocations of the function will not see | |
97 -- changes on the underlying waterway.waterway_axis that might have | |
98 -- occured. | |
99 DROP TABLE axis; | |
100 | |
101 RETURN result_geom; | |
102 END; | |
103 $$ | |
104 LANGUAGE plpgsql | |
105 PARALLEL RESTRICTED; |