Mercurial > gemma

annotate schema/tap_tests_data.sql @ 5560:f2204f91d286

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Join the log lines of imports to the log exports to recover data from them. Used in SR export to extract information that where in the meta json but now are only found in the log.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Wed, 09 Feb 2022 18:34:40 +0100
parents 751a0f5da294
children
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
4404
b0c974fc7d34 Fixup rev. 5e38667f740c
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
19 INSERT INTO countries VALUES ('AT'), ('RO'), ('DE')
b0c974fc7d34 Fixup rev. 5e38667f740c
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
20 ON CONFLICT (country_code) DO NOTHING;
4126
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents: 4118
diff changeset
21 INSERT INTO language_codes VALUES ('DE');
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents: 4118
diff changeset
22 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
23
4389
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
24 WITH insert_st AS (
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
25 INSERT INTO users.stretches (
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
26 name, stretch, area, objnam, source_organization, staging_done
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
27 ) VALUES (
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
28 'AT',
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
29 isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
30 ('AT', 'XXX', '00001', '00000', 1)::isrs),
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
31 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
32 'AT',
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
33 'AT',
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
34 true
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
35 ), (
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
36 'AT_RO',
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
37 isrsrange(('AT', 'XXX', '00001', '00000', 1)::isrs,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
38 ('AT', 'XXX', '00001', '00000', 2)::isrs),
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
39 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
40 'AT',
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
41 'AT',
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
42 true
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
43 ), (
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
44 'RO',
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
45 isrsrange(('RO', 'XXX', '00001', '00000', 0)::isrs,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
46 ('RO', 'XXX', '00001', '00000', 1)::isrs),
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
47 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
48 'RO',
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
49 'RO',
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
50 true
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
51 ) RETURNING id, objnam)
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4126
diff changeset
52 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
53
343
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 319
diff changeset
54 INSERT INTO users.list_users VALUES (
5515
751a0f5da294 Fixup rev. 8797274e2739: Adapt test data
Tom Gottfried <tom@intevation.de>
parents: 5514
diff changeset
55 'waterway_user', 'test_user_at', 'user_at1$', 'AT', NULL, 'xxx', false, true);
343
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 319
diff changeset
56 INSERT INTO users.list_users VALUES (
5515
751a0f5da294 Fixup rev. 8797274e2739: Adapt test data
Tom Gottfried <tom@intevation.de>
parents: 5514
diff changeset
57 'waterway_user', 'test_user_ro', 'user_ro1$', 'RO', NULL, 'xxy', false, true);
343
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 319
diff changeset
58 INSERT INTO users.list_users VALUES (
5515
751a0f5da294 Fixup rev. 8797274e2739: Adapt test data
Tom Gottfried <tom@intevation.de>
parents: 5514
diff changeset
59 'waterway_admin', 'test_admin_at', 'admin_at1$', 'AT', NULL, 'yyy', false, true);
343
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 319
diff changeset
60 INSERT INTO users.list_users VALUES (
5515
751a0f5da294 Fixup rev. 8797274e2739: Adapt test data
Tom Gottfried <tom@intevation.de>
parents: 5514
diff changeset
61 'waterway_admin', 'test_admin_at2', 'admin_at2$', 'AT', NULL, 'yyy', false, true);
1904
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
62 INSERT INTO users.list_users VALUES (
5515
751a0f5da294 Fixup rev. 8797274e2739: Adapt test data
Tom Gottfried <tom@intevation.de>
parents: 5514
diff changeset
63 'waterway_admin', 'test_admin_ro', 'admin_ro1$', 'RO', NULL, 'yyx', false, true);
1873
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1629
diff changeset
64 INSERT INTO users.list_users VALUES (
5515
751a0f5da294 Fixup rev. 8797274e2739: Adapt test data
Tom Gottfried <tom@intevation.de>
parents: 5514
diff changeset
65 'sys_admin', 'test_sys_admin1', 'sys_admin1$', 'AT', NULL, 'zzz', false, true);
108
f6aba3ee3a75 Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
66
577
e6c9d2da2e20 Backed out changeset f3452ce5c056
Tom Gottfried <tom@intevation.de>
parents: 567
diff changeset
67 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
68
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
69 WITH
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
70 gs AS (
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
71 INSERT INTO waterway.gauges (
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
72 location,
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
73 validity,
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
74 objname,
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
75 geom,
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
76 zero_point,
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
77 date_info,
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
78 source_organization,
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
79 lastupdate)
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
80 VALUES (
579
642df1164aca Ensure gauges are identified by appropriate ISRS location code
Tom Gottfried <tom@intevation.de>
parents: 577
diff changeset
81 ('AT', 'XXX', '00001', 'G0001', 1)::isrs,
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
82 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
83 'testgauge',
f6aba3ee3a75 Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
84 ST_geomfromtext('POINT(0 0)', 4326),
f6aba3ee3a75 Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
85 0,
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
86 current_timestamp,
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
87 'testorganization',
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
88 current_timestamp)
4118
b785b6bef578 Adapted db tests to new historization-implementation.
Sascha Wilde <wilde@intevation.de>
parents: 4056
diff changeset
89 RETURNING location, validity),
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
90 bns AS (
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
91 VALUES (
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
92 'testbottleneck1',
108
f6aba3ee3a75 Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
93 isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs,
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
94 ('AT', 'XXX', '00001', '00000', 2)::isrs),
1983
f9f1babe52ae Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents: 1904
diff changeset
95 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
96 'AT', 'AT', 'AT',
3645
02951a62e8c6 'Historicise' bottlenecks on import
Tom Gottfried <tom@intevation.de>
parents: 3302
diff changeset
97 1, 'depth', current_timestamp, 'testorganization', false
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
98 ), (
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
99 'testbottleneck2',
108
f6aba3ee3a75 Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
100 isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs,
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
101 ('AT', 'XXX', '00001', '00000', 2)::isrs),
1983
f9f1babe52ae Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents: 1904
diff changeset
102 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
103 'AT', 'AT', 'AT',
3645
02951a62e8c6 'Historicise' bottlenecks on import
Tom Gottfried <tom@intevation.de>
parents: 3302
diff changeset
104 1, 'depth', current_timestamp, 'testorganization', true
5025
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents: 5009
diff changeset
105 ), (
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents: 5009
diff changeset
106 'testbottleneck3',
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents: 5009
diff changeset
107 isrsrange(('RO', 'XXX', '00001', '00000', 0)::isrs,
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents: 5009
diff changeset
108 ('RO', 'XXX', '00001', '00000', 2)::isrs),
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents: 5009
diff changeset
109 ST_geomfromtext('MULTIPOLYGON(((1 0, 1 1, 2 1, 2 0, 1 0)))', 4326),
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents: 5009
diff changeset
110 'RO', 'RO', 'RO',
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents: 5009
diff changeset
111 1, 'depth', current_timestamp, 'testorganization', true
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents: 5009
diff changeset
112 ), (
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents: 5009
diff changeset
113 'testbottleneck4',
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents: 5009
diff changeset
114 isrsrange(('RO', 'XXX', '00001', '00000', 0)::isrs,
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents: 5009
diff changeset
115 ('RO', 'XXX', '00001', '00000', 2)::isrs),
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents: 5009
diff changeset
116 ST_geomfromtext('MULTIPOLYGON(((1 0, 1 1, 2 1, 2 0, 1 0)))', 4326),
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents: 5009
diff changeset
117 'RO', 'RO', 'RO',
4c658a8f34da Fix row level security policies for waterway admin
Tom Gottfried <tom@intevation.de>
parents: 5009
diff changeset
118 1, 'depth', current_timestamp, 'testorganization', true
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
119 ))
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
120 INSERT INTO waterway.bottlenecks (
4118
b785b6bef578 Adapted db tests to new historization-implementation.
Sascha Wilde <wilde@intevation.de>
parents: 4056
diff changeset
121 gauge_location, validity,
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
122 bottleneck_id, stretch, area, rb, lb, responsible_country,
3645
02951a62e8c6 'Historicise' bottlenecks on import
Tom Gottfried <tom@intevation.de>
parents: 3302
diff changeset
123 revisiting_time, limiting, date_info, source_organization, staging_done)
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
124 SELECT * FROM gs, bns;
108
f6aba3ee3a75 Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
125
1629
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1336
diff changeset
126 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
127 ('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
128 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
129 'someENC'
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1336
diff changeset
130 ), (
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1336
diff changeset
131 ('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
132 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
133 'someENC'
2431
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
134 ), (
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
135 ('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
136 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
137 'someENC'
1629
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1336
diff changeset
138 );
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1336
diff changeset
139
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1336
diff changeset
140 INSERT INTO waterway.waterway_axis (wtwaxs, objnam) VALUES (
5009
e8b2dc771f9e Store axis as MultiLinestring
Tom Gottfried <tom@intevation.de>
parents: 4404
diff changeset
141 ST_SetSRID(
e8b2dc771f9e Store axis as MultiLinestring
Tom Gottfried <tom@intevation.de>
parents: 4404
diff changeset
142 ST_Multi(ST_CurveToLine('CIRCULARSTRING(0 0, 0.5 0.5, 0.6 0.4)')),
2398
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2381
diff changeset
143 4326),
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2381
diff changeset
144 'testriver'
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2381
diff changeset
145 ), (
5009
e8b2dc771f9e Store axis as MultiLinestring
Tom Gottfried <tom@intevation.de>
parents: 4404
diff changeset
146 ST_SetSRID(
e8b2dc771f9e Store axis as MultiLinestring
Tom Gottfried <tom@intevation.de>
parents: 4404
diff changeset
147 ST_Multi(ST_CurveToLine('CIRCULARSTRING(0.6 0.4, 1 0, 1.5 -0.00001)')),
2688
d316a6e41f54 Test if overlapping axis chunks are sewed together correctly
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
148 4326),
1629
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1336
diff changeset
149 'testriver'
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1336
diff changeset
150 ), (
5009
e8b2dc771f9e Store axis as MultiLinestring
Tom Gottfried <tom@intevation.de>
parents: 4404
diff changeset
151 ST_SetSRID('MULTILINESTRING((0.5 0.5, 1 1))'::geometry, 4326),
1629
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1336
diff changeset
152 'testriver'
2431
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
153 ), (
5009
e8b2dc771f9e Store axis as MultiLinestring
Tom Gottfried <tom@intevation.de>
parents: 4404
diff changeset
154 ST_SetSRID('MULTILINESTRING((1.5 0, 1.55001 0))'::geometry, 4326),
2688
d316a6e41f54 Test if overlapping axis chunks are sewed together correctly
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
155 'testriver'
d316a6e41f54 Test if overlapping axis chunks are sewed together correctly
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
156 ), (
5009
e8b2dc771f9e Store axis as MultiLinestring
Tom Gottfried <tom@intevation.de>
parents: 4404
diff changeset
157 ST_SetSRID('MULTILINESTRING((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
158 'testriver'
1629
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1336
diff changeset
159 );
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1336
diff changeset
160
2398
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2381
diff changeset
161 -- 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
162 WITH RECURSIVE
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2381
diff changeset
163 buffer AS (
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2381
diff changeset
164 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
165 FROM waterway.waterway_axis),
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2381
diff changeset
166 cleaned AS (
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2381
diff changeset
167 (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
168 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
169 UNION
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2381
diff changeset
170 (SELECT ids || id,
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2381
diff changeset
171 ST_Difference(buf, others),
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2381
diff changeset
172 ST_Union(buf, others)
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2381
diff changeset
173 FROM cleaned, buffer
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2381
diff changeset
174 WHERE id <> ALL(ids)
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2381
diff changeset
175 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
176 FETCH FIRST ROW ONLY)),
d316a6e41f54 Test if overlapping axis chunks are sewed together correctly
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
177 cleaned1 AS (
2781
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2688
diff changeset
178 SELECT DISTINCT geom
2688
d316a6e41f54 Test if overlapping axis chunks are sewed together correctly
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
179 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
180 waterway.waterway_axis
d316a6e41f54 Test if overlapping axis chunks are sewed together correctly
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
181 WHERE ST_Intersects(geom, wtwaxs))
d316a6e41f54 Test if overlapping axis chunks are sewed together correctly
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
182 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
183
2230
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
184 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
185 VALUES ('AT', 'AT', '\x'), ('RO', 'RO', '\x');
1904
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
186
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
187 WITH
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
188 job AS (
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
189 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
190 'test', 'test_admin_ro', 'test') RETURNING id),
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
191 log AS (
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
192 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
193 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
194 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
195 SELECT id, 'waterway.bottlenecks', 1 FROM job;
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
196
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
197 WITH
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
198 config AS (
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
199 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
200 '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
201 INSERT INTO import.import_configuration_attributes
1904
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
202 SELECT id, 'test key', 'test value' FROM config;