Mercurial > gemma
changeset 2762:f95ec0bb565c
Added endpoint to deliver average waterlevels for a given gauge.
GET /api/data/average-waterlevels/{GAUGE}?from={FROM}&to={TO}
from and to are optional and defaults to end of today and going a year backwards.
Output is CSV in form of
#date,#min,#max,#mean,#median,#Q25,#Q75
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Thu, 21 Mar 2019 18:07:49 +0100 |
parents | 71e7237110ba |
children | a06d11d1f0b3 |
files | 3rdpartylibs.sh pkg/controllers/gauges.go pkg/controllers/routes.go |
diffstat | 3 files changed, 188 insertions(+), 1 deletions(-) [+] |
line wrap: on
line diff
--- a/3rdpartylibs.sh Thu Mar 21 17:31:03 2019 +0100 +++ b/3rdpartylibs.sh Thu Mar 21 18:07:49 2019 +0100 @@ -41,6 +41,9 @@ go get -u -v golang.org/x/sync/semaphore # Go License (aka BSD-3-Clause?) +go get -u -v gonum.org/v1/gonum/stat +# BSD-3-Clause + ## list of additional licenses that get fetched and installed as dependencies # github.com/fsnotify/fsnotify/ BSD-3-Clause # github.com/hashicorp/hcl/ MPL-2.0
--- a/pkg/controllers/gauges.go Thu Mar 21 17:31:03 2019 +0100 +++ b/pkg/controllers/gauges.go Thu Mar 21 18:07:49 2019 +0100 @@ -19,10 +19,12 @@ "fmt" "log" "net/http" + "sort" "strconv" "time" "github.com/gorilla/mux" + "gonum.org/v1/gonum/stat" "gemma.intevation.de/gemma/pkg/common" "gemma.intevation.de/gemma/pkg/middleware" @@ -56,12 +58,191 @@ SELECT measure_date, water_level, - predicted + predicted, FROM waterway.gauge_measurements WHERE ` + selectWaterlevelsMeasuredSQL = ` +SELECT + measure_date, + water_level +FROM waterway.gauge_measurements +WHERE + NOT predicted + AND fk_gauge_id = ( + $1::char(2), + $2::char(3), + $3::char(5), + $4::char(5), + $5::int + ) + AND + measure_date BETWEEN + $6::timestamp with time zone AND $7::timestamp with time zone +ORDER BY measure_date +` ) +func averageWaterlevels(rw http.ResponseWriter, req *http.Request) { + gauge := mux.Vars(req)["gauge"] + + isrs, err := models.IsrsFromString(gauge) + if err != nil { + http.Error( + rw, fmt.Sprintf("error: Invalid ISRS code: %v", err), + http.StatusBadRequest) + return + } + + var from, to time.Time + + if t := req.FormValue("to"); t != "" { + var err error + if to, err = time.ParseInLocation(common.DateFormat, t, time.UTC); err != nil { + http.Error( + rw, fmt.Sprintf("error: bad from date: %v", err), + http.StatusBadRequest) + return + } + } else { + y, m, d := time.Now().Date() + to = time.Date(y, m, d, 0, 0, 0, 0, time.UTC) + } + + if f := req.FormValue("from"); f != "" { + var err error + if from, err = time.ParseInLocation(common.DateFormat, f, time.UTC); err != nil { + http.Error( + rw, fmt.Sprintf("error: bad from date: %v", err), + http.StatusBadRequest) + return + } + } else { + from = to.AddDate(-1, 0, 0) + } + + to = to.AddDate(0, 0, 1).Add(-time.Nanosecond) + + if to.Before(from) { + from, to = to, from + } + + conn := middleware.GetDBConn(req) + + ctx := req.Context() + + rows, err := conn.QueryContext( + ctx, + selectWaterlevelsMeasuredSQL, + isrs.CountryCode, + isrs.LoCode, + isrs.FairwaySection, + isrs.Orc, + isrs.Hectometre, + from, to, + ) + if err != nil { + http.Error( + rw, fmt.Sprintf("error: %v", err), + http.StatusInternalServerError) + return + } + defer rows.Close() + + rw.Header().Add("Content-Type", "text/csv") + + out := csv.NewWriter(rw) + + var last time.Time + var values []float64 + + record := []string{ + "#date", + "#min", + "#max", + "#mean", + "#median", + "#q25", + "#q75", + } + + if err := out.Write(record); err != nil { + log.Printf("error: %v\n", err) + // Too late for an HTTP error code. + return + } + + format := func(v float64) string { + return strconv.FormatFloat(v, 'f', -1, 64) + } + + write := func() error { + if len(values) > 0 { + sort.Float64s(values) + // date + record[0] = last.Format(common.DateFormat) + // min + record[1] = format(values[0]) + // max + record[2] = format(values[len(values)-1]) + // mean + record[3] = format(stat.Mean(values, nil)) + // median + record[4] = format(values[len(values)/2]) + // Q25 + record[5] = format( + stat.Quantile(0.25, stat.Empirical, values, nil)) + // Q75 + record[6] = format( + stat.Quantile(0.75, stat.Empirical, values, nil)) + + err := out.Write(record) + values = values[:0] + return err + } + return nil + } + + for rows.Next() { + var ( + date time.Time + value float64 + ) + if err := rows.Scan(&date, &value); err != nil { + log.Printf("error: %v\n", err) + // Too late for an HTTP error code. + return + } + oy, om, od := last.Date() + ny, nm, nd := date.Date() + if oy != ny || om != nm || od != nd { + if err := write(); err != nil { + log.Printf("error: %v\n", err) + // Too late for an HTTP error code. + return + } + last = date + } else { + values = append(values, value) + } + } + write() + + if err := rows.Err(); err != nil { + log.Printf("error: %v", err) + // Too late for an HTTP error code. + return + } + + out.Flush() + if err := out.Error(); err != nil { + log.Printf("error: %v", err) + // Too late for an HTTP error code. + return + } + +} + func nashSutcliffe( _ interface{}, req *http.Request,
--- a/pkg/controllers/routes.go Thu Mar 21 17:31:03 2019 +0100 +++ b/pkg/controllers/routes.go Thu Mar 21 18:07:49 2019 +0100 @@ -302,6 +302,9 @@ api.Handle("/data/waterlevels/{gauge}", any( middleware.DBConn(http.HandlerFunc(waterlevels)))).Methods(http.MethodGet) + api.Handle("/data/average-waterlevels/{gauge}", any( + middleware.DBConn(http.HandlerFunc(averageWaterlevels)))).Methods(http.MethodGet) + api.Handle("/data/nash-sutcliffe/{gauge}", any(&JSONHandler{ Handle: nashSutcliffe, })).Methods(http.MethodGet)