Mercurial > gemma
view schema/tap_tests_data.sql @ 2781:a2127495093e
Prevent duplicate waterway axis and area geometries
Duplicate axis geometries can prevent finding a contiguous axis
between two distance marks in the current implementation of
ISRSrange_axis(). Although duplicate area geometries did not show
to be harmful up to now, they do not seem to be useful and generally
duplicate geometries have the potential to make debugging of any
geometry processing harder.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Fri, 22 Mar 2019 14:35:32 +0100 |
parents | d316a6e41f54 |
children | ec6163c6687d |
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.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 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;