Mercurial > gemma
view 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 |
line wrap: on
line source
-- This is Free Software under GNU Affero General Public License v >= 3.0 -- without warranty, see README.md and license for details. -- SPDX-License-Identifier: AGPL-3.0-or-later -- License-Filename: LICENSES/AGPL-3.0.txt -- Copyright (C) 2018, 2019 by via donau -- – Österreichische Wasserstraßen-Gesellschaft mbH -- Software engineering by Intevation GmbH -- Author(s): -- * Tom Gottfried <tom@intevation.de> -- * Sascha Teichmann <sascha.teichmann@intevation.de> -- -- Test data used in *_tests.sql scripts -- INSERT INTO countries VALUES ('AT'), ('RO'), ('DE') ON CONFLICT (country_code) DO NOTHING; INSERT INTO language_codes VALUES ('DE'); INSERT INTO depth_references VALUES ('ZPG'); WITH insert_st AS ( INSERT INTO users.stretches ( name, stretch, area, objnam, source_organization, staging_done ) VALUES ( 'AT', isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs, ('AT', 'XXX', '00001', '00000', 1)::isrs), ST_geomfromtext('MULTIPOLYGON(((-1 0, -1 1, 0 1, 0 0, -1 0)))', 4326), 'AT', 'AT', true ), ( 'AT_RO', isrsrange(('AT', 'XXX', '00001', '00000', 1)::isrs, ('AT', 'XXX', '00001', '00000', 2)::isrs), ST_geomfromtext('MULTIPOLYGON(((0 0, 0 1, 1 1, 1 0, 0 0)))', 4326), 'AT', 'AT', true ), ( 'RO', isrsrange(('RO', 'XXX', '00001', '00000', 0)::isrs, ('RO', 'XXX', '00001', '00000', 1)::isrs), ST_geomfromtext('MULTIPOLYGON(((1 0, 1 1, 2 1, 2 0, 1 0)))', 4326), 'RO', 'RO', true ) RETURNING id, objnam) INSERT INTO users.stretch_countries SELECT id, objnam FROM insert_st; INSERT INTO users.list_users VALUES ( 'waterway_user', 'test_user_at', 'user_at1$', 'AT', NULL, 'xxx', false, true); INSERT INTO users.list_users VALUES ( 'waterway_user', 'test_user_ro', 'user_ro1$', 'RO', NULL, 'xxy', false, true); INSERT INTO users.list_users VALUES ( 'waterway_admin', 'test_admin_at', 'admin_at1$', 'AT', NULL, 'yyy', false, true); INSERT INTO users.list_users VALUES ( 'waterway_admin', 'test_admin_at2', 'admin_at2$', 'AT', NULL, 'yyy', false, true); INSERT INTO users.list_users VALUES ( 'waterway_admin', 'test_admin_ro', 'admin_ro1$', 'RO', NULL, 'yyx', false, true); INSERT INTO users.list_users VALUES ( 'sys_admin', 'test_sys_admin1', 'sys_admin1$', 'AT', NULL, 'zzz', false, true); INSERT INTO limiting_factors VALUES ('depth'), ('width'); WITH gs AS ( INSERT INTO waterway.gauges ( location, validity, objname, geom, zero_point, date_info, source_organization, lastupdate) VALUES ( ('AT', 'XXX', '00001', 'G0001', 1)::isrs, tstzrange(current_timestamp - '1 day'::interval, current_timestamp), 'testgauge', ST_geomfromtext('POINT(0 0)', 4326), 0, current_timestamp, 'testorganization', current_timestamp) RETURNING location, validity), bns AS ( VALUES ( 'testbottleneck1', isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs, ('AT', 'XXX', '00001', '00000', 2)::isrs), ST_geomfromtext('MULTIPOLYGON(((0 0, 0 1, 1 1, 1 0, 0 0)))', 4326), 'AT', 'AT', 'AT', 1, 'depth', current_timestamp, 'testorganization', false ), ( 'testbottleneck2', isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs, ('AT', 'XXX', '00001', '00000', 2)::isrs), ST_geomfromtext('MULTIPOLYGON(((0 0, 0 1, 1 1, 1 0, 0 0)))', 4326), 'AT', 'AT', 'AT', 1, 'depth', current_timestamp, 'testorganization', true ), ( 'testbottleneck3', isrsrange(('RO', 'XXX', '00001', '00000', 0)::isrs, ('RO', 'XXX', '00001', '00000', 2)::isrs), ST_geomfromtext('MULTIPOLYGON(((1 0, 1 1, 2 1, 2 0, 1 0)))', 4326), 'RO', 'RO', 'RO', 1, 'depth', current_timestamp, 'testorganization', true ), ( 'testbottleneck4', isrsrange(('RO', 'XXX', '00001', '00000', 0)::isrs, ('RO', 'XXX', '00001', '00000', 2)::isrs), ST_geomfromtext('MULTIPOLYGON(((1 0, 1 1, 2 1, 2 0, 1 0)))', 4326), 'RO', 'RO', 'RO', 1, 'depth', current_timestamp, 'testorganization', true )) INSERT INTO waterway.bottlenecks ( gauge_location, validity, bottleneck_id, stretch, area, rb, lb, responsible_country, revisiting_time, limiting, date_info, source_organization, staging_done) SELECT * FROM gs, bns; INSERT INTO waterway.distance_marks_virtual VALUES ( ('AT', 'XXX', '00001', '00000', 0)::isrs, ST_SetSRID('POINT(0 0)'::geometry, 4326), 'someENC' ), ( ('AT', 'XXX', '00001', '00000', 1)::isrs, ST_SetSRID('POINT(1 0)'::geometry, 4326), 'someENC' ), ( ('AT', 'XXX', '00001', '00000', 2)::isrs, ST_SetSRID('POINT(1.6 0)'::geometry, 4326), 'someENC' ); INSERT INTO waterway.waterway_axis (wtwaxs, objnam) VALUES ( ST_SetSRID( ST_Multi(ST_CurveToLine('CIRCULARSTRING(0 0, 0.5 0.5, 0.6 0.4)')), 4326), 'testriver' ), ( ST_SetSRID( ST_Multi(ST_CurveToLine('CIRCULARSTRING(0.6 0.4, 1 0, 1.5 -0.00001)')), 4326), 'testriver' ), ( ST_SetSRID('MULTILINESTRING((0.5 0.5, 1 1))'::geometry, 4326), 'testriver' ), ( ST_SetSRID('MULTILINESTRING((1.5 0, 1.55001 0))'::geometry, 4326), 'testriver' ), ( ST_SetSRID('MULTILINESTRING((1.55 0, 2 0))'::geometry, 4326), 'testriver' ); -- Simulate waterway area as non-intersecting buffers around axis WITH RECURSIVE buffer AS ( SELECT id, ST_Buffer(wtwaxs, 10000, 'endcap=flat')::geometry AS buf FROM waterway.waterway_axis), cleaned AS ( (SELECT ARRAY[id] AS ids, buf AS cbuf, buf AS others FROM buffer ORDER BY id FETCH FIRST ROW ONLY) UNION (SELECT ids || id, ST_Difference(buf, others), ST_Union(buf, others) FROM cleaned, buffer WHERE id <> ALL(ids) ORDER BY id ASC, ids DESC FETCH FIRST ROW ONLY)), cleaned1 AS ( SELECT DISTINCT geom FROM (SELECT (ST_Dump(cbuf)).geom FROM cleaned) AS dmp, waterway.waterway_axis WHERE ST_Intersects(geom, wtwaxs)) INSERT INTO waterway.waterway_area (area) SELECT geom FROM cleaned1; INSERT INTO users.templates (template_name, country, template_data) VALUES ('AT', 'AT', '\x'), ('RO', 'RO', '\x'); WITH job AS ( INSERT INTO import.imports (kind, username, data) VALUES ( 'test', 'test_admin_ro', 'test') RETURNING id), log AS ( INSERT INTO import.import_logs (import_id, msg) SELECT id, 'test' FROM job) INSERT INTO import.track_imports (import_id, relation, key) SELECT id, 'waterway.bottlenecks', 1 FROM job; WITH config AS ( INSERT INTO import.import_configuration (kind, username) VALUES ( 'test', 'test_admin_ro') RETURNING id) INSERT INTO import.import_configuration_attributes SELECT id, 'test key', 'test value' FROM config;