# HG changeset patch # User Sascha L. Teichmann # Date 1553075221 -3600 # Node ID 87aed4f9b1b813689c8d89e5d589e315929a2800 # Parent 85de42146bdbf040900f120ce084a953a917f43e Added calculation of Nash Sutcliffe efficiency coefficents. GET /api/data/nash-sutcliffe/{gauge}?when={WHEN} 'when' is optional in form of "2006-01-02T15:04:05.000" and defaults to current server time. curl -H "X-Gemma-Auth:$KEY" http://${server}:${port}/api/data/nash-sutcliffe/${gauge} | jq . { "when": "2019-03-20T10:38:05.687", "coeffs": [ { "value": 0, "samples": 0, "hours": 24 }, { "value": 0, "samples": 0, "hours": 48 }, { "value": 0, "samples": 0, "hours": 72 } ] } diff -r 85de42146bdb -r 87aed4f9b1b8 pkg/common/nashsutcliffe.go --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/pkg/common/nashsutcliffe.go Wed Mar 20 10:47:01 2019 +0100 @@ -0,0 +1,81 @@ +// This is Free Software under GNU Affero General Public License v >= 3.0 +// without warranty, see README.md and license for details. +// +// SPDX-License-Identifier: AGPL-3.0-or-later +// License-Filename: LICENSES/AGPL-3.0.txt +// +// Copyright (C) 2019 by via donau +// – Österreichische Wasserstraßen-Gesellschaft mbH +// Software engineering by Intevation GmbH +// +// Author(s): +// * Sascha L. Teichmann + +package common + +import ( + "sort" + "time" +) + +type NSMeasurement struct { + When time.Time + Predicted float64 + Observed float64 +} + +func NashSutcliffeSort(measurements []NSMeasurement) { + sort.Slice(measurements, func(i, j int) bool { + return measurements[i].When.Before(measurements[j].When) + }) +} + +func NashSutcliffe(measurements []NSMeasurement, from, to time.Time) (float64, int) { + + if len(measurements) == 0 { + return 0, 0 + } + + if to.Before(from) { + from, to = to, from + } + + begin := sort.Search(len(measurements), func(i int) bool { + return !measurements[i].When.Before(from) + }) + if begin >= len(measurements) { + return 0, 0 + } + + end := sort.Search(len(measurements), func(i int) bool { + return measurements[i].When.After(to) + }) + if end >= len(measurements) { + end = len(measurements) - 1 + } + if end <= begin { + return 0, 0 + } + sample := measurements[begin:end] + + if len(sample) == 0 { + return 0, 0 + } + + var mo float64 + for i := range sample { + mo += sample[i].Observed + } + + mo /= float64(len(sample)) + + var num, denom float64 + for i := range sample { + d1 := sample[i].Predicted - sample[i].Observed + num += d1 * d1 + d2 := sample[i].Observed - mo + denom += d2 * d2 + } + + return 1 - num/denom, len(sample) +} diff -r 85de42146bdb -r 87aed4f9b1b8 pkg/controllers/gauges.go --- a/pkg/controllers/gauges.go Tue Mar 19 19:21:04 2019 +0100 +++ b/pkg/controllers/gauges.go Wed Mar 20 10:47:01 2019 +0100 @@ -14,6 +14,7 @@ package controllers import ( + "database/sql" "encoding/csv" "fmt" "log" @@ -23,11 +24,34 @@ "github.com/gorilla/mux" + "gemma.intevation.de/gemma/pkg/common" "gemma.intevation.de/gemma/pkg/middleware" "gemma.intevation.de/gemma/pkg/models" ) const ( + selectPredictedObserveredSQL = ` +SELECT + a.measure_date AS measure_date, + a.water_level AS predicted, + b.water_level AS observed +FROM waterway.gauge_measurements a JOIN waterway.gauge_measurements b + ON a.fk_gauge_id = b.fk_gauge_id AND + a.measure_date = b.measure_date AND + a.predicted AND NOT b.predicted +WHERE + a.fk_gauge_id = ( + $1::char(1), + $2::char(2), + $3::char(3), + $4::char(4), + $5::int + ) AND + a.measure_date BETWEEN + $6::timestamp AND $6::timestamp - '72hours'::interval +ORDER BY a.measure_date +` + selectWaterlevelsSQL = ` SELECT measure_date, @@ -38,6 +62,99 @@ ` ) +func nashSutcliffe( + _ interface{}, + req *http.Request, + conn *sql.Conn, +) (jr JSONResult, err error) { + gauge := mux.Vars(req)["gauge"] + + var isrs *models.Isrs + if isrs, err = models.IsrsFromString(gauge); err != nil { + err = JSONError{ + Code: http.StatusBadRequest, + Message: fmt.Sprintf("error: Invalid ISRS code: %v", err), + } + return + } + + var when time.Time + if w := req.FormValue("when"); w != "" { + if when, err = time.Parse(models.ImportTimeFormat, w); err != nil { + err = JSONError{ + Code: http.StatusBadRequest, + Message: fmt.Sprintf("error: wrong time format: %v", err), + } + return + } + } else { + when = time.Now() + } + + ctx := req.Context() + + var rows *sql.Rows + if rows, err = conn.QueryContext( + ctx, + selectPredictedObserveredSQL, + isrs.CountryCode, + isrs.LoCode, + isrs.FairwaySection, + isrs.Orc, + isrs.Hectometre, + when, + ); err != nil { + return + } + defer rows.Close() + + var measurements []common.NSMeasurement + + for rows.Next() { + var m common.NSMeasurement + if err = rows.Scan( + &m.When, + &m.Predicted, + &m.Observed, + ); err != nil { + return + } + measurements = append(measurements, m) + } + if err = rows.Err(); err != nil { + return + } + + type coeff struct { + Value float64 `json:"value"` + Samples int `json:"samples"` + Hours int `json:"hours"` + } + + type coeffs struct { + When models.ImportTime `json:"when"` + Coeffs []coeff `json:"coeffs"` + } + + cs := make([]coeff, 3) + for i := range cs { + cs[i].Hours = (i + 1) * 24 + cs[i].Value, cs[i].Samples = common.NashSutcliffe( + measurements, + when, + when.Add(time.Duration(-cs[i].Hours)*time.Hour), + ) + } + + jr = JSONResult{ + Result: &coeffs{ + When: models.ImportTime{when}, + Coeffs: cs, + }, + } + return +} + func waterlevels(rw http.ResponseWriter, req *http.Request) { gauge := mux.Vars(req)["gauge"] diff -r 85de42146bdb -r 87aed4f9b1b8 pkg/controllers/routes.go --- a/pkg/controllers/routes.go Tue Mar 19 19:21:04 2019 +0100 +++ b/pkg/controllers/routes.go Wed Mar 20 10:47:01 2019 +0100 @@ -302,6 +302,10 @@ api.Handle("/data/waterlevels/{gauge}", any( middleware.DBConn(http.HandlerFunc(waterlevels)))).Methods(http.MethodGet) + api.Handle("/data/nash-sutcliffe/{gauge}", any(&JSONHandler{ + Handle: nashSutcliffe, + })).Methods(http.MethodGet) + // Token handling: Login/Logout. api.HandleFunc("/login", login). Methods(http.MethodPost)