Mercurial > gemma
annotate schema/isrs_tests.sql @ 2632:0b14de0bb85f
client: importoverview2: moved refresh button to box header, added loading animation like in other boxes
author | Markus Kottlaender <markus@intevation.de> |
---|---|
date | Wed, 13 Mar 2019 17:13:15 +0100 |
parents | dc4fae4bdb8f |
children | d316a6e41f54 |
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 |
2563
dc4fae4bdb8f
Expose axis snapping tolerance to users
Tom Gottfried <tom@intevation.de>
parents:
2535
diff
changeset
|
7 -- Copyright (C) 2018, 2019 by via donau |
1298
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 |
2535
73c8762cee60
Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents:
2453
diff
changeset
|
37 SELECT throws_ok($$ |
73c8762cee60
Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents:
2453
diff
changeset
|
38 SELECT ISRSrange_points(isrsrange( |
73c8762cee60
Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents:
2453
diff
changeset
|
39 ('AT', 'XXX', '00001', '00000', 0)::isrs, |
73c8762cee60
Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents:
2453
diff
changeset
|
40 ('AT', 'XXX', '00001', '00000', 99999)::isrs)) |
73c8762cee60
Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents:
2453
diff
changeset
|
41 $$, |
73c8762cee60
Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents:
2453
diff
changeset
|
42 'P0002', NULL, |
73c8762cee60
Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents:
2453
diff
changeset
|
43 'ISRSrange_points fails if not both distance marks can be found'); |
73c8762cee60
Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents:
2453
diff
changeset
|
44 |
73c8762cee60
Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents:
2453
diff
changeset
|
45 SELECT throws_ok($$ |
73c8762cee60
Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents:
2453
diff
changeset
|
46 SELECT ISRSrange_axis(isrsrange( |
73c8762cee60
Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents:
2453
diff
changeset
|
47 ('AT', 'XXX', '00001', '00000', 0)::isrs, |
73c8762cee60
Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents:
2453
diff
changeset
|
48 ('AT', 'XXX', '00001', '00000', 2)::isrs), |
73c8762cee60
Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents:
2453
diff
changeset
|
49 0) |
73c8762cee60
Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents:
2453
diff
changeset
|
50 $$, |
73c8762cee60
Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents:
2453
diff
changeset
|
51 'P0002', NULL, |
73c8762cee60
Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents:
2453
diff
changeset
|
52 'ISRSrange_axis fails if no contiguous axis can be constructed'); |
73c8762cee60
Stretch area generation: Throw errors instead of returning NULL
Tom Gottfried <tom@intevation.de>
parents:
2453
diff
changeset
|
53 |
2081
40711ca3aa19
Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
54 SELECT ok( |
2563
dc4fae4bdb8f
Expose axis snapping tolerance to users
Tom Gottfried <tom@intevation.de>
parents:
2535
diff
changeset
|
55 ISRSrange_area(ISRSrange_axis(isrsrange( |
dc4fae4bdb8f
Expose axis snapping tolerance to users
Tom Gottfried <tom@intevation.de>
parents:
2535
diff
changeset
|
56 ('AT', 'XXX', '00001', '00000', 0)::isrs, |
dc4fae4bdb8f
Expose axis snapping tolerance to users
Tom Gottfried <tom@intevation.de>
parents:
2535
diff
changeset
|
57 ('AT', 'XXX', '00001', '00000', 1)::isrs), |
dc4fae4bdb8f
Expose axis snapping tolerance to users
Tom Gottfried <tom@intevation.de>
parents:
2535
diff
changeset
|
58 5), |
1629
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
59 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
|
60 ) IS NULL, |
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
61 '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
|
62 |
2453 | 63 SELECT results_eq($$ |
64 SELECT every(ST_DWithin( | |
65 ST_Boundary(ISRSrange_area( | |
2563
dc4fae4bdb8f
Expose axis snapping tolerance to users
Tom Gottfried <tom@intevation.de>
parents:
2535
diff
changeset
|
66 ISRSrange_axis(isrsrange( |
dc4fae4bdb8f
Expose axis snapping tolerance to users
Tom Gottfried <tom@intevation.de>
parents:
2535
diff
changeset
|
67 ('AT', 'XXX', '00001', '00000', 0)::isrs, |
dc4fae4bdb8f
Expose axis snapping tolerance to users
Tom Gottfried <tom@intevation.de>
parents:
2535
diff
changeset
|
68 ('AT', 'XXX', '00001', '00000', 1)::isrs), |
dc4fae4bdb8f
Expose axis snapping tolerance to users
Tom Gottfried <tom@intevation.de>
parents:
2535
diff
changeset
|
69 5), |
2453 | 70 (SELECT ST_Collect(CAST(area AS geometry)) |
71 FROM waterway.waterway_area))), | |
72 geom, | |
73 1)) | |
74 FROM waterway.distance_marks_virtual | |
75 WHERE location_code IN( | |
76 ('AT', 'XXX', '00001', '00000', 0)::isrs, | |
77 ('AT', 'XXX', '00001', '00000', 1)::isrs) | |
78 $$, | |
79 $$ | |
80 SELECT true | |
81 $$, | |
1629
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
82 '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
|
83 |
2398
8481e6266691
Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
2232
diff
changeset
|
84 \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
|
85 SELECT ok( |
f9f1babe52ae
Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents:
1629
diff
changeset
|
86 2 = ST_NumGeometries( |
f9f1babe52ae
Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents:
1629
diff
changeset
|
87 ISRSrange_area( |
2563
dc4fae4bdb8f
Expose axis snapping tolerance to users
Tom Gottfried <tom@intevation.de>
parents:
2535
diff
changeset
|
88 ISRSrange_axis(isrsrange( |
dc4fae4bdb8f
Expose axis snapping tolerance to users
Tom Gottfried <tom@intevation.de>
parents:
2535
diff
changeset
|
89 ('AT', 'XXX', '00001', '00000', 0)::isrs, |
dc4fae4bdb8f
Expose axis snapping tolerance to users
Tom Gottfried <tom@intevation.de>
parents:
2535
diff
changeset
|
90 ('AT', 'XXX', '00001', '00000', 1)::isrs), |
dc4fae4bdb8f
Expose axis snapping tolerance to users
Tom Gottfried <tom@intevation.de>
parents:
2535
diff
changeset
|
91 5), |
1983
f9f1babe52ae
Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents:
1629
diff
changeset
|
92 ST_SetSRID(ST_Collect( |
f9f1babe52ae
Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents:
1629
diff
changeset
|
93 ST_Translate(:'test_area', |
f9f1babe52ae
Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents:
1629
diff
changeset
|
94 (ST_XMax(:'test_area'::geometry) |
f9f1babe52ae
Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents:
1629
diff
changeset
|
95 - ST_XMin(:'test_area'::geometry))/2 |
f9f1babe52ae
Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents:
1629
diff
changeset
|
96 + 0.1, 0), |
f9f1babe52ae
Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents:
1629
diff
changeset
|
97 ST_Translate(:'test_area', |
f9f1babe52ae
Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents:
1629
diff
changeset
|
98 -((ST_XMax(:'test_area'::geometry) |
f9f1babe52ae
Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents:
1629
diff
changeset
|
99 - ST_XMin(:'test_area'::geometry))/2 |
f9f1babe52ae
Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents:
1629
diff
changeset
|
100 + 0.1), 0)), |
f9f1babe52ae
Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents:
1629
diff
changeset
|
101 4326))), |
2232
7936b46a88d4
Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
2081
diff
changeset
|
102 '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
|
103 |
7936b46a88d4
Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
2081
diff
changeset
|
104 SELECT ok( |
7936b46a88d4
Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
2081
diff
changeset
|
105 1 = ST_NumGeometries( |
7936b46a88d4
Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
2081
diff
changeset
|
106 ISRSrange_area( |
2563
dc4fae4bdb8f
Expose axis snapping tolerance to users
Tom Gottfried <tom@intevation.de>
parents:
2535
diff
changeset
|
107 ISRSrange_axis(isrsrange( |
dc4fae4bdb8f
Expose axis snapping tolerance to users
Tom Gottfried <tom@intevation.de>
parents:
2535
diff
changeset
|
108 ('AT', 'XXX', '00001', '00000', 0)::isrs, |
dc4fae4bdb8f
Expose axis snapping tolerance to users
Tom Gottfried <tom@intevation.de>
parents:
2535
diff
changeset
|
109 ('AT', 'XXX', '00001', '00000', 1)::isrs), |
dc4fae4bdb8f
Expose axis snapping tolerance to users
Tom Gottfried <tom@intevation.de>
parents:
2535
diff
changeset
|
110 5), |
2232
7936b46a88d4
Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
2081
diff
changeset
|
111 ST_SetSRID(ST_Collect( |
7936b46a88d4
Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
2081
diff
changeset
|
112 ST_Translate(:'test_area', |
7936b46a88d4
Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
2081
diff
changeset
|
113 (ST_XMax(:'test_area'::geometry) |
7936b46a88d4
Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
2081
diff
changeset
|
114 - ST_XMin(:'test_area'::geometry))/3, |
7936b46a88d4
Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
2081
diff
changeset
|
115 0), |
7936b46a88d4
Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
2081
diff
changeset
|
116 ST_Translate(:'test_area', |
7936b46a88d4
Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
2081
diff
changeset
|
117 -((ST_XMax(:'test_area'::geometry) |
7936b46a88d4
Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
2081
diff
changeset
|
118 - ST_XMin(:'test_area'::geometry))/3), |
7936b46a88d4
Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
2081
diff
changeset
|
119 0)), |
7936b46a88d4
Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
2081
diff
changeset
|
120 4326))), |
7936b46a88d4
Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
2081
diff
changeset
|
121 '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
|
122 |
2453 | 123 SELECT results_eq($$ |
124 SELECT every(ST_DWithin( | |
125 ST_Boundary(ISRSrange_area( | |
2563
dc4fae4bdb8f
Expose axis snapping tolerance to users
Tom Gottfried <tom@intevation.de>
parents:
2535
diff
changeset
|
126 ISRSrange_axis(isrsrange( |
dc4fae4bdb8f
Expose axis snapping tolerance to users
Tom Gottfried <tom@intevation.de>
parents:
2535
diff
changeset
|
127 ('AT', 'XXX', '00001', '00000', 0)::isrs, |
dc4fae4bdb8f
Expose axis snapping tolerance to users
Tom Gottfried <tom@intevation.de>
parents:
2535
diff
changeset
|
128 ('AT', 'XXX', '00001', '00000', 2)::isrs), |
dc4fae4bdb8f
Expose axis snapping tolerance to users
Tom Gottfried <tom@intevation.de>
parents:
2535
diff
changeset
|
129 5), |
2453 | 130 (SELECT ST_Collect(CAST(area AS geometry)) |
131 FROM waterway.waterway_area))), | |
132 geom, | |
133 1)) | |
134 FROM waterway.distance_marks_virtual | |
135 WHERE location_code IN( | |
2431
48495bd3081d
Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents:
2398
diff
changeset
|
136 ('AT', 'XXX', '00001', '00000', 0)::isrs, |
2453 | 137 ('AT', 'XXX', '00001', '00000', 2)::isrs) |
138 $$, | |
139 $$ | |
140 SELECT true | |
141 $$, | |
2431
48495bd3081d
Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents:
2398
diff
changeset
|
142 'Area generated from non-matching distance mark and non-contiguous axis'); |