Mercurial > gemma
changeset 3363:4b8ca498c4cd
mergen latest-waterlevel-for-gauges in default
author | Markus Kottlaender <markus@intevation.de> |
---|---|
date | Tue, 21 May 2019 18:10:46 +0200 |
parents | bda4034304b1 (current diff) 3e7a670896bd (diff) |
children | b62b9d5ce06d |
files | |
diffstat | 1 files changed, 10 insertions(+), 2 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/geoserver_views.sql Tue May 21 17:47:25 2019 +0200 +++ b/schema/geoserver_views.sql Tue May 21 18:10:46 2019 +0200 @@ -1,4 +1,9 @@ CREATE OR REPLACE VIEW waterway.gauges_geoserver AS + WITH + waterlevel_latest AS ( + SELECT DISTINCT ON (location) location, water_level + FROM waterway.gauge_measurements + ORDER BY location, measure_date DESC) SELECT g.location, isrs_asText(g.location) AS isrs_code, @@ -13,12 +18,15 @@ g.source_organization, json_strip_nulls(json_object_agg(coalesce(r.depth_reference,'empty'), r.value)) - AS reference_water_levels + AS reference_water_levels, + wl.water_level AS gm_waterlevel FROM waterway.gauges g LEFT JOIN waterway.gauges_reference_water_levels r USING (location, validity) + LEFT JOIN waterlevel_latest wl + USING (location) WHERE NOT g.erased - GROUP BY g.location, g.validity; + GROUP BY g.location, g.validity, wl.water_level; CREATE OR REPLACE VIEW waterway.distance_marks_geoserver AS SELECT location_code,