Mercurial > gemma

annotate schema/tap_tests_data.sql @ 4389:5e38667f740c stretches-for-responsibility

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Use stretches as areas of responsibility. This is heavily based on a patch by Tom Gottfried (read: >90% of the work was done by Tom).
author Sascha Wilde <wilde@intevation.de>
date Thu, 12 Sep 2019 18:13:47 +0200
parents 52f7264265bb
children b0c974fc7d34
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: 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
4389
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
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: 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');
4126
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents: 4118
diff changeset
20 INSERT INTO language_codes VALUES ('DE');
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents: 4118
diff changeset
21 INSERT INTO depth_references VALUES ('ZPG');
108
f6aba3ee3a75 Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
22
4389
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
23 WITH insert_st AS (
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
24 INSERT INTO users.stretches (
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
25 name, stretch, area, objnam, source_organization, staging_done
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
26 ) VALUES (
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
27 'AT',
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
28 isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
29 ('AT', 'XXX', '00001', '00000', 1)::isrs),
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
30 ST_geomfromtext('MULTIPOLYGON(((-1 0, -1 1, 0 1, 0 0, -1 0)))', 4326),
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
31 'AT',
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
32 'AT',
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
33 true
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
34 ), (
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
35 'AT_RO',
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
36 isrsrange(('AT', 'XXX', '00001', '00000', 1)::isrs,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
37 ('AT', 'XXX', '00001', '00000', 2)::isrs),
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
38 ST_geomfromtext('MULTIPOLYGON(((0 0, 0 1, 1 1, 1 0, 0 0)))', 4326),
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
39 'AT',
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
40 'AT',
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
41 true
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
42 ), (
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
43 'RO',
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
44 isrsrange(('RO', 'XXX', '00001', '00000', 0)::isrs,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
45 ('RO', 'XXX', '00001', '00000', 1)::isrs),
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
46 ST_geomfromtext('MULTIPOLYGON(((1 0, 1 1, 2 1, 2 0, 1 0)))', 4326),
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
47 'RO',
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
48 'RO',
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
49 true
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
50 ) RETURNING id, objnam)
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
51 INSERT INTO users.stretch_countries SELECT id, objnam FROM insert_st;
108
f6aba3ee3a75 Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
52
343
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 319
diff changeset
53 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
54 '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
55 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
56 '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
57 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
58 '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
59 INSERT INTO users.list_users VALUES (
1904
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
60 '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
61 INSERT INTO users.list_users VALUES (
1873
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1629
diff changeset
62 '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
63 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
64 '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
65
577
e6c9d2da2e20 Backed out changeset f3452ce5c056
Tom Gottfried <tom@intevation.de>
parents: 567
diff changeset
66 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
67
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
68 WITH
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
69 gs AS (
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
70 INSERT INTO waterway.gauges (
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
71 location,
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
72 validity,
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
73 objname,
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
74 geom,
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
75 zero_point,
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
76 date_info,
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
77 source_organization,
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
78 lastupdate)
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
79 VALUES (
579
642df1164aca Ensure gauges are identified by appropriate ISRS location code
Tom Gottfried <tom@intevation.de>
parents: 577
diff changeset
80 ('AT', 'XXX', '00001', 'G0001', 1)::isrs,
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
81 tstzrange(current_timestamp - '1 day'::interval, current_timestamp),
108
f6aba3ee3a75 Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
82 'testgauge',
f6aba3ee3a75 Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
83 ST_geomfromtext('POINT(0 0)', 4326),
f6aba3ee3a75 Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
84 0,
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
85 current_timestamp,
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
86 'testorganization',
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
87 current_timestamp)
4118
b785b6bef578 Adapted db tests to new historization-implementation.
Sascha Wilde <wilde@intevation.de>
parents: 4056
diff changeset
88 RETURNING location, validity),
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
89 bns AS (
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
90 VALUES (
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
91 'testbottleneck1',
108
f6aba3ee3a75 Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
92 isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs,
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
93 ('AT', 'XXX', '00001', '00000', 2)::isrs),
1983
f9f1babe52ae Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents: 1904
diff changeset
94 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
95 'AT', 'AT', 'AT',
3645
02951a62e8c6 'Historicise' bottlenecks on import
Tom Gottfried <tom@intevation.de>
parents: 3302
diff changeset
96 1, 'depth', current_timestamp, 'testorganization', false
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
97 ), (
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
98 'testbottleneck2',
108
f6aba3ee3a75 Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
99 isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs,
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
100 ('AT', 'XXX', '00001', '00000', 2)::isrs),
1983
f9f1babe52ae Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents: 1904
diff changeset
101 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
102 'AT', 'AT', 'AT',
3645
02951a62e8c6 'Historicise' bottlenecks on import
Tom Gottfried <tom@intevation.de>
parents: 3302
diff changeset
103 1, 'depth', current_timestamp, 'testorganization', true
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
104 ))
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
105 INSERT INTO waterway.bottlenecks (
4118
b785b6bef578 Adapted db tests to new historization-implementation.
Sascha Wilde <wilde@intevation.de>
parents: 4056
diff changeset
106 gauge_location, validity,
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
107 bottleneck_id, stretch, area, rb, lb, responsible_country,
3645
02951a62e8c6 'Historicise' bottlenecks on import
Tom Gottfried <tom@intevation.de>
parents: 3302
diff changeset
108 revisiting_time, limiting, date_info, source_organization, staging_done)
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
109 SELECT * FROM gs, bns;
108
f6aba3ee3a75 Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
110
1629
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1336
diff changeset
111 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
112 ('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
113 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
114 'someENC'
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1336
diff changeset
115 ), (
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1336
diff changeset
116 ('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
117 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
118 'someENC'
2431
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
119 ), (
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
120 ('AT', 'XXX', '00001', '00000', 2)::isrs,
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
121 ST_SetSRID('POINT(1.6 0)'::geometry, 4326),
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
122 'someENC'
1629
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1336
diff changeset
123 );
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1336
diff changeset
124
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1336
diff changeset
125 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
126 ST_SetSRID(ST_CurveToLine(
2398
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2381
diff changeset
127 '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
128 4326),
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2381
diff changeset
129 'testriver'
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2381
diff changeset
130 ), (
2688
d316a6e41f54 Test if overlapping axis chunks are sewed together correctly
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
131 ST_SetSRID(ST_CurveToLine('CIRCULARSTRING(0.6 0.4, 1 0, 1.5 -0.00001)'),
d316a6e41f54 Test if overlapping axis chunks are sewed together correctly
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
132 4326),
1629
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1336
diff changeset
133 'testriver'
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1336
diff changeset
134 ), (
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1336
diff changeset
135 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
136 'testriver'
2431
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
137 ), (
2688
d316a6e41f54 Test if overlapping axis chunks are sewed together correctly
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
138 ST_SetSRID('LINESTRING(1.5 0, 1.55001 0)'::geometry, 4326),
d316a6e41f54 Test if overlapping axis chunks are sewed together correctly
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
139 'testriver'
d316a6e41f54 Test if overlapping axis chunks are sewed together correctly
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
140 ), (
d316a6e41f54 Test if overlapping axis chunks are sewed together correctly
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
141 ST_SetSRID('LINESTRING(1.55 0, 2 0)'::geometry, 4326),
2431
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
142 'testriver'
1629
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1336
diff changeset
143 );
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1336
diff changeset
144
2398
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2381
diff changeset
145 -- 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
146 WITH RECURSIVE
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2381
diff changeset
147 buffer AS (
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2381
diff changeset
148 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
149 FROM waterway.waterway_axis),
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2381
diff changeset
150 cleaned AS (
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2381
diff changeset
151 (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
152 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
153 UNION
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2381
diff changeset
154 (SELECT ids || id,
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2381
diff changeset
155 ST_Difference(buf, others),
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2381
diff changeset
156 ST_Union(buf, others)
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2381
diff changeset
157 FROM cleaned, buffer
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2381
diff changeset
158 WHERE id <> ALL(ids)
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2381
diff changeset
159 ORDER BY id ASC, ids DESC
2688
d316a6e41f54 Test if overlapping axis chunks are sewed together correctly
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
160 FETCH FIRST ROW ONLY)),
d316a6e41f54 Test if overlapping axis chunks are sewed together correctly
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
161 cleaned1 AS (
2781
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2688
diff changeset
162 SELECT DISTINCT geom
2688
d316a6e41f54 Test if overlapping axis chunks are sewed together correctly
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
163 FROM (SELECT (ST_Dump(cbuf)).geom FROM cleaned) AS dmp,
d316a6e41f54 Test if overlapping axis chunks are sewed together correctly
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
164 waterway.waterway_axis
d316a6e41f54 Test if overlapping axis chunks are sewed together correctly
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
165 WHERE ST_Intersects(geom, wtwaxs))
d316a6e41f54 Test if overlapping axis chunks are sewed together correctly
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
166 INSERT INTO waterway.waterway_area (area) SELECT geom FROM cleaned1;
2398
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2381
diff changeset
167
2230
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
168 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
169 VALUES ('AT', 'AT', '\x'), ('RO', 'RO', '\x');
1904
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
170
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
171 WITH
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
172 job AS (
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
173 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
174 'test', 'test_admin_ro', 'test') RETURNING id),
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
175 log AS (
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
176 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
177 SELECT id, 'test' FROM job)
4056
1ecfe5d015b0 Fixup rev. 4bf1c8d91bac: take new column into account in tests
Tom Gottfried <tom@intevation.de>
parents: 3645
diff changeset
178 INSERT INTO import.track_imports (import_id, relation, key)
1904
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
179 SELECT id, 'waterway.bottlenecks', 1 FROM job;
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
180
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
181 WITH
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
182 config AS (
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
183 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
184 '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
185 INSERT INTO import.import_configuration_attributes
1904
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
186 SELECT id, 'test key', 'test value' FROM config;