Mercurial > gemma
changeset 3361:3e7a670896bd latest-waterlevel-for-gauges
added latest waterlevel measurement to guages_geoserver view
author | Markus Kottlaender <markus@intevation.de> |
---|---|
date | Tue, 21 May 2019 17:41:19 +0200 |
parents | af6d8020b4a6 |
children | 4b8ca498c4cd |
files | schema/geoserver_views.sql |
diffstat | 1 files changed, 10 insertions(+), 2 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/geoserver_views.sql Tue May 21 17:11:39 2019 +0200 +++ b/schema/geoserver_views.sql Tue May 21 17:41:19 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,