Mercurial > gemma

comparison schema/updates/1423/02.adapt_func.sql @ 5009:e8b2dc771f9e

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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;