Mercurial > gemma
annotate schema/isrs_tests.sql @ 2454:219c5b57ef5b
Added icon to gouges in search result.
Could be better, but the more fitting "ruler-vertical" is not
available in font awesome 4.7...
author | Sascha Wilde <wilde@intevation.de> |
---|---|
date | Fri, 01 Mar 2019 18:10:26 +0100 |
parents | 00cac7890574 |
children | 73c8762cee60 |
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 |
2453 | 45 SELECT results_eq($$ |
46 SELECT every(ST_DWithin( | |
47 ST_Boundary(ISRSrange_area( | |
48 isrsrange( | |
49 ('AT', 'XXX', '00001', '00000', 0)::isrs, | |
50 ('AT', 'XXX', '00001', '00000', 1)::isrs), | |
51 (SELECT ST_Collect(CAST(area AS geometry)) | |
52 FROM waterway.waterway_area))), | |
53 geom, | |
54 1)) | |
55 FROM waterway.distance_marks_virtual | |
56 WHERE location_code IN( | |
57 ('AT', 'XXX', '00001', '00000', 0)::isrs, | |
58 ('AT', 'XXX', '00001', '00000', 1)::isrs) | |
59 $$, | |
60 $$ | |
61 SELECT true | |
62 $$, | |
1629
9d51f022b8ee
Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
63 '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
|
64 |
2398
8481e6266691
Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
2232
diff
changeset
|
65 \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
|
66 SELECT ok( |
f9f1babe52ae
Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents:
1629
diff
changeset
|
67 2 = ST_NumGeometries( |
f9f1babe52ae
Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents:
1629
diff
changeset
|
68 ISRSrange_area( |
f9f1babe52ae
Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents:
1629
diff
changeset
|
69 isrsrange( |
f9f1babe52ae
Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents:
1629
diff
changeset
|
70 ('AT', 'XXX', '00001', '00000', 0)::isrs, |
f9f1babe52ae
Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents:
1629
diff
changeset
|
71 ('AT', 'XXX', '00001', '00000', 1)::isrs), |
f9f1babe52ae
Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents:
1629
diff
changeset
|
72 ST_SetSRID(ST_Collect( |
f9f1babe52ae
Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents:
1629
diff
changeset
|
73 ST_Translate(:'test_area', |
f9f1babe52ae
Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents:
1629
diff
changeset
|
74 (ST_XMax(:'test_area'::geometry) |
f9f1babe52ae
Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents:
1629
diff
changeset
|
75 - ST_XMin(:'test_area'::geometry))/2 |
f9f1babe52ae
Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents:
1629
diff
changeset
|
76 + 0.1, 0), |
f9f1babe52ae
Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents:
1629
diff
changeset
|
77 ST_Translate(:'test_area', |
f9f1babe52ae
Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents:
1629
diff
changeset
|
78 -((ST_XMax(:'test_area'::geometry) |
f9f1babe52ae
Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents:
1629
diff
changeset
|
79 - ST_XMin(:'test_area'::geometry))/2 |
f9f1babe52ae
Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents:
1629
diff
changeset
|
80 + 0.1), 0)), |
f9f1babe52ae
Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents:
1629
diff
changeset
|
81 4326))), |
2232
7936b46a88d4
Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
2081
diff
changeset
|
82 '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
|
83 |
7936b46a88d4
Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
2081
diff
changeset
|
84 SELECT ok( |
7936b46a88d4
Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
2081
diff
changeset
|
85 1 = ST_NumGeometries( |
7936b46a88d4
Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
2081
diff
changeset
|
86 ISRSrange_area( |
7936b46a88d4
Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
2081
diff
changeset
|
87 isrsrange( |
7936b46a88d4
Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
2081
diff
changeset
|
88 ('AT', 'XXX', '00001', '00000', 0)::isrs, |
7936b46a88d4
Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
2081
diff
changeset
|
89 ('AT', 'XXX', '00001', '00000', 1)::isrs), |
7936b46a88d4
Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
2081
diff
changeset
|
90 ST_SetSRID(ST_Collect( |
7936b46a88d4
Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
2081
diff
changeset
|
91 ST_Translate(:'test_area', |
7936b46a88d4
Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
2081
diff
changeset
|
92 (ST_XMax(:'test_area'::geometry) |
7936b46a88d4
Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
2081
diff
changeset
|
93 - ST_XMin(:'test_area'::geometry))/3, |
7936b46a88d4
Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
2081
diff
changeset
|
94 0), |
7936b46a88d4
Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
2081
diff
changeset
|
95 ST_Translate(:'test_area', |
7936b46a88d4
Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
2081
diff
changeset
|
96 -((ST_XMax(:'test_area'::geometry) |
7936b46a88d4
Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
2081
diff
changeset
|
97 - ST_XMin(:'test_area'::geometry))/3), |
7936b46a88d4
Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
2081
diff
changeset
|
98 0)), |
7936b46a88d4
Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
2081
diff
changeset
|
99 4326))), |
7936b46a88d4
Handle invalid multipolygons in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents:
2081
diff
changeset
|
100 '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
|
101 |
2453 | 102 SELECT results_eq($$ |
103 SELECT every(ST_DWithin( | |
104 ST_Boundary(ISRSrange_area( | |
105 isrsrange( | |
106 ('AT', 'XXX', '00001', '00000', 0)::isrs, | |
107 ('AT', 'XXX', '00001', '00000', 2)::isrs), | |
108 (SELECT ST_Collect(CAST(area AS geometry)) | |
109 FROM waterway.waterway_area))), | |
110 geom, | |
111 1)) | |
112 FROM waterway.distance_marks_virtual | |
113 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
|
114 ('AT', 'XXX', '00001', '00000', 0)::isrs, |
2453 | 115 ('AT', 'XXX', '00001', '00000', 2)::isrs) |
116 $$, | |
117 $$ | |
118 SELECT true | |
119 $$, | |
2431
48495bd3081d
Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents:
2398
diff
changeset
|
120 'Area generated from non-matching distance mark and non-contiguous axis'); |