Mercurial > gemma

annotate schema/updates/1469/01.fix_isrsrange_area.sql @ 5559:ce9a9a1bf92f

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
5559
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1 CREATE OR REPLACE FUNCTION ISRSrange_area(
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
2 axis geometry,
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
3 area geometry
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
4 ) RETURNS geometry
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
5 AS $$
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
6 DECLARE
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
7 area_subset geometry;
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
8 result_geom geometry;
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
9 BEGIN
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
10 -- In case area is a multipolygon, process the union of those
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
11 -- polygons, which intersect with the axis. The union is to avoid
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
12 -- problems with invalid/self-intersecting multipolygons
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
13 SELECT ST_Union(a_dmp.geom)
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
14 INTO STRICT area_subset
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
15 FROM (SELECT ST_MakeValid(ST_Transform(geom, ST_SRID(axis)))
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
16 FROM ST_Dump(area)) AS a_dmp (geom)
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
17 WHERE ST_Intersects(a_dmp.geom, axis)
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
18 HAVING ST_Union(a_dmp.geom) IS NOT NULL;
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
19
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
20 WITH
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
21 rotated_ends AS (
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
22 SELECT ST_Collect(ST_Scale(
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
23 ST_Translate(e,
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
24 (ST_X(p1) - ST_X(p2)) / 2,
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
25 (ST_Y(p1) - ST_Y(p2)) / 2),
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
26 ST_Point(d, d), p1)) AS blade
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
27 FROM (SELECT i, ST_PointN(axis, i) AS p1
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
28 FROM (VALUES (1), (-1)) AS idx (i)) AS ep,
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
29 ST_Rotate(ST_PointN(axis, i*2), pi()/2, p1) AS ep2 (p2),
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
30 ST_Makeline(p1, p2) AS e (e),
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
31 LATERAL (
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
32 SELECT (ST_MaxDistance(p1, area_subset) / ST_Length(e))
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
33 * 2) AS d (d)),
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
34 range_area AS (
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
35 -- Split area by orthogonal lines at the ends of the clipped axis
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
36 SELECT (ST_Dump(ST_CollectionExtract(
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
37 ST_Split(area_subset, blade), 3))).geom
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
38 FROM rotated_ends)
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
39 -- From the polygons returned by the last CTE, select only those
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
40 -- around the clipped axis
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
41 SELECT ST_Multi(ST_CollectionExtract(ST_MakeValid(ST_Transform(
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
42 ST_Union(range_area.geom), ST_SRID(area))), 3))
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
43 INTO result_geom
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
44 FROM range_area
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
45 WHERE ST_Intersects(ST_Buffer(range_area.geom, -0.0001), axis);
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
46
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
47 RETURN result_geom;
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
48 END;
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
49 $$
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
50 LANGUAGE plpgsql
ce9a9a1bf92f Make invalid output of ISRSrange_area() less likely, next try
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
51 STABLE PARALLEL SAFE;