Mercurial > gemma

comparison schema/isrs_functions.sql @ 2431:48495bd3081d

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Construct stretch area between distance marks also from non-contiguous axis Before, both distance marks were required to be next to the same linestring of the waterway axis.
author Tom Gottfried <tom@intevation.de>
date Thu, 28 Feb 2019 19:44:38 +0100
parents 4c16f5ad1905
children 7677a2850a2d
comparison
equal deleted inserted replaced
2430:2a93a8649751 2431:48495bd3081d
24 CREATE OR REPLACE FUNCTION ISRSrange_area( 24 CREATE OR REPLACE FUNCTION ISRSrange_area(
25 stretch isrsrange, 25 stretch isrsrange,
26 area geometry 26 area geometry
27 ) RETURNS geometry 27 ) RETURNS geometry
28 AS $$ 28 AS $$
29 WITH 29 WITH RECURSIVE
30 -- Get coordinates of location codes 30 -- Get coordinates of location codes
31 points_geog AS ( 31 points_geog AS (
32 SELECT geom FROM waterway.distance_marks_virtual 32 SELECT geom FROM waterway.distance_marks_virtual
33 WHERE location_code = lower(stretch) 33 WHERE location_code = lower(stretch)
34 OR location_code = upper(stretch)), 34 OR location_code = upper(stretch)),
35 utm_zone AS ( 35 utm_zone AS (
36 -- Find best matchting UTM zone 36 -- Find best matchting UTM zone
37 SELECT best_utm(ST_Collect(geom::geometry)) AS z 37 SELECT best_utm(ST_Collect(geom::geometry)) AS z
38 FROM points_geog), 38 FROM points_geog),
39 axis AS ( 39 axis AS (
40 -- Transform and sew together contiguous axis chunks 40 SELECT id, ST_Transform(wtwaxs::geometry, z) AS wtwaxs
41 SELECT ST_LineMerge(ST_Collect(ST_Transform(
42 wtwaxs::geometry, z))) AS wtwaxs
43 FROM waterway.waterway_axis, utm_zone), 41 FROM waterway.waterway_axis, utm_zone),
44 -- In order to guarantee the following ST_Covers to work, 42 -- In order to guarantee the following ST_Covers to work,
45 -- snap distance mark coordinates to axis 43 -- snap distance mark coordinates to axis
46 points AS ( 44 points AS (
47 SELECT ST_ClosestPoint( 45 SELECT ST_ClosestPoint(
48 wtwaxs, 46 wtwaxs,
49 ST_Transform(points_geog.geom::geometry, z)) AS geom 47 ST_Transform(points_geog.geom::geometry, z)) AS geom
50 FROM axis, points_geog, utm_zone), 48 FROM points_geog, utm_zone, (
51 axis_segment AS ( 49 SELECT ST_Collect(wtwaxs) AS wtwaxs
52 -- select the contiguous axis on which distance marks lie 50 FROM axis) AS ax),
53 SELECT line 51 axis_snapped AS (
54 FROM ( 52 -- Iteratively connect non-contiguous axis chunks
55 SELECT (ST_Dump(wtwaxs)).geom AS line 53 -- to find the contiguous axis on which given distance marks lie
56 FROM axis) AS lines, 54 (SELECT ARRAY[id] AS ids, wtwaxs
55 FROM axis, points
56 WHERE ST_Intersects(
57 ST_Buffer(axis.wtwaxs, 0.0001), points.geom)
58 FETCH FIRST ROW ONLY)
59 UNION
60 -- Connect endpoint of next linestring with closest
61 -- endpoint of merged linestring until a contiguous
62 -- linestring connecting both distance marks is build up
63 (SELECT refids || id,
64 ST_LineMerge(ST_Collect(ARRAY(
65 -- Linestring build up so far
66 SELECT refgeom
67 UNION
68 -- Fill eventual gap
69 SELECT ST_MakeLine(
70 ST_ClosestPoint(
71 ST_Boundary(refgeom), ST_Boundary(geom)),
72 ST_ClosestPoint(
73 ST_Boundary(geom), ST_Boundary(refgeom)))
74 UNION
75 -- Linestring to be added
76 SELECT geom)))
77 FROM axis_snapped AS axis_snapped (refids, refgeom),
78 axis AS axis (id, geom),
57 (SELECT ST_Collect(points.geom) AS pts 79 (SELECT ST_Collect(points.geom) AS pts
58 FROM points) AS points 80 FROM points) AS points
59 WHERE ST_Covers(ST_Buffer(lines.line, 0.0001), points.pts)), 81 WHERE id <> ALL(refids)
82 AND NOT ST_Covers(ST_Buffer(refgeom, 0.0001), points.pts)
83 ORDER BY ST_Distance(ST_Boundary(refgeom), ST_Boundary(geom))
84 FETCH FIRST ROW ONLY)),
85 axis_segment AS (
86 -- Fetch end result from snapping
87 SELECT wtwaxs AS line
88 FROM axis_snapped
89 WHERE array_length(ids, 1) = (
90 SELECT max(array_length(ids, 1)) FROM axis_snapped)),
60 axis_substring AS ( 91 axis_substring AS (
61 -- Use linear referencing to clip axis between distance marks. 92 -- Use linear referencing to clip axis between distance marks.
62 -- Simplification is used to work-around the problem, that 93 -- Simplification is used to work-around the problem, that
63 -- ST_LineSubstring might generate very small line segments at an 94 -- ST_LineSubstring might generate very small line segments at an
64 -- end of the resulting linestring, that significantly differ from 95 -- end of the resulting linestring, that significantly differ from