Mercurial > gemma
annotate schema/tap_tests_data.sql @ 5625:39c1698fb0ff
Bumped dev version
author | Sascha Wilde <wilde@sha-bang.de> |
---|---|
date | Thu, 22 Dec 2022 18:43:46 +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; |