Mercurial > gemma
comparison pkg/controllers/gauges.go @ 2826:f7ae108c2838
longterm waterlevels statistics: Generate a comment line with the interval of years.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Wed, 27 Mar 2019 13:43:41 +0100 |
parents | ff58440ce809 |
children | b5555005f51e |
comparison
equal
deleted
inserted
replaced
2825:d6c90d2c8c8d | 2826:f7ae108c2838 |
---|---|
23 "strconv" | 23 "strconv" |
24 "strings" | 24 "strings" |
25 "time" | 25 "time" |
26 | 26 |
27 "github.com/gorilla/mux" | 27 "github.com/gorilla/mux" |
28 "github.com/jackc/pgx/pgtype" | |
28 "gonum.org/v1/gonum/stat" | 29 "gonum.org/v1/gonum/stat" |
29 | 30 |
30 "gemma.intevation.de/gemma/pkg/common" | 31 "gemma.intevation.de/gemma/pkg/common" |
31 "gemma.intevation.de/gemma/pkg/middleware" | 32 "gemma.intevation.de/gemma/pkg/middleware" |
32 "gemma.intevation.de/gemma/pkg/models" | 33 "gemma.intevation.de/gemma/pkg/models" |
64 predicted | 65 predicted |
65 FROM waterway.gauge_measurements | 66 FROM waterway.gauge_measurements |
66 WHERE | 67 WHERE |
67 ` | 68 ` |
68 | 69 |
69 selectAllWaterlevelsMeasuredSQL = ` | 70 selectAllWaterlevelsMeasuredRangeSQL = ` |
70 WITH g AS ( | 71 SELECT |
71 SELECT ( | 72 min(measure_date), |
73 max(measure_date) | |
74 FROM waterway.gauge_measurements WHERE | |
75 fk_gauge_id = ( | |
72 $1::char(2), | 76 $1::char(2), |
73 $2::char(3), | 77 $2::char(3), |
74 $3::char(5), | 78 $3::char(5), |
75 $4::char(5), | 79 $4::char(5), |
76 $5::int)::isrs loc | 80 $5::int |
77 ) | 81 )::isrs |
82 AND NOT predicted | |
83 AND staging_done | |
84 ` | |
85 | |
86 selectAllWaterlevelsMeasuredSQL = ` | |
78 SELECT | 87 SELECT |
79 extract(day from measure_date)::varchar || ':' || | 88 extract(day from measure_date)::varchar || ':' || |
80 extract(month from measure_date)::varchar AS day_month, | 89 extract(month from measure_date)::varchar AS day_month, |
81 percentile_disc(0.25) within group (order by water_level) AS q25, | 90 percentile_disc(0.25) within group (order by water_level) AS q25, |
82 percentile_disc(0.5) within group (order by water_level) AS median, | 91 percentile_disc(0.5) within group (order by water_level) AS median, |
83 percentile_disc(0.75) within group (order by water_level) AS q75, | 92 percentile_disc(0.75) within group (order by water_level) AS q75, |
84 avg(water_level) AS mean, | 93 avg(water_level) AS mean, |
85 min(water_level) AS min, | 94 min(water_level) AS min, |
86 max(water_level) AS max | 95 max(water_level) AS max |
87 FROM waterway.gauge_measurements, g | 96 FROM waterway.gauge_measurements WHERE |
88 WHERE fk_gauge_id = g.loc AND NOT predicted AND staging_done | 97 fk_gauge_id = ( |
98 $1::char(2), | |
99 $2::char(3), | |
100 $3::char(5), | |
101 $4::char(5), | |
102 $5::int | |
103 )::isrs | |
104 AND NOT predicted | |
105 AND staging_done | |
89 GROUP BY extract(day from measure_date)::varchar || ':' || | 106 GROUP BY extract(day from measure_date)::varchar || ':' || |
90 extract(month from measure_date)::varchar; | 107 extract(month from measure_date)::varchar; |
91 ` | 108 ` |
92 selectYearWaterlevelsMeasuredSQL = ` | 109 selectYearWaterlevelsMeasuredSQL = ` |
93 SELECT | 110 SELECT |
233 | 250 |
234 conn := middleware.GetDBConn(req) | 251 conn := middleware.GetDBConn(req) |
235 | 252 |
236 ctx := req.Context() | 253 ctx := req.Context() |
237 | 254 |
255 var begin, end pgtype.Timestamp | |
256 | |
257 err = conn.QueryRowContext( | |
258 ctx, | |
259 selectAllWaterlevelsMeasuredRangeSQL, | |
260 isrs.CountryCode, | |
261 isrs.LoCode, | |
262 isrs.FairwaySection, | |
263 isrs.Orc, | |
264 isrs.Hectometre, | |
265 ).Scan(&begin, &end) | |
266 | |
267 switch { | |
268 case err == sql.ErrNoRows || begin.Status != pgtype.Present || end.Status != pgtype.Present: | |
269 http.NotFound(rw, req) | |
270 return | |
271 case err != nil: | |
272 http.Error( | |
273 rw, fmt.Sprintf("error: %v", err), | |
274 http.StatusInternalServerError) | |
275 return | |
276 } | |
277 | |
238 rows, err := conn.QueryContext( | 278 rows, err := conn.QueryContext( |
239 ctx, | 279 ctx, |
240 selectAllWaterlevelsMeasuredSQL, | 280 selectAllWaterlevelsMeasuredSQL, |
241 isrs.CountryCode, | 281 isrs.CountryCode, |
242 isrs.LoCode, | 282 isrs.LoCode, |
310 rw.Header().Add("Content-Type", "text/csv") | 350 rw.Header().Add("Content-Type", "text/csv") |
311 | 351 |
312 out := csv.NewWriter(rw) | 352 out := csv.NewWriter(rw) |
313 | 353 |
314 record := []string{ | 354 record := []string{ |
355 fmt.Sprintf("#interval: %d-%d", | |
356 begin.Time.UTC().Year(), | |
357 end.Time.UTC().Year()), | |
358 "", | |
359 "", | |
360 "", | |
361 "", | |
362 "", | |
363 "", | |
364 } | |
365 | |
366 if err := out.Write(record); err != nil { | |
367 log.Printf("error: %v\n", err) | |
368 // Too late for an HTTP error code. | |
369 return | |
370 } | |
371 | |
372 record = []string{ | |
315 "#date", | 373 "#date", |
316 "#min", | 374 "#min", |
317 "#max", | 375 "#max", |
318 "#mean", | 376 "#mean", |
319 "#median", | 377 "#median", |