Mercurial > gemma
view schema/tap_tests_data.sql @ 3252:fccb28813159
client: wterlevel diagram: improved performance
By not rendering points that are outside of the visible area of the chart, performance was significantly improved.
But still the chart is not really very responsive and smooth when viewing large data sets.
author | Markus Kottlaender <markus@intevation.de> |
---|---|
date | Tue, 14 May 2019 12:24:14 +0200 |
parents | a2127495093e |
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;