Mercurial > gemma

annotate schema/isrs_tests.sql @ 2081:40711ca3aa19

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add function to get text representation of location code The text representation is appropriate for user visible output as well as for use in SQL expressions where the composite type is not supported.
author Tom Gottfried <tom@intevation.de>
date Thu, 31 Jan 2019 12:36:54 +0100
parents f9f1babe52ae
children 7936b46a88d4
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
1983
f9f1babe52ae Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents: 1629
diff changeset
45 \set test_area 'POLYGON((-1 1, 2 1, 2 -1, -1 -1, -1 1))'
1629
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
46 SELECT ok(
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
47 ST_DWithin(
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
48 (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
49 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
50 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
51 ('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
52 ('AT', 'XXX', '00001', '00000', 1)::isrs),
1983
f9f1babe52ae Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents: 1629
diff changeset
53 ST_SetSRID(:'test_area'::geometry,
1629
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
54 4326)))::geography,
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
55 1)
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
56 AND
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
57 ST_DWithin(
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
58 (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
59 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
60 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
61 ('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
62 ('AT', 'XXX', '00001', '00000', 1)::isrs),
1983
f9f1babe52ae Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents: 1629
diff changeset
63 ST_SetSRID(:'test_area'::geometry,
1629
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
64 4326)))::geography,
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
65 1),
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
66 '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
67
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))),
f9f1babe52ae Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents: 1629
diff changeset
84 'Two intersecting polygons lead to two polygons in result');