Mercurial > gemma

comparison schema/geoserver_views.sql @ 3456:ca395be62023

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add reference gauge forecast accuracy to bottlenecks view Instead of duplicating more sub-queries and CTEs, join the gauges view, which already has all calculated attributes.
author Tom Gottfried <tom@intevation.de>
date Fri, 24 May 2019 14:24:24 +0200
parents 7f65790b6bd6
children 096968d5628f
comparison
equal deleted inserted replaced
3455:188b55ef814f 3456:ca395be62023
62 CREATE OR REPLACE VIEW waterway.bottlenecks_geoserver AS 62 CREATE OR REPLACE VIEW waterway.bottlenecks_geoserver AS
63 WITH 63 WITH
64 fairway_availability_latest AS ( 64 fairway_availability_latest AS (
65 SELECT DISTINCT ON (bottleneck_id) bottleneck_id, date_info, critical 65 SELECT DISTINCT ON (bottleneck_id) bottleneck_id, date_info, critical
66 FROM waterway.fairway_availability 66 FROM waterway.fairway_availability
67 ORDER BY bottleneck_id, date_info DESC), 67 ORDER BY bottleneck_id, date_info DESC)
68 waterlevel_latest AS (
69 SELECT DISTINCT ON (location) location, water_level
70 FROM waterway.gauge_measurements
71 ORDER BY location, measure_date DESC)
72 SELECT 68 SELECT
73 b.id, 69 b.id,
74 b.bottleneck_id, 70 b.bottleneck_id,
75 b.objnam, 71 b.objnam,
76 b.nobjnm, 72 b.nobjnm,
83 b.limiting, 79 b.limiting,
84 b.date_info, 80 b.date_info,
85 b.source_organization, 81 b.source_organization,
86 g.location AS gauge_isrs_code, 82 g.location AS gauge_isrs_code,
87 g.objname AS gauge_objname, 83 g.objname AS gauge_objname,
88 json_strip_nulls(json_object_agg(coalesce(r.depth_reference,'empty'), 84 g.reference_water_levels,
89 r.value))
90 AS reference_water_levels,
91 fal.date_info AS fa_date_info, 85 fal.date_info AS fa_date_info,
92 fal.critical AS fa_critical, 86 fal.critical AS fa_critical,
93 wl.water_level AS gm_waterlevel 87 g.gm_waterlevel,
88 g.forecast_accuracy_3d,
89 g.forecast_accuracy_1d
94 FROM waterway.bottlenecks b 90 FROM waterway.bottlenecks b
95 LEFT JOIN waterway.gauges g 91 LEFT JOIN waterway.gauges_geoserver g
96 ON b.gauge_location = g.location AND b.gauge_validity = g.validity 92 ON b.gauge_location = g.location AND b.gauge_validity = g.validity
97 LEFT JOIN waterway.gauges_reference_water_levels r
98 USING (location, validity)
99 LEFT JOIN fairway_availability_latest fal 93 LEFT JOIN fairway_availability_latest fal
100 ON b.id = fal.bottleneck_id 94 ON b.id = fal.bottleneck_id;
101 LEFT JOIN waterlevel_latest wl
102 USING (location)
103 WHERE NOT g.erased
104 GROUP BY b.id, g.location, g.validity,
105 fal.date_info, fal.critical, wl.water_level;
106 95
107 CREATE OR REPLACE VIEW waterway.stretches_geoserver AS 96 CREATE OR REPLACE VIEW waterway.stretches_geoserver AS
108 SELECT 97 SELECT
109 id, 98 id,
110 name, 99 name,