Mercurial > gemma

annotate schema/tap_tests_data.sql @ 5682:33499bd1b829 sr-v2

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Sort indices in spatial index.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Sun, 11 Feb 2024 21:26:22 +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;