gemma: schema/tap_tests_data.sql annotate

annotate schema/tap_tests_data.sql @ 2425:4c16f5ad1905

Make area generation more robust Valid geometries can become invalid geometries during transformation e.g. due to very small 'minimum clearance' (see PostGIS docs) and numerical inaccuracy of the transformation. Thus, try to repair polygons of the input area after transformation and calculate the union of the result polygons before transformation to avoid 'TopologyException' in the union operations. The simulated waterway area of the changed axis in the test data is affected here, but real waterway area data with complex shore lines could likely be affected, too.

author	Tom Gottfried <tom@intevation.de>
date	Thu, 28 Feb 2019 16:36:49 +0100
parents	8481e6266691
children	48495bd3081d

rev	line source
1298 6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 580 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: 580 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: 580 diff changeset	3
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 580 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: 580 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: 580 diff changeset	6
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 580 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: 580 diff changeset	8 -- – Österreichische Wasserstraßen-Gesellschaft mbH
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 580 diff changeset	9 -- Software engineering by Intevation GmbH
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 580 diff changeset	10
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 580 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>
1336 f65d1767452c add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 1301 diff changeset	13 -- * Sascha Teichmann <sascha.teichmann@intevation.de>
1298 6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 580 diff changeset	14
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 182 diff changeset	15 --
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 182 diff changeset	16 -- Test data used in *_tests.sql scripts
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 182 diff changeset	17 --
109 0e486e6ee60d Set search path in test data script for standalone use. Tom Gottfried <tom@intevation.de> parents: 108 diff changeset	18
2381 99274fed6f3d Validate country and hectometre in ISRS code Tom Gottfried <tom@intevation.de> parents: 2230 diff changeset	19 INSERT INTO countries VALUES ('AT'), ('RO'), ('DE');
108 f6aba3ee3a75 Put test data in own file for easier debugging. Tom Gottfried <tom@intevation.de> parents: diff changeset	20
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 182 diff changeset	21 INSERT INTO users.responsibility_areas VALUES
211 e7826710d9c4 Make responsibility area mandatory Tom Gottfried <tom@intevation.de> parents: 207 diff changeset	22 ('AT', ST_geomfromtext('MULTIPOLYGON(((0 0, 0 1, 1 1, 1 0, 0 0)))', 4326)),
e7826710d9c4 Make responsibility area mandatory Tom Gottfried <tom@intevation.de> parents: 207 diff changeset	23 ('RO', ST_geomfromtext('MULTIPOLYGON(((1 0, 1 1, 2 1, 2 0, 1 0)))', 4326));
108 f6aba3ee3a75 Put test data in own file for easier debugging. Tom Gottfried <tom@intevation.de> parents: diff changeset	24
343 5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	25 INSERT INTO users.list_users VALUES (
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	26 'waterway_user', 'test_user_at', 'user_at1$', 'AT', NULL, 'xxx');
343 5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	27 INSERT INTO users.list_users VALUES (
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	28 'waterway_user', 'test_user_ro', 'user_ro1$', 'RO', NULL, 'xxy');
343 5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	29 INSERT INTO users.list_users VALUES (
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	30 'waterway_admin', 'test_admin_at', 'admin_at1$', 'AT', NULL, 'yyy');
343 5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	31 INSERT INTO users.list_users VALUES (
1904 931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	32 'waterway_admin', 'test_admin_at2', 'admin_at2$', 'AT', NULL, 'yyy');
931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	33 INSERT INTO users.list_users VALUES (
1873 9f8f7d3fd655 Fix policies interfering badly with integrity checks Tom Gottfried <tom@intevation.de> parents: 1629 diff changeset	34 'waterway_admin', 'test_admin_ro', 'admin_ro1$', 'RO', NULL, 'yyx');
9f8f7d3fd655 Fix policies interfering badly with integrity checks Tom Gottfried <tom@intevation.de> parents: 1629 diff changeset	35 INSERT INTO users.list_users VALUES (
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	36 'sys_admin', 'test_sys_admin1', 'sys_admin1$', 'AT', NULL, 'zzz');
108 f6aba3ee3a75 Put test data in own file for easier debugging. Tom Gottfried <tom@intevation.de> parents: diff changeset	37
577 e6c9d2da2e20 Backed out changeset f3452ce5c056 Tom Gottfried <tom@intevation.de> parents: 567 diff changeset	38 INSERT INTO limiting_factors VALUES ('depth'), ('width');
108 f6aba3ee3a75 Put test data in own file for easier debugging. Tom Gottfried <tom@intevation.de> parents: diff changeset	39
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 182 diff changeset	40 INSERT INTO waterway.gauges (
580 e78bdbb6cac8 Remove worthless attribute Tom Gottfried <tom@intevation.de> parents: 579 diff changeset	41 location, objname, geom, zero_point, source_organization)
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	42 VALUES (
579 642df1164aca Ensure gauges are identified by appropriate ISRS location code Tom Gottfried <tom@intevation.de> parents: 577 diff changeset	43 ('AT', 'XXX', '00001', 'G0001', 1)::isrs,
108 f6aba3ee3a75 Put test data in own file for easier debugging. Tom Gottfried <tom@intevation.de> parents: diff changeset	44 'testgauge',
f6aba3ee3a75 Put test data in own file for easier debugging. Tom Gottfried <tom@intevation.de> parents: diff changeset	45 ST_geomfromtext('POINT(0 0)', 4326),
f6aba3ee3a75 Put test data in own file for easier debugging. Tom Gottfried <tom@intevation.de> parents: diff changeset	46 0,
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	47 'testorganization'
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	48 );
108 f6aba3ee3a75 Put test data in own file for easier debugging. Tom Gottfried <tom@intevation.de> parents: diff changeset	49
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 182 diff changeset	50 INSERT INTO waterway.bottlenecks (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	51 bottleneck_id, fk_g_fid, stretch, area, rb, lb, responsible_country,
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	52 revisiting_time, limiting, source_organization, staging_done)
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	53 VALUES (
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	54 'testbottleneck1',
579 642df1164aca Ensure gauges are identified by appropriate ISRS location code Tom Gottfried <tom@intevation.de> parents: 577 diff changeset	55 ('AT', 'XXX', '00001', 'G0001', 1)::isrs,
108 f6aba3ee3a75 Put test data in own file for easier debugging. Tom Gottfried <tom@intevation.de> parents: diff changeset	56 isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs,
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	57 ('AT', 'XXX', '00001', '00000', 2)::isrs),
1983 f9f1babe52ae Fix area generation from multipolygon input Tom Gottfried <tom@intevation.de> parents: 1904 diff changeset	58 ST_geomfromtext('MULTIPOLYGON(((0 0, 0 1, 1 1, 1 0, 0 0)))', 4326),
108 f6aba3ee3a75 Put test data in own file for easier debugging. Tom Gottfried <tom@intevation.de> parents: diff changeset	59 'AT', 'AT', 'AT',
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	60 1, 'depth', 'testorganization', false
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	61 ), (
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	62 'testbottleneck2',
579 642df1164aca Ensure gauges are identified by appropriate ISRS location code Tom Gottfried <tom@intevation.de> parents: 577 diff changeset	63 ('AT', 'XXX', '00001', 'G0001', 1)::isrs,
108 f6aba3ee3a75 Put test data in own file for easier debugging. Tom Gottfried <tom@intevation.de> parents: diff changeset	64 isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs,
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	65 ('AT', 'XXX', '00001', '00000', 2)::isrs),
1983 f9f1babe52ae Fix area generation from multipolygon input Tom Gottfried <tom@intevation.de> parents: 1904 diff changeset	66 ST_geomfromtext('MULTIPOLYGON(((0 0, 0 1, 1 1, 1 0, 0 0)))', 4326),
108 f6aba3ee3a75 Put test data in own file for easier debugging. Tom Gottfried <tom@intevation.de> parents: diff changeset	67 'AT', 'AT', 'AT',
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	68 1, 'depth', 'testorganization', true
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	69 );
108 f6aba3ee3a75 Put test data in own file for easier debugging. Tom Gottfried <tom@intevation.de> parents: diff changeset	70
1629 9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: 1336 diff changeset	71 INSERT INTO waterway.distance_marks_virtual VALUES (
9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: 1336 diff changeset	72 ('AT', 'XXX', '00001', '00000', 0)::isrs,
9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: 1336 diff changeset	73 ST_SetSRID('POINT(0 0)'::geometry, 4326),
9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: 1336 diff changeset	74 'someENC'
9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: 1336 diff changeset	75 ), (
9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: 1336 diff changeset	76 ('AT', 'XXX', '00001', '00000', 1)::isrs,
9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: 1336 diff changeset	77 ST_SetSRID('POINT(1 0)'::geometry, 4326),
9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: 1336 diff changeset	78 'someENC'
9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: 1336 diff changeset	79 );
9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: 1336 diff changeset	80
9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: 1336 diff changeset	81 INSERT INTO waterway.waterway_axis (wtwaxs, objnam) VALUES (
9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: 1336 diff changeset	82 ST_SetSRID(ST_CurveToLine(
2398 8481e6266691 Fix corner case in area generation from stretch Tom Gottfried <tom@intevation.de> parents: 2381 diff changeset	83 'CIRCULARSTRING(0 0, 0.5 0.5, 0.6 0.4)'),
8481e6266691 Fix corner case in area generation from stretch Tom Gottfried <tom@intevation.de> parents: 2381 diff changeset	84 4326),
8481e6266691 Fix corner case in area generation from stretch Tom Gottfried <tom@intevation.de> parents: 2381 diff changeset	85 'testriver'
8481e6266691 Fix corner case in area generation from stretch Tom Gottfried <tom@intevation.de> parents: 2381 diff changeset	86 ), (
2425 4c16f5ad1905 Make area generation more robust Tom Gottfried <tom@intevation.de> parents: 2398 diff changeset	87 ST_SetSRID(ST_CurveToLine('CIRCULARSTRING(0.6 0.4, 1 0, 1.5 0)'), 4326),
1629 9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: 1336 diff changeset	88 'testriver'
9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: 1336 diff changeset	89 ), (
9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: 1336 diff changeset	90 ST_SetSRID('LINESTRING(0.5 0.5, 1 1)'::geometry, 4326),
9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: 1336 diff changeset	91 'testriver'
9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: 1336 diff changeset	92 );
9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: 1336 diff changeset	93
2398 8481e6266691 Fix corner case in area generation from stretch Tom Gottfried <tom@intevation.de> parents: 2381 diff changeset	94 -- Simulate waterway area as non-intersecting buffers around axis
8481e6266691 Fix corner case in area generation from stretch Tom Gottfried <tom@intevation.de> parents: 2381 diff changeset	95 WITH RECURSIVE
8481e6266691 Fix corner case in area generation from stretch Tom Gottfried <tom@intevation.de> parents: 2381 diff changeset	96 buffer AS (
8481e6266691 Fix corner case in area generation from stretch Tom Gottfried <tom@intevation.de> parents: 2381 diff changeset	97 SELECT id, ST_Buffer(wtwaxs, 10000, 'endcap=flat')::geometry AS buf
8481e6266691 Fix corner case in area generation from stretch Tom Gottfried <tom@intevation.de> parents: 2381 diff changeset	98 FROM waterway.waterway_axis),
8481e6266691 Fix corner case in area generation from stretch Tom Gottfried <tom@intevation.de> parents: 2381 diff changeset	99 cleaned AS (
8481e6266691 Fix corner case in area generation from stretch Tom Gottfried <tom@intevation.de> parents: 2381 diff changeset	100 (SELECT ARRAY[id] AS ids, buf AS cbuf, buf AS others
8481e6266691 Fix corner case in area generation from stretch Tom Gottfried <tom@intevation.de> parents: 2381 diff changeset	101 FROM buffer ORDER BY id FETCH FIRST ROW ONLY)
8481e6266691 Fix corner case in area generation from stretch Tom Gottfried <tom@intevation.de> parents: 2381 diff changeset	102 UNION
8481e6266691 Fix corner case in area generation from stretch Tom Gottfried <tom@intevation.de> parents: 2381 diff changeset	103 (SELECT ids \|\| id,
8481e6266691 Fix corner case in area generation from stretch Tom Gottfried <tom@intevation.de> parents: 2381 diff changeset	104 ST_Difference(buf, others),
8481e6266691 Fix corner case in area generation from stretch Tom Gottfried <tom@intevation.de> parents: 2381 diff changeset	105 ST_Union(buf, others)
8481e6266691 Fix corner case in area generation from stretch Tom Gottfried <tom@intevation.de> parents: 2381 diff changeset	106 FROM cleaned, buffer
8481e6266691 Fix corner case in area generation from stretch Tom Gottfried <tom@intevation.de> parents: 2381 diff changeset	107 WHERE id <> ALL(ids)
8481e6266691 Fix corner case in area generation from stretch Tom Gottfried <tom@intevation.de> parents: 2381 diff changeset	108 ORDER BY id ASC, ids DESC
8481e6266691 Fix corner case in area generation from stretch Tom Gottfried <tom@intevation.de> parents: 2381 diff changeset	109 FETCH FIRST ROW ONLY))
8481e6266691 Fix corner case in area generation from stretch Tom Gottfried <tom@intevation.de> parents: 2381 diff changeset	110 INSERT INTO waterway.waterway_area (area) SELECT cbuf FROM cleaned;
8481e6266691 Fix corner case in area generation from stretch Tom Gottfried <tom@intevation.de> parents: 2381 diff changeset	111
2230 4374d942b23d Remove debris and associate templates to country Tom Gottfried <tom@intevation.de> parents: 2147 diff changeset	112 INSERT INTO users.templates (template_name, country, template_data)
4374d942b23d Remove debris and associate templates to country Tom Gottfried <tom@intevation.de> parents: 2147 diff changeset	113 VALUES ('AT', 'AT', '\x'), ('RO', 'RO', '\x');
1904 931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	114
931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	115 WITH
931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	116 job AS (
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	117 INSERT INTO import.imports (kind, username, data) VALUES (
1904 931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	118 'test', 'test_admin_ro', 'test') RETURNING id),
931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	119 log AS (
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	120 INSERT INTO import.import_logs (import_id, msg)
1904 931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	121 SELECT id, 'test' FROM job)
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	122 INSERT INTO import.track_imports
1904 931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	123 SELECT id, 'waterway.bottlenecks', 1 FROM job;
931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	124
931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	125 WITH
931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	126 config AS (
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	127 INSERT INTO import.import_configuration (kind, username) VALUES (
1904 931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	128 'test', 'test_admin_ro') RETURNING id)
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	129 INSERT INTO import.import_configuration_attributes
1904 931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	130 SELECT id, 'test key', 'test value' FROM config;

Mercurial > gemma

annotate schema/tap_tests_data.sql @ 2425:4c16f5ad1905