Mercurial > gemma
changeset 2287:3154120af837
schema/gemma.sql: Added fairway_availability.critical and date_info to bottlenecks_geoserver view.
author | Bjoern Schilberg <bjoern@intevation.de> |
---|---|
date | Fri, 15 Feb 2019 14:08:42 +0100 |
parents | d69bd6631362 |
children | 3f9847c89491 |
files | schema/gemma.sql |
diffstat | 1 files changed, 13 insertions(+), 2 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/gemma.sql Fri Feb 15 13:42:08 2019 +0100 +++ b/schema/gemma.sql Fri Feb 15 14:08:42 2019 +0100 @@ -473,7 +473,12 @@ ) -- Published view for GeoServer - CREATE VIEW bottlenecks_geoserver AS SELECT + CREATE VIEW bottlenecks_geoserver AS + 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) + SELECT b.id, b.bottleneck_id, b.objnam, @@ -491,7 +496,9 @@ g.objname AS gauge_objname, rwl_ldc.value AS ldc, rwl_mw.value AS mw, - rwl_hdc.value AS hdc + rwl_hdc.value AS hdc, + fal.date_info AS fa_date_info, + fal.critical AS fa_critical FROM bottlenecks b, gauges g, (SELECT gauge_id, value FROM gauges_reference_water_levels WHERE reference_water_level='LDC') rwl_ldc, @@ -499,6 +506,10 @@ WHERE reference_water_level='MW') rwl_mw, (SELECT gauge_id, value FROM gauges_reference_water_levels WHERE reference_water_level='HDC') rwl_hdc + LEFT JOIN LATERAL ( + SELECT bottleneck_id,date_info,critical + FROM fairway_availability_latest + WHERE b.id=bottleneck_id) fal 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