Mercurial > gemma

comparison schema/geoserver_views.sql @ 3469:096968d5628f

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added latest sounding result date to bottlenecks_geoserver view.
author Sascha Wilde <wilde@intevation.de>
date Mon, 27 May 2019 11:22:12 +0200
parents ca395be62023
children 965b2fbb1890
comparison
equal deleted inserted replaced
3468:850e81f13e96 3469:096968d5628f
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 sounding_result_latest AS (
69 SELECT DISTINCT ON (bottleneck_id) bottleneck_id, max(date_info) AS date_max
70 FROM waterway.sounding_results
71 GROUP BY bottleneck_id
72 ORDER BY bottleneck_id DESC)
68 SELECT 73 SELECT
69 b.id, 74 b.id,
70 b.bottleneck_id, 75 b.bottleneck_id,
71 b.objnam, 76 b.objnam,
72 b.nobjnm, 77 b.nobjnm,
83 g.objname AS gauge_objname, 88 g.objname AS gauge_objname,
84 g.reference_water_levels, 89 g.reference_water_levels,
85 fal.date_info AS fa_date_info, 90 fal.date_info AS fa_date_info,
86 fal.critical AS fa_critical, 91 fal.critical AS fa_critical,
87 g.gm_waterlevel, 92 g.gm_waterlevel,
93 srl.date_max,
88 g.forecast_accuracy_3d, 94 g.forecast_accuracy_3d,
89 g.forecast_accuracy_1d 95 g.forecast_accuracy_1d
90 FROM waterway.bottlenecks b 96 FROM waterway.bottlenecks b
91 LEFT JOIN waterway.gauges_geoserver g 97 LEFT JOIN waterway.gauges_geoserver g
92 ON b.gauge_location = g.location AND b.gauge_validity = g.validity 98 ON b.gauge_location = g.location AND b.gauge_validity = g.validity
93 LEFT JOIN fairway_availability_latest fal 99 LEFT JOIN fairway_availability_latest fal
94 ON b.id = fal.bottleneck_id; 100 ON b.id = fal.bottleneck_id
101 LEFT JOIN sounding_result_latest srl
102 ON b.id = srl.bottleneck_id;
95 103
96 CREATE OR REPLACE VIEW waterway.stretches_geoserver AS 104 CREATE OR REPLACE VIEW waterway.stretches_geoserver AS
97 SELECT 105 SELECT
98 id, 106 id,
99 name, 107 name,