Mercurial > gemma
changeset 2962:8d825551bc72
Fairway availibity for bottlenecks: Started with fetchting the relevant data. TODO: Range calculations. WIP.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Mon, 08 Apr 2019 16:38:57 +0200 |
parents | 5bc941d9ec43 |
children | 27ffd94afcb5 |
files | pkg/controllers/bottlenecks.go pkg/controllers/routes.go |
diffstat | 2 files changed, 290 insertions(+), 0 deletions(-) [+] |
line wrap: on
line diff
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/pkg/controllers/bottlenecks.go Mon Apr 08 16:38:57 2019 +0200 @@ -0,0 +1,287 @@ +// 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 <sascha.teichmann@intevation.de> + +package controllers + +import ( + "database/sql" + "fmt" + "log" + "net/http" + "sort" + "strconv" + "strings" + "time" + + "gemma.intevation.de/gemma/pkg/common" + "github.com/gorilla/mux" +) + +const ( + selectAvailableDepthSQL = ` +WITH data AS ( + SELECT + efa.measure_date, + efa.available_depth_value, + efa.water_level_value + FROM waterway.effective_fairway_availability efa + JOIN waterway.fairway_availability fa + ON efa.fairway_availability_id = fa.id + JOIN waterway.bottlenecks bn + ON fa.bottleneck_id = bn.id + WHERE + bn.objnam = $1 AND + efa.level_of_service = $2 AND + efa.measure_type = 'Measured' AND + efa.available_depth_value IS NOT NULL AND + efa.water_level_value IS NOT NULL +), +before AS ( + SELECT * FROM data WHERE measure_date < $3 + ORDER BY measure_date DESC LIMIT 1 +), +inside AS ( + SELECT * FROM data WHERE measure_date BETWEEN $3 AND $4 +), +after AS ( + SELECT * FROM data WHERE measure_date > $4 + ORDER BY measure_date LIMIT 1 +) +SELECT * FROM before +UNION ALL +SELECT * FROM inside +UNION ALL +SELECT * FROM after +ORDER BY measure_date +` + + selectGaugeLevelsSQL = ` +SELECT + grwl.depth_reference, + grwl.value +FROM waterway.gauges_reference_water_levels grwl JOIN + waterway.bottlenecks bns + ON bns.fk_g_fid = grwl.gauge_id +WHERE bns.objnam = $1 AND ( + grwl.depth_reference like 'HDC%' OR + grwl.depth_reference like 'LDC%' OR + grwl.depth_reference like 'MW%' +) +` +) + +func bottleneckAvailabilty( + _ interface{}, + req *http.Request, + conn *sql.Conn, +) (jr JSONResult, err error) { + bn := mux.Vars(req)["objnam"] + + if bn == "" { + err = JSONError{ + Code: http.StatusBadRequest, + Message: "Missing objnam of bottleneck", + } + return + } + + type referenceValue struct { + level int + value int + } + + ctx := req.Context() + + loadReferenceValues := func() ([]referenceValue, error) { + rows, err := conn.QueryContext(ctx, selectGaugeLevelsSQL, bn) + if err != nil { + return nil, err + } + defer rows.Close() + + var levels []referenceValue + + loop: + for rows.Next() { + var what string + var value int + if err := rows.Scan(&what, &value); err != nil { + return nil, err + } + var level int + switch { + case strings.HasPrefix(what, "LDC"): + level = 0 + case strings.HasPrefix(what, "MW"): + level = 1 + case strings.HasPrefix(what, "HDC"): + level = 2 + default: + return nil, fmt.Errorf("Unexpected reference level type '%s'", what) + } + for i := range levels { + if levels[i].level == level { + levels[i].value = value + continue loop + } + } + levels = append(levels, referenceValue{level: level, value: value}) + } + + if err := rows.Err(); err != nil { + return nil, err + } + + sort.Slice(levels, func(i, j int) bool { return levels[i].level < levels[j].level }) + + return levels, nil + } + + var refVals []referenceValue + if refVals, err = loadReferenceValues(); err != nil { + return + } + + if len(refVals) == 0 { + err = JSONError{ + Code: http.StatusNotFound, + Message: "No gauge reference values found for bottleneck", + } + } + + var from, to time.Time + + if f := req.FormValue("from"); f != "" { + if from, err = time.Parse(common.TimeFormat, f); err != nil { + err = JSONError{ + Code: http.StatusBadRequest, + Message: fmt.Sprintf("Invalid time format for 'from' field: %v", err), + } + return + } + from = from.UTC() + } else { + from = time.Now().AddDate(-1, 0, 0).UTC() + } + + if t := req.FormValue("to"); t != "" { + if to, err = time.Parse(common.TimeFormat, t); err != nil { + err = JSONError{ + Code: http.StatusBadRequest, + Message: fmt.Sprintf("Invalid time format for 'from' field: %v", err), + } + return + } + to = to.UTC() + } else { + to = from.AddDate(1, 0, 0).UTC() + } + + if to.Before(from) { + to, from = from, to + } + + log.Printf("info: time interval: (%v - %v)\n", from, to) + + var los int + if l := req.FormValue("los"); l != "" { + if los, err = strconv.Atoi(l); err != nil { + err = JSONError{ + Code: http.StatusBadRequest, + Message: fmt.Sprintf("Invalid value for field 'los': %v", err), + } + return + } + } else { + los = 1 + } + + type measurement struct { + when time.Time + depth int + level int + } + + loadDepthValues := func() ([]measurement, error) { + + rows, err := conn.QueryContext( + ctx, selectAvailableDepthSQL, bn, los, from, to) + if err != nil { + return nil, err + } + defer rows.Close() + + var ms []measurement + + for rows.Next() { + var m measurement + if err := rows.Scan(&m.when, &m.depth, &m.level); err != nil { + return nil, err + } + ms = append(ms, m) + } + + if err := rows.Err(); err != nil { + return nil, err + } + + return ms, nil + } + + var ms []measurement + + if ms, err = loadDepthValues(); err != nil { + return + } + + if len(ms) == 0 { + err = JSONError{ + Code: http.StatusNotFound, + Message: "No available fairway depth values found", + } + return + } + + // TODO: Calculate the ranges. + + type outputLevel struct { + Level string `json:"level"` + Value int `json:"value"` + } + + type output struct { + Levels []outputLevel `json:"levels"` + } + + out := output{} + + for i := range refVals { + var level string + switch refVals[i].level { + case 0: + level = "LDC" + case 1: + level = "MW" + case 2: + level = "HDC" + } + out.Levels = append(out.Levels, outputLevel{ + Level: level, + Value: refVals[i].value, + }) + } + + jr = JSONResult{Result: &out} + + return +}
--- a/pkg/controllers/routes.go Mon Apr 08 16:27:37 2019 +0200 +++ b/pkg/controllers/routes.go Mon Apr 08 16:38:57 2019 +0200 @@ -298,6 +298,9 @@ })).Methods(http.MethodPut) // Handler to serve data to the client. + api.Handle("/data/bottleneck/availability/{objnam}", any(&JSONHandler{ + Handle: bottleneckAvailabilty, + })).Methods(http.MethodGet) api.Handle("/data/waterlevels/{gauge}", any( middleware.DBConn(http.HandlerFunc(waterlevels)))).Methods(http.MethodGet)