Mercurial > gemma

comparison pkg/controllers/gauges.go @ 3302:ec6163c6687d

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
'Historicise' gauges on import Gauge data sets will be updated or a new version will be inserted depending on temporal validity and a timestamp marking the last update in the RIS-Index of a data set. The trigger on date_info is removed because the value is actually an attribut coming from the RIS-Index. Gauge measurements and predictions are associated to the version with matching temporal validity. Bottlenecks are always associated to the actual version of the gauge, although this might change as soon as bottlenecks are 'historicised', too.
author Tom Gottfried <tom@intevation.de>
date Thu, 16 May 2019 18:41:43 +0200
parents 232fc90e6ee2
children 18777f6df3ef
comparison
equal deleted inserted replaced
3301:6514b943654e 3302:ec6163c6687d
42 date_issue, 42 date_issue,
43 predicted, 43 predicted,
44 water_level 44 water_level
45 FROM ( 45 FROM (
46 SELECT 46 SELECT
47 fk_gauge_id, 47 location,
48 measure_date, 48 measure_date,
49 date_issue, 49 date_issue,
50 false AS predicted, 50 false AS predicted,
51 water_level 51 water_level
52 FROM waterway.gauge_measurements 52 FROM waterway.gauge_measurements
53 UNION ALL 53 UNION ALL
54 SELECT 54 SELECT
55 fk_gauge_id, 55 location,
56 measure_date, 56 measure_date,
57 date_issue, 57 date_issue,
58 true AS predicted, 58 true AS predicted,
59 water_level 59 water_level
60 FROM waterway.gauge_predictions 60 FROM waterway.gauge_predictions
61 ) AS gmp 61 ) AS gmp
62 WHERE 62 WHERE
63 fk_gauge_id = ( 63 location = (
64 $1::char(2), 64 $1::char(2),
65 $2::char(3), 65 $2::char(3),
66 $3::char(5), 66 $3::char(5),
67 $4::char(5), 67 $4::char(5),
68 $5::int 68 $5::int
78 value_min, 78 value_min,
79 value_max, 79 value_max,
80 predicted 80 predicted
81 FROM ( 81 FROM (
82 SELECT 82 SELECT
83 fk_gauge_id, 83 location,
84 measure_date, 84 measure_date,
85 date_issue, 85 date_issue,
86 water_level, 86 water_level,
87 NULL AS value_min, 87 NULL AS value_min,
88 NULL AS value_max, 88 NULL AS value_max,
89 false AS predicted 89 false AS predicted
90 FROM waterway.gauge_measurements 90 FROM waterway.gauge_measurements
91 UNION ALL 91 UNION ALL
92 SELECT 92 SELECT
93 fk_gauge_id, 93 location,
94 measure_date, 94 measure_date,
95 date_issue, 95 date_issue,
96 water_level, 96 water_level,
97 lower(conf_interval) AS value_min, 97 lower(conf_interval) AS value_min,
98 upper(conf_interval) AS value_max, 98 upper(conf_interval) AS value_max,
104 104
105 selectAllWaterlevelsMeasuredRangeSQL = ` 105 selectAllWaterlevelsMeasuredRangeSQL = `
106 SELECT 106 SELECT
107 min(measure_date), 107 min(measure_date),
108 max(measure_date) 108 max(measure_date)
109 FROM waterway.gauge_measurements WHERE 109 FROM waterway.gauge_measurements
110 fk_gauge_id = ( 110 WHERE
111 location = (
111 $1::char(2), 112 $1::char(2),
112 $2::char(3), 113 $2::char(3),
113 $3::char(5), 114 $3::char(5),
114 $4::char(5), 115 $4::char(5),
115 $5::int 116 $5::int
125 percentile_disc(0.5) within group (order by water_level) AS median, 126 percentile_disc(0.5) within group (order by water_level) AS median,
126 percentile_disc(0.75) within group (order by water_level) AS q75, 127 percentile_disc(0.75) within group (order by water_level) AS q75,
127 avg(water_level) AS mean, 128 avg(water_level) AS mean,
128 min(water_level) AS min, 129 min(water_level) AS min,
129 max(water_level) AS max 130 max(water_level) AS max
130 FROM waterway.gauge_measurements WHERE 131 FROM waterway.gauge_measurements
131 fk_gauge_id = ( 132 WHERE
133 location = (
132 $1::char(2), 134 $1::char(2),
133 $2::char(3), 135 $2::char(3),
134 $3::char(5), 136 $3::char(5),
135 $4::char(5), 137 $4::char(5),
136 $5::int 138 $5::int
143 SELECT 145 SELECT
144 measure_date, 146 measure_date,
145 water_level 147 water_level
146 FROM waterway.gauge_measurements 148 FROM waterway.gauge_measurements
147 WHERE 149 WHERE
148 staging_done AND 150 location = (
149 fk_gauge_id = (
150 $1::char(2), 151 $1::char(2),
151 $2::char(3), 152 $2::char(3),
152 $3::char(5), 153 $3::char(5),
153 $4::char(5), 154 $4::char(5),
154 $5::int 155 $5::int
155 ) 156 )::isrs
157 AND staging_done
156 AND measure_date BETWEEN $6 AND $7 158 AND measure_date BETWEEN $6 AND $7
157 ORDER BY measure_date 159 ORDER BY measure_date
158 ` 160 `
159 ) 161 )
160 162
661 return 663 return
662 } 664 }
663 665
664 filters := filterAnd{ 666 filters := filterAnd{
665 buildFilterTerm( 667 buildFilterTerm(
666 "fk_gauge_id = ($%d::char(2), $%d::char(3), $%d::char(5), $%d::char(5), $%d::int)", 668 "location = ($%d::char(2), $%d::char(3), $%d::char(5), $%d::char(5), $%d::int)",
667 isrs.CountryCode, 669 isrs.CountryCode,
668 isrs.LoCode, 670 isrs.LoCode,
669 isrs.FairwaySection, 671 isrs.FairwaySection,
670 isrs.Orc, 672 isrs.Orc,
671 isrs.Hectometre, 673 isrs.Hectometre,
672 ), 674 ),
673 &filterOr{ 675 &filterOr{
674 &filterNot{&filterTerm{format: "predicted"}}, 676 &filterNot{&filterTerm{format: "predicted"}},
675 buildFilterTerm( 677 buildFilterTerm(
676 `date_issue = ( 678 `date_issue = (
677 SELECT max(date_issue) FROM waterway.gauge_measurements 679 SELECT max(date_issue)
678 WHERE fk_gauge_id = ($%d::char(2), $%d::char(3), $%d::char(5), $%d::char(5), $%d::int))`, 680 FROM waterway.gauge_measurements gm
681 WHERE location = ($%d::char(2), $%d::char(3), $%d::char(5), $%d::char(5), $%d::int))`,
679 isrs.CountryCode, 682 isrs.CountryCode,
680 isrs.LoCode, 683 isrs.LoCode,
681 isrs.FairwaySection, 684 isrs.FairwaySection,
682 isrs.Orc, 685 isrs.Orc,
683 isrs.Hectometre, 686 isrs.Hectometre,