Mercurial > gemma
changeset 5259:680be197844d
Merged branch new-fwa.
| author | Sascha Wilde <wilde@intevation.de> |
|---|---|
| date | Wed, 13 May 2020 11:28:34 +0200 |
| parents | 1b5c80ea5582 (current diff) 256ebbeb1252 (diff) |
| children | 771984cb74d8 |
| files | pkg/controllers/bottlenecks.go pkg/controllers/stretches.go |
| diffstat | 18 files changed, 1137 insertions(+), 1556 deletions(-) [+] |
line wrap: on
line diff
--- a/client/src/components/TimeSlider.vue Tue May 12 16:52:08 2020 +0200 +++ b/client/src/components/TimeSlider.vue Wed May 13 11:28:34 2020 +0200 @@ -257,7 +257,10 @@ zoom = d3 .zoom() .scaleExtent([0.8, 102000]) - .translateExtent([[0, 0], [svgWidth, svgHeight]]) + .translateExtent([ + [0, 0], + [svgWidth, svgHeight] + ]) .extent([[0, 0], [(svgWidth, svgHeight)]]) .on("zoom", this.zoomed);
--- a/client/src/components/fairway/Fairwayprofile.vue Tue May 12 16:52:08 2020 +0200 +++ b/client/src/components/fairway/Fairwayprofile.vue Wed May 13 11:28:34 2020 +0200 @@ -791,7 +791,10 @@ }); graph .append("path") - .datum([{ x: 0, y: 0 }, { x: this.totalLength, y: 0 }]) + .datum([ + { x: 0, y: 0 }, + { x: this.totalLength, y: 0 } + ]) .attr("fill-opacity", 0.65) .attr("fill", WATER_COLOR) .attr("stroke", "transparent")
--- a/client/src/components/gauge/HydrologicalConditions.vue Tue May 12 16:52:08 2020 +0200 +++ b/client/src/components/gauge/HydrologicalConditions.vue Wed May 13 11:28:34 2020 +0200 @@ -450,9 +450,7 @@ .attr("class", "main") .attr( "transform", - `translate(${dimensions.mainMargin.left}, ${ - dimensions.mainMargin.top - })` + `translate(${dimensions.mainMargin.left}, ${dimensions.mainMargin.top})` ); // create container for navigation diagram @@ -519,9 +517,7 @@ .attr("height", dimensions.mainHeight) .attr( "transform", - `translate(${dimensions.mainMargin.left}, ${ - dimensions.mainMargin.top - })` + `translate(${dimensions.mainMargin.left}, ${dimensions.mainMargin.top})` ); this.createZoom({ @@ -890,13 +886,22 @@ const brush = d3 .brushX() .handleSize(4) - .extent([[0, 0], [dimensions.width, dimensions.navHeight]]); + .extent([ + [0, 0], + [dimensions.width, dimensions.navHeight] + ]); const zoom = d3 .zoom() .scaleExtent([1, Infinity]) - .translateExtent([[0, 0], [dimensions.width, dimensions.mainHeight]]) - .extent([[0, 0], [dimensions.width, dimensions.mainHeight]]); + .translateExtent([ + [0, 0], + [dimensions.width, dimensions.mainHeight] + ]) + .extent([ + [0, 0], + [dimensions.width, dimensions.mainHeight] + ]); brush.on("brush end", () => { if (d3.event.sourceEvent && d3.event.sourceEvent.type === "zoom")
--- a/client/src/components/gauge/Waterlevel.vue Tue May 12 16:52:08 2020 +0200 +++ b/client/src/components/gauge/Waterlevel.vue Wed May 13 11:28:34 2020 +0200 @@ -465,9 +465,7 @@ .attr("class", "main") .attr( "transform", - `translate(${dimensions.mainMargin.left}, ${ - dimensions.mainMargin.top - })` + `translate(${dimensions.mainMargin.left}, ${dimensions.mainMargin.top})` ); // create container for navigation diagram @@ -531,9 +529,7 @@ .attr("height", dimensions.mainHeight) .attr( "transform", - `translate(${dimensions.mainMargin.left}, ${ - dimensions.mainMargin.top - })` + `translate(${dimensions.mainMargin.left}, ${dimensions.mainMargin.top})` ); this.createZoom({ @@ -851,7 +847,10 @@ // draw in nav navigation .append("path") - .datum([{ x: new Date(), y: hi + dy }, { x: new Date(), y: lo - dy }]) + .datum([ + { x: new Date(), y: hi + dy }, + { x: new Date(), y: lo - dy } + ]) .attr("class", "now-line") .attr( "d", @@ -1033,13 +1032,22 @@ const brush = d3 .brushX() .handleSize(4) - .extent([[0, 0], [dimensions.width, dimensions.navHeight]]); + .extent([ + [0, 0], + [dimensions.width, dimensions.navHeight] + ]); const zoom = d3 .zoom() .scaleExtent([1, Infinity]) - .translateExtent([[0, 0], [dimensions.width, dimensions.mainHeight]]) - .extent([[0, 0], [dimensions.width, dimensions.mainHeight]]); + .translateExtent([ + [0, 0], + [dimensions.width, dimensions.mainHeight] + ]) + .extent([ + [0, 0], + [dimensions.width, dimensions.mainHeight] + ]); brush.on("brush end", () => { if (d3.event.sourceEvent && d3.event.sourceEvent.type === "zoom")
--- a/client/src/components/layers/LegendElement.vue Tue May 12 16:52:08 2020 +0200 +++ b/client/src/components/layers/LegendElement.vue Wed May 13 11:28:34 2020 +0200 @@ -174,7 +174,12 @@ if (this.layer.get("id") === "BOTTLENECKS") { feature = new Feature({ geometry: new Polygon([ - [[-1.7, -1.2], [-1.7, 0.5], [1.7, 1.2], [1.7, -0.5]] + [ + [-1.7, -1.2], + [-1.7, 0.5], + [1.7, 1.2], + [1.7, -0.5] + ] ]) }); if (typeof mapStyle === "function") { @@ -192,7 +197,11 @@ }); } else { feature = new Feature({ - geometry: new LineString([[-1, -1], [0, 0], [1, 1]]) + geometry: new LineString([ + [-1, -1], + [0, 0], + [1, 1] + ]) }); } // special case if we need to call the style function with a special
--- a/client/src/store/fairwayavailability.js Tue May 12 16:52:08 2020 +0200 +++ b/client/src/store/fairwayavailability.js Wed May 13 11:28:34 2020 +0200 @@ -14,17 +14,18 @@ /*eslint no-unused-vars: ["error", { "varsIgnorePattern": "_" }]*/ -import { HTTP } from "@/lib/http"; import { + endOfMonth, + endOfQuarter, + endOfYear, format, startOfMonth, - endOfMonth, - startOfYear, - endOfYear, startOfQuarter, - endOfQuarter + startOfYear } from "date-fns"; +import { HTTP } from "@/lib/http"; + const LIMITINGFACTORS = { WIDTH: "width", DEPTH: "depth" @@ -259,9 +260,16 @@ type } = options; let { from, to } = options; - let name = feature.hasOwnProperty("properties") - ? feature.properties.name - : feature.get("objnam"); + let name = ""; + if (type === TYPES.BOTTLENECK) { + name = feature.hasOwnProperty("properties") + ? feature.properties.bottleneck_id + : feature.get("bottleneck_id"); + } else { + name = feature.hasOwnProperty("properties") + ? feature.properties.name + : feature.get("objnam"); + } [from, to] = getIntervallBorders(from, to, frequency); let additionalParams = ""; let endpoint = type; @@ -275,7 +283,7 @@ } const start = encodeURIComponent("00:00:00+00:00"); const end = encodeURIComponent("23:59:59+00:00"); - const URL = `data/${endpoint}/fairway-depth/${encodeURIComponent( + const URL = `data/fairway/${endpoint}/${encodeURIComponent( name )}?from=${from}T${start}&to=${to}T${end}&mode=${frequency}&los=${LOS}${additionalParams}`; HTTP.get(URL, { @@ -304,9 +312,16 @@ type } = options; let { from, to } = options; - let name = feature.hasOwnProperty("properties") - ? feature.properties.name - : feature.get("objnam"); + let name = ""; + if (type === TYPES.BOTTLENECK) { + name = feature.hasOwnProperty("properties") + ? feature.properties.bottleneck_id + : feature.get("bottleneck_id"); + } else { + name = feature.hasOwnProperty("properties") + ? feature.properties.name + : feature.get("objnam"); + } [from, to] = getIntervallBorders(from, to, frequency); const start = encodeURIComponent("00:00:00+00:00"); const end = encodeURIComponent("23:59:59+00:00"); @@ -320,7 +335,7 @@ } else if (type == TYPES.SECTION || type == TYPES.STRETCH) { additionalParams = `&depthbreaks=${depthLimit1},${depthLimit2}&widthbreaks=${widthLimit1},${widthLimit2}`; } - const URL = `data/${endpoint}/availability/${encodeURIComponent( + const URL = `data/availability/${endpoint}/${encodeURIComponent( name )}?from=${from}T${start}&to=${to}T${end}&mode=${frequency}&los=${LOS}${additionalParams}`; HTTP.get(URL, {
--- a/client/src/store/gauges.js Tue May 12 16:52:08 2020 +0200 +++ b/client/src/store/gauges.js Wed May 13 11:28:34 2020 +0200 @@ -265,9 +265,7 @@ loadYearWaterlevels({ state, commit }) { return new Promise((resolve, reject) => { HTTP.get( - `/data/year-waterlevels/${state.selectedGaugeISRS}/${ - state.yearCompare - }`, + `/data/year-waterlevels/${state.selectedGaugeISRS}/${state.yearCompare}`, { headers: { "X-Gemma-Auth": localStorage.getItem("token") } }
--- a/go.sum Tue May 12 16:52:08 2020 +0200 +++ b/go.sum Wed May 13 11:28:34 2020 +0200 @@ -49,6 +49,7 @@ github.com/golang/protobuf v1.3.1/go.mod h1:6lQm79b+lXiMfvg/cZm0SGofjICqVBUtrP5yJMmIC1U= github.com/google/btree v1.0.0/go.mod h1:lNA+9X1NB3Zf8V7Ke586lFgjr2dZNuvo3lPJSGZ5JPQ= github.com/google/go-cmp v0.2.0/go.mod h1:oXzfMopK8JAjlY9xF4vHSVASa0yLyX7SntLO5aqRK0M= +github.com/gopherjs/gopherjs v0.0.0-20181017120253-0766667cb4d1 h1:EGx4pi6eqNxGaHF6qqu48+N2wcFQ5qg5FXgOdqsJ5d8= github.com/gopherjs/gopherjs v0.0.0-20181017120253-0766667cb4d1/go.mod h1:wJfORRmW1u3UXTncJ5qlYoELFm8eSnnEO6hX4iZ3EWY= github.com/gorilla/mux v1.7.4 h1:VuZ8uybHlWmqV03+zRzdwKL4tUnIp1MAQtp1mIFE1bc= github.com/gorilla/mux v1.7.4/go.mod h1:DVbg23sWSpFRCP0SfiEN6jmj59UnW/n46BH5rLB71So=
--- a/pkg/common/errors.go Tue May 12 16:52:08 2020 +0200 +++ b/pkg/common/errors.go Wed May 13 11:28:34 2020 +0200 @@ -30,3 +30,15 @@ } return errors.New(b.String()) } + +// JoinErrors creates a comma separated string out of the given errors. +func JoinErrors(errors []error) string { + var b strings.Builder + for _, err := range errors { + if b.Len() > 0 { + b.WriteString(", ") + } + b.WriteString(err.Error()) + } + return b.String() +}
--- a/pkg/common/time.go Tue May 12 16:52:08 2020 +0200 +++ b/pkg/common/time.go Wed May 13 11:28:34 2020 +0200 @@ -97,3 +97,24 @@ return time.Unix(int64(secs), int64(nsecs)) } } + +func MinTime(a, b time.Time) time.Time { + if a.Before(b) { + return a + } + return b +} + +func MaxTime(a, b time.Time) time.Time { + if a.After(b) { + return a + } + return b +} + +func OrderTime(a, b time.Time) (time.Time, time.Time) { + if a.Before(b) { + return a, b + } + return b, a +}
--- a/pkg/controllers/bottlenecks.go Tue May 12 16:52:08 2020 +0200 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,794 +0,0 @@ -// 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> -// * Sascha Wilde <wilde@intevation.de> - -package controllers - -import ( - "context" - "database/sql" - "encoding/csv" - "fmt" - "log" - "net/http" - "sort" - "strconv" - "strings" - "time" - - "github.com/gorilla/mux" - - "gemma.intevation.de/gemma/pkg/common" - "gemma.intevation.de/gemma/pkg/middleware" -) - -const ( - selectLimitingSQL = ` -SELECT limiting FROM waterway.bottlenecks bn - WHERE bn.validity @> current_timestamp AND objnam = $1 -` - - selectAvailableDepthSQL = ` -WITH data AS ( - SELECT - efa.measure_date, - efa.available_depth_value, - efa.available_width_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.bottleneck_id - WHERE - bn.validity @> current_timestamp AND - bn.objnam = $1 AND - efa.level_of_service = $2 AND - efa.measure_type = 'Measured' AND - (efa.available_depth_value IS NOT NULL OR - efa.available_width_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 -` - - selectGaugeLDCSQL = ` -SELECT - grwl.value -FROM waterway.gauges_reference_water_levels grwl - JOIN waterway.bottlenecks bns - ON grwl.location = bns.gauge_location - AND grwl.validity @> COALESCE(upper(bns.validity), current_timestamp) -WHERE lower(bns.validity) = (SELECT max(lower(validity)) - FROM waterway.bottlenecks WHERE objnam = $1) - AND bns.objnam = $1 - AND grwl.depth_reference like 'LDC%' -` -) - -type ( - availMeasurement struct { - when time.Time - depth int16 - width int16 - value int16 - } - - availMeasurements []availMeasurement -) - -// afdRefs are the typical available fairway depth reference values. -var afdRefs = []float64{ - 230, - 250, -} - -func (measurement *availMeasurement) getDepth() float64 { - return float64(measurement.depth) -} - -func (measurement *availMeasurement) getValue() float64 { - return float64(measurement.value) -} - -func (measurement *availMeasurement) getWidth() float64 { - return float64(measurement.width) -} - -func limitingFactor(limiting string) func(*availMeasurement) float64 { - switch limiting { - case "depth": - return (*availMeasurement).getDepth - case "width": - return (*availMeasurement).getWidth - default: - log.Printf("warn: unknown limitation '%s'. default to 'depth'\n", limiting) - return (*availMeasurement).getDepth - } -} - -// According to clarification, it has to be assumed, that at times -// with no data, the best case (which by convention is the highest -// class created by classify()) should be assumed. That is due to the -// fact, that at times where bottlenecks are not a limiting factor on -// the waterway, services don't provide any data for the bottleneck in -// question. -// -// FIXME: A potential improvement could be to intersect the time -// ranges with the time ranges where bottlenecks were "active" (this -// _might_ be derivable from the validity periods in the bottleneck -// data. So it _might_ be possible do detect actual missing data (BN -// valid, but no data from FA service). Anyway, this is left out for -// now, as many clarification regarding the base assumtions would be -// needed and the results still might be unrelyable. -func optimisticPadClassification( - from, to time.Time, - classified []time.Duration, -) []time.Duration { - - var actualDuration time.Duration - for _, v := range classified { - actualDuration += v - } - - // If the actual duration is smaller than the length - // of the classifaction interval extend the - // time spend in the highest class by the difference. - if delta := to.Sub(from) - actualDuration; delta > 0 { - classified[len(classified)-1] += delta - } - - return classified -} - -func (measurements availMeasurements) classify( - from, to time.Time, - breaks []float64, - access func(*availMeasurement) float64, -) []time.Duration { - - if len(breaks) == 0 { - return []time.Duration{} - } - - result := make([]time.Duration, len(breaks)+1) - classes := make([]float64, len(breaks)+2) - values := make([]time.Time, len(classes)) - - // Add sentinels - classes[0] = breaks[0] - 9999 - classes[len(classes)-1] = breaks[len(breaks)-1] + 9999 - for i := range breaks { - classes[i+1] = breaks[i] - } - - idx := sort.Search(len(measurements), func(i int) bool { - // All values before from can be ignored. - return !measurements[i].when.Before(from) - }) - - if idx >= len(measurements) { - return optimisticPadClassification(from, to, result) - } - - // Be safe for interpolation. - if idx > 0 { - idx-- - } - - measurements = measurements[idx:] - - for i := 0; i < len(measurements)-1; i++ { - p1 := &measurements[i] - p2 := &measurements[i+1] - - if p1.when.After(to) { - return optimisticPadClassification(from, to, result) - } - - if p2.when.Before(from) { - continue - } - - if p2.when.Sub(p1.when).Hours() > 1.5 { - // Don't interpolate ranges bigger then one and a half hour - continue - } - - lo, hi := maxTime(p1.when, from), minTime(p2.when, to) - - m1, m2 := access(p1), access(p2) - if m1 == m2 { // The whole interval is in only one class. - for j := 0; j < len(classes)-1; j++ { - if classes[j] <= m1 && m1 <= classes[j+1] { - result[j] += hi.Sub(lo) - break - } - } - continue - } - - f := common.InterpolateTime( - p1.when, m1, - p2.when, m2, - ) - - for j, c := range classes { - values[j] = f(c) - } - - for j := 0; j < len(values)-1; j++ { - start, end := orderTime(values[j], values[j+1]) - - if start.After(hi) || end.Before(lo) { - continue - } - - start, end = maxTime(start, lo), minTime(end, hi) - result[j] += end.Sub(start) - } - } - - return optimisticPadClassification(from, to, result) -} - -func orderTime(a, b time.Time) (time.Time, time.Time) { - if a.Before(b) { - return a, b - } - return b, a -} - -func minTime(a, b time.Time) time.Time { - if a.Before(b) { - return a - } - return b -} - -func maxTime(a, b time.Time) time.Time { - if a.After(b) { - return a - } - return b -} - -func durationsToPercentage(duration time.Duration, classes []time.Duration) []float64 { - percents := make([]float64, len(classes)) - total := 100 / duration.Seconds() - for i, v := range classes { - percents[i] = v.Seconds() * total - } - return percents -} - -func parseFormTime( - rw http.ResponseWriter, - req *http.Request, - field string, - def time.Time, -) (time.Time, bool) { - f := req.FormValue(field) - if f == "" { - return def.UTC(), true - } - v, err := common.ParseTime(f) - if err != nil { - http.Error( - rw, fmt.Sprintf("Invalid format for '%s'.", field), - http.StatusBadRequest, - ) - return time.Time{}, false - } - return v.UTC(), true -} - -func parseFormInt( - rw http.ResponseWriter, - req *http.Request, - field string, - def int, -) (int, bool) { - f := req.FormValue(field) - if f == "" { - return def, true - } - v, err := strconv.Atoi(f) - if err != nil { - http.Error( - rw, fmt.Sprintf("Invalid format for '%s'.", field), - http.StatusBadRequest, - ) - return 0, false - } - return v, true -} - -func intervalMode(mode string) int { - switch strings.ToLower(mode) { - case "monthly": - return 0 - case "quarterly": - return 1 - case "yearly": - return 2 - default: - return 0 - } -} - -func loadDepthValues( - ctx context.Context, - conn *sql.Conn, - bottleneck string, - los int, - from, to time.Time, -) (availMeasurements, error) { - - rows, err := conn.QueryContext( - ctx, selectAvailableDepthSQL, bottleneck, los, from, to) - if err != nil { - return nil, err - } - defer rows.Close() - - var ms availMeasurements - - for rows.Next() { - var m availMeasurement - if err := rows.Scan( - &m.when, - &m.depth, - &m.width, - &m.value, - ); err != nil { - return nil, err - } - m.when = m.when.UTC() - ms = append(ms, m) - } - - if err := rows.Err(); err != nil { - return nil, err - } - - return ms, nil -} - -func loadLDCReferenceValue( - ctx context.Context, - conn *sql.Conn, - bottleneck string, -) ([]float64, error) { - var value float64 - err := conn.QueryRowContext(ctx, selectGaugeLDCSQL, bottleneck).Scan(&value) - switch { - case err == sql.ErrNoRows: - return nil, nil - case err != nil: - return nil, err - } - return []float64{value}, nil -} - -func breaksToReferenceValue(breaks string) []float64 { - parts := strings.Split(breaks, ",") - var values []float64 - - for _, part := range parts { - part = strings.TrimSpace(part) - if v, err := strconv.ParseFloat(part, 64); err == nil { - values = append(values, v) - } - } - - sort.Float64s(values) - - // dedup - for i := 1; i < len(values); { - if values[i-1] == values[i] { - copy(values[i:], values[i+1:]) - values = values[:len(values)-1] - } else { - i++ - } - } - return values -} - -func bottleneckAvailabilty(rw http.ResponseWriter, req *http.Request) { - - mode := intervalMode(req.FormValue("mode")) - bn := mux.Vars(req)["objnam"] - - if bn == "" { - http.Error( - rw, - "Missing objnam of bottleneck", - http.StatusBadRequest, - ) - return - } - - from, ok := parseFormTime(rw, req, "from", time.Now().AddDate(-1, 0, 0)) - if !ok { - return - } - - to, ok := parseFormTime(rw, req, "to", from.AddDate(1, 0, 0)) - if !ok { - return - } - - if to.Before(from) { - to, from = from, to - } - - los, ok := parseFormInt(rw, req, "los", 1) - if !ok { - return - } - - conn := middleware.GetDBConn(req) - ctx := req.Context() - - var limiting string - err := conn.QueryRowContext(ctx, selectLimitingSQL, bn).Scan(&limiting) - switch { - case err == sql.ErrNoRows: - http.Error( - rw, fmt.Sprintf("Unknown limitation for %s.", bn), - http.StatusNotFound) - return - case err != nil: - http.Error( - rw, fmt.Sprintf("DB error: %v.", err), - http.StatusInternalServerError) - return - } - - access := limitingFactor(limiting) - - ldcRefs, err := loadLDCReferenceValue(ctx, conn, bn) - if err != nil { - http.Error( - rw, - fmt.Sprintf("Internal server error: %v", err), - http.StatusInternalServerError, - ) - return - } - - if len(ldcRefs) == 0 { - http.Error( - rw, - "No gauge reference values found for bottleneck", - http.StatusNotFound, - ) - return - } - - var breaks []float64 - if b := req.FormValue("breaks"); b != "" { - breaks = breaksToReferenceValue(b) - } else { - breaks = afdRefs - } - - log.Printf("info: time interval: (%v - %v)\n", from, to) - - var ms availMeasurements - if ms, err = loadDepthValues(ctx, conn, bn, los, from, to); err != nil { - return - } - - if len(ms) == 0 { - http.Error( - rw, - "No available fairway depth values found", - http.StatusNotFound, - ) - return - } - - rw.Header().Add("Content-Type", "text/csv") - - out := csv.NewWriter(rw) - - record := make([]string, 1+2+len(breaks)+1) - record[0] = "#time" - record[1] = fmt.Sprintf("# < LDC (%.1f) [%%]", ldcRefs[0]) - record[2] = fmt.Sprintf("# >= LDC (%.1f) [%%]", ldcRefs[0]) - for i, v := range breaks { - if i == 0 { - record[3] = fmt.Sprintf("#d < %.1f [%%]", v) - } - record[i+4] = fmt.Sprintf("#d >= %.1f [%%]", v) - } - - if err := out.Write(record); err != nil { - // Too late for HTTP status message. - log.Printf("error: %v\n", err) - return - } - - interval := intervals[mode](from, to) - - now := time.Now() - for pfrom, pto, label := interval(); label != ""; pfrom, pto, label = interval() { - // Don't interpolate for the future - if now.Sub(pto) < 0 { - pto = now - } - - lnwl := ms.classify( - pfrom, pto, - ldcRefs, - (*availMeasurement).getValue, - ) - - afd := ms.classify( - pfrom, pto, - breaks, - access, - ) - - duration := pto.Sub(pfrom) - lnwlPercents := durationsToPercentage(duration, lnwl) - afdPercents := durationsToPercentage(duration, afd) - - record[0] = label - for i, v := range lnwlPercents { - record[1+i] = fmt.Sprintf("%.3f", v) - } - for i, v := range afdPercents { - record[3+i] = fmt.Sprintf("%.3f", v) - } - - if err := out.Write(record); err != nil { - // Too late for HTTP status message. - log.Printf("error: %v\n", err) - return - } - } - - out.Flush() - if err := out.Error(); err != nil { - // Too late for HTTP status message. - log.Printf("error: %v\n", err) - } -} - -func bottleneckAvailableFairwayDepth(rw http.ResponseWriter, req *http.Request) { - - mode := intervalMode(req.FormValue("mode")) - - bn := mux.Vars(req)["objnam"] - if bn == "" { - http.Error( - rw, "Missing objnam of bottleneck", - http.StatusBadRequest) - return - } - - from, ok := parseFormTime(rw, req, "from", time.Now().AddDate(-1, 0, 0)) - if !ok { - return - } - - to, ok := parseFormTime(rw, req, "to", from.AddDate(1, 0, 0)) - if !ok { - return - } - - if to.Before(from) { - to, from = from, to - } - - los, ok := parseFormInt(rw, req, "los", 1) - if !ok { - return - } - - conn := middleware.GetDBConn(req) - ctx := req.Context() - - var limiting string - err := conn.QueryRowContext(ctx, selectLimitingSQL, bn).Scan(&limiting) - switch { - case err == sql.ErrNoRows: - http.Error( - rw, fmt.Sprintf("Unknown limitation for %s.", bn), - http.StatusNotFound) - return - case err != nil: - http.Error( - rw, fmt.Sprintf("DB error: %v.", err), - http.StatusInternalServerError) - return - } - - access := limitingFactor(limiting) - - log.Printf("info: time interval: (%v - %v)\n", from, to) - - // load the measurements - ms, err := loadDepthValues(ctx, conn, bn, los, from, to) - if err != nil { - http.Error( - rw, fmt.Sprintf("Loading measurements failed: %v.", err), - http.StatusInternalServerError) - return - } - - ldcRefs, err := loadLDCReferenceValue(ctx, conn, bn) - if err != nil { - http.Error( - rw, fmt.Sprintf("Loading LDC failed: %v.", err), - http.StatusInternalServerError) - return - } - if len(ldcRefs) == 0 { - http.Error(rw, "No LDC found", http.StatusNotFound) - return - } - - var breaks []float64 - if b := req.FormValue("breaks"); b != "" { - breaks = breaksToReferenceValue(b) - } else { - breaks = afdRefs - } - - rw.Header().Add("Content-Type", "text/csv") - - out := csv.NewWriter(rw) - - // label, ldc, classes - record := make([]string, 1+2+len(breaks)+1) - record[0] = "#time" - record[1] = fmt.Sprintf("# < LDC (%.1f) [d]", ldcRefs[0]) - record[2] = fmt.Sprintf("# >= LDC (%.1f) [d]", ldcRefs[0]) - for i, v := range breaks { - if i == 0 { - record[3] = fmt.Sprintf("# < %.1f [d]", v) - } - record[i+4] = fmt.Sprintf("# >= %.1f [d]", v) - } - - if err := out.Write(record); err != nil { - // Too late for HTTP status message. - log.Printf("error: %v\n", err) - return - } - - //log.Println(len(ms)) - //for i := range ms { - // log.Println(ms[i].when, ms[i].depth) - //} - - log.Printf("info: measurements: %d\n", len(ms)) - if len(ms) > 1 { - log.Printf("info: first: %v\n", ms[0].when) - log.Printf("info: last: %v\n", ms[len(ms)-1].when) - log.Printf("info: interval: %.2f [h]\n", ms[len(ms)-1].when.Sub(ms[0].when).Hours()) - } - - interval := intervals[mode](from, to) - - now := time.Now() - for pfrom, pto, label := interval(); label != ""; pfrom, pto, label = interval() { - // Don't interpolate for the future - if now.Sub(pto) < 0 { - pto = now - } - - ldc := ms.classify( - pfrom, pto, - ldcRefs, - (*availMeasurement).getValue, - ) - - ranges := ms.classify( - pfrom, pto, - breaks, - access, - ) - - // Round to full days - ldcRounded := common.RoundToFullDays(ldc) - rangesRounded := common.RoundToFullDays(ranges) - - record[0] = label - for i, v := range ldcRounded { - record[i+1] = fmt.Sprintf("%d", v) - } - - for i, d := range rangesRounded { - record[3+i] = fmt.Sprintf("%d", d) - } - - if err := out.Write(record); err != nil { - // Too late for HTTP status message. - log.Printf("error: %v\n", err) - return - } - } - - out.Flush() - if err := out.Error(); err != nil { - // Too late for HTTP status message. - log.Printf("error: %v\n", err) - } -} - -var intervals = []func(time.Time, time.Time) func() (time.Time, time.Time, string){ - monthly, - quarterly, - yearly, -} - -func monthly(from, to time.Time) func() (time.Time, time.Time, string) { - pfrom := from - return func() (time.Time, time.Time, string) { - if pfrom.After(to) { - return time.Time{}, time.Time{}, "" - } - f := pfrom - pfrom = pfrom.AddDate(0, 1, 0) - label := fmt.Sprintf("%02d-%d", f.Month(), f.Year()) - return f, f.AddDate(0, 1, 0).Add(-time.Nanosecond), label - } -} - -func quarterly(from, to time.Time) func() (time.Time, time.Time, string) { - pfrom := from - return func() (time.Time, time.Time, string) { - if pfrom.After(to) { - return time.Time{}, time.Time{}, "" - } - f := pfrom - pfrom = pfrom.AddDate(0, 3, 0) - label := fmt.Sprintf("Q%d-%d", (int(f.Month())-1)/3+1, f.Year()) - return f, f.AddDate(0, 3, 0).Add(-time.Nanosecond), label - } -} - -func yearly(from, to time.Time) func() (time.Time, time.Time, string) { - pfrom := from - return func() (time.Time, time.Time, string) { - if pfrom.After(to) { - return time.Time{}, time.Time{}, "" - } - f := pfrom - pfrom = pfrom.AddDate(1, 0, 0) - label := fmt.Sprintf("%d", f.Year()) - return f, f.AddDate(1, 0, 0).Add(-time.Nanosecond), label - } -}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/pkg/controllers/fwa.go Wed May 13 11:28:34 2020 +0200 @@ -0,0 +1,930 @@ +// 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) 2018, 2019, 2020 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 ( + "context" + "database/sql" + "encoding/csv" + "fmt" + "log" + "net/http" + "sort" + "strconv" + "strings" + "time" + + "github.com/gorilla/mux" + + "gemma.intevation.de/gemma/pkg/common" + "gemma.intevation.de/gemma/pkg/middleware" +) + +const ( + selectBottlenecksLimitingSQL = ` +SELECT + lower(validity), + upper(validity), + limiting +FROM + waterway.bottlenecks +WHERE + bottleneck_id = $1 AND + validity && tstzrange($2, $3)` + + selectSymbolBottlenecksSQL = ` +SELECT + distinct(b.bottleneck_id) +FROM + %s s, waterway.bottlenecks b +WHERE + ST_Intersects(b.area, s.area) + AND s.name = $1 + AND b.validity && tstzrange($2, $3)` + + selectLDCsSQL = ` +SELECT + lower(grwl.validity), + upper(grwl.validity), + grwl.value +FROM + waterway.gauges_reference_water_levels grwl + JOIN waterway.bottlenecks bns + ON grwl.location = bns.gauge_location +WHERE + grwl.depth_reference like 'LDC%' + AND bns.bottleneck_id = $1 + AND grwl.validity && tstzrange($2, $3)` + + selectMeasurementsSQL = ` +WITH data AS ( + SELECT + efa.measure_date, + efa.available_depth_value, + efa.available_width_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.bottleneck_id + WHERE + bn.validity @> efa.measure_date AND + bn.bottleneck_id = $1 AND + efa.level_of_service = $2 AND + efa.measure_type = 'Measured' AND + (efa.available_depth_value IS NOT NULL OR + efa.available_width_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` +) + +type ( + timeRange struct { + lower time.Time + upper time.Time + } + + ldc struct { + timeRange + value []float64 + } + + ldcs []*ldc + + limitingFactor int + + limitingValidity struct { + timeRange + limiting limitingFactor + ldcs ldcs + } + + limitingValidities []limitingValidity + + availMeasurement struct { + when time.Time + depth int16 + width int16 + value int16 + } + + availMeasurements []availMeasurement + + bottleneck struct { + id string + validities limitingValidities + measurements availMeasurements + } + + bottlenecks []bottleneck + + fwaMode int +) + +const ( + fwaMonthly fwaMode = iota + fwaQuarterly + fwaYearly +) + +const ( + limitingDepth limitingFactor = iota + limitingWidth +) + +var limitingAccess = [...]func(*availMeasurement) float64{ + limitingDepth: (*availMeasurement).getDepth, + limitingWidth: (*availMeasurement).getWidth, +} + +// afdRefs are the typical available fairway depth reference values. +var afdRefs = []float64{ + 230, + 250, +} + +func (ls ldcs) find(from, to time.Time) *ldc { + for _, l := range ls { + if l.intersects(from, to) { + return l + } + } + return nil +} + +func fairwayAvailability(rw http.ResponseWriter, req *http.Request) { + + from, to, ok := parseFromTo(rw, req) + if !ok { + return + } + + vars := mux.Vars(req) + name := vars["name"] + if name == "" { + http.Error(rw, "missing 'name' parameter.", http.StatusBadRequest) + return + } + + los, ok := parseFormInt(rw, req, "los", 3) + if !ok { + return + } + + ctx := req.Context() + conn := middleware.GetDBConn(req) + + var bns bottlenecks + var err error + + switch vars["kind"] { + case "bottleneck": + bns = bottlenecks{{id: name}} + case "stretch": + bns, err = loadSymbolBottlenecks(ctx, conn, "users.stretches", name, from, to) + case "section": + bns, err = loadSymbolBottlenecks(ctx, conn, "waterway.sections", name, from, to) + default: + http.Error(rw, "Invalid kind type.", http.StatusBadRequest) + return + } + + if err != nil { + log.Printf("error: %v\n", err) + http.Error(rw, "cannot extract bottlenecks", http.StatusBadRequest) + return + } + + // If there are no bottlenecks there is nothing to do. + if len(bns) == 0 { + http.Error(rw, "No bottlenecks found.", http.StatusNotFound) + return + } + + // load validities and limiting factors + for i := range bns { + if err := bns[i].loadLimitingValidities(ctx, conn, from, to); err != nil { + log.Printf("error: %v\n", err) + http.Error(rw, "cannot load validities", http.StatusInternalServerError) + return + } + // load LCDs + if err := bns[i].loadLDCs(ctx, conn, from, to); err != nil { + log.Printf("error: %v\n", err) + http.Error(rw, "cannot load LDCs", http.StatusInternalServerError) + return + } + // load values + if err := bns[i].loadValues(ctx, conn, from, to, los); err != nil { + log.Printf("error: %v\n", err) + http.Error(rw, "cannot load values", http.StatusInternalServerError) + return + } + } + + // separate breaks for depth and width + var ( + breaks = parseBreaks(req.FormValue("breaks"), afdRefs) + depthBreaks = parseBreaks(req.FormValue("depthbreaks"), breaks) + widthBreaks = parseBreaks(req.FormValue("widthbreaks"), breaks) + chooseBreaks = [...][]float64{ + limitingDepth: depthBreaks, + limitingWidth: widthBreaks, + } + + useDepth = bns.hasLimiting(limitingDepth, from, to) + useWidth = bns.hasLimiting(limitingWidth, from, to) + ) + + if useDepth && useWidth && len(widthBreaks) != len(depthBreaks) { + http.Error( + rw, + fmt.Sprintf("class breaks lengths differ: %d != %d", + len(widthBreaks), len(depthBreaks)), + http.StatusBadRequest, + ) + return + } + + availability := vars["type"] == "availability" + + var record []string + if !availability { + // in days + record = makeHeader(useDepth && useWidth, 1, breaks, 'd') + } else { + // percentage + record = makeHeader(useDepth && useWidth, 3, breaks, '%') + } + + rw.Header().Add("Content-Type", "text/csv") + + out := csv.NewWriter(rw) + + if err := out.Write(record); err != nil { + // Too late for HTTP status message. + log.Printf("error: %v\n", err) + return + } + + for i := range record[1:] { + record[i+1] = "0" + } + + // For every day on every bottleneck we need to find out if this day is valid. + validities := make([]func(time.Time, time.Time) *limitingValidity, len(bns)) + for i := range bns { + validities[i] = bns[i].validities.find() + } + + // Mode reflects if we use monthly, quarterly od yearly intervals. + mode := parseFWAMode(req.FormValue("mode")) + + label, finish := interval(mode, from) + + var ( + totalDays, overLDCDays int + missingLDCs = make([]int, len(validities)) + counters = make([]int, len(breaks)+1) + ) + + var current, next time.Time + + write := func() error { + record[0] = label(current) + + if !availability { + record[1] = strconv.Itoa(totalDays - overLDCDays) + record[2] = strconv.Itoa(overLDCDays) + for i, c := range counters { + record[3+i] = strconv.Itoa(c) + } + } else { + overPerc := float64(overLDCDays) * 100 / float64(totalDays) + record[1] = fmt.Sprintf("%.3f", 100-overPerc) + record[2] = fmt.Sprintf("%.3f", overPerc) + for i, c := range counters { + perc := float64(c) * 100 / float64(totalDays) + record[3+i] = fmt.Sprintf("%.3f", perc) + } + } + + return out.Write(record) + } + + // Stop yesterday + end := common.MinTime(dusk(time.Now()).Add(-time.Nanosecond), to) + + // We step through the time in steps of one day. + for current = from; current.Before(end); { + + next = current.AddDate(0, 0, 1) + + // Assume that a bottleneck is over LDC. + overLDC := true + lowest := len(counters) - 1 + + var hasValid bool + + // check all bottlenecks + for i, validity := range validities { + + // Check if bottleneck is available for this day. + vs := validity(current, next) + if vs == nil { + continue + } + + // Let's see if we have a LDC for this day. + ldc := vs.ldcs.find(current, next) + if ldc == nil { + missingLDCs[i]++ + continue + } + + hasValid = true + + if overLDC { // If its already not shipable we need no further tests. + result := bns[i].measurements.classify( + current, next, + ldc.value, + (*availMeasurement).getValue) + + if result[1] < 12*time.Hour { + overLDC = false + } + } + + if min := minClass(bns[i].measurements.classify( + current, next, + chooseBreaks[vs.limiting], + limitingAccess[vs.limiting]), + 12*time.Hour, + ); min < lowest { + lowest = min + } + } + + if hasValid { + if overLDC { + overLDCDays++ + } + counters[lowest]++ + } else { // assume that all is in best conditions + overLDCDays++ + counters[len(counters)-1]++ + } + + totalDays++ + + if finish(next) { + if err := write(); err != nil { + // Too late for HTTP status message. + log.Printf("error: %v\n", err) + return + } + + // Reset counters + overLDCDays, totalDays = 0, 0 + for i := range counters { + counters[i] = 0 + } + } + + current = next + } + + // Write rest if last period was not finished. + if totalDays > 0 { + if err := write(); err != nil { + // Too late for HTTP status message. + log.Printf("error: %v\n", err) + return + } + } + + // TODO: Log missing LDCs + + out.Flush() + if err := out.Error(); err != nil { + // Too late for HTTP status message. + log.Printf("error: %v\n", err) + } +} + +func minClass(classes []time.Duration, threshold time.Duration) int { + var sum time.Duration + for i, v := range classes { + if sum += v; sum >= threshold { + return i + } + } + return len(classes) - 1 +} + +func dusk(t time.Time) time.Time { + return time.Date( + t.Year(), + t.Month(), + t.Day(), + 0, 0, 0, 0, + t.Location()) +} + +func dawn(t time.Time) time.Time { + return time.Date( + t.Year(), + t.Month(), + t.Day(), + 23, 59, 59, 999999999, + t.Location()) +} + +func parseFromTo( + rw http.ResponseWriter, + req *http.Request, +) (time.Time, time.Time, bool) { + from, ok := parseFormTime(rw, req, "from", time.Now().AddDate(-1, 0, 0)) + if !ok { + return time.Time{}, time.Time{}, false + } + + to, ok := parseFormTime(rw, req, "to", from.AddDate(1, 0, 0)) + if !ok { + return time.Time{}, time.Time{}, false + } + + from, to = common.OrderTime(from, to) + // Operate on daily basis so go to full days. + return dusk(from), dawn(to), true +} + +func parseFWAMode(mode string) fwaMode { + switch strings.ToLower(mode) { + case "monthly": + return fwaMonthly + case "quarterly": + return fwaQuarterly + case "yearly": + return fwaYearly + default: + return fwaMonthly + } +} + +func breaksToReferenceValue(breaks string) []float64 { + parts := strings.Split(breaks, ",") + var values []float64 + + for _, part := range parts { + part = strings.TrimSpace(part) + if v, err := strconv.ParseFloat(part, 64); err == nil { + values = append(values, v) + } + } + + return common.DedupFloat64s(values) +} + +func parseBreaks(breaks string, defaults []float64) []float64 { + if breaks != "" { + return breaksToReferenceValue(breaks) + } + return defaults +} + +func (tr *timeRange) intersects(from, to time.Time) bool { + return !(to.Before(tr.lower) || from.After(tr.upper)) +} + +func (tr *timeRange) toUTC() { + tr.lower = tr.lower.UTC() + tr.upper = tr.upper.UTC() +} + +func (lvs limitingValidities) find() func(from, to time.Time) *limitingValidity { + + var last *limitingValidity + + return func(from, to time.Time) *limitingValidity { + if last != nil && last.intersects(from, to) { + return last + } + for i := range lvs { + if lv := &lvs[i]; lv.intersects(from, to) { + last = lv + return lv + } + } + return nil + } +} + +func (lvs limitingValidities) hasLimiting(limiting limitingFactor, from, to time.Time) bool { + for i := range lvs { + if lvs[i].limiting == limiting && lvs[i].intersects(from, to) { + return true + } + } + return false +} + +func (bns bottlenecks) hasLimiting(limiting limitingFactor, from, to time.Time) bool { + for i := range bns { + if bns[i].validities.hasLimiting(limiting, from, to) { + return true + } + } + return false +} + +func parseLimitingFactor(limiting string) limitingFactor { + switch limiting { + case "depth": + return limitingDepth + case "width": + return limitingWidth + default: + log.Printf("warn: unknown limitation '%s'. default to 'depth'\n", limiting) + return limitingDepth + } +} + +func loadLimitingValidities( + ctx context.Context, + conn *sql.Conn, + bottleneckID string, + from, to time.Time, +) (limitingValidities, error) { + + var lvs limitingValidities + + rows, err := conn.QueryContext( + ctx, + selectBottlenecksLimitingSQL, + bottleneckID, + from, to) + + if err != nil { + return nil, err + } + defer rows.Close() + + for rows.Next() { + var ( + lv limitingValidity + limiting string + upper sql.NullTime + ) + if err := rows.Scan( + &lv.lower, + &upper, + &limiting, + ); err != nil { + return nil, err + } + if upper.Valid { + lv.upper = upper.Time + } else { + lv.upper = to.Add(24 * time.Hour) + } + lv.toUTC() + lv.limiting = parseLimitingFactor(limiting) + lvs = append(lvs, lv) + } + + return lvs, rows.Err() +} + +func loadSymbolBottlenecks( + ctx context.Context, + conn *sql.Conn, + what, name string, + from, to time.Time, +) (bottlenecks, error) { + + rows, err := conn.QueryContext( + ctx, + fmt.Sprintf(selectSymbolBottlenecksSQL, what), + name, + from, to) + if err != nil { + return nil, err + } + defer rows.Close() + + var bns bottlenecks + + for rows.Next() { + var b bottleneck + if err := rows.Scan(&b.id); err != nil { + return nil, err + } + bns = append(bns, b) + } + + return bns, rows.Err() +} + +func (bn *bottleneck) loadLimitingValidities( + ctx context.Context, + conn *sql.Conn, + from, to time.Time, +) error { + vs, err := loadLimitingValidities( + ctx, + conn, + bn.id, + from, to) + if err == nil { + bn.validities = vs + } + return err +} + +func (bn *bottleneck) loadLDCs( + ctx context.Context, + conn *sql.Conn, + from, to time.Time, +) error { + rows, err := conn.QueryContext( + ctx, selectLDCsSQL, + bn.id, + from, to) + if err != nil { + return err + } + defer rows.Close() + for rows.Next() { + l := ldc{value: []float64{0}} + var upper sql.NullTime + if err := rows.Scan(&l.lower, &upper, &l.value[0]); err != nil { + return err + } + if upper.Valid { + l.upper = upper.Time + } else { + l.upper = to.Add(24 * time.Hour) + } + l.toUTC() + for i := range bn.validities { + vs := &bn.validities[i] + + if vs.intersects(l.lower, l.upper) { + vs.ldcs = append(vs.ldcs, &l) + } + } + } + return rows.Err() +} + +func (bn *bottleneck) loadValues( + ctx context.Context, + conn *sql.Conn, + from, to time.Time, + los int, +) error { + rows, err := conn.QueryContext( + ctx, selectMeasurementsSQL, + bn.id, + los, + from, to) + if err != nil { + return err + } + defer rows.Close() + + var ms availMeasurements + + for rows.Next() { + var m availMeasurement + if err := rows.Scan( + &m.when, + &m.depth, + &m.width, + &m.value, + ); err != nil { + return err + } + m.when = m.when.UTC() + ms = append(ms, m) + } + if err := rows.Err(); err != nil { + return err + } + bn.measurements = ms + return nil +} + +func (measurement *availMeasurement) getDepth() float64 { + return float64(measurement.depth) +} + +func (measurement *availMeasurement) getValue() float64 { + return float64(measurement.value) +} + +func (measurement *availMeasurement) getWidth() float64 { + return float64(measurement.width) +} + +func (measurements availMeasurements) classify( + from, to time.Time, + breaks []float64, + access func(*availMeasurement) float64, +) []time.Duration { + + if len(breaks) == 0 { + return []time.Duration{} + } + + result := make([]time.Duration, len(breaks)+1) + classes := make([]float64, len(breaks)+2) + values := make([]time.Time, len(classes)) + + // Add sentinels + classes[0] = breaks[0] - 9999 + classes[len(classes)-1] = breaks[len(breaks)-1] + 9999 + for i := range breaks { + classes[i+1] = breaks[i] + } + + idx := sort.Search(len(measurements), func(i int) bool { + // All values before from can be ignored. + return !measurements[i].when.Before(from) + }) + + if idx >= len(measurements) { + return result + } + + // Be safe for interpolation. + if idx > 0 { + idx-- + } + + measurements = measurements[idx:] + + for i := 0; i < len(measurements)-1; i++ { + p1 := &measurements[i] + p2 := &measurements[i+1] + + if p1.when.After(to) { + return result + } + + if p2.when.Before(from) { + continue + } + + // TODO: Discuss if we want somethinh like this. + if false && p2.when.Sub(p1.when).Hours() > 1.5 { + // Don't interpolate ranges bigger then one and a half hour + continue + } + + lo, hi := common.MaxTime(p1.when, from), common.MinTime(p2.when, to) + + m1, m2 := access(p1), access(p2) + if m1 == m2 { // The whole interval is in only one class. + for j := 0; j < len(classes)-1; j++ { + if classes[j] <= m1 && m1 <= classes[j+1] { + result[j] += hi.Sub(lo) + break + } + } + continue + } + + f := common.InterpolateTime( + p1.when, m1, + p2.when, m2, + ) + + for j, c := range classes { + values[j] = f(c) + } + + for j := 0; j < len(values)-1; j++ { + start, end := common.OrderTime(values[j], values[j+1]) + + if start.After(hi) || end.Before(lo) { + continue + } + + start, end = common.MaxTime(start, lo), common.MinTime(end, hi) + result[j] += end.Sub(start) + } + } + + return result +} + +func interval(mode fwaMode, t time.Time) ( + label func(time.Time) string, + finish func(time.Time) bool, +) { + switch mode { + case fwaMonthly: + label, finish = monthLabel, otherMonth(t) + case fwaQuarterly: + label, finish = quarterLabel, otherQuarter(t) + case fwaYearly: + label, finish = yearLabel, otherYear(t) + default: + panic("Unknown mode") + } + return +} + +func monthLabel(t time.Time) string { + return fmt.Sprintf("%02d-%d", t.Month(), t.Year()) +} + +func quarterLabel(t time.Time) string { + return fmt.Sprintf("Q%d-%d", (int(t.Month())-1)/3+1, t.Year()) +} + +func yearLabel(t time.Time) string { + return strconv.Itoa(t.Year()) +} + +func otherMonth(t time.Time) func(time.Time) bool { + return func(x time.Time) bool { + flag := t.Day() == x.Day() + if flag { + t = x + } + return flag + } +} + +func otherQuarter(t time.Time) func(time.Time) bool { + return func(x time.Time) bool { + flag := (t.Month()-1)/3 != (x.Month()-1)/3 + if flag { + t = x + } + return flag + } +} + +func otherYear(t time.Time) func(time.Time) bool { + return func(x time.Time) bool { + flag := t.Year() != x.Year() + if flag { + t = x + } + return flag + } +} + +func makeHeader(flag bool, prec int, breaks []float64, unit rune) []string { + record := make([]string, 1+2+len(breaks)+1) + record[0] = "# time" + record[1] = fmt.Sprintf("# < LDC [%c]", unit) + record[2] = fmt.Sprintf("# >= LDC [%c]", unit) + for i, v := range breaks { + if flag { + if i == 0 { + record[3] = fmt.Sprintf("# < break_1 [%c]", unit) + } + record[i+4] = fmt.Sprintf("# >= break_%d [%c]", i+1, unit) + } else { + if i == 0 { + record[3] = fmt.Sprintf("# < %.*f [%c]", prec, v, unit) + } + record[i+4] = fmt.Sprintf("# >= %.*f [%c]", prec, v, unit) + } + } + return record +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/pkg/controllers/misc.go Wed May 13 11:28:34 2020 +0200 @@ -0,0 +1,65 @@ +// 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, 2020 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 ( + "fmt" + "net/http" + "strconv" + "time" + + "gemma.intevation.de/gemma/pkg/common" +) + +func parseFormTime( + rw http.ResponseWriter, + req *http.Request, + field string, + def time.Time, +) (time.Time, bool) { + f := req.FormValue(field) + if f == "" { + return def.UTC(), true + } + v, err := common.ParseTime(f) + if err != nil { + http.Error( + rw, fmt.Sprintf("Invalid format for '%s'.", field), + http.StatusBadRequest, + ) + return time.Time{}, false + } + return v.UTC(), true +} + +func parseFormInt( + rw http.ResponseWriter, + req *http.Request, + field string, + def int, +) (int, bool) { + f := req.FormValue(field) + if f == "" { + return def, true + } + v, err := strconv.Atoi(f) + if err != nil { + http.Error( + rw, fmt.Sprintf("Invalid format for '%s'.", field), + http.StatusBadRequest, + ) + return 0, false + } + return v, true +}
--- a/pkg/controllers/routes.go Tue May 12 16:52:08 2020 +0200 +++ b/pkg/controllers/routes.go Wed May 13 11:28:34 2020 +0200 @@ -324,21 +324,12 @@ // Handler to serve data to the client. + api.Handle("/data/{type:availability|fairway}/{kind:stretch|section|bottleneck}/{name:.+}", any( + mw.DBConn(http.HandlerFunc(fairwayAvailability)))).Methods(http.MethodGet) + api.Handle("/data/stretch/shape/{name:.+}", any( mw.DBConn(http.HandlerFunc(stretchShapeDownload)))).Methods(http.MethodGet) - api.Handle("/data/{kind:stretch|section}/availability/{name:.+}", any( - mw.DBConn(http.HandlerFunc(stretchAvailabilty)))).Methods(http.MethodGet) - - api.Handle("/data/{kind:stretch|section}/fairway-depth/{name:.+}", any( - mw.DBConn(http.HandlerFunc(stretchAvailableFairwayDepth)))).Methods(http.MethodGet) - - api.Handle("/data/bottleneck/fairway-depth/{objnam:.+}", any( - mw.DBConn(http.HandlerFunc(bottleneckAvailableFairwayDepth)))).Methods(http.MethodGet) - - api.Handle("/data/bottleneck/availability/{objnam:.+}", any( - mw.DBConn(http.HandlerFunc(bottleneckAvailabilty)))).Methods(http.MethodGet) - api.Handle("/data/waterlevels/{gauge:.+}", any( mw.DBConn(http.HandlerFunc(waterlevels)))).Methods(http.MethodGet)
--- a/pkg/controllers/stretches.go Tue May 12 16:52:08 2020 +0200 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,709 +0,0 @@ -// 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> -// * Sascha Wilde <wilde@intevation.de> - -package controllers - -import ( - "context" - "database/sql" - "encoding/csv" - "fmt" - "log" - "net/http" - "runtime" - "strings" - "sync" - "time" - - "github.com/gorilla/mux" - - "gemma.intevation.de/gemma/pkg/common" - "gemma.intevation.de/gemma/pkg/middleware" -) - -// The following requests are taking _all_ bottlenecks into account, not only -// the currently valid ones. This is neccessary, as we are doing reports on -// arbitrary time ranges and bottlenecks currently active might have been in the -// selected time range. -// -// FIXME: the better solution would be to limit the bottlenecks to those with: -// b.validity && REQUESTED_TIME_RANGE - -const ( - selectSectionBottlenecks = ` -SELECT - distinct(b.objnam), - b.limiting -FROM waterway.sections s, waterway.bottlenecks b -WHERE ST_Intersects(b.area, s.area) - AND s.name = $1` - - selectStretchBottlenecks = ` -SELECT - distinct(b.objnam), - b.limiting -FROM users.stretches s, waterway.bottlenecks b -WHERE ST_Intersects(b.area, s.area) - AND s.name = $1` -) - -type ( - stretchBottleneck struct { - name string - limiting string - } - - stretchBottlenecks []stretchBottleneck - - fullStretchBottleneck struct { - *stretchBottleneck - measurements availMeasurements - ldc []float64 - breaks []float64 - access func(*availMeasurement) float64 - } -) - -func (bns stretchBottlenecks) contains(limiting string) bool { - for i := range bns { - if bns[i].limiting == limiting { - return true - } - } - return false -} - -func maxDuration(a time.Duration, b time.Duration) time.Duration { - if a > b { - return a - } - return b -} - -func sumClassesTo(breaks []time.Duration, to int) time.Duration { - var result time.Duration - for i := 0; i <= to; i++ { - result += breaks[i] - } - return result -} - -func aggregateClasses( - new []time.Duration, - agg []time.Duration, -) []time.Duration { - newAgg := make([]time.Duration, len(agg)) - - for i := 0; i < len(new)-1; i++ { - oldSum := sumClassesTo(agg, i) - newSum := sumClassesTo(new, i) - newAgg[i] = maxDuration(newSum, oldSum) - sumClassesTo(newAgg, i-1) - } - // adjust highest class so the sum of all classes in agg - // matches the original sum of all classes in new. - newAgg[len(new)-1] = - sumClassesTo(new, len(new)-1) - sumClassesTo(newAgg, len(new)-2) - return newAgg -} - -func loadFullStretchBottleneck( - ctx context.Context, - conn *sql.Conn, - bn *stretchBottleneck, - los int, - from, to time.Time, - depthbreaks, widthbreaks []float64, -) (*fullStretchBottleneck, error) { - measurements, err := loadDepthValues(ctx, conn, bn.name, los, from, to) - if err != nil { - return nil, err - } - ldc, err := loadLDCReferenceValue(ctx, conn, bn.name) - if err != nil { - return nil, err - } - - if len(ldc) == 0 { - return nil, fmt.Errorf("no LDC found for bottleneck: %s", bn.name) - } - - var access func(*availMeasurement) float64 - var breaks []float64 - - switch bn.limiting { - case "width": - access = (*availMeasurement).getWidth - breaks = widthbreaks - case "depth": - access = (*availMeasurement).getDepth - breaks = depthbreaks - default: - log.Printf( - "warn: unknown limitation '%s'. default to 'depth'.\n", - bn.limiting) - access = (*availMeasurement).getDepth - breaks = depthbreaks - } - - return &fullStretchBottleneck{ - stretchBottleneck: bn, - measurements: measurements, - ldc: ldc, - breaks: breaks, - access: access, - }, nil -} - -func loadStretchBottlenecks( - ctx context.Context, - conn *sql.Conn, - stretch bool, - name string, -) (stretchBottlenecks, error) { - var sql string - if stretch { - sql = selectStretchBottlenecks - } else { - sql = selectSectionBottlenecks - } - - rows, err := conn.QueryContext(ctx, sql, name) - if err != nil { - return nil, err - } - defer rows.Close() - - var bns stretchBottlenecks - - for rows.Next() { - var bn stretchBottleneck - if err := rows.Scan( - &bn.name, - &bn.limiting, - ); err != nil { - return nil, err - } - bns = append(bns, bn) - } - - if err := rows.Err(); err != nil { - return nil, err - } - - return bns, nil -} - -func stretchAvailableFairwayDepth(rw http.ResponseWriter, req *http.Request) { - - vars := mux.Vars(req) - stretch := vars["kind"] == "stretch" - name := vars["name"] - mode := intervalMode(req.FormValue("mode")) - - depthbreaks, widthbreaks := afdRefs, afdRefs - - from, ok := parseFormTime(rw, req, "from", time.Now().AddDate(-1, 0, 0)) - if !ok { - return - } - - to, ok := parseFormTime(rw, req, "to", from.AddDate(1, 0, 0)) - if !ok { - return - } - - if to.Before(from) { - to, from = from, to - } - - los, ok := parseFormInt(rw, req, "los", 1) - if !ok { - return - } - - conn := middleware.GetDBConn(req) - ctx := req.Context() - - bns, err := loadStretchBottlenecks(ctx, conn, stretch, name) - if err != nil { - http.Error( - rw, fmt.Sprintf("DB error: %v.", err), - http.StatusInternalServerError) - return - } - - if len(bns) == 0 { - http.Error(rw, "No bottlenecks found.", http.StatusNotFound) - return - } - - if b := req.FormValue("depthbreaks"); b != "" { - depthbreaks = breaksToReferenceValue(b) - } - - if b := req.FormValue("widthbreaks"); b != "" { - widthbreaks = breaksToReferenceValue(b) - } - - useDepth, useWidth := bns.contains("depth"), bns.contains("width") - - if useDepth && useWidth && len(widthbreaks) != len(depthbreaks) { - http.Error( - rw, - fmt.Sprintf("class breaks lengths differ: %d != %d", - len(widthbreaks), len(depthbreaks)), - http.StatusBadRequest, - ) - return - } - - log.Printf("info: time interval: (%v - %v)\n", from, to) - - var loaded []*fullStretchBottleneck - var errors []error - - for i := range bns { - l, err := loadFullStretchBottleneck( - ctx, - conn, - &bns[i], - los, - from, to, - depthbreaks, widthbreaks, - ) - if err != nil { - log.Printf("error: %v\n", err) - errors = append(errors, err) - continue - } - loaded = append(loaded, l) - } - - if len(loaded) == 0 { - http.Error( - rw, - fmt.Sprintf("No bottleneck loaded: %v", joinErrors(errors)), - http.StatusInternalServerError, - ) - return - } - - n := runtime.NumCPU() / 2 - if n == 0 { - n = 1 - } - - type result struct { - label string - from time.Time - to time.Time - ldc []time.Duration - breaks []time.Duration - } - - jobCh := make(chan *result) - - var wg sync.WaitGroup - - for i := 0; i < n; i++ { - wg.Add(1) - go func() { - defer wg.Done() - for res := range jobCh { - - var ldc, breaks []time.Duration - - now := time.Now() - for _, bn := range loaded { - // Don't interpolate for the future - if now.Sub(res.to) < 0 { - res.to = now - } - - l := bn.measurements.classify( - res.from, res.to, - bn.ldc, - (*availMeasurement).getValue, - ) - b := bn.measurements.classify( - res.from, res.to, - bn.breaks, - bn.access, - ) - - if ldc == nil { - ldc, breaks = l, b - } else { - ldc = aggregateClasses(l, ldc) - breaks = aggregateClasses(b, breaks) - } - } - - res.ldc = ldc - res.breaks = breaks - } - }() - } - - var results []*result - - interval := intervals[mode](from, to) - - var breaks []float64 - - if useDepth { - breaks = depthbreaks - } else { - breaks = widthbreaks - } - - for pfrom, pto, label := interval(); label != ""; pfrom, pto, label = interval() { - - res := &result{ - label: label, - from: pfrom, - to: pto, - } - results = append(results, res) - jobCh <- res - } - - close(jobCh) - wg.Wait() - - rw.Header().Add("Content-Type", "text/csv") - - out := csv.NewWriter(rw) - - // label, lnwl, classes - record := make([]string, 1+2+len(breaks)+1) - record[0] = "# time" - record[1] = "# < LDC [d]" - record[2] = "# >= LDC [d]" - for i, v := range breaks { - if useDepth && useWidth { - if i == 0 { - record[3] = "# < break_1 [d]" - } - record[i+4] = fmt.Sprintf("# >= break_%d", i+1) - } else { - if i == 0 { - record[3] = fmt.Sprintf("# < %.1f [d]", v) - } - record[i+4] = fmt.Sprintf("# >= %.1f [d]", v) - } - } - - if err := out.Write(record); err != nil { - // Too late for HTTP status message. - log.Printf("error: %v\n", err) - return - } - - empty := fmt.Sprintf("%.3f", 0.0) - for i := range record[1:] { - record[i+1] = empty - } - - for _, r := range results { - // Round to full days - ldcRounded := common.RoundToFullDays(r.ldc) - rangesRounded := common.RoundToFullDays(r.breaks) - - record[0] = r.label - for i, v := range ldcRounded { - record[1+i] = fmt.Sprintf("%d", v) - } - - for i, d := range rangesRounded { - record[3+i] = fmt.Sprintf("%d", d) - } - - if err := out.Write(record); err != nil { - // Too late for HTTP status message. - log.Printf("error: %v\n", err) - return - } - } - - out.Flush() - if err := out.Error(); err != nil { - // Too late for HTTP status message. - log.Printf("error: %v\n", err) - } -} - -func joinErrors(errors []error) string { - var b strings.Builder - for _, err := range errors { - if b.Len() > 0 { - b.WriteString(", ") - } - b.WriteString(err.Error()) - } - return b.String() -} - -func stretchAvailabilty(rw http.ResponseWriter, req *http.Request) { - - vars := mux.Vars(req) - stretch := vars["kind"] == "stretch" - name := vars["name"] - mode := intervalMode(req.FormValue("mode")) - - if name == "" { - http.Error( - rw, - fmt.Sprintf("Missing %s name", vars["kind"]), - http.StatusBadRequest, - ) - return - } - - from, ok := parseFormTime(rw, req, "from", time.Now().AddDate(-1, 0, 0)) - if !ok { - return - } - - to, ok := parseFormTime(rw, req, "to", from.AddDate(1, 0, 0)) - if !ok { - return - } - - if to.Before(from) { - to, from = from, to - } - - los, ok := parseFormInt(rw, req, "los", 1) - if !ok { - return - } - - depthbreaks, widthbreaks := afdRefs, afdRefs - - if b := req.FormValue("depthbreaks"); b != "" { - depthbreaks = breaksToReferenceValue(b) - } - - if b := req.FormValue("widthbreaks"); b != "" { - widthbreaks = breaksToReferenceValue(b) - } - - conn := middleware.GetDBConn(req) - ctx := req.Context() - - bns, err := loadStretchBottlenecks(ctx, conn, stretch, name) - if err != nil { - http.Error( - rw, fmt.Sprintf("DB error: %v.", err), - http.StatusInternalServerError) - return - } - - if len(bns) == 0 { - http.Error( - rw, - "No bottlenecks found.", - http.StatusNotFound, - ) - return - } - - useDepth, useWidth := bns.contains("depth"), bns.contains("width") - - if useDepth && useWidth && len(widthbreaks) != len(depthbreaks) { - http.Error( - rw, - fmt.Sprintf("class breaks lengths differ: %d != %d", - len(widthbreaks), len(depthbreaks)), - http.StatusBadRequest, - ) - return - } - - log.Printf("info: time interval: (%v - %v)\n", from, to) - - var loaded []*fullStretchBottleneck - var errors []error - - for i := range bns { - l, err := loadFullStretchBottleneck( - ctx, - conn, - &bns[i], - los, - from, to, - depthbreaks, widthbreaks, - ) - if err != nil { - log.Printf("error: %v\n", err) - errors = append(errors, err) - continue - } - loaded = append(loaded, l) - } - - if len(loaded) == 0 { - http.Error( - rw, - fmt.Sprintf("No bottleneck loaded: %v", joinErrors(errors)), - http.StatusInternalServerError, - ) - return - } - - n := runtime.NumCPU() / 2 - if n == 0 { - n = 1 - } - - type result struct { - label string - from time.Time - to time.Time - ldc []float64 - breaks []float64 - } - - jobCh := make(chan *result) - - var wg sync.WaitGroup - - for i := 0; i < n; i++ { - wg.Add(1) - go func() { - defer wg.Done() - for res := range jobCh { - var ldc, breaks []time.Duration - - now := time.Now() - for _, bn := range loaded { - // Don't interpolate for the future - if now.Sub(res.to) < 0 { - res.to = now - } - - l := bn.measurements.classify( - res.from, res.to, - bn.ldc, - (*availMeasurement).getValue, - ) - - b := bn.measurements.classify( - res.from, res.to, - bn.breaks, - bn.access, - ) - - if ldc == nil { - ldc, breaks = l, b - } else { - ldc = aggregateClasses(l, ldc) - breaks = aggregateClasses(b, breaks) - } - } - - duration := res.to.Sub(res.from) - - res.ldc = durationsToPercentage(duration, ldc) - res.breaks = durationsToPercentage(duration, breaks) - } - }() - } - - var results []*result - - interval := intervals[mode](from, to) - - var breaks []float64 - - if useDepth { - breaks = depthbreaks - } else { - breaks = widthbreaks - } - - for pfrom, pto, label := interval(); label != ""; pfrom, pto, label = interval() { - - res := &result{ - label: label, - from: pfrom, - to: pto, - } - results = append(results, res) - - jobCh <- res - } - - close(jobCh) - wg.Wait() - - rw.Header().Add("Content-Type", "text/csv") - - out := csv.NewWriter(rw) - - // label, lnwl, classes - record := make([]string, 1+2+len(breaks)+1) - record[0] = "# time" - record[1] = "# < LDC [%%]" - record[2] = "# >= LDC [%%]" - for i, v := range breaks { - if useDepth && useWidth { - if i == 0 { - record[3] = "# < break_1 [%%]" - } - record[i+4] = fmt.Sprintf("# >= break_%d [%%]", i+1) - } else { - if i == 0 { - record[3] = fmt.Sprintf("# < %.3f [%%]", v) - } - record[i+4] = fmt.Sprintf("# >= %.3f [%%]", v) - } - } - - if err := out.Write(record); err != nil { - // Too late for HTTP status message. - log.Printf("error: %v\n", err) - return - } - - empty := fmt.Sprintf("%.3f", 0.0) - for i := range record[1:] { - record[i+1] = empty - } - - for _, res := range results { - record[0] = res.label - - for i, v := range res.ldc { - record[1+i] = fmt.Sprintf("%.3f", v) - } - - for i, v := range res.breaks { - record[3+i] = fmt.Sprintf("%.3f", v) - } - - if err := out.Write(record); err != nil { - // Too late for HTTP status message. - log.Printf("error: %v\n", err) - return - } - } - - out.Flush() - if err := out.Error(); err != nil { - // Too late for HTTP status message. - log.Printf("error: %v\n", err) - } -}
--- a/schema/default_sysconfig.sql Tue May 12 16:52:08 2020 +0200 +++ b/schema/default_sysconfig.sql Wed May 13 11:28:34 2020 +0200 @@ -4,7 +4,7 @@ -- SPDX-License-Identifier: AGPL-3.0-or-later -- License-Filename: LICENSES/AGPL-3.0.txt --- Copyright (C) 2018, 2019 by via donau +-- Copyright (C) 2018, 2019, 2020 by via donau -- – Österreichische Wasserstraßen-Gesellschaft mbH -- Software engineering by Intevation GmbH @@ -220,6 +220,7 @@ ('waterway', 'bottleneck_overview', 4326, NULL, $$ SELECT objnam AS name, + bn.bottleneck_id, ST_Centroid(area) AS point, (lower(stretch)).hectometre AS from, (upper(stretch)).hectometre AS to,
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1439/01.add_bn_id_to_bn_overview.sql Wed May 13 11:28:34 2020 +0200 @@ -0,0 +1,22 @@ +-- Add bottleneck_id column to bottleneck_overview view. + +UPDATE sys_admin.published_services + SET + view_def = $$ + SELECT + objnam AS name, + bn.bottleneck_id, + ST_Centroid(area) AS point, + (lower(stretch)).hectometre AS from, + (upper(stretch)).hectometre AS to, + sr.current::text, + responsible_country + FROM waterway.bottlenecks bn LEFT JOIN ( + SELECT bottleneck_id, max(date_info) AS current + FROM waterway.sounding_results + GROUP BY bottleneck_id) sr + ON sr.bottleneck_id = bn.bottleneck_id + WHERE bn.validity @> current_timestamp + ORDER BY objnam + $$ + WHERE schema = 'waterway' AND name = 'bottleneck_overview';