Mercurial > gemma

comparison schema/isrs_functions.sql @ 2425:4c16f5ad1905

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Make area generation more robust Valid geometries can become invalid geometries during transformation e.g. due to very small 'minimum clearance' (see PostGIS docs) and numerical inaccuracy of the transformation. Thus, try to repair polygons of the input area after transformation and calculate the union of the result polygons before transformation to avoid 'TopologyException' in the union operations. The simulated waterway area of the changed axis in the test data is affected here, but real waterway area data with complex shore lines could likely be affected, too.
author Tom Gottfried <tom@intevation.de>
date Thu, 28 Feb 2019 16:36:49 +0100
parents 8481e6266691
children 48495bd3081d
comparison
equal deleted inserted replaced
2424:b6deb03ef13f 2425:4c16f5ad1905
76 area_subset AS ( 76 area_subset AS (
77 -- In case area is a multipolygon, process the union of those 77 -- In case area is a multipolygon, process the union of those
78 -- polygons, which intersect with the axis. The union is to avoid 78 -- polygons, which intersect with the axis. The union is to avoid
79 -- problems with invalid/self-intersecting multipolygons 79 -- problems with invalid/self-intersecting multipolygons
80 SELECT ST_Union(a_dmp.geom) AS area 80 SELECT ST_Union(a_dmp.geom) AS area
81 FROM axis_substring, utm_zone, 81 FROM axis_substring, utm_zone, LATERAL (
82 ST_Dump(ST_Transform(area, z)) AS a_dmp 82 SELECT ST_MakeValid(ST_Transform(geom, z)) AS geom
83 FROM ST_Dump(area)) AS a_dmp
83 WHERE ST_Intersects(a_dmp.geom, axis_substring.line) 84 WHERE ST_Intersects(a_dmp.geom, axis_substring.line)
84 ), 85 ),
85 rotated_ends AS ( 86 rotated_ends AS (
86 SELECT ST_Collect(ST_Scale( 87 SELECT ST_Collect(ST_Scale(
87 ST_Translate(e, 88 ST_Translate(e,
100 SELECT (ST_Dump(ST_CollectionExtract( 101 SELECT (ST_Dump(ST_CollectionExtract(
101 ST_Split(area, blade), 3))).geom 102 ST_Split(area, blade), 3))).geom
102 FROM area_subset, rotated_ends) 103 FROM area_subset, rotated_ends)
103 -- From the polygons returned by the last CTE, select only those 104 -- From the polygons returned by the last CTE, select only those
104 -- around the clipped axis 105 -- around the clipped axis
105 SELECT ST_Multi(ST_Union(ST_Transform(range_area.geom, ST_SRID(area)))) 106 SELECT ST_Multi(ST_Transform(ST_Union(range_area.geom), ST_SRID(area)))
106 FROM axis_substring, range_area 107 FROM axis_substring, range_area
107 WHERE ST_Intersects(ST_Buffer(range_area.geom, -0.0001), 108 WHERE ST_Intersects(ST_Buffer(range_area.geom, -0.0001),
108 axis_substring.line) 109 axis_substring.line)
109 $$ 110 $$
110 LANGUAGE sql; 111 LANGUAGE sql;