Mercurial > gemma
view schema/tap_tests_data.sql @ 4894:8eb36d0d5bdf fairway-marks-import
Draft implementation of fairway marks import
Currently works (at least) for IENC feature BCNLAT. In order to be able to
store different types of fairway marks, more tables and import types are
needed.
Currently stupidly stores every entry received from the data source. Sorting
out duplicates and invalidating entries removed from the data source needs
to implemented.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Tue, 14 Jan 2020 15:01:42 +0100 |
parents | b0c974fc7d34 |
children | e8b2dc771f9e |
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'); INSERT INTO users.list_users VALUES ( 'waterway_user', 'test_user_ro', 'user_ro1$', 'RO', NULL, 'xxy'); INSERT INTO users.list_users VALUES ( 'waterway_admin', 'test_admin_at', 'admin_at1$', 'AT', NULL, 'yyy'); INSERT INTO users.list_users VALUES ( 'waterway_admin', 'test_admin_at2', 'admin_at2$', 'AT', NULL, 'yyy'); INSERT INTO users.list_users VALUES ( 'waterway_admin', 'test_admin_ro', 'admin_ro1$', 'RO', NULL, 'yyx'); INSERT INTO users.list_users VALUES ( 'sys_admin', 'test_sys_admin1', 'sys_admin1$', 'AT', NULL, 'zzz'); 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 )) 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_CurveToLine( 'CIRCULARSTRING(0 0, 0.5 0.5, 0.6 0.4)'), 4326), 'testriver' ), ( ST_SetSRID(ST_CurveToLine('CIRCULARSTRING(0.6 0.4, 1 0, 1.5 -0.00001)'), 4326), 'testriver' ), ( ST_SetSRID('LINESTRING(0.5 0.5, 1 1)'::geometry, 4326), 'testriver' ), ( ST_SetSRID('LINESTRING(1.5 0, 1.55001 0)'::geometry, 4326), 'testriver' ), ( ST_SetSRID('LINESTRING(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;