Mercurial > gemma
diff schema/geoserver_views.sql @ 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 | b90b17d0b5a9 |
children | adf06af7a79c |
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,