Mercurial > gemma

comparison pkg/controllers/gauges.go @ 2762:f95ec0bb565c

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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 87aed4f9b1b8
children a06d11d1f0b3
comparison
equal deleted inserted replaced
2761:71e7237110ba 2762:f95ec0bb565c
17 "database/sql" 17 "database/sql"
18 "encoding/csv" 18 "encoding/csv"
19 "fmt" 19 "fmt"
20 "log" 20 "log"
21 "net/http" 21 "net/http"
22 "sort"
22 "strconv" 23 "strconv"
23 "time" 24 "time"
24 25
25 "github.com/gorilla/mux" 26 "github.com/gorilla/mux"
27 "gonum.org/v1/gonum/stat"
26 28
27 "gemma.intevation.de/gemma/pkg/common" 29 "gemma.intevation.de/gemma/pkg/common"
28 "gemma.intevation.de/gemma/pkg/middleware" 30 "gemma.intevation.de/gemma/pkg/middleware"
29 "gemma.intevation.de/gemma/pkg/models" 31 "gemma.intevation.de/gemma/pkg/models"
30 ) 32 )
54 56
55 selectWaterlevelsSQL = ` 57 selectWaterlevelsSQL = `
56 SELECT 58 SELECT
57 measure_date, 59 measure_date,
58 water_level, 60 water_level,
59 predicted 61 predicted,
60 FROM waterway.gauge_measurements 62 FROM waterway.gauge_measurements
61 WHERE 63 WHERE
62 ` 64 `
65 selectWaterlevelsMeasuredSQL = `
66 SELECT
67 measure_date,
68 water_level
69 FROM waterway.gauge_measurements
70 WHERE
71 NOT predicted
72 AND fk_gauge_id = (
73 $1::char(2),
74 $2::char(3),
75 $3::char(5),
76 $4::char(5),
77 $5::int
78 )
79 AND
80 measure_date BETWEEN
81 $6::timestamp with time zone AND $7::timestamp with time zone
82 ORDER BY measure_date
83 `
63 ) 84 )
85
86 func averageWaterlevels(rw http.ResponseWriter, req *http.Request) {
87 gauge := mux.Vars(req)["gauge"]
88
89 isrs, err := models.IsrsFromString(gauge)
90 if err != nil {
91 http.Error(
92 rw, fmt.Sprintf("error: Invalid ISRS code: %v", err),
93 http.StatusBadRequest)
94 return
95 }
96
97 var from, to time.Time
98
99 if t := req.FormValue("to"); t != "" {
100 var err error
101 if to, err = time.ParseInLocation(common.DateFormat, t, time.UTC); err != nil {
102 http.Error(
103 rw, fmt.Sprintf("error: bad from date: %v", err),
104 http.StatusBadRequest)
105 return
106 }
107 } else {
108 y, m, d := time.Now().Date()
109 to = time.Date(y, m, d, 0, 0, 0, 0, time.UTC)
110 }
111
112 if f := req.FormValue("from"); f != "" {
113 var err error
114 if from, err = time.ParseInLocation(common.DateFormat, f, time.UTC); err != nil {
115 http.Error(
116 rw, fmt.Sprintf("error: bad from date: %v", err),
117 http.StatusBadRequest)
118 return
119 }
120 } else {
121 from = to.AddDate(-1, 0, 0)
122 }
123
124 to = to.AddDate(0, 0, 1).Add(-time.Nanosecond)
125
126 if to.Before(from) {
127 from, to = to, from
128 }
129
130 conn := middleware.GetDBConn(req)
131
132 ctx := req.Context()
133
134 rows, err := conn.QueryContext(
135 ctx,
136 selectWaterlevelsMeasuredSQL,
137 isrs.CountryCode,
138 isrs.LoCode,
139 isrs.FairwaySection,
140 isrs.Orc,
141 isrs.Hectometre,
142 from, to,
143 )
144 if err != nil {
145 http.Error(
146 rw, fmt.Sprintf("error: %v", err),
147 http.StatusInternalServerError)
148 return
149 }
150 defer rows.Close()
151
152 rw.Header().Add("Content-Type", "text/csv")
153
154 out := csv.NewWriter(rw)
155
156 var last time.Time
157 var values []float64
158
159 record := []string{
160 "#date",
161 "#min",
162 "#max",
163 "#mean",
164 "#median",
165 "#q25",
166 "#q75",
167 }
168
169 if err := out.Write(record); err != nil {
170 log.Printf("error: %v\n", err)
171 // Too late for an HTTP error code.
172 return
173 }
174
175 format := func(v float64) string {
176 return strconv.FormatFloat(v, 'f', -1, 64)
177 }
178
179 write := func() error {
180 if len(values) > 0 {
181 sort.Float64s(values)
182 // date
183 record[0] = last.Format(common.DateFormat)
184 // min
185 record[1] = format(values[0])
186 // max
187 record[2] = format(values[len(values)-1])
188 // mean
189 record[3] = format(stat.Mean(values, nil))
190 // median
191 record[4] = format(values[len(values)/2])
192 // Q25
193 record[5] = format(
194 stat.Quantile(0.25, stat.Empirical, values, nil))
195 // Q75
196 record[6] = format(
197 stat.Quantile(0.75, stat.Empirical, values, nil))
198
199 err := out.Write(record)
200 values = values[:0]
201 return err
202 }
203 return nil
204 }
205
206 for rows.Next() {
207 var (
208 date time.Time
209 value float64
210 )
211 if err := rows.Scan(&date, &value); err != nil {
212 log.Printf("error: %v\n", err)
213 // Too late for an HTTP error code.
214 return
215 }
216 oy, om, od := last.Date()
217 ny, nm, nd := date.Date()
218 if oy != ny || om != nm || od != nd {
219 if err := write(); err != nil {
220 log.Printf("error: %v\n", err)
221 // Too late for an HTTP error code.
222 return
223 }
224 last = date
225 } else {
226 values = append(values, value)
227 }
228 }
229 write()
230
231 if err := rows.Err(); err != nil {
232 log.Printf("error: %v", err)
233 // Too late for an HTTP error code.
234 return
235 }
236
237 out.Flush()
238 if err := out.Error(); err != nil {
239 log.Printf("error: %v", err)
240 // Too late for an HTTP error code.
241 return
242 }
243
244 }
64 245
65 func nashSutcliffe( 246 func nashSutcliffe(
66 _ interface{}, 247 _ interface{},
67 req *http.Request, 248 req *http.Request,
68 conn *sql.Conn, 249 conn *sql.Conn,