Mercurial > gemma
annotate schema/tap_tests_data.sql @ 5560:f2204f91d286
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 |
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 | 19 INSERT INTO countries VALUES ('AT'), ('RO'), ('DE') |
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 | 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 | 91 VALUES ( |
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 | 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 | 98 ), ( |
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 | 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; |