Mercurial > gemma

annotate schema/updates/1468/01.makevalid_isrsrange_area.sql @ 5521:3cfbc5769e8b

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