Mercurial > gemma
diff schema/gemma.sql @ 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 | 37ae1bee3e4a |
children | 3dd2de314b1b |
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