Mercurial > gemma

comparison schema/isrs_functions.sql @ 2956:974122125a76

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Let it be an error if closest points of DISMARs on axis are equal This might be the case e.g. if both distance marks are very far away from the available axis geometries. Instead of returning a point in such a case, which would likely be an unexpected result, raise an exception by means of STRICT.
author Tom Gottfried <tom@intevation.de>
date Mon, 08 Apr 2019 14:53:09 +0200
parents dc4fae4bdb8f
children 524f1382558a
comparison
equal deleted inserted replaced
2955:e9ff3e8d3c46 2956:974122125a76
63 axis AS ( 63 axis AS (
64 SELECT id, ST_Transform(wtwaxs::geometry, z) AS wtwaxs 64 SELECT id, ST_Transform(wtwaxs::geometry, z) AS wtwaxs
65 FROM waterway.waterway_axis, utm_zone), 65 FROM waterway.waterway_axis, utm_zone),
66 -- In order to guarantee the following ST_Covers to work, 66 -- In order to guarantee the following ST_Covers to work,
67 -- snap distance mark coordinates to axis 67 -- snap distance mark coordinates to axis
68 points AS ( 68 points0 AS (
69 SELECT ST_ClosestPoint( 69 SELECT ST_ClosestPoint(
70 wtwaxs, 70 wtwaxs,
71 ST_Transform(geom, z)) AS geom 71 ST_Transform(geom, z)) AS geom
72 FROM ST_Dump(ISRSrange_points(stretch)), utm_zone, ( 72 FROM ST_Dump(ISRSrange_points(stretch)), utm_zone, (
73 SELECT ST_Collect(wtwaxs) AS wtwaxs 73 SELECT ST_Collect(wtwaxs) AS wtwaxs
74 FROM axis) AS ax), 74 FROM axis) AS ax),
75 -- Ensure two distinct points on axis have been found
76 points AS (
77 SELECT geom
78 FROM points0
79 WHERE 2 = (SELECT count(DISTINCT geom) FROM points0)),
75 axis_snapped AS ( 80 axis_snapped AS (
76 -- Iteratively connect non-contiguous axis chunks 81 -- Iteratively connect non-contiguous axis chunks
77 -- to find the contiguous axis on which given distance marks lie 82 -- to find the contiguous axis on which given distance marks lie
78 (SELECT ARRAY[id] AS ids, wtwaxs 83 (SELECT ARRAY[id] AS ids, wtwaxs
79 FROM axis, points 84 FROM axis, points
117 -- Simplification is used to work-around the problem, that 122 -- Simplification is used to work-around the problem, that
118 -- ST_LineSubstring might generate very small line segments at an 123 -- ST_LineSubstring might generate very small line segments at an
119 -- end of the resulting linestring, that significantly differ from 124 -- end of the resulting linestring, that significantly differ from
120 -- the direction of the input linestring due to finite precision 125 -- the direction of the input linestring due to finite precision
121 -- of the calculation. The generated small segment of the 126 -- of the calculation. The generated small segment of the
122 -- resulting line leads to unexpected results of the buffer with 127 -- resulting line would lead e.g. to unexpected results in an area
123 -- endcap=flat in the CTE below. 128 -- generated by ISRSrange_area().
124 SELECT ST_SimplifyPreserveTopology(ST_LineSubstring( 129 SELECT ST_SimplifyPreserveTopology(ST_LineSubstring(
125 axis_segment.line, min(fractions.f), max(fractions.f)), 130 axis_segment.line, min(fractions.f), max(fractions.f)),
126 0.0001) AS line 131 0.0001) AS line
127 INTO STRICT result_geom 132 INTO STRICT result_geom
128 FROM axis_segment, LATERAL ( 133 FROM axis_segment, LATERAL (