Mercurial > gemma
changeset 3456:ca395be62023
Add reference gauge forecast accuracy to bottlenecks view
Instead of duplicating more sub-queries and CTEs, join the gauges
view, which already has all calculated attributes.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Fri, 24 May 2019 14:24:24 +0200 |
parents | 188b55ef814f |
children | 870812d8f247 |
files | schema/geoserver_views.sql |
diffstat | 1 files changed, 7 insertions(+), 18 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/geoserver_views.sql Fri May 24 13:46:56 2019 +0200 +++ b/schema/geoserver_views.sql Fri May 24 14:24:24 2019 +0200 @@ -64,11 +64,7 @@ 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), - waterlevel_latest AS ( - SELECT DISTINCT ON (location) location, water_level - FROM waterway.gauge_measurements - ORDER BY location, measure_date DESC) + ORDER BY bottleneck_id, date_info DESC) SELECT b.id, b.bottleneck_id, @@ -85,24 +81,17 @@ b.source_organization, g.location AS gauge_isrs_code, g.objname AS gauge_objname, - json_strip_nulls(json_object_agg(coalesce(r.depth_reference,'empty'), - r.value)) - AS reference_water_levels, + g.reference_water_levels, fal.date_info AS fa_date_info, fal.critical AS fa_critical, - wl.water_level AS gm_waterlevel + g.gm_waterlevel, + g.forecast_accuracy_3d, + g.forecast_accuracy_1d FROM waterway.bottlenecks b - LEFT JOIN waterway.gauges g + LEFT JOIN waterway.gauges_geoserver g ON b.gauge_location = g.location AND b.gauge_validity = g.validity - LEFT JOIN waterway.gauges_reference_water_levels r - USING (location, validity) LEFT JOIN fairway_availability_latest fal - ON b.id = fal.bottleneck_id - LEFT JOIN waterlevel_latest wl - USING (location) - WHERE NOT g.erased - GROUP BY b.id, g.location, g.validity, - fal.date_info, fal.critical, wl.water_level; + ON b.id = fal.bottleneck_id; CREATE OR REPLACE VIEW waterway.stretches_geoserver AS SELECT