Mercurial > gemma

comparison schema/isrs_functions.sql @ 1879:9a2fbeaabd52 dev-pdf-generation

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
merging in from branch default
author Bernhard Reiter <bernhard@intevation.de>
date Tue, 15 Jan 2019 10:07:10 +0100
parents 50548a6df009
children c21f72775f6b
comparison
equal deleted inserted replaced
1878:f030182f82f1 1879:9a2fbeaabd52
1 -- This is Free Software under GNU Affero General Public License v >= 3.0
2 -- without warranty, see README.md and license for details.
3
4 -- SPDX-License-Identifier: AGPL-3.0-or-later
5 -- License-Filename: LICENSES/AGPL-3.0.txt
6
7 -- Copyright (C) 2018 by via donau
8 -- – Österreichische Wasserstraßen-Gesellschaft mbH
9 -- Software engineering by Intevation GmbH
10
11 -- Author(s):
12 -- * Tom Gottfried <tom@intevation.de>
13 -- * Sascha Wilde <wilde@intevation.de>
14
1 -- Clip an area to a stretch given by a pair of ISRS location codes. 15 -- Clip an area to a stretch given by a pair of ISRS location codes.
2 -- Uses the table waterway.distance_marks_virtual to map ISRS location codes 16 -- Uses the table waterway.distance_marks_virtual to map ISRS location codes
3 -- to their geo-location and the table waterway.waterway_axis to retrieve 17 -- to their geo-location and the table waterway.waterway_axis to retrieve
4 -- perpendicular direction at these geo-locations. 18 -- perpendicular direction at these geo-locations.
5 -- Distance marks are assumed to be near the axis and the area passed as 19 -- Distance marks are assumed to be near the axis and the area passed as
47 FROM ( 61 FROM (
48 SELECT (ST_Dump(wtwaxs)).geom AS line 62 SELECT (ST_Dump(wtwaxs)).geom AS line
49 FROM axis) AS lines, 63 FROM axis) AS lines,
50 (SELECT ST_Collect(from_point.geom, to_point.geom) AS pts 64 (SELECT ST_Collect(from_point.geom, to_point.geom) AS pts
51 FROM from_point, to_point) AS points 65 FROM from_point, to_point) AS points
52 WHERE ST_Covers(lines.line, points.pts)), 66 WHERE ST_Covers(ST_Buffer(lines.line, 0.0001), points.pts)),
53 axis_substring AS ( 67 axis_substring AS (
54 -- Use linear referencing to clip axis between distance marks 68 -- Use linear referencing to clip axis between distance marks
55 SELECT ST_LineSubstring( 69 SELECT ST_LineSubstring(
56 axis_segment.line, 70 axis_segment.line,
57 ST_LineLocatePoint(axis_segment.line, from_point.geom), 71 ST_LineLocatePoint(axis_segment.line, from_point.geom),