gemma: schema/isrs_functions.sql annotate

annotate schema/isrs_functions.sql @ 2956:974122125a76

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

rev	line source
1720 29a0fe218af9 Add missing license header Tom Gottfried <tom@intevation.de> parents: 1717 diff changeset	1 -- This is Free Software under GNU Affero General Public License v >= 3.0
29a0fe218af9 Add missing license header Tom Gottfried <tom@intevation.de> parents: 1717 diff changeset	2 -- without warranty, see README.md and license for details.
29a0fe218af9 Add missing license header Tom Gottfried <tom@intevation.de> parents: 1717 diff changeset	3
29a0fe218af9 Add missing license header Tom Gottfried <tom@intevation.de> parents: 1717 diff changeset	4 -- SPDX-License-Identifier: AGPL-3.0-or-later
29a0fe218af9 Add missing license header Tom Gottfried <tom@intevation.de> parents: 1717 diff changeset	5 -- License-Filename: LICENSES/AGPL-3.0.txt
29a0fe218af9 Add missing license header Tom Gottfried <tom@intevation.de> parents: 1717 diff changeset	6
2373 4aabbf324e55 Fix cutting of axis segment between two distance marks Tom Gottfried <tom@intevation.de> parents: 2232 diff changeset	7 -- Copyright (C) 2018, 2019 by via donau
1720 29a0fe218af9 Add missing license header Tom Gottfried <tom@intevation.de> parents: 1717 diff changeset	8 -- – Österreichische Wasserstraßen-Gesellschaft mbH
29a0fe218af9 Add missing license header Tom Gottfried <tom@intevation.de> parents: 1717 diff changeset	9 -- Software engineering by Intevation GmbH
29a0fe218af9 Add missing license header Tom Gottfried <tom@intevation.de> parents: 1717 diff changeset	10
29a0fe218af9 Add missing license header Tom Gottfried <tom@intevation.de> parents: 1717 diff changeset	11 -- Author(s):
29a0fe218af9 Add missing license header Tom Gottfried <tom@intevation.de> parents: 1717 diff changeset	12 -- * Tom Gottfried <tom@intevation.de>
29a0fe218af9 Add missing license header Tom Gottfried <tom@intevation.de> parents: 1717 diff changeset	13 -- * Sascha Wilde <wilde@intevation.de>
29a0fe218af9 Add missing license header Tom Gottfried <tom@intevation.de> parents: 1717 diff changeset	14
2455 54c9fe587fe6 Subdivide SQL function to prepare for improved error handling Tom Gottfried <tom@intevation.de> parents: 2450 diff changeset	15 CREATE OR REPLACE FUNCTION best_utm(stretch isrsrange) RETURNS integer
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling Tom Gottfried <tom@intevation.de> parents: 2450 diff changeset	16 AS $$
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling Tom Gottfried <tom@intevation.de> parents: 2450 diff changeset	17 SELECT best_utm(ST_Collect(geom::geometry))
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling Tom Gottfried <tom@intevation.de> parents: 2450 diff changeset	18 FROM waterway.distance_marks_virtual
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling Tom Gottfried <tom@intevation.de> parents: 2450 diff changeset	19 WHERE location_code IN (lower(stretch), upper(stretch))
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling Tom Gottfried <tom@intevation.de> parents: 2450 diff changeset	20 $$
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling Tom Gottfried <tom@intevation.de> parents: 2450 diff changeset	21 LANGUAGE sql
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling Tom Gottfried <tom@intevation.de> parents: 2450 diff changeset	22 STABLE PARALLEL SAFE;
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling Tom Gottfried <tom@intevation.de> parents: 2450 diff changeset	23
2535 73c8762cee60 Stretch area generation: Throw errors instead of returning NULL Tom Gottfried <tom@intevation.de> parents: 2514 diff changeset	24 -- Return a multipoint with coordinates of stretch end points
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL Tom Gottfried <tom@intevation.de> parents: 2514 diff changeset	25 CREATE OR REPLACE FUNCTION ISRSrange_points(
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL Tom Gottfried <tom@intevation.de> parents: 2514 diff changeset	26 stretch isrsrange
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL Tom Gottfried <tom@intevation.de> parents: 2514 diff changeset	27 ) RETURNS geometry
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL Tom Gottfried <tom@intevation.de> parents: 2514 diff changeset	28 AS $$
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL Tom Gottfried <tom@intevation.de> parents: 2514 diff changeset	29 DECLARE result_geom geometry;
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL Tom Gottfried <tom@intevation.de> parents: 2514 diff changeset	30 BEGIN
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL Tom Gottfried <tom@intevation.de> parents: 2514 diff changeset	31 SELECT geom
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL Tom Gottfried <tom@intevation.de> parents: 2514 diff changeset	32 INTO STRICT result_geom
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL Tom Gottfried <tom@intevation.de> parents: 2514 diff changeset	33 FROM (
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL Tom Gottfried <tom@intevation.de> parents: 2514 diff changeset	34 SELECT ST_Collect(CAST(geom AS geometry)) AS geom
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL Tom Gottfried <tom@intevation.de> parents: 2514 diff changeset	35 FROM waterway.distance_marks_virtual
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL Tom Gottfried <tom@intevation.de> parents: 2514 diff changeset	36 WHERE location_code IN(lower(stretch), upper(stretch))) AS pts
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL Tom Gottfried <tom@intevation.de> parents: 2514 diff changeset	37 -- Ensure both have been found
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL Tom Gottfried <tom@intevation.de> parents: 2514 diff changeset	38 WHERE ST_NumGeometries(geom) = 2;
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL Tom Gottfried <tom@intevation.de> parents: 2514 diff changeset	39
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL Tom Gottfried <tom@intevation.de> parents: 2514 diff changeset	40 RETURN result_geom;
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL Tom Gottfried <tom@intevation.de> parents: 2514 diff changeset	41 END;
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL Tom Gottfried <tom@intevation.de> parents: 2514 diff changeset	42 $$
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL Tom Gottfried <tom@intevation.de> parents: 2514 diff changeset	43 LANGUAGE plpgsql
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL Tom Gottfried <tom@intevation.de> parents: 2514 diff changeset	44 STABLE PARALLEL SAFE;
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL Tom Gottfried <tom@intevation.de> parents: 2514 diff changeset	45
2455 54c9fe587fe6 Subdivide SQL function to prepare for improved error handling Tom Gottfried <tom@intevation.de> parents: 2450 diff changeset	46 -- Clip waterway axis to a stretch given by a pair of ISRS location codes.
1629 9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: diff changeset	47 -- Uses the table waterway.distance_marks_virtual to map ISRS location codes
2455 54c9fe587fe6 Subdivide SQL function to prepare for improved error handling Tom Gottfried <tom@intevation.de> parents: 2450 diff changeset	48 -- to their geo-location.
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling Tom Gottfried <tom@intevation.de> parents: 2450 diff changeset	49 -- Distance marks are assumed to be near the axis.
2563 dc4fae4bdb8f Expose axis snapping tolerance to users Tom Gottfried <tom@intevation.de> parents: 2535 diff changeset	50 -- Returns the axis geometry transformed to the best matching UTM zone.
2514 79f4a20e31c2 Introduce distance limit up to which axis linestrings are connected Tom Gottfried <tom@intevation.de> parents: 2455 diff changeset	51 CREATE OR REPLACE FUNCTION ISRSrange_axis(
79f4a20e31c2 Introduce distance limit up to which axis linestrings are connected Tom Gottfried <tom@intevation.de> parents: 2455 diff changeset	52 stretch isrsrange,
79f4a20e31c2 Introduce distance limit up to which axis linestrings are connected Tom Gottfried <tom@intevation.de> parents: 2455 diff changeset	53 tolerance float
79f4a20e31c2 Introduce distance limit up to which axis linestrings are connected Tom Gottfried <tom@intevation.de> parents: 2455 diff changeset	54 -- in m, up to which linestrings will be connected at their boundary
79f4a20e31c2 Introduce distance limit up to which axis linestrings are connected Tom Gottfried <tom@intevation.de> parents: 2455 diff changeset	55 ) RETURNS geometry
1629 9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: diff changeset	56 AS $$
2535 73c8762cee60 Stretch area generation: Throw errors instead of returning NULL Tom Gottfried <tom@intevation.de> parents: 2514 diff changeset	57 DECLARE result_geom geometry;
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL Tom Gottfried <tom@intevation.de> parents: 2514 diff changeset	58 BEGIN
2431 48495bd3081d Construct stretch area between distance marks also from non-contiguous axis Tom Gottfried <tom@intevation.de> parents: 2425 diff changeset	59 WITH RECURSIVE
1629 9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: diff changeset	60 utm_zone AS (
9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: diff changeset	61 -- Find best matchting UTM zone
2455 54c9fe587fe6 Subdivide SQL function to prepare for improved error handling Tom Gottfried <tom@intevation.de> parents: 2450 diff changeset	62 SELECT best_utm(stretch) AS z),
1629 9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: diff changeset	63 axis AS (
2431 48495bd3081d Construct stretch area between distance marks also from non-contiguous axis Tom Gottfried <tom@intevation.de> parents: 2425 diff changeset	64 SELECT id, ST_Transform(wtwaxs::geometry, z) AS wtwaxs
1629 9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: diff changeset	65 FROM waterway.waterway_axis, utm_zone),
9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: diff changeset	66 -- In order to guarantee the following ST_Covers to work,
9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: diff changeset	67 -- snap distance mark coordinates to axis
2956 974122125a76 Let it be an error if closest points of DISMARs on axis are equal Tom Gottfried <tom@intevation.de> parents: 2563 diff changeset	68 points0 AS (
1629 9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: diff changeset	69 SELECT ST_ClosestPoint(
9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: diff changeset	70 wtwaxs,
2535 73c8762cee60 Stretch area generation: Throw errors instead of returning NULL Tom Gottfried <tom@intevation.de> parents: 2514 diff changeset	71 ST_Transform(geom, z)) AS geom
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL Tom Gottfried <tom@intevation.de> parents: 2514 diff changeset	72 FROM ST_Dump(ISRSrange_points(stretch)), utm_zone, (
2431 48495bd3081d Construct stretch area between distance marks also from non-contiguous axis Tom Gottfried <tom@intevation.de> parents: 2425 diff changeset	73 SELECT ST_Collect(wtwaxs) AS wtwaxs
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis Tom Gottfried <tom@intevation.de> parents: 2425 diff changeset	74 FROM axis) AS ax),
2956 974122125a76 Let it be an error if closest points of DISMARs on axis are equal Tom Gottfried <tom@intevation.de> parents: 2563 diff changeset	75 -- Ensure two distinct points on axis have been found
974122125a76 Let it be an error if closest points of DISMARs on axis are equal Tom Gottfried <tom@intevation.de> parents: 2563 diff changeset	76 points AS (
974122125a76 Let it be an error if closest points of DISMARs on axis are equal Tom Gottfried <tom@intevation.de> parents: 2563 diff changeset	77 SELECT geom
974122125a76 Let it be an error if closest points of DISMARs on axis are equal Tom Gottfried <tom@intevation.de> parents: 2563 diff changeset	78 FROM points0
974122125a76 Let it be an error if closest points of DISMARs on axis are equal Tom Gottfried <tom@intevation.de> parents: 2563 diff changeset	79 WHERE 2 = (SELECT count(DISTINCT geom) FROM points0)),
2431 48495bd3081d Construct stretch area between distance marks also from non-contiguous axis Tom Gottfried <tom@intevation.de> parents: 2425 diff changeset	80 axis_snapped AS (
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis Tom Gottfried <tom@intevation.de> parents: 2425 diff changeset	81 -- Iteratively connect non-contiguous axis chunks
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis Tom Gottfried <tom@intevation.de> parents: 2425 diff changeset	82 -- to find the contiguous axis on which given distance marks lie
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis Tom Gottfried <tom@intevation.de> parents: 2425 diff changeset	83 (SELECT ARRAY[id] AS ids, wtwaxs
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis Tom Gottfried <tom@intevation.de> parents: 2425 diff changeset	84 FROM axis, points
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis Tom Gottfried <tom@intevation.de> parents: 2425 diff changeset	85 WHERE ST_Intersects(
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis Tom Gottfried <tom@intevation.de> parents: 2425 diff changeset	86 ST_Buffer(axis.wtwaxs, 0.0001), points.geom)
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis Tom Gottfried <tom@intevation.de> parents: 2425 diff changeset	87 FETCH FIRST ROW ONLY)
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis Tom Gottfried <tom@intevation.de> parents: 2425 diff changeset	88 UNION
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis Tom Gottfried <tom@intevation.de> parents: 2425 diff changeset	89 -- Connect endpoint of next linestring with closest
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis Tom Gottfried <tom@intevation.de> parents: 2425 diff changeset	90 -- endpoint of merged linestring until a contiguous
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis Tom Gottfried <tom@intevation.de> parents: 2425 diff changeset	91 -- linestring connecting both distance marks is build up
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis Tom Gottfried <tom@intevation.de> parents: 2425 diff changeset	92 (SELECT refids \|\| id,
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis Tom Gottfried <tom@intevation.de> parents: 2425 diff changeset	93 ST_LineMerge(ST_Collect(ARRAY(
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis Tom Gottfried <tom@intevation.de> parents: 2425 diff changeset	94 -- Linestring build up so far
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis Tom Gottfried <tom@intevation.de> parents: 2425 diff changeset	95 SELECT refgeom
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis Tom Gottfried <tom@intevation.de> parents: 2425 diff changeset	96 UNION
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis Tom Gottfried <tom@intevation.de> parents: 2425 diff changeset	97 -- Fill eventual gap
2450 7677a2850a2d Substitute unnecessarily home-made shortest line Tom Gottfried <tom@intevation.de> parents: 2431 diff changeset	98 SELECT ST_ShortestLine(
7677a2850a2d Substitute unnecessarily home-made shortest line Tom Gottfried <tom@intevation.de> parents: 2431 diff changeset	99 ST_Boundary(refgeom), ST_Boundary(geom))
2431 48495bd3081d Construct stretch area between distance marks also from non-contiguous axis Tom Gottfried <tom@intevation.de> parents: 2425 diff changeset	100 UNION
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis Tom Gottfried <tom@intevation.de> parents: 2425 diff changeset	101 -- Linestring to be added
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis Tom Gottfried <tom@intevation.de> parents: 2425 diff changeset	102 SELECT geom)))
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis Tom Gottfried <tom@intevation.de> parents: 2425 diff changeset	103 FROM axis_snapped AS axis_snapped (refids, refgeom),
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis Tom Gottfried <tom@intevation.de> parents: 2425 diff changeset	104 axis AS axis (id, geom),
2373 4aabbf324e55 Fix cutting of axis segment between two distance marks Tom Gottfried <tom@intevation.de> parents: 2232 diff changeset	105 (SELECT ST_Collect(points.geom) AS pts
4aabbf324e55 Fix cutting of axis segment between two distance marks Tom Gottfried <tom@intevation.de> parents: 2232 diff changeset	106 FROM points) AS points
2431 48495bd3081d Construct stretch area between distance marks also from non-contiguous axis Tom Gottfried <tom@intevation.de> parents: 2425 diff changeset	107 WHERE id <> ALL(refids)
2514 79f4a20e31c2 Introduce distance limit up to which axis linestrings are connected Tom Gottfried <tom@intevation.de> parents: 2455 diff changeset	108 AND ST_DWithin(
79f4a20e31c2 Introduce distance limit up to which axis linestrings are connected Tom Gottfried <tom@intevation.de> parents: 2455 diff changeset	109 ST_Boundary(refgeom), ST_Boundary(geom), tolerance)
2431 48495bd3081d Construct stretch area between distance marks also from non-contiguous axis Tom Gottfried <tom@intevation.de> parents: 2425 diff changeset	110 AND NOT ST_Covers(ST_Buffer(refgeom, 0.0001), points.pts)
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis Tom Gottfried <tom@intevation.de> parents: 2425 diff changeset	111 ORDER BY ST_Distance(ST_Boundary(refgeom), ST_Boundary(geom))
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis Tom Gottfried <tom@intevation.de> parents: 2425 diff changeset	112 FETCH FIRST ROW ONLY)),
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis Tom Gottfried <tom@intevation.de> parents: 2425 diff changeset	113 axis_segment AS (
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis Tom Gottfried <tom@intevation.de> parents: 2425 diff changeset	114 -- Fetch end result from snapping
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis Tom Gottfried <tom@intevation.de> parents: 2425 diff changeset	115 SELECT wtwaxs AS line
2535 73c8762cee60 Stretch area generation: Throw errors instead of returning NULL Tom Gottfried <tom@intevation.de> parents: 2514 diff changeset	116 FROM axis_snapped,
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL Tom Gottfried <tom@intevation.de> parents: 2514 diff changeset	117 (SELECT ST_Collect(points.geom) AS pts
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL Tom Gottfried <tom@intevation.de> parents: 2514 diff changeset	118 FROM points) AS points
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL Tom Gottfried <tom@intevation.de> parents: 2514 diff changeset	119 -- Return end result only if both distance marks were connected
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL Tom Gottfried <tom@intevation.de> parents: 2514 diff changeset	120 WHERE ST_Covers(ST_Buffer(wtwaxs, 0.0001), points.pts))
2455 54c9fe587fe6 Subdivide SQL function to prepare for improved error handling Tom Gottfried <tom@intevation.de> parents: 2450 diff changeset	121 -- Use linear referencing to clip axis between distance marks.
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling Tom Gottfried <tom@intevation.de> parents: 2450 diff changeset	122 -- Simplification is used to work-around the problem, that
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling Tom Gottfried <tom@intevation.de> parents: 2450 diff changeset	123 -- ST_LineSubstring might generate very small line segments at an
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling Tom Gottfried <tom@intevation.de> parents: 2450 diff changeset	124 -- end of the resulting linestring, that significantly differ from
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling Tom Gottfried <tom@intevation.de> parents: 2450 diff changeset	125 -- the direction of the input linestring due to finite precision
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling Tom Gottfried <tom@intevation.de> parents: 2450 diff changeset	126 -- of the calculation. The generated small segment of the
2956 974122125a76 Let it be an error if closest points of DISMARs on axis are equal Tom Gottfried <tom@intevation.de> parents: 2563 diff changeset	127 -- resulting line would lead e.g. to unexpected results in an area
974122125a76 Let it be an error if closest points of DISMARs on axis are equal Tom Gottfried <tom@intevation.de> parents: 2563 diff changeset	128 -- generated by ISRSrange_area().
2455 54c9fe587fe6 Subdivide SQL function to prepare for improved error handling Tom Gottfried <tom@intevation.de> parents: 2450 diff changeset	129 SELECT ST_SimplifyPreserveTopology(ST_LineSubstring(
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling Tom Gottfried <tom@intevation.de> parents: 2450 diff changeset	130 axis_segment.line, min(fractions.f), max(fractions.f)),
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling Tom Gottfried <tom@intevation.de> parents: 2450 diff changeset	131 0.0001) AS line
2535 73c8762cee60 Stretch area generation: Throw errors instead of returning NULL Tom Gottfried <tom@intevation.de> parents: 2514 diff changeset	132 INTO STRICT result_geom
2455 54c9fe587fe6 Subdivide SQL function to prepare for improved error handling Tom Gottfried <tom@intevation.de> parents: 2450 diff changeset	133 FROM axis_segment, LATERAL (
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling Tom Gottfried <tom@intevation.de> parents: 2450 diff changeset	134 SELECT ST_LineLocatePoint(axis_segment.line, points.geom) AS f
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling Tom Gottfried <tom@intevation.de> parents: 2450 diff changeset	135 FROM points) AS fractions
2535 73c8762cee60 Stretch area generation: Throw errors instead of returning NULL Tom Gottfried <tom@intevation.de> parents: 2514 diff changeset	136 GROUP BY axis_segment.line;
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL Tom Gottfried <tom@intevation.de> parents: 2514 diff changeset	137
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL Tom Gottfried <tom@intevation.de> parents: 2514 diff changeset	138 RETURN result_geom;
73c8762cee60 Stretch area generation: Throw errors instead of returning NULL Tom Gottfried <tom@intevation.de> parents: 2514 diff changeset	139 END;
2455 54c9fe587fe6 Subdivide SQL function to prepare for improved error handling Tom Gottfried <tom@intevation.de> parents: 2450 diff changeset	140 $$
2535 73c8762cee60 Stretch area generation: Throw errors instead of returning NULL Tom Gottfried <tom@intevation.de> parents: 2514 diff changeset	141 LANGUAGE plpgsql
2455 54c9fe587fe6 Subdivide SQL function to prepare for improved error handling Tom Gottfried <tom@intevation.de> parents: 2450 diff changeset	142 STABLE PARALLEL SAFE;
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling Tom Gottfried <tom@intevation.de> parents: 2450 diff changeset	143
2563 dc4fae4bdb8f Expose axis snapping tolerance to users Tom Gottfried <tom@intevation.de> parents: 2535 diff changeset	144 -- Clip an area to a stretch given by a geometry representing an axis (e.g.
dc4fae4bdb8f Expose axis snapping tolerance to users Tom Gottfried <tom@intevation.de> parents: 2535 diff changeset	145 -- the output of ISRSrange_axis()).
dc4fae4bdb8f Expose axis snapping tolerance to users Tom Gottfried <tom@intevation.de> parents: 2535 diff changeset	146 -- Clipping is done by cutting the area in perpendicular direction at
dc4fae4bdb8f Expose axis snapping tolerance to users Tom Gottfried <tom@intevation.de> parents: 2535 diff changeset	147 -- the ends of the given axis.
2455 54c9fe587fe6 Subdivide SQL function to prepare for improved error handling Tom Gottfried <tom@intevation.de> parents: 2450 diff changeset	148 -- The area passed as argument is assumed to intersect with the axis
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling Tom Gottfried <tom@intevation.de> parents: 2450 diff changeset	149 -- (use e.g. waterway area or fairway dimensions).
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling Tom Gottfried <tom@intevation.de> parents: 2450 diff changeset	150 -- If a multipolygon is passed, the union of the polygons intersecting with the
2563 dc4fae4bdb8f Expose axis snapping tolerance to users Tom Gottfried <tom@intevation.de> parents: 2535 diff changeset	151 -- axis is used for clipping.
2455 54c9fe587fe6 Subdivide SQL function to prepare for improved error handling Tom Gottfried <tom@intevation.de> parents: 2450 diff changeset	152 CREATE OR REPLACE FUNCTION ISRSrange_area(
2563 dc4fae4bdb8f Expose axis snapping tolerance to users Tom Gottfried <tom@intevation.de> parents: 2535 diff changeset	153 axis geometry,
2455 54c9fe587fe6 Subdivide SQL function to prepare for improved error handling Tom Gottfried <tom@intevation.de> parents: 2450 diff changeset	154 area geometry
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling Tom Gottfried <tom@intevation.de> parents: 2450 diff changeset	155 ) RETURNS geometry
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling Tom Gottfried <tom@intevation.de> parents: 2450 diff changeset	156 AS $$
54c9fe587fe6 Subdivide SQL function to prepare for improved error handling Tom Gottfried <tom@intevation.de> parents: 2450 diff changeset	157 WITH
2232 7936b46a88d4 Handle invalid multipolygons in area generation from stretch Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	158 area_subset AS (
7936b46a88d4 Handle invalid multipolygons in area generation from stretch Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	159 -- In case area is a multipolygon, process the union of those
7936b46a88d4 Handle invalid multipolygons in area generation from stretch Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	160 -- polygons, which intersect with the axis. The union is to avoid
7936b46a88d4 Handle invalid multipolygons in area generation from stretch Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	161 -- problems with invalid/self-intersecting multipolygons
7936b46a88d4 Handle invalid multipolygons in area generation from stretch Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	162 SELECT ST_Union(a_dmp.geom) AS area
2563 dc4fae4bdb8f Expose axis snapping tolerance to users Tom Gottfried <tom@intevation.de> parents: 2535 diff changeset	163 FROM (SELECT ST_MakeValid(ST_Transform(geom, ST_SRID(axis)))
dc4fae4bdb8f Expose axis snapping tolerance to users Tom Gottfried <tom@intevation.de> parents: 2535 diff changeset	164 FROM ST_Dump(area)) AS a_dmp (geom)
dc4fae4bdb8f Expose axis snapping tolerance to users Tom Gottfried <tom@intevation.de> parents: 2535 diff changeset	165 WHERE ST_Intersects(a_dmp.geom, axis)
2232 7936b46a88d4 Handle invalid multipolygons in area generation from stretch Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	166 ),
2398 8481e6266691 Fix corner case in area generation from stretch Tom Gottfried <tom@intevation.de> parents: 2373 diff changeset	167 rotated_ends AS (
8481e6266691 Fix corner case in area generation from stretch Tom Gottfried <tom@intevation.de> parents: 2373 diff changeset	168 SELECT ST_Collect(ST_Scale(
8481e6266691 Fix corner case in area generation from stretch Tom Gottfried <tom@intevation.de> parents: 2373 diff changeset	169 ST_Translate(e,
8481e6266691 Fix corner case in area generation from stretch Tom Gottfried <tom@intevation.de> parents: 2373 diff changeset	170 (ST_X(p1) - ST_X(p2)) / 2,
8481e6266691 Fix corner case in area generation from stretch Tom Gottfried <tom@intevation.de> parents: 2373 diff changeset	171 (ST_Y(p1) - ST_Y(p2)) / 2),
8481e6266691 Fix corner case in area generation from stretch Tom Gottfried <tom@intevation.de> parents: 2373 diff changeset	172 ST_Point(d, d), p1)) AS blade
2563 dc4fae4bdb8f Expose axis snapping tolerance to users Tom Gottfried <tom@intevation.de> parents: 2535 diff changeset	173 FROM (SELECT i, ST_PointN(axis, i) AS p1
2398 8481e6266691 Fix corner case in area generation from stretch Tom Gottfried <tom@intevation.de> parents: 2373 diff changeset	174 FROM (VALUES (1), (-1)) AS idx (i)) AS ep,
2563 dc4fae4bdb8f Expose axis snapping tolerance to users Tom Gottfried <tom@intevation.de> parents: 2535 diff changeset	175 ST_Rotate(ST_PointN(axis, i*2), pi()/2, p1) AS ep2 (p2),
2398 8481e6266691 Fix corner case in area generation from stretch Tom Gottfried <tom@intevation.de> parents: 2373 diff changeset	176 ST_Makeline(p1, p2) AS e (e),
2563 dc4fae4bdb8f Expose axis snapping tolerance to users Tom Gottfried <tom@intevation.de> parents: 2535 diff changeset	177 area_subset,
2398 8481e6266691 Fix corner case in area generation from stretch Tom Gottfried <tom@intevation.de> parents: 2373 diff changeset	178 LATERAL (SELECT (ST_MaxDistance(p1, area) / ST_Length(e))
8481e6266691 Fix corner case in area generation from stretch Tom Gottfried <tom@intevation.de> parents: 2373 diff changeset	179 * 2) AS d (d)),
1629 9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: diff changeset	180 range_area AS (
2398 8481e6266691 Fix corner case in area generation from stretch Tom Gottfried <tom@intevation.de> parents: 2373 diff changeset	181 -- Split area by orthogonal lines at the ends of the clipped axis
8481e6266691 Fix corner case in area generation from stretch Tom Gottfried <tom@intevation.de> parents: 2373 diff changeset	182 SELECT (ST_Dump(ST_CollectionExtract(
8481e6266691 Fix corner case in area generation from stretch Tom Gottfried <tom@intevation.de> parents: 2373 diff changeset	183 ST_Split(area, blade), 3))).geom
8481e6266691 Fix corner case in area generation from stretch Tom Gottfried <tom@intevation.de> parents: 2373 diff changeset	184 FROM area_subset, rotated_ends)
1983 f9f1babe52ae Fix area generation from multipolygon input Tom Gottfried <tom@intevation.de> parents: 1811 diff changeset	185 -- From the polygons returned by the last CTE, select only those
1629 9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: diff changeset	186 -- around the clipped axis
2425 4c16f5ad1905 Make area generation more robust Tom Gottfried <tom@intevation.de> parents: 2398 diff changeset	187 SELECT ST_Multi(ST_Transform(ST_Union(range_area.geom), ST_SRID(area)))
2563 dc4fae4bdb8f Expose axis snapping tolerance to users Tom Gottfried <tom@intevation.de> parents: 2535 diff changeset	188 FROM range_area
dc4fae4bdb8f Expose axis snapping tolerance to users Tom Gottfried <tom@intevation.de> parents: 2535 diff changeset	189 WHERE ST_Intersects(ST_Buffer(range_area.geom, -0.0001), axis)
1629 9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: diff changeset	190 $$
9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: diff changeset	191 LANGUAGE sql;

Mercurial > gemma

annotate schema/isrs_functions.sql @ 2956:974122125a76