Mercurial > gemma

comparison schema/geoserver_views.sql @ 3361:3e7a670896bd latest-waterlevel-for-gauges

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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
comparison
equal deleted inserted replaced
3359:af6d8020b4a6 3361:3e7a670896bd
1 CREATE OR REPLACE VIEW waterway.gauges_geoserver AS 1 CREATE OR REPLACE VIEW waterway.gauges_geoserver AS
2 WITH
3 waterlevel_latest AS (
4 SELECT DISTINCT ON (location) location, water_level
5 FROM waterway.gauge_measurements
6 ORDER BY location, measure_date DESC)
2 SELECT 7 SELECT
3 g.location, 8 g.location,
4 isrs_asText(g.location) AS isrs_code, 9 isrs_asText(g.location) AS isrs_code,
5 g.objname, 10 g.objname,
6 g.geom, 11 g.geom,
11 g.geodref, 16 g.geodref,
12 g.date_info, 17 g.date_info,
13 g.source_organization, 18 g.source_organization,
14 json_strip_nulls(json_object_agg(coalesce(r.depth_reference,'empty'), 19 json_strip_nulls(json_object_agg(coalesce(r.depth_reference,'empty'),
15 r.value)) 20 r.value))
16 AS reference_water_levels 21 AS reference_water_levels,
22 wl.water_level AS gm_waterlevel
17 FROM waterway.gauges g 23 FROM waterway.gauges g
18 LEFT JOIN waterway.gauges_reference_water_levels r 24 LEFT JOIN waterway.gauges_reference_water_levels r
19 USING (location, validity) 25 USING (location, validity)
26 LEFT JOIN waterlevel_latest wl
27 USING (location)
20 WHERE NOT g.erased 28 WHERE NOT g.erased
21 GROUP BY g.location, g.validity; 29 GROUP BY g.location, g.validity, wl.water_level;
22 30
23 CREATE OR REPLACE VIEW waterway.distance_marks_geoserver AS 31 CREATE OR REPLACE VIEW waterway.distance_marks_geoserver AS
24 SELECT location_code, 32 SELECT location_code,
25 isrs_asText(location_code) AS location, 33 isrs_asText(location_code) AS location,
26 geom::Geometry(POINT, 4326), 34 geom::Geometry(POINT, 4326),