Mercurial > gemma
changeset 3469:096968d5628f
Added latest sounding result date to bottlenecks_geoserver view.
author | Sascha Wilde <wilde@intevation.de> |
---|---|
date | Mon, 27 May 2019 11:22:12 +0200 |
parents | 850e81f13e96 |
children | 104c4f683c74 |
files | schema/geoserver_views.sql |
diffstat | 1 files changed, 10 insertions(+), 2 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/geoserver_views.sql Mon May 27 11:21:11 2019 +0200 +++ b/schema/geoserver_views.sql Mon May 27 11:22:12 2019 +0200 @@ -64,7 +64,12 @@ fairway_availability_latest AS ( SELECT DISTINCT ON (bottleneck_id) bottleneck_id, date_info, critical FROM waterway.fairway_availability - ORDER BY bottleneck_id, date_info DESC) + ORDER BY bottleneck_id, date_info DESC), + sounding_result_latest AS ( + SELECT DISTINCT ON (bottleneck_id) bottleneck_id, max(date_info) AS date_max + FROM waterway.sounding_results + GROUP BY bottleneck_id + ORDER BY bottleneck_id DESC) SELECT b.id, b.bottleneck_id, @@ -85,13 +90,16 @@ fal.date_info AS fa_date_info, fal.critical AS fa_critical, g.gm_waterlevel, + srl.date_max, g.forecast_accuracy_3d, g.forecast_accuracy_1d FROM waterway.bottlenecks b LEFT JOIN waterway.gauges_geoserver g ON b.gauge_location = g.location AND b.gauge_validity = g.validity LEFT JOIN fairway_availability_latest fal - ON b.id = fal.bottleneck_id; + ON b.id = fal.bottleneck_id + LEFT JOIN sounding_result_latest srl + ON b.id = srl.bottleneck_id; CREATE OR REPLACE VIEW waterway.stretches_geoserver AS SELECT