Mercurial > gemma
view schema/isrs_functions.sql @ 4017:639bdb17c3f2
Fixed offset for fairway box
This was broken by
changeset: 4080:bf86f9a08733
user: Thomas Junk <thomas.junk@intevation.de>
Date: Thu Jul 18 15:04:30 2019 +0200
summary: improve fairwaydiagram printing positioning
For the record: I think the current implementation exceptionally
flawed. Instead of adding extra offset parameters to the diagram
elements the whole building block with all contained elements should
be translated in one step, that would be less cluttered and less error
prone...
author | Sascha Wilde <wilde@intevation.de> |
---|---|
date | Fri, 19 Jul 2019 16:59:25 +0200 |
parents | 0d45a3c34900 |
children | e8b2dc771f9e |
line wrap: on
line source
-- This is Free Software under GNU Affero General Public License v >= 3.0 -- without warranty, see README.md and license for details. -- SPDX-License-Identifier: AGPL-3.0-or-later -- License-Filename: LICENSES/AGPL-3.0.txt -- Copyright (C) 2018, 2019 by via donau -- – Österreichische Wasserstraßen-Gesellschaft mbH -- Software engineering by Intevation GmbH -- Author(s): -- * Tom Gottfried <tom@intevation.de> -- * Sascha Wilde <wilde@intevation.de> CREATE OR REPLACE FUNCTION best_utm(stretch isrsrange) RETURNS integer AS $$ SELECT best_utm(ST_Collect(geom::geometry)) FROM waterway.distance_marks_virtual WHERE location_code IN (lower(stretch), upper(stretch)) $$ LANGUAGE sql STABLE PARALLEL SAFE; -- Return a multipoint with coordinates of stretch end points CREATE OR REPLACE FUNCTION ISRSrange_points( stretch isrsrange ) RETURNS geometry AS $$ DECLARE result_geom geometry; BEGIN SELECT geom INTO STRICT result_geom FROM ( SELECT ST_Collect(CAST(geom AS geometry)) AS geom FROM waterway.distance_marks_virtual WHERE location_code IN(lower(stretch), upper(stretch))) AS pts -- Ensure both have been found WHERE ST_NumGeometries(geom) = 2; RETURN result_geom; END; $$ LANGUAGE plpgsql STABLE PARALLEL SAFE; -- Clip waterway axis to a stretch given by a pair of ISRS location codes. -- Uses the table waterway.distance_marks_virtual to map ISRS location codes -- to their geo-location. -- Distance marks are assumed to be near the axis. -- Returns the axis geometry transformed to the best matching UTM zone. CREATE OR REPLACE FUNCTION ISRSrange_axis( stretch isrsrange, tolerance float -- in m, up to which linestrings will be connected at their boundary ) RETURNS geometry AS $$ DECLARE z int; DECLARE result_geom geometry; BEGIN -- Find best matchting UTM zone z = best_utm(stretch); CREATE TEMP TABLE axis AS SELECT id, wtwaxs, ST_Boundary(wtwaxs) AS bdr FROM (SELECT id, ST_Transform(wtwaxs::geometry, z) AS wtwaxs FROM waterway.waterway_axis) AS axs; CREATE INDEX axs_bdr ON axis USING GiST (bdr); ANALYZE axis; WITH RECURSIVE -- In order to guarantee the following ST_Covers to work, -- snap distance mark coordinates to axis points0 AS ( SELECT ST_ClosestPoint( wtwaxs, ST_Transform(geom, z)) AS geom FROM ST_Dump(ISRSrange_points(stretch)), ( SELECT ST_Collect(wtwaxs) AS wtwaxs FROM axis) AS ax), -- Ensure two distinct points on axis have been found points AS ( SELECT geom FROM points0 WHERE 2 = (SELECT count(DISTINCT geom) FROM points0)), axis_snapped AS ( -- Iteratively connect non-contiguous axis chunks -- to find the contiguous axis on which given distance marks lie (SELECT ARRAY[id] AS ids, wtwaxs FROM axis, points WHERE ST_Intersects( ST_Buffer(axis.wtwaxs, 0.0001), points.geom) FETCH FIRST ROW ONLY) UNION -- Connect endpoint of next linestring with closest -- endpoint of merged linestring until a contiguous -- linestring connecting both distance marks is build up (SELECT refids || id, ST_LineMerge(ST_Collect(ARRAY( -- Linestring build up so far SELECT refgeom UNION -- Fill eventual gap SELECT ST_ShortestLine( ST_Boundary(refgeom), bdr) UNION -- Linestring to be added SELECT geom))) FROM axis_snapped AS axis_snapped (refids, refgeom), axis AS axis (id, geom, bdr), (SELECT ST_Collect(points.geom) AS pts FROM points) AS points WHERE id <> ALL(refids) AND ST_DWithin( ST_Boundary(refgeom), bdr, tolerance) AND NOT ST_Covers(ST_Buffer(refgeom, 0.0001), points.pts) ORDER BY ST_Boundary(refgeom) <-> bdr FETCH FIRST ROW ONLY)), axis_segment AS ( -- Fetch end result from snapping SELECT wtwaxs AS line FROM axis_snapped, (SELECT ST_Collect(points.geom) AS pts FROM points) AS points -- Return end result only if both distance marks were connected WHERE ST_Covers(ST_Buffer(wtwaxs, 0.0001), points.pts)) -- Use linear referencing to clip axis between distance marks. -- Simplification is used to work-around the problem, that -- ST_LineSubstring might generate very small line segments at an -- end of the resulting linestring, that significantly differ from -- the direction of the input linestring due to finite precision -- of the calculation. The generated small segment of the -- resulting line would lead e.g. to unexpected results in an area -- generated by ISRSrange_area(). SELECT ST_SimplifyPreserveTopology(ST_LineSubstring( axis_segment.line, min(fractions.f), max(fractions.f)), 0.0001) AS line INTO STRICT result_geom FROM axis_segment, LATERAL ( SELECT ST_LineLocatePoint(axis_segment.line, points.geom) AS f FROM points) AS fractions GROUP BY axis_segment.line; -- Drop temporary table to avoid side effects on PostgreSQL's MVCC, -- because otherwise subsequent invocations of the function will not see -- changes on the underlying waterway.waterway_axis that might have -- occured. DROP TABLE axis; RETURN result_geom; END; $$ LANGUAGE plpgsql PARALLEL RESTRICTED; -- Clip an area to a stretch given by a geometry representing an axis (e.g. -- the output of ISRSrange_axis()). -- Clipping is done by cutting the area in perpendicular direction at -- the ends of the given axis. -- The area passed as argument is assumed to intersect with the axis -- (use e.g. waterway area or fairway dimensions). -- If a multipolygon is passed, the union of the polygons intersecting with the -- axis is used for clipping. CREATE OR REPLACE FUNCTION ISRSrange_area( axis geometry, 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 rotated_ends AS ( SELECT ST_Collect(ST_Scale( ST_Translate(e, (ST_X(p1) - ST_X(p2)) / 2, (ST_Y(p1) - ST_Y(p2)) / 2), ST_Point(d, d), p1)) AS blade FROM (SELECT i, ST_PointN(axis, i) AS p1 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), 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_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); RETURN result_geom; END; $$ LANGUAGE plpgsql STABLE PARALLEL SAFE; -- Check if a given string looks like an ISRS code CREATE OR REPLACE FUNCTION is_ISRSstring(str text) RETURNS boolean AS $$ BEGIN str = upper(str); RETURN (SELECT str SIMILAR TO '[A-Z]{2}[A-Z0-9]{13}[0-9]{5}') AND is_country(substring(str from 1 for 2)); END; $$ LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE;