Mercurial > gemma
changeset 3003:92818da6133d
Cleanup VIEW definition
NULLS LAST is not necessary on non-nullable columns;
avoid materializing unused columns in a CTE;
use boolean value directly instead of comparing to auto-casted string;
gauge_measurements might contain not only waterlevels;
avoid unnecessary subselects.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Wed, 10 Apr 2019 18:50:20 +0200 |
parents | 46ab2cb39471 |
children | 0d74727725d3 |
files | schema/gemma.sql |
diffstat | 1 files changed, 17 insertions(+), 24 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/gemma.sql Wed Apr 10 17:46:50 2019 +0200 +++ b/schema/gemma.sql Wed Apr 10 18:50:20 2019 +0200 @@ -663,15 +663,16 @@ -- Published view for GeoServer 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), - 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) + 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), + waterlevel_latest AS ( + SELECT DISTINCT ON (fk_gauge_id) fk_gauge_id, water_level + FROM gauge_measurements + WHERE is_waterlevel AND NOT predicted + ORDER BY fk_gauge_id, measure_date DESC) SELECT b.id, b.bottleneck_id, @@ -693,21 +694,13 @@ 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 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 - 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 - GROUP BY b.id, g.location, fal.date_info, fal.critical, gmw.water_level; + wl.water_level AS gm_waterlevel + FROM bottlenecks b + LEFT JOIN gauges g ON b.fk_g_fid = g.location + LEFT JOIN gauges_reference_water_levels r ON g.location = r.gauge_id + LEFT JOIN fairway_availability_latest fal ON b.id = fal.bottleneck_id + LEFT JOIN waterlevel_latest wl ON b.fk_g_fid = wl.fk_gauge_id + GROUP BY b.id, g.location, fal.date_info, fal.critical, wl.water_level ; -- Configure primary keys for geoserver views