Mercurial > gemma

annotate schema/isrs_tests.sql @ 2431:48495bd3081d

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Construct stretch area between distance marks also from non-contiguous axis Before, both distance marks were required to be next to the same linestring of the waterway axis.
author Tom Gottfried <tom@intevation.de>
date Thu, 28 Feb 2019 19:44:38 +0100
parents 8481e6266691
children 00cac7890574
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
1298
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 578
diff changeset
1 -- This is Free Software under GNU Affero General Public License v >= 3.0
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 578
diff changeset
2 -- without warranty, see README.md and license for details.
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 578
diff changeset
3
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 578
diff changeset
4 -- SPDX-License-Identifier: AGPL-3.0-or-later
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 578
diff changeset
5 -- License-Filename: LICENSES/AGPL-3.0.txt
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 578
diff changeset
6
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 578
diff changeset
7 -- Copyright (C) 2018 by via donau
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 578
diff changeset
8 -- – Österreichische Wasserstraßen-Gesellschaft mbH
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 578
diff changeset
9 -- Software engineering by Intevation GmbH
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 578
diff changeset
10
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 578
diff changeset
11 -- Author(s):
1301
2304778c4432 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1298
diff changeset
12 -- * Tom Gottfried <tom@intevation.de>
1298
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 578
diff changeset
13
569
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
14 --
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
15 -- pgTAP test script for ISRS location code types and functions
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
16 --
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
17
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
18 SELECT results_eq($$
578
61af85a432bf Fix extraction of object reference code
Tom Gottfried <tom@intevation.de>
parents: 569
diff changeset
19 SELECT isrs_fromText('DEBON03901G007906548')
569
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
20 $$,
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
21 $$
578
61af85a432bf Fix extraction of object reference code
Tom Gottfried <tom@intevation.de>
parents: 569
diff changeset
22 SELECT CAST(('DE', 'BON', '03901', 'G0079', 6548) AS isrs)
569
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
23 $$,
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
24 'Correct ISRS text input gives respective ISRS location code');
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
25
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
26 SELECT throws_ok($$
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
27 SELECT isrs_fromText('DEUXXX039000000005023')
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
28 $$,
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
29 22023, NULL,
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
30 'ISRS text input needs to have correct length');
1629
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
31
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
32 SELECT ok(
2081
40711ca3aa19 Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
33 'DEBON03901G007906548' = isrs_asText(isrs_fromText('DEBON03901G007906548'))
40711ca3aa19 Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
34 ,
40711ca3aa19 Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
35 'isrs_asText() is the inverse of isrs_fromText()');
40711ca3aa19 Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
36
40711ca3aa19 Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
37 SELECT ok(
1629
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
38 ISRSrange_area(isrsrange(
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
39 ('AT', 'XXX', '00001', '00000', 0)::isrs,
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
40 ('AT', 'XXX', '00001', '00000', 1)::isrs),
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
41 ST_SetSRID('POLYGON((0 1, 0 2, 1 2, 1 1, 0 1))'::geometry, 4326)
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
42 ) IS NULL,
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
43 'ISRSrange_area returns NULL, if given area does not intersect with axis');
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
44
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
45 SELECT ok(
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
46 ST_DWithin(
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
47 (SELECT geom FROM waterway.distance_marks_virtual
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
48 WHERE location_code = ('AT', 'XXX', '00001', '00000', 0)::isrs),
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
49 ST_Boundary(ISRSrange_area(isrsrange(
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
50 ('AT', 'XXX', '00001', '00000', 0)::isrs,
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
51 ('AT', 'XXX', '00001', '00000', 1)::isrs),
2398
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2232
diff changeset
52 (SELECT ST_Collect(CAST(area AS geometry))
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2232
diff changeset
53 FROM waterway.waterway_area))),
1629
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
54 1)
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
55 AND
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
56 ST_DWithin(
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
57 (SELECT geom FROM waterway.distance_marks_virtual
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
58 WHERE location_code = ('AT', 'XXX', '00001', '00000', 1)::isrs),
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
59 ST_Boundary(ISRSrange_area(isrsrange(
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
60 ('AT', 'XXX', '00001', '00000', 0)::isrs,
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
61 ('AT', 'XXX', '00001', '00000', 1)::isrs),
2398
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2232
diff changeset
62 (SELECT ST_Collect(CAST(area AS geometry))
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2232
diff changeset
63 FROM waterway.waterway_area))),
1629
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
64 1),
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
65 'Resulting polygon almost ST_Touches points corresponding to stretch');
1983
f9f1babe52ae Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents: 1629
diff changeset
66
2398
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2232
diff changeset
67 \set test_area 'POLYGON((-1 1, 2 1, 2 -1, -1 -1, -1 1))'
1983
f9f1babe52ae Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents: 1629
diff changeset
68 SELECT ok(
f9f1babe52ae Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents: 1629
diff changeset
69 2 = ST_NumGeometries(
f9f1babe52ae Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents: 1629
diff changeset
70 ISRSrange_area(
f9f1babe52ae Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents: 1629
diff changeset
71 isrsrange(
f9f1babe52ae Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents: 1629
diff changeset
72 ('AT', 'XXX', '00001', '00000', 0)::isrs,
f9f1babe52ae Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents: 1629
diff changeset
73 ('AT', 'XXX', '00001', '00000', 1)::isrs),
f9f1babe52ae Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents: 1629
diff changeset
74 ST_SetSRID(ST_Collect(
f9f1babe52ae Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents: 1629
diff changeset
75 ST_Translate(:'test_area',
f9f1babe52ae Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents: 1629
diff changeset
76 (ST_XMax(:'test_area'::geometry)
f9f1babe52ae Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents: 1629
diff changeset
77 - ST_XMin(:'test_area'::geometry))/2
f9f1babe52ae Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents: 1629
diff changeset
78 + 0.1, 0),
f9f1babe52ae Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents: 1629
diff changeset
79 ST_Translate(:'test_area',
f9f1babe52ae Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents: 1629
diff changeset
80 -((ST_XMax(:'test_area'::geometry)
f9f1babe52ae Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents: 1629
diff changeset
81 - ST_XMin(:'test_area'::geometry))/2
f9f1babe52ae Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents: 1629
diff changeset
82 + 0.1), 0)),
f9f1babe52ae Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents: 1629
diff changeset
83 4326))),
2232
7936b46a88d4 Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2081
diff changeset
84 'Two polygons intersecting the axis lead to two polygons in result');
7936b46a88d4 Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2081
diff changeset
85
7936b46a88d4 Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2081
diff changeset
86 SELECT ok(
7936b46a88d4 Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2081
diff changeset
87 1 = ST_NumGeometries(
7936b46a88d4 Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2081
diff changeset
88 ISRSrange_area(
7936b46a88d4 Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2081
diff changeset
89 isrsrange(
7936b46a88d4 Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2081
diff changeset
90 ('AT', 'XXX', '00001', '00000', 0)::isrs,
7936b46a88d4 Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2081
diff changeset
91 ('AT', 'XXX', '00001', '00000', 1)::isrs),
7936b46a88d4 Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2081
diff changeset
92 ST_SetSRID(ST_Collect(
7936b46a88d4 Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2081
diff changeset
93 ST_Translate(:'test_area',
7936b46a88d4 Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2081
diff changeset
94 (ST_XMax(:'test_area'::geometry)
7936b46a88d4 Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2081
diff changeset
95 - ST_XMin(:'test_area'::geometry))/3,
7936b46a88d4 Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2081
diff changeset
96 0),
7936b46a88d4 Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2081
diff changeset
97 ST_Translate(:'test_area',
7936b46a88d4 Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2081
diff changeset
98 -((ST_XMax(:'test_area'::geometry)
7936b46a88d4 Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2081
diff changeset
99 - ST_XMin(:'test_area'::geometry))/3),
7936b46a88d4 Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2081
diff changeset
100 0)),
7936b46a88d4 Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2081
diff changeset
101 4326))),
7936b46a88d4 Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2081
diff changeset
102 'Self-intersecting multipolygon leads to one polygon in result');
2431
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2398
diff changeset
103
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2398
diff changeset
104 SELECT ok(
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2398
diff changeset
105 ISRSrange_area(
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2398
diff changeset
106 isrsrange(
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2398
diff changeset
107 ('AT', 'XXX', '00001', '00000', 0)::isrs,
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2398
diff changeset
108 ('AT', 'XXX', '00001', '00000', 2)::isrs),
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2398
diff changeset
109 (SELECT ST_Collect(CAST(area AS geometry))
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2398
diff changeset
110 FROM waterway.waterway_area)) IS NOT NULL,
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2398
diff changeset
111 'Area generated from non-matching distance mark and non-contiguous axis');