Mercurial > gemma
diff schema/isrs_functions.sql @ 2431:48495bd3081d
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 |
line wrap: on
line diff
--- a/schema/isrs_functions.sql Thu Feb 28 17:30:26 2019 +0100 +++ b/schema/isrs_functions.sql Thu Feb 28 19:44:38 2019 +0100 @@ -26,7 +26,7 @@ area geometry ) RETURNS geometry AS $$ - WITH + WITH RECURSIVE -- Get coordinates of location codes points_geog AS ( SELECT geom FROM waterway.distance_marks_virtual @@ -37,9 +37,7 @@ SELECT best_utm(ST_Collect(geom::geometry)) AS z FROM points_geog), axis AS ( - -- Transform and sew together contiguous axis chunks - SELECT ST_LineMerge(ST_Collect(ST_Transform( - wtwaxs::geometry, z))) AS wtwaxs + SELECT id, ST_Transform(wtwaxs::geometry, z) AS wtwaxs FROM waterway.waterway_axis, utm_zone), -- In order to guarantee the following ST_Covers to work, -- snap distance mark coordinates to axis @@ -47,16 +45,49 @@ SELECT ST_ClosestPoint( wtwaxs, ST_Transform(points_geog.geom::geometry, z)) AS geom - FROM axis, points_geog, utm_zone), - axis_segment AS ( - -- select the contiguous axis on which distance marks lie - SELECT line - FROM ( - SELECT (ST_Dump(wtwaxs)).geom AS line - FROM axis) AS lines, + FROM points_geog, utm_zone, ( + SELECT ST_Collect(wtwaxs) AS wtwaxs + FROM axis) AS ax), + axis_snapped AS ( + -- Iteratively connect non-contiguous axis chunks + -- to find the contiguous axis on which given distance marks lie + (SELECT ARRAY[id] AS ids, wtwaxs + FROM axis, points + WHERE ST_Intersects( + ST_Buffer(axis.wtwaxs, 0.0001), points.geom) + FETCH FIRST ROW ONLY) + UNION + -- Connect endpoint of next linestring with closest + -- endpoint of merged linestring until a contiguous + -- linestring connecting both distance marks is build up + (SELECT refids || id, + ST_LineMerge(ST_Collect(ARRAY( + -- Linestring build up so far + SELECT refgeom + UNION + -- Fill eventual gap + SELECT ST_MakeLine( + ST_ClosestPoint( + ST_Boundary(refgeom), ST_Boundary(geom)), + ST_ClosestPoint( + ST_Boundary(geom), ST_Boundary(refgeom))) + UNION + -- Linestring to be added + SELECT geom))) + FROM axis_snapped AS axis_snapped (refids, refgeom), + axis AS axis (id, geom), (SELECT ST_Collect(points.geom) AS pts FROM points) AS points - WHERE ST_Covers(ST_Buffer(lines.line, 0.0001), points.pts)), + WHERE id <> ALL(refids) + AND NOT ST_Covers(ST_Buffer(refgeom, 0.0001), points.pts) + ORDER BY ST_Distance(ST_Boundary(refgeom), ST_Boundary(geom)) + FETCH FIRST ROW ONLY)), + axis_segment AS ( + -- Fetch end result from snapping + SELECT wtwaxs AS line + FROM axis_snapped + WHERE array_length(ids, 1) = ( + SELECT max(array_length(ids, 1)) FROM axis_snapped)), axis_substring AS ( -- Use linear referencing to clip axis between distance marks. -- Simplification is used to work-around the problem, that