view schema/tap_tests_data.sql @ 2455:54c9fe587fe6

Subdivide SQL function to prepare for improved error handling The context of an error (e.g. the function in which it occured) can be inferred by the database client. Not doing all in one statement will render the context more meaningful.
author Tom Gottfried <tom@intevation.de>
date Fri, 01 Mar 2019 18:38:02 +0100
parents 48495bd3081d
children 79f4a20e31c2
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 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');

INSERT INTO users.responsibility_areas VALUES
    ('AT', ST_geomfromtext('MULTIPOLYGON(((0 0, 0 1, 1 1, 1 0, 0 0)))', 4326)),
    ('RO', ST_geomfromtext('MULTIPOLYGON(((1 0, 1 1, 2 1, 2 0, 1 0)))', 4326));

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');

INSERT INTO waterway.gauges (
    location, objname, geom, zero_point, source_organization)
    VALUES (
        ('AT', 'XXX', '00001', 'G0001', 1)::isrs,
        'testgauge',
        ST_geomfromtext('POINT(0 0)', 4326),
        0,
        'testorganization'
    );

INSERT INTO waterway.bottlenecks (
    bottleneck_id, fk_g_fid, stretch, area, rb, lb, responsible_country,
    revisiting_time, limiting, source_organization, staging_done)
    VALUES (
        'testbottleneck1',
        ('AT', 'XXX', '00001', 'G0001', 1)::isrs,
        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', 'testorganization', false
    ), (
        'testbottleneck2',
        ('AT', 'XXX', '00001', 'G0001', 1)::isrs,
        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', 'testorganization', true
    );

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)'), 4326),
    'testriver'
), (
    ST_SetSRID('LINESTRING(0.5 0.5, 1 1)'::geometry, 4326),
    'testriver'
), (
    ST_SetSRID('LINESTRING(1.5 0.1, 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))
INSERT INTO waterway.waterway_area (area) SELECT cbuf FROM cleaned;

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
    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;