Mercurial > gemma

comparison schema/geoserver_views.sql @ 3666:db87f34805fb

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Align bottleneck validity at gauges Ensuring the validity of a bottleneck version is always contained by the validity of the referenced gauge version allows to reliably determine matching reference values of the gauge at a point in time. Since this implies that a bottleneck version might be cut into more than one time ranges, the concept of having only one non-erased version is no longer applicable and replaced by using the 'current' version of a bottleneck. Fairway availability data are always kept with the 'current' bottleneck version to have them at hand alltogether for analyses over longer time ranges.
author Tom Gottfried <tom@intevation.de>
date Sat, 15 Jun 2019 14:36:50 +0200
parents 2a079d0a71c1
children eb11ada33fa7
comparison
equal deleted inserted replaced
3665:29ef6d41e4af 3666:db87f34805fb
134 ON b.gauge_location = g.location AND b.gauge_validity = g.validity 134 ON b.gauge_location = g.location AND b.gauge_validity = g.validity
135 LEFT JOIN fairway_availability_latest fal 135 LEFT JOIN fairway_availability_latest fal
136 ON b.id = fal.bottleneck_id 136 ON b.id = fal.bottleneck_id
137 LEFT JOIN sounding_result_latest srl 137 LEFT JOIN sounding_result_latest srl
138 ON b.bottleneck_id = srl.bottleneck_id 138 ON b.bottleneck_id = srl.bottleneck_id
139 WHERE NOT b.erased; 139 WHERE b.validity @> current_timestamp;
140 140
141 CREATE OR REPLACE VIEW waterway.stretches_geoserver AS 141 CREATE OR REPLACE VIEW waterway.stretches_geoserver AS
142 SELECT 142 SELECT
143 s.id, 143 s.id,
144 s.name, 144 s.name,
199 responsible_country 199 responsible_country
200 FROM waterway.bottlenecks bn LEFT JOIN ( 200 FROM waterway.bottlenecks bn LEFT JOIN (
201 SELECT bottleneck_id, max(date_info) AS current 201 SELECT bottleneck_id, max(date_info) AS current
202 FROM waterway.sounding_results 202 FROM waterway.sounding_results
203 GROUP BY bottleneck_id) sr ON sr.bottleneck_id = bn.bottleneck_id 203 GROUP BY bottleneck_id) sr ON sr.bottleneck_id = bn.bottleneck_id
204 WHERE NOT bn.erased 204 WHERE bn.validity @> current_timestamp
205 ORDER BY objnam; 205 ORDER BY objnam;
206 206
207 CREATE OR REPLACE VIEW waterway.sounding_differences AS 207 CREATE OR REPLACE VIEW waterway.sounding_differences AS
208 SELECT 208 SELECT
209 sd.id AS id, 209 sd.id AS id,