Mercurial > gemma
view schema/isrs_functions.sql @ 1729:74f7d4c531bc
Scheduled imports: Refactored calling of database configured import to be reusable elsewhere.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Wed, 09 Jan 2019 13:03:24 +0100 |
parents | 50548a6df009 |
children | c21f72775f6b |
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 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> -- Clip an area 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 and the table waterway.waterway_axis to retrieve -- perpendicular direction at these geo-locations. -- Distance marks are assumed to be near the axis and the area passed as -- argument is assumed to intersect with the axis -- (use e.g. waterway area or fairway dimensions). CREATE OR REPLACE FUNCTION ISRSrange_area( stretch isrsrange, area geometry ) RETURNS geometry AS $$ WITH -- Get coordinates of location codes from_geog AS ( SELECT geom FROM waterway.distance_marks_virtual WHERE location_code = lower(stretch)), to_geog AS ( SELECT geom FROM waterway.distance_marks_virtual WHERE location_code = upper(stretch)), utm_zone AS ( -- Find best matchting UTM zone SELECT best_utm(ST_Collect( from_geog.geom::geometry, to_geog.geom::geometry)) AS z FROM from_geog, to_geog), axis AS ( -- Transform and sew together contiguous axis chunks SELECT ST_LineMerge(ST_Collect(ST_Transform( wtwaxs::geometry, z))) AS wtwaxs FROM waterway.waterway_axis, utm_zone), -- In order to guarantee the following ST_Covers to work, -- snap distance mark coordinates to axis from_point AS ( SELECT ST_ClosestPoint( wtwaxs, ST_Transform(from_geog.geom::geometry, z)) AS geom FROM axis, from_geog, utm_zone), to_point AS ( SELECT ST_ClosestPoint( wtwaxs, ST_Transform(to_geog.geom::geometry, z)) AS geom FROM axis, to_geog, utm_zone), axis_segment AS ( -- select the contiguous axis on which distance marks lie SELECT line FROM ( SELECT (ST_Dump(wtwaxs)).geom AS line FROM axis) AS lines, (SELECT ST_Collect(from_point.geom, to_point.geom) AS pts FROM from_point, to_point) AS points WHERE ST_Covers(ST_Buffer(lines.line, 0.0001), points.pts)), axis_substring AS ( -- Use linear referencing to clip axis between distance marks SELECT ST_LineSubstring( axis_segment.line, ST_LineLocatePoint(axis_segment.line, from_point.geom), ST_LineLocatePoint(axis_segment.line, to_point.geom) ) AS line FROM axis_segment, from_point, to_point), range_area AS ( -- Create a buffer around the clipped axis, as large as it could -- potentially be intersecting with the area polygon that -- intersects with the clipped axis. Get the intersection of that -- buffer with the area polygon, which can potentially -- be a multipolygon. SELECT (ST_Dump(ST_Intersection( ST_Buffer( axis_substring.line, ST_MaxDistance( axis_substring.line, ST_Transform(area, z)), 'endcap=flat'), ST_Transform(area, z)))).geom FROM axis_substring, utm_zone) -- From the polygons returned by the last CTE, select only the one -- around the clipped axis SELECT ST_Transform(range_area.geom, ST_SRID(area)) FROM axis_substring, range_area WHERE ST_Intersects(range_area.geom, axis_substring.line) $$ LANGUAGE sql;