Mercurial > gemma

annotate schema/tap_tests_data.sql @ 4126:52f7264265bb

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Bulk-delete tracked entries per table Especially when many entries in one table are tracked by one import, this makes declining an import a lot faster.
author Tom Gottfried <tom@intevation.de>
date Thu, 01 Aug 2019 17:02:09 +0200
parents b785b6bef578
children 5e38667f740c
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
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 580
diff changeset
7 -- Copyright (C) 2018 by via donau
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 580
diff changeset
8 -- – Österreichische Wasserstraßen-Gesellschaft mbH
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 580
diff changeset
9 -- Software engineering by Intevation GmbH
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 580
diff changeset
10
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 580
diff changeset
11 -- Author(s):
1301
2304778c4432 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1298
diff changeset
12 -- * Tom Gottfried <tom@intevation.de>
1336
f65d1767452c add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1301
diff changeset
13 -- * Sascha Teichmann <sascha.teichmann@intevation.de>
1298
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 580
diff changeset
14
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
15 --
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
16 -- Test data used in *_tests.sql scripts
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
17 --
109
0e486e6ee60d Set search path in test data script for standalone use.
Tom Gottfried <tom@intevation.de>
parents: 108
diff changeset
18
2381
99274fed6f3d Validate country and hectometre in ISRS code
Tom Gottfried <tom@intevation.de>
parents: 2230
diff changeset
19 INSERT INTO countries VALUES ('AT'), ('RO'), ('DE');
4126
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents: 4118
diff changeset
20 INSERT INTO language_codes VALUES ('DE');
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents: 4118
diff changeset
21 INSERT INTO depth_references VALUES ('ZPG');
108
f6aba3ee3a75 Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
22
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
23 INSERT INTO users.responsibility_areas VALUES
211
e7826710d9c4 Make responsibility area mandatory
Tom Gottfried <tom@intevation.de>
parents: 207
diff changeset
24 ('AT', ST_geomfromtext('MULTIPOLYGON(((0 0, 0 1, 1 1, 1 0, 0 0)))', 4326)),
e7826710d9c4 Make responsibility area mandatory
Tom Gottfried <tom@intevation.de>
parents: 207
diff changeset
25 ('RO', ST_geomfromtext('MULTIPOLYGON(((1 0, 1 1, 2 1, 2 0, 1 0)))', 4326));
108
f6aba3ee3a75 Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
26
343
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 319
diff changeset
27 INSERT INTO users.list_users VALUES (
262
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
28 'waterway_user', 'test_user_at', 'user_at1$', 'AT', NULL, 'xxx');
343
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 319
diff changeset
29 INSERT INTO users.list_users VALUES (
262
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
30 'waterway_user', 'test_user_ro', 'user_ro1$', 'RO', NULL, 'xxy');
343
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 319
diff changeset
31 INSERT INTO users.list_users VALUES (
262
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
32 'waterway_admin', 'test_admin_at', 'admin_at1$', 'AT', NULL, 'yyy');
343
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 319
diff changeset
33 INSERT INTO users.list_users VALUES (
1904
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
34 'waterway_admin', 'test_admin_at2', 'admin_at2$', 'AT', NULL, 'yyy');
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
35 INSERT INTO users.list_users VALUES (
1873
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1629
diff changeset
36 'waterway_admin', 'test_admin_ro', 'admin_ro1$', 'RO', NULL, 'yyx');
9f8f7d3fd655 Fix policies interfering badly with integrity checks
Tom Gottfried <tom@intevation.de>
parents: 1629
diff changeset
37 INSERT INTO users.list_users VALUES (
262
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
38 'sys_admin', 'test_sys_admin1', 'sys_admin1$', 'AT', NULL, 'zzz');
108
f6aba3ee3a75 Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
39
577
e6c9d2da2e20 Backed out changeset f3452ce5c056
Tom Gottfried <tom@intevation.de>
parents: 567
diff changeset
40 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
41
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
42 WITH
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
43 gs AS (
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
44 INSERT INTO waterway.gauges (
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
45 location,
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
46 validity,
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
47 objname,
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
48 geom,
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
49 zero_point,
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
50 date_info,
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
51 source_organization,
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
52 lastupdate)
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
53 VALUES (
579
642df1164aca Ensure gauges are identified by appropriate ISRS location code
Tom Gottfried <tom@intevation.de>
parents: 577
diff changeset
54 ('AT', 'XXX', '00001', 'G0001', 1)::isrs,
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
55 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
56 'testgauge',
f6aba3ee3a75 Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
57 ST_geomfromtext('POINT(0 0)', 4326),
f6aba3ee3a75 Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
58 0,
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
59 current_timestamp,
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
60 'testorganization',
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
61 current_timestamp)
4118
b785b6bef578 Adapted db tests to new historization-implementation.
Sascha Wilde <wilde@intevation.de>
parents: 4056
diff changeset
62 RETURNING location, validity),
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
63 bns AS (
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
64 VALUES (
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
65 'testbottleneck1',
108
f6aba3ee3a75 Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
66 isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs,
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
67 ('AT', 'XXX', '00001', '00000', 2)::isrs),
1983
f9f1babe52ae Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents: 1904
diff changeset
68 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
69 'AT', 'AT', 'AT',
3645
02951a62e8c6 'Historicise' bottlenecks on import
Tom Gottfried <tom@intevation.de>
parents: 3302
diff changeset
70 1, 'depth', current_timestamp, 'testorganization', false
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
71 ), (
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
72 'testbottleneck2',
108
f6aba3ee3a75 Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
73 isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs,
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
74 ('AT', 'XXX', '00001', '00000', 2)::isrs),
1983
f9f1babe52ae Fix area generation from multipolygon input
Tom Gottfried <tom@intevation.de>
parents: 1904
diff changeset
75 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
76 'AT', 'AT', 'AT',
3645
02951a62e8c6 'Historicise' bottlenecks on import
Tom Gottfried <tom@intevation.de>
parents: 3302
diff changeset
77 1, 'depth', current_timestamp, 'testorganization', true
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
78 ))
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
79 INSERT INTO waterway.bottlenecks (
4118
b785b6bef578 Adapted db tests to new historization-implementation.
Sascha Wilde <wilde@intevation.de>
parents: 4056
diff changeset
80 gauge_location, validity,
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
81 bottleneck_id, stretch, area, rb, lb, responsible_country,
3645
02951a62e8c6 'Historicise' bottlenecks on import
Tom Gottfried <tom@intevation.de>
parents: 3302
diff changeset
82 revisiting_time, limiting, date_info, source_organization, staging_done)
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
83 SELECT * FROM gs, bns;
108
f6aba3ee3a75 Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
84
1629
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1336
diff changeset
85 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
86 ('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
87 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
88 'someENC'
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1336
diff changeset
89 ), (
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1336
diff changeset
90 ('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
91 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
92 'someENC'
2431
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
93 ), (
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
94 ('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
95 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
96 'someENC'
1629
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1336
diff changeset
97 );
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1336
diff changeset
98
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1336
diff changeset
99 INSERT INTO waterway.waterway_axis (wtwaxs, objnam) VALUES (
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1336
diff changeset
100 ST_SetSRID(ST_CurveToLine(
2398
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2381
diff changeset
101 'CIRCULARSTRING(0 0, 0.5 0.5, 0.6 0.4)'),
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2381
diff changeset
102 4326),
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2381
diff changeset
103 'testriver'
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2381
diff changeset
104 ), (
2688
d316a6e41f54 Test if overlapping axis chunks are sewed together correctly
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
105 ST_SetSRID(ST_CurveToLine('CIRCULARSTRING(0.6 0.4, 1 0, 1.5 -0.00001)'),
d316a6e41f54 Test if overlapping axis chunks are sewed together correctly
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
106 4326),
1629
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1336
diff changeset
107 'testriver'
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1336
diff changeset
108 ), (
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1336
diff changeset
109 ST_SetSRID('LINESTRING(0.5 0.5, 1 1)'::geometry, 4326),
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1336
diff changeset
110 'testriver'
2431
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
111 ), (
2688
d316a6e41f54 Test if overlapping axis chunks are sewed together correctly
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
112 ST_SetSRID('LINESTRING(1.5 0, 1.55001 0)'::geometry, 4326),
d316a6e41f54 Test if overlapping axis chunks are sewed together correctly
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
113 'testriver'
d316a6e41f54 Test if overlapping axis chunks are sewed together correctly
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
114 ), (
d316a6e41f54 Test if overlapping axis chunks are sewed together correctly
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
115 ST_SetSRID('LINESTRING(1.55 0, 2 0)'::geometry, 4326),
2431
48495bd3081d Construct stretch area between distance marks also from non-contiguous axis
Tom Gottfried <tom@intevation.de>
parents: 2425
diff changeset
116 'testriver'
1629
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1336
diff changeset
117 );
9d51f022b8ee Introduce SQL function to clip an area to a stretch
Tom Gottfried <tom@intevation.de>
parents: 1336
diff changeset
118
2398
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2381
diff changeset
119 -- 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
120 WITH RECURSIVE
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2381
diff changeset
121 buffer AS (
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2381
diff changeset
122 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
123 FROM waterway.waterway_axis),
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2381
diff changeset
124 cleaned AS (
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2381
diff changeset
125 (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
126 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
127 UNION
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2381
diff changeset
128 (SELECT ids || id,
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2381
diff changeset
129 ST_Difference(buf, others),
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2381
diff changeset
130 ST_Union(buf, others)
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2381
diff changeset
131 FROM cleaned, buffer
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2381
diff changeset
132 WHERE id <> ALL(ids)
8481e6266691 Fix corner case in area generation from stretch
Tom Gottfried <tom@intevation.de>
parents: 2381
diff changeset
133 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
134 FETCH FIRST ROW ONLY)),
d316a6e41f54 Test if overlapping axis chunks are sewed together correctly
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
135 cleaned1 AS (
2781
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2688
diff changeset
136 SELECT DISTINCT geom
2688
d316a6e41f54 Test if overlapping axis chunks are sewed together correctly
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
137 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
138 waterway.waterway_axis
d316a6e41f54 Test if overlapping axis chunks are sewed together correctly
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
139 WHERE ST_Intersects(geom, wtwaxs))
d316a6e41f54 Test if overlapping axis chunks are sewed together correctly
Tom Gottfried <tom@intevation.de>
parents: 2514
diff changeset
140 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
141
2230
4374d942b23d Remove debris and associate templates to country
Tom Gottfried <tom@intevation.de>
parents: 2147
diff changeset
142 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
143 VALUES ('AT', 'AT', '\x'), ('RO', 'RO', '\x');
1904
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
144
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
145 WITH
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
146 job AS (
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
147 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
148 'test', 'test_admin_ro', 'test') RETURNING id),
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
149 log AS (
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
150 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
151 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
152 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
153 SELECT id, 'waterway.bottlenecks', 1 FROM job;
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
154
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
155 WITH
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
156 config AS (
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
157 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
158 '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
159 INSERT INTO import.import_configuration_attributes
1904
931b15be6d7f Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents: 1873
diff changeset
160 SELECT id, 'test key', 'test value' FROM config;