Mercurial > gemma

comparison pkg/controllers/bottlenecks.go @ 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 02951a62e8c6
children 268348a58c9c
comparison
equal deleted inserted replaced
3665:29ef6d41e4af 3666:db87f34805fb
31 "gemma.intevation.de/gemma/pkg/middleware" 31 "gemma.intevation.de/gemma/pkg/middleware"
32 ) 32 )
33 33
34 const ( 34 const (
35 selectLimitingSQL = ` 35 selectLimitingSQL = `
36 SELECT limiting from waterway.bottlenecks WHERE NOT erased AND objnam = $1` 36 SELECT limiting from waterway.bottlenecks
37 WHERE bn.validity @> current_timestamp AND objnam = $1
38 `
37 39
38 selectAvailableDepthSQL = ` 40 selectAvailableDepthSQL = `
39 WITH data AS ( 41 WITH data AS (
40 SELECT 42 SELECT
41 efa.measure_date, 43 efa.measure_date,
46 JOIN waterway.fairway_availability fa 48 JOIN waterway.fairway_availability fa
47 ON efa.fairway_availability_id = fa.id 49 ON efa.fairway_availability_id = fa.id
48 JOIN waterway.bottlenecks bn 50 JOIN waterway.bottlenecks bn
49 ON fa.bottleneck_id = bn.id 51 ON fa.bottleneck_id = bn.id
50 WHERE 52 WHERE
51 NOT bn.erased AND 53 bn.validity @> current_timestamp AND
52 bn.objnam = $1 AND 54 bn.objnam = $1 AND
53 efa.level_of_service = $2 AND 55 efa.level_of_service = $2 AND
54 efa.measure_type = 'Measured' AND 56 efa.measure_type = 'Measured' AND
55 (efa.available_depth_value IS NOT NULL OR 57 (efa.available_depth_value IS NOT NULL OR
56 efa.available_width_value IS NOT NULL) AND 58 efa.available_width_value IS NOT NULL) AND
80 grwl.value 82 grwl.value
81 FROM waterway.gauges_reference_water_levels grwl 83 FROM waterway.gauges_reference_water_levels grwl
82 JOIN waterway.bottlenecks bns 84 JOIN waterway.bottlenecks bns
83 ON grwl.location = bns.gauge_location 85 ON grwl.location = bns.gauge_location
84 AND grwl.validity = bns.gauge_validity 86 AND grwl.validity = bns.gauge_validity
85 WHERE NOT bns.erased AND bns.objnam = $1 AND grwl.depth_reference like 'LDC%' 87 WHERE bns.validity @> current_timestamp
88 AND bns.objnam = $1
89 AND grwl.depth_reference like 'LDC%'
86 ` 90 `
87 ) 91 )
88 92
89 type ( 93 type (
90 availMeasurement struct { 94 availMeasurement struct {