Mercurial > gemma

comparison pkg/controllers/gauges.go @ 2826:f7ae108c2838

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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",