Mercurial > gemma
comparison schema/updates/1469/01.fix_isrsrange_area.sql @ 5559:ce9a9a1bf92f
Make invalid output of ISRSrange_area() less likely, next try
Since ST_MakeValid() might return a collection of lower-to-equal dimension
geometries, distill only the polygons from it. This should prevent
respective errors when trying to save the result to a column of type
MultiPolygon.
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Thu, 02 Dec 2021 12:37:33 +0100 |
| parents | |
| children |
comparison
equal
deleted
inserted
replaced
| 5558:a8eeb53fe43b | 5559:ce9a9a1bf92f |
|---|---|
| 1 CREATE OR REPLACE FUNCTION ISRSrange_area( | |
| 2 axis geometry, | |
| 3 area geometry | |
| 4 ) RETURNS geometry | |
| 5 AS $$ | |
| 6 DECLARE | |
| 7 area_subset geometry; | |
| 8 result_geom geometry; | |
| 9 BEGIN | |
| 10 -- In case area is a multipolygon, process the union of those | |
| 11 -- polygons, which intersect with the axis. The union is to avoid | |
| 12 -- problems with invalid/self-intersecting multipolygons | |
| 13 SELECT ST_Union(a_dmp.geom) | |
| 14 INTO STRICT area_subset | |
| 15 FROM (SELECT ST_MakeValid(ST_Transform(geom, ST_SRID(axis))) | |
| 16 FROM ST_Dump(area)) AS a_dmp (geom) | |
| 17 WHERE ST_Intersects(a_dmp.geom, axis) | |
| 18 HAVING ST_Union(a_dmp.geom) IS NOT NULL; | |
| 19 | |
| 20 WITH | |
| 21 rotated_ends AS ( | |
| 22 SELECT ST_Collect(ST_Scale( | |
| 23 ST_Translate(e, | |
| 24 (ST_X(p1) - ST_X(p2)) / 2, | |
| 25 (ST_Y(p1) - ST_Y(p2)) / 2), | |
| 26 ST_Point(d, d), p1)) AS blade | |
| 27 FROM (SELECT i, ST_PointN(axis, i) AS p1 | |
| 28 FROM (VALUES (1), (-1)) AS idx (i)) AS ep, | |
| 29 ST_Rotate(ST_PointN(axis, i*2), pi()/2, p1) AS ep2 (p2), | |
| 30 ST_Makeline(p1, p2) AS e (e), | |
| 31 LATERAL ( | |
| 32 SELECT (ST_MaxDistance(p1, area_subset) / ST_Length(e)) | |
| 33 * 2) AS d (d)), | |
| 34 range_area AS ( | |
| 35 -- Split area by orthogonal lines at the ends of the clipped axis | |
| 36 SELECT (ST_Dump(ST_CollectionExtract( | |
| 37 ST_Split(area_subset, blade), 3))).geom | |
| 38 FROM rotated_ends) | |
| 39 -- From the polygons returned by the last CTE, select only those | |
| 40 -- around the clipped axis | |
| 41 SELECT ST_Multi(ST_CollectionExtract(ST_MakeValid(ST_Transform( | |
| 42 ST_Union(range_area.geom), ST_SRID(area))), 3)) | |
| 43 INTO result_geom | |
| 44 FROM range_area | |
| 45 WHERE ST_Intersects(ST_Buffer(range_area.geom, -0.0001), axis); | |
| 46 | |
| 47 RETURN result_geom; | |
| 48 END; | |
| 49 $$ | |
| 50 LANGUAGE plpgsql | |
| 51 STABLE PARALLEL SAFE; |