Mercurial > gemma
diff schema/isrs_functions.sql @ 3561:453f15ba8030
Improve error handling in area generation
Instead of hitting a NOT NULL constraint later on, raise an error
if no candidate area for cutting is found.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Fri, 31 May 2019 16:58:42 +0200 |
parents | 524f1382558a |
children | 0d45a3c34900 |
line wrap: on
line diff
--- a/schema/isrs_functions.sql Fri May 31 16:54:09 2019 +0200 +++ b/schema/isrs_functions.sql Fri May 31 16:58:42 2019 +0200 @@ -165,16 +165,21 @@ area geometry ) RETURNS geometry AS $$ +DECLARE + area_subset geometry; + result_geom geometry; +BEGIN + -- In case area is a multipolygon, process the union of those + -- polygons, which intersect with the axis. The union is to avoid + -- problems with invalid/self-intersecting multipolygons + SELECT ST_Union(a_dmp.geom) + INTO STRICT area_subset + FROM (SELECT ST_MakeValid(ST_Transform(geom, ST_SRID(axis))) + FROM ST_Dump(area)) AS a_dmp (geom) + WHERE ST_Intersects(a_dmp.geom, axis) + HAVING ST_Union(a_dmp.geom) IS NOT NULL; + WITH - area_subset AS ( - -- In case area is a multipolygon, process the union of those - -- polygons, which intersect with the axis. The union is to avoid - -- problems with invalid/self-intersecting multipolygons - SELECT ST_Union(a_dmp.geom) AS area - FROM (SELECT ST_MakeValid(ST_Transform(geom, ST_SRID(axis))) - FROM ST_Dump(area)) AS a_dmp (geom) - WHERE ST_Intersects(a_dmp.geom, axis) - ), rotated_ends AS ( SELECT ST_Collect(ST_Scale( ST_Translate(e, @@ -185,18 +190,23 @@ FROM (VALUES (1), (-1)) AS idx (i)) AS ep, ST_Rotate(ST_PointN(axis, i*2), pi()/2, p1) AS ep2 (p2), ST_Makeline(p1, p2) AS e (e), - area_subset, - LATERAL (SELECT (ST_MaxDistance(p1, area) / ST_Length(e)) + LATERAL ( + SELECT (ST_MaxDistance(p1, area_subset) / ST_Length(e)) * 2) AS d (d)), range_area AS ( -- Split area by orthogonal lines at the ends of the clipped axis SELECT (ST_Dump(ST_CollectionExtract( - ST_Split(area, blade), 3))).geom - FROM area_subset, rotated_ends) + ST_Split(area_subset, blade), 3))).geom + FROM rotated_ends) -- From the polygons returned by the last CTE, select only those -- around the clipped axis SELECT ST_Multi(ST_Transform(ST_Union(range_area.geom), ST_SRID(area))) + INTO result_geom FROM range_area - WHERE ST_Intersects(ST_Buffer(range_area.geom, -0.0001), axis) + WHERE ST_Intersects(ST_Buffer(range_area.geom, -0.0001), axis); + + RETURN result_geom; +END; $$ - LANGUAGE sql; + LANGUAGE plpgsql + STABLE PARALLEL SAFE;