gemma: schema/isrs_tests.sql annotate

annotate schema/isrs_tests.sql @ 2455:54c9fe587fe6

Subdivide SQL function to prepare for improved error handling The context of an error (e.g. the function in which it occured) can be inferred by the database client. Not doing all in one statement will render the context more meaningful.

author	Tom Gottfried <tom@intevation.de>
date	Fri, 01 Mar 2019 18:38:02 +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 00cac7890574 Improve TAP tests Tom Gottfried <tom@intevation.de> parents: 2431 diff changeset	45 SELECT results_eq($$
00cac7890574 Improve TAP tests Tom Gottfried <tom@intevation.de> parents: 2431 diff changeset	46 SELECT every(ST_DWithin(
00cac7890574 Improve TAP tests Tom Gottfried <tom@intevation.de> parents: 2431 diff changeset	47 ST_Boundary(ISRSrange_area(
00cac7890574 Improve TAP tests Tom Gottfried <tom@intevation.de> parents: 2431 diff changeset	48 isrsrange(
00cac7890574 Improve TAP tests Tom Gottfried <tom@intevation.de> parents: 2431 diff changeset	49 ('AT', 'XXX', '00001', '00000', 0)::isrs,
00cac7890574 Improve TAP tests Tom Gottfried <tom@intevation.de> parents: 2431 diff changeset	50 ('AT', 'XXX', '00001', '00000', 1)::isrs),
00cac7890574 Improve TAP tests Tom Gottfried <tom@intevation.de> parents: 2431 diff changeset	51 (SELECT ST_Collect(CAST(area AS geometry))
00cac7890574 Improve TAP tests Tom Gottfried <tom@intevation.de> parents: 2431 diff changeset	52 FROM waterway.waterway_area))),
00cac7890574 Improve TAP tests Tom Gottfried <tom@intevation.de> parents: 2431 diff changeset	53 geom,
00cac7890574 Improve TAP tests Tom Gottfried <tom@intevation.de> parents: 2431 diff changeset	54 1))
00cac7890574 Improve TAP tests Tom Gottfried <tom@intevation.de> parents: 2431 diff changeset	55 FROM waterway.distance_marks_virtual
00cac7890574 Improve TAP tests Tom Gottfried <tom@intevation.de> parents: 2431 diff changeset	56 WHERE location_code IN(
00cac7890574 Improve TAP tests Tom Gottfried <tom@intevation.de> parents: 2431 diff changeset	57 ('AT', 'XXX', '00001', '00000', 0)::isrs,
00cac7890574 Improve TAP tests Tom Gottfried <tom@intevation.de> parents: 2431 diff changeset	58 ('AT', 'XXX', '00001', '00000', 1)::isrs)
00cac7890574 Improve TAP tests Tom Gottfried <tom@intevation.de> parents: 2431 diff changeset	59 $$,
00cac7890574 Improve TAP tests Tom Gottfried <tom@intevation.de> parents: 2431 diff changeset	60 $$
00cac7890574 Improve TAP tests Tom Gottfried <tom@intevation.de> parents: 2431 diff changeset	61 SELECT true
00cac7890574 Improve TAP tests Tom Gottfried <tom@intevation.de> parents: 2431 diff changeset	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 00cac7890574 Improve TAP tests Tom Gottfried <tom@intevation.de> parents: 2431 diff changeset	102 SELECT results_eq($$
00cac7890574 Improve TAP tests Tom Gottfried <tom@intevation.de> parents: 2431 diff changeset	103 SELECT every(ST_DWithin(
00cac7890574 Improve TAP tests Tom Gottfried <tom@intevation.de> parents: 2431 diff changeset	104 ST_Boundary(ISRSrange_area(
00cac7890574 Improve TAP tests Tom Gottfried <tom@intevation.de> parents: 2431 diff changeset	105 isrsrange(
00cac7890574 Improve TAP tests Tom Gottfried <tom@intevation.de> parents: 2431 diff changeset	106 ('AT', 'XXX', '00001', '00000', 0)::isrs,
00cac7890574 Improve TAP tests Tom Gottfried <tom@intevation.de> parents: 2431 diff changeset	107 ('AT', 'XXX', '00001', '00000', 2)::isrs),
00cac7890574 Improve TAP tests Tom Gottfried <tom@intevation.de> parents: 2431 diff changeset	108 (SELECT ST_Collect(CAST(area AS geometry))
00cac7890574 Improve TAP tests Tom Gottfried <tom@intevation.de> parents: 2431 diff changeset	109 FROM waterway.waterway_area))),
00cac7890574 Improve TAP tests Tom Gottfried <tom@intevation.de> parents: 2431 diff changeset	110 geom,
00cac7890574 Improve TAP tests Tom Gottfried <tom@intevation.de> parents: 2431 diff changeset	111 1))
00cac7890574 Improve TAP tests Tom Gottfried <tom@intevation.de> parents: 2431 diff changeset	112 FROM waterway.distance_marks_virtual
00cac7890574 Improve TAP tests Tom Gottfried <tom@intevation.de> parents: 2431 diff changeset	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 00cac7890574 Improve TAP tests Tom Gottfried <tom@intevation.de> parents: 2431 diff changeset	115 ('AT', 'XXX', '00001', '00000', 2)::isrs)
00cac7890574 Improve TAP tests Tom Gottfried <tom@intevation.de> parents: 2431 diff changeset	116 $$,
00cac7890574 Improve TAP tests Tom Gottfried <tom@intevation.de> parents: 2431 diff changeset	117 $$
00cac7890574 Improve TAP tests Tom Gottfried <tom@intevation.de> parents: 2431 diff changeset	118 SELECT true
00cac7890574 Improve TAP tests Tom Gottfried <tom@intevation.de> parents: 2431 diff changeset	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');

Mercurial > gemma

annotate schema/isrs_tests.sql @ 2455:54c9fe587fe6