Mercurial > gemma
changeset 5229:0b051ee3f238 new-fwa
Added bottleneck_id column to bottleneck_overview view.
| author | wilde@azure1.rgb.intevation.de |
|---|---|
| date | Tue, 12 May 2020 13:14:15 +0200 |
| parents | f3a88039d822 |
| children | 40c9acd114cd |
| files | schema/default_sysconfig.sql schema/updates/1439/01.add_bn_id_to_bn_overview.sql schema/version.sql |
| diffstat | 3 files changed, 25 insertions(+), 2 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/default_sysconfig.sql Tue May 12 12:43:31 2020 +0200 +++ b/schema/default_sysconfig.sql Tue May 12 13:14:15 2020 +0200 @@ -4,7 +4,7 @@ -- SPDX-License-Identifier: AGPL-3.0-or-later -- License-Filename: LICENSES/AGPL-3.0.txt --- Copyright (C) 2018, 2019 by via donau +-- Copyright (C) 2018, 2019, 2020 by via donau -- – Österreichische Wasserstraßen-Gesellschaft mbH -- Software engineering by Intevation GmbH @@ -220,6 +220,7 @@ ('waterway', 'bottleneck_overview', 4326, NULL, $$ SELECT objnam AS name, + bn.bottleneck_id, ST_Centroid(area) AS point, (lower(stretch)).hectometre AS from, (upper(stretch)).hectometre AS to,
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1439/01.add_bn_id_to_bn_overview.sql Tue May 12 13:14:15 2020 +0200 @@ -0,0 +1,22 @@ +-- Add bottleneck_id column to bottleneck_overview view. + +UPDATE sys_admin.published_services + SET + view_def = $$ + SELECT + objnam AS name, + bn.bottleneck_id, + ST_Centroid(area) AS point, + (lower(stretch)).hectometre AS from, + (upper(stretch)).hectometre AS to, + sr.current::text, + responsible_country + FROM waterway.bottlenecks bn LEFT JOIN ( + SELECT bottleneck_id, max(date_info) AS current + FROM waterway.sounding_results + GROUP BY bottleneck_id) sr + ON sr.bottleneck_id = bn.bottleneck_id + WHERE bn.validity @> current_timestamp + ORDER BY objnam + $$ + WHERE schema = 'waterway' AND name = 'bottleneck_overview';