Mercurial > gemma
view schema/demo-data/users.sql @ 2987:524f1382558a
Make snapping of many axis segments faster
Since a CTE is materialized anyhow, make the one for the UTM projected
axis a temporary table, extended by the precalculated geometry
boundaries and a spatial index on these, which then can be used in
geometry comparisons within the recursive CTE. This makes snapping
several hundreds of segments faster by more than an order of magnitude
at the expense of longer runtime for snapping only very few segments,
because of the additional cost for index creation. This could be
further optimized by first checking if there is a direct match without
any snapping, but since the function is currently only used in import
processes, a runtime of a few seconds seems acceptable.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Wed, 10 Apr 2019 10:02:07 +0200 |
parents | 918091d1df72 |
children | ca7821e1f720 |
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): -- * Sascha Wilde <sascha.wilde@intevation.de> -- * Christine Tschuprine <christine.tschuprine@intevation.de> -- * Thomas Junk <thomas.junk@intevation.de> -- * Tom Gottfried <tom@intevation.de> BEGIN; -- PREREQUISITES: -- Cluster has roles as created by roles.sql, -- responsibility areas from responsibility_areas.sql are imported -- Fill in Profiles COPY internal.user_profiles (username, country, email_address, map_extent) FROM stdin; sophie AT sophie@example.com BOX(9.52115482500011 46.3786430870001,17.1483378500001 49.0097744750001) David Wagner AT wagner@example.com BOX(9.52115482500011 46.3786430870001,17.1483378500001 49.0097744750001) Dipl.-Inform. Stefan-Albert Zimmermann AT zimmermann@example.com BOX(9.52115482500011 46.3786430870001,17.1483378500001 49.0097744750001) JohannaPichler AT pichler@example.com BOX(9.52115482500011 46.3786430870001,17.1483378500001 49.0097744750001) Katharina Gruber AT gruber@example.com BOX(9.52115482500011 46.3786430870001,17.1483378500001 49.0097744750001) Maximilian Wimmer AT wimmer@example.com BOX(9.52115482500011 46.3786430870001,17.1483378500001 49.0097744750001) oana HR oana@example.com BOX(13.5014754570001 42.4163272160001,19.4078381750001 46.5469790650001) Christina Kovačević HR kovacevic@example.com BOX(13.5014754570001 42.4163272160001,19.4078381750001 46.5469790650001) Ivo Marković HR markovic@example.com BOX(13.5014754570001 42.4163272160001,19.4078381750001 46.5469790650001) Leonard Vuković HR vukovic@example.com BOX(13.5014754570001 42.4163272160001,19.4078381750001 46.5469790650001) Neven Novak HR novak@example.com BOX(13.5014754570001 42.4163272160001,19.4078381750001 46.5469790650001) lucian RO lucian@example.com BOX(20.2428259690001 43.6500499480001,29.6995548840001 48.2748322560001) Elena Popescu RO popescu@example.com BOX(20.2428259690001 43.6500499480001,29.6995548840001 48.2748322560001) Eugen Munteanu RO munteanu@example.com BOX(20.2428259690001 43.6500499480001,29.6995548840001 48.2748322560001) Gabriela Gheorghe RO gheorghe@example.com BOX(20.2428259690001 43.6500499480001,29.6995548840001 48.2748322560001) Marina Stoica RO stoica@example.com BOX(20.2428259690001 43.6500499480001,29.6995548840001 48.2748322560001) penka BG penka@example.com BOX(22.3450232340001 41.238104147,28.6035262380001 44.228434539) БорисДаманиов BG daimanov@example.com BOX(22.3450232340001 41.238104147,28.6035262380001 44.228434539) Фидан Дубарова BG dubarova@example.com BOX(22.3450232340001 41.238104147,28.6035262380001 44.228434539) Дипл. Инж. Константин Ясенов BG ясенов@example.com BOX(22.3450232340001 41.238104147,28.6035262380001 44.228434539) Люба Ходкивич BG hodkiewicz@example.com BOX(22.3450232340001 41.238104147,28.6035262380001 44.228434539) Andrej Kovačič SK kovacic@example.com BOX(16.8444804280001 47.7500064090001,22.5396366780001 49.601779684) Milana Božič SK bozic@example.com BOX(16.8444804280001 47.7500064090001,22.5396366780001 49.601779684) Vladislav Zupančič SK zupancic@example.com BOX(16.8444804280001 47.7500064090001,22.5396366780001 49.601779684) Zuzanna Korošec SK korosec@example.com BOX(16.8444804280001 47.7500064090001,22.5396366780001 49.601779684) \. COMMIT;