Mercurial > gemma
changeset 2327:ead321473840
schema/gemma.sql: Added to gm_waterlevel bottlenecks_geoserver view.
author | Bjoern Schilberg <bjoern@intevation.de> |
---|---|
date | Tue, 19 Feb 2019 13:45:39 +0100 |
parents | 7cf8a276a80c |
children | c98491f9c466 |
files | schema/gemma.sql |
diffstat | 1 files changed, 11 insertions(+), 2 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/gemma.sql Tue Feb 19 11:42:42 2019 +0100 +++ b/schema/gemma.sql Tue Feb 19 13:45:39 2019 +0100 @@ -484,7 +484,11 @@ WITH fairway_availability_latest AS ( SELECT DISTINCT ON (bottleneck_id) bottleneck_id,date_info,critical FROM fairway_availability - ORDER BY bottleneck_id, date_info DESC NULLS LAST) + ORDER BY bottleneck_id, date_info DESC NULLS LAST), + gauge_measurements_waterlevel AS ( + SELECT DISTINCT ON (fk_gauge_id) fk_gauge_id,measure_date,predicted,water_level + FROM gauge_measurements WHERE predicted ='false' + ORDER BY fk_gauge_id, measure_date DESC NULLS LAST) SELECT b.id, b.bottleneck_id, @@ -505,7 +509,8 @@ rwl_mw.value AS mw, rwl_hdc.value AS hdc, fal.date_info AS fa_date_info, - fal.critical AS fa_critical + fal.critical AS fa_critical, + gmw.water_level as gm_waterlevel FROM bottlenecks b, gauges g, (SELECT gauge_id, value FROM gauges_reference_water_levels WHERE depth_reference = 'LDC') rwl_ldc, @@ -517,6 +522,10 @@ SELECT bottleneck_id,date_info,critical FROM fairway_availability_latest WHERE b.id=bottleneck_id) fal ON TRUE + LEFT JOIN LATERAL ( + SELECT water_level + FROM gauge_measurements_waterlevel + WHERE b.fk_g_fid=fk_gauge_id) gmw ON TRUE WHERE b.fk_g_fid = g.location AND g.location = rwl_ldc.gauge_id AND g.location = rwl_mw.gauge_id AND g.location = rwl_hdc.gauge_id