Mercurial > gemma
changeset 2395:975e52210474
Don't hard code the available water level ref. systems.
author | Sascha Wilde <wilde@intevation.de> |
---|---|
date | Wed, 27 Feb 2019 14:02:28 +0100 |
parents | 85c47d63fdb9 |
children | 6af41bb6656f |
files | schema/gemma.sql |
diffstat | 1 files changed, 7 insertions(+), 12 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/gemma.sql Wed Feb 27 10:56:18 2019 +0100 +++ b/schema/gemma.sql Wed Feb 27 14:02:28 2019 +0100 @@ -499,19 +499,15 @@ b.source_organization, g.location AS gauge_isrs_code, g.objname AS gauge_objname, - rwl_ldc.value AS ldc, - rwl_mw.value AS mw, - rwl_hdc.value AS hdc, + json_object_agg(r.depth_reference, r.value) AS reference_water_levels, fal.date_info AS fa_date_info, 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, - (SELECT gauge_id, value FROM gauges_reference_water_levels - WHERE depth_reference = 'MW') rwl_mw, - (SELECT gauge_id, value FROM gauges_reference_water_levels - WHERE depth_reference = 'HDC') rwl_hdc + FROM bottlenecks b LEFT JOIN gauges g ON b.fk_g_fid = g.location + LEFT JOIN LATERAL ( + SELECT gauge_id,depth_reference,value + FROM gauges_reference_water_levels + ) r ON r.gauge_id = b.fk_g_fid LEFT JOIN LATERAL ( SELECT bottleneck_id,date_info,critical FROM fairway_availability_latest @@ -520,8 +516,7 @@ 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 + GROUP BY b.id, g.location, fal.date_info, fal.critical, gmw.water_level; CREATE TABLE sounding_results ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,