Mercurial > gemma

annotate pkg/controllers/gauges.go @ 5490:5f47eeea988d logging

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Use own logging package.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Mon, 20 Sep 2021 17:45:39 +0200
parents 3b3cf2083730
children 6270951dda28
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
2694
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
1 // This is Free Software under GNU Affero General Public License v >= 3.0
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
2 // without warranty, see README.md and license for details.
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
3 //
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
4 // SPDX-License-Identifier: AGPL-3.0-or-later
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
5 // License-Filename: LICENSES/AGPL-3.0.txt
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
6 //
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
7 // Copyright (C) 2019 by via donau
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
8 // – Österreichische Wasserstraßen-Gesellschaft mbH
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
9 // Software engineering by Intevation GmbH
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
10 //
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
11 // Author(s):
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
12 // * Sascha L. Teichmann <sascha.teichmann@intevation.de>
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
13
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
14 package controllers
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
15
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
16 import (
3088
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
17 "context"
2741
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
18 "database/sql"
2694
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
19 "encoding/csv"
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
20 "fmt"
2878
4f66a3ba424b Fixed selection for Nash-Sutcliffe coeff calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2861
diff changeset
21 "math"
2694
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
22 "net/http"
2762
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
23 "sort"
2694
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
24 "strconv"
2809
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
25 "strings"
2694
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
26 "time"
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
27
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
28 "github.com/gorilla/mux"
2826
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
29 "github.com/jackc/pgx/pgtype"
2762
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
30 "gonum.org/v1/gonum/stat"
2694
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
31
2741
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
32 "gemma.intevation.de/gemma/pkg/common"
5490
5f47eeea988d Use own logging package.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4786
diff changeset
33 "gemma.intevation.de/gemma/pkg/log"
2694
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
34 "gemma.intevation.de/gemma/pkg/models"
4244
4394daeea96a Moved JSONHandler into middleware package.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4243
diff changeset
35
4394daeea96a Moved JSONHandler into middleware package.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4243
diff changeset
36 mw "gemma.intevation.de/gemma/pkg/middleware"
2694
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
37 )
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
38
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
39 const (
2741
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
40 selectPredictedObserveredSQL = `
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
41 SELECT
2878
4f66a3ba424b Fixed selection for Nash-Sutcliffe coeff calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2861
diff changeset
42 measure_date,
3088
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
43 date_issue,
2878
4f66a3ba424b Fixed selection for Nash-Sutcliffe coeff calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2861
diff changeset
44 predicted,
4f66a3ba424b Fixed selection for Nash-Sutcliffe coeff calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2861
diff changeset
45 water_level
3277
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3223
diff changeset
46 FROM (
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3223
diff changeset
47 SELECT
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3277
diff changeset
48 location,
3277
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3223
diff changeset
49 measure_date,
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3223
diff changeset
50 date_issue,
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3223
diff changeset
51 false AS predicted,
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3223
diff changeset
52 water_level
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3223
diff changeset
53 FROM waterway.gauge_measurements
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3223
diff changeset
54 UNION ALL
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3223
diff changeset
55 SELECT
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3277
diff changeset
56 location,
3277
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3223
diff changeset
57 measure_date,
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3223
diff changeset
58 date_issue,
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3223
diff changeset
59 true AS predicted,
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3223
diff changeset
60 water_level
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3223
diff changeset
61 FROM waterway.gauge_predictions
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3223
diff changeset
62 ) AS gmp
4786
3b3cf2083730 Avoid type cast that prevents index usage
Tom Gottfried <tom@intevation.de>
parents: 4244
diff changeset
63 WHERE location = ($1, $2, $3, $4, $5)::isrs
3b3cf2083730 Avoid type cast that prevents index usage
Tom Gottfried <tom@intevation.de>
parents: 4244
diff changeset
64 AND measure_date BETWEEN
3b3cf2083730 Avoid type cast that prevents index usage
Tom Gottfried <tom@intevation.de>
parents: 4244
diff changeset
65 $6::timestamptz - '72hours'::interval AND $6::timestamptz
2878
4f66a3ba424b Fixed selection for Nash-Sutcliffe coeff calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2861
diff changeset
66 ORDER BY measure_date, date_issue
2741
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
67 `
2694
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
68 selectWaterlevelsSQL = `
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
69 SELECT
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
70 measure_date,
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
71 water_level,
2783
2806821cfd63 Added min and max values to CSV output of /api/data/waterlevels/{gauge} .
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2766
diff changeset
72 value_min,
2806821cfd63 Added min and max values to CSV output of /api/data/waterlevels/{gauge} .
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2766
diff changeset
73 value_max,
2766
1f173d1a731d Fixed broken SQL statement.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2763
diff changeset
74 predicted
3277
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3223
diff changeset
75 FROM (
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3223
diff changeset
76 SELECT
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3277
diff changeset
77 location,
3277
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3223
diff changeset
78 measure_date,
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3223
diff changeset
79 date_issue,
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3223
diff changeset
80 water_level,
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3223
diff changeset
81 NULL AS value_min,
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3223
diff changeset
82 NULL AS value_max,
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3223
diff changeset
83 false AS predicted
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3223
diff changeset
84 FROM waterway.gauge_measurements
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3223
diff changeset
85 UNION ALL
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3223
diff changeset
86 SELECT
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3277
diff changeset
87 location,
3277
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3223
diff changeset
88 measure_date,
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3223
diff changeset
89 date_issue,
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3223
diff changeset
90 water_level,
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3223
diff changeset
91 lower(conf_interval) AS value_min,
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3223
diff changeset
92 upper(conf_interval) AS value_max,
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3223
diff changeset
93 true AS predicted
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3223
diff changeset
94 FROM waterway.gauge_predictions
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3223
diff changeset
95 ) AS gmp
2694
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
96 WHERE
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
97 `
2800
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
98
2826
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
99 selectAllWaterlevelsMeasuredRangeSQL = `
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
100 SELECT
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
101 min(measure_date),
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
102 max(measure_date)
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3277
diff changeset
103 FROM waterway.gauge_measurements
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3277
diff changeset
104 WHERE
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3277
diff changeset
105 location = (
2800
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
106 $1::char(2),
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
107 $2::char(3),
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
108 $3::char(5),
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
109 $4::char(5),
2826
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
110 $5::int
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
111 )::isrs
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
112 AND staging_done
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
113 `
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
114
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
115 selectAllWaterlevelsMeasuredSQL = `
2809
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
116 SELECT
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
117 extract(day from measure_date)::varchar || ':' ||
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
118 extract(month from measure_date)::varchar AS day_month,
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
119 percentile_disc(0.25) within group (order by water_level) AS q25,
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
120 percentile_disc(0.5) within group (order by water_level) AS median,
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
121 percentile_disc(0.75) within group (order by water_level) AS q75,
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
122 avg(water_level) AS mean,
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
123 min(water_level) AS min,
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
124 max(water_level) AS max
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3277
diff changeset
125 FROM waterway.gauge_measurements
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3277
diff changeset
126 WHERE
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3277
diff changeset
127 location = (
2826
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
128 $1::char(2),
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
129 $2::char(3),
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
130 $3::char(5),
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
131 $4::char(5),
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
132 $5::int
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
133 )::isrs
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
134 AND staging_done
2809
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
135 GROUP BY extract(day from measure_date)::varchar || ':' ||
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
136 extract(month from measure_date)::varchar;
2800
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
137 `
2803
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
138 selectYearWaterlevelsMeasuredSQL = `
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
139 SELECT
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
140 measure_date,
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
141 water_level
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
142 FROM waterway.gauge_measurements
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
143 WHERE
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3277
diff changeset
144 location = (
2803
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
145 $1::char(2),
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
146 $2::char(3),
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
147 $3::char(5),
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
148 $4::char(5),
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
149 $5::int
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3277
diff changeset
150 )::isrs
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3277
diff changeset
151 AND staging_done
2806
c6374c520228 Use between filter to speed up SQL for fetching yearly waterlevels.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2803
diff changeset
152 AND measure_date BETWEEN $6 AND $7
2803
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
153 ORDER BY measure_date
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
154 `
2694
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
155 )
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
156
2783
2806821cfd63 Added min and max values to CSV output of /api/data/waterlevels/{gauge} .
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2766
diff changeset
157 func float64format(v float64) string {
2806821cfd63 Added min and max values to CSV output of /api/data/waterlevels/{gauge} .
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2766
diff changeset
158 return strconv.FormatFloat(v, 'f', -1, 64)
2806821cfd63 Added min and max values to CSV output of /api/data/waterlevels/{gauge} .
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2766
diff changeset
159 }
2806821cfd63 Added min and max values to CSV output of /api/data/waterlevels/{gauge} .
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2766
diff changeset
160
2806821cfd63 Added min and max values to CSV output of /api/data/waterlevels/{gauge} .
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2766
diff changeset
161 func nullFloat64format(v sql.NullFloat64) string {
2806821cfd63 Added min and max values to CSV output of /api/data/waterlevels/{gauge} .
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2766
diff changeset
162 if v.Valid {
2806821cfd63 Added min and max values to CSV output of /api/data/waterlevels/{gauge} .
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2766
diff changeset
163 return float64format(v.Float64)
2806821cfd63 Added min and max values to CSV output of /api/data/waterlevels/{gauge} .
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2766
diff changeset
164 }
2806821cfd63 Added min and max values to CSV output of /api/data/waterlevels/{gauge} .
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2766
diff changeset
165 return ""
2806821cfd63 Added min and max values to CSV output of /api/data/waterlevels/{gauge} .
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2766
diff changeset
166 }
2806821cfd63 Added min and max values to CSV output of /api/data/waterlevels/{gauge} .
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2766
diff changeset
167
2806821cfd63 Added min and max values to CSV output of /api/data/waterlevels/{gauge} .
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2766
diff changeset
168 func boolFormat(b bool) string {
2806821cfd63 Added min and max values to CSV output of /api/data/waterlevels/{gauge} .
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2766
diff changeset
169 if b {
2806821cfd63 Added min and max values to CSV output of /api/data/waterlevels/{gauge} .
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2766
diff changeset
170 return "t"
2806821cfd63 Added min and max values to CSV output of /api/data/waterlevels/{gauge} .
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2766
diff changeset
171 }
2806821cfd63 Added min and max values to CSV output of /api/data/waterlevels/{gauge} .
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2766
diff changeset
172 return "f"
2806821cfd63 Added min and max values to CSV output of /api/data/waterlevels/{gauge} .
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2766
diff changeset
173 }
2806821cfd63 Added min and max values to CSV output of /api/data/waterlevels/{gauge} .
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2766
diff changeset
174
2803
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
175 func yearWaterlevels(rw http.ResponseWriter, req *http.Request) {
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
176
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
177 gauge := mux.Vars(req)["gauge"]
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
178
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
179 isrs, err := models.IsrsFromString(gauge)
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
180 if err != nil {
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
181 http.Error(
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
182 rw, fmt.Sprintf("error: Invalid ISRS code: %v", err),
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
183 http.StatusBadRequest)
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
184 return
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
185 }
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
186
2806
c6374c520228 Use between filter to speed up SQL for fetching yearly waterlevels.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2803
diff changeset
187 year, _ := strconv.Atoi(mux.Vars(req)["year"])
2803
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
188
4244
4394daeea96a Moved JSONHandler into middleware package.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4243
diff changeset
189 conn := mw.GetDBConn(req)
2803
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
190
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
191 ctx := req.Context()
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
192
2806
c6374c520228 Use between filter to speed up SQL for fetching yearly waterlevels.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2803
diff changeset
193 begin := time.Date(year, time.January, 1, 0, 0, 0, 0, time.UTC)
c6374c520228 Use between filter to speed up SQL for fetching yearly waterlevels.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2803
diff changeset
194 end := time.Date(year+1, time.January, 1, 0, 0, 0, 0, time.UTC).Add(-time.Microsecond)
c6374c520228 Use between filter to speed up SQL for fetching yearly waterlevels.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2803
diff changeset
195
5490
5f47eeea988d Use own logging package.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4786
diff changeset
196 log.Infof("begin %s\n", begin)
5f47eeea988d Use own logging package.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4786
diff changeset
197 log.Infof("end %s\n", end)
2806
c6374c520228 Use between filter to speed up SQL for fetching yearly waterlevels.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2803
diff changeset
198
2803
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
199 rows, err := conn.QueryContext(
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
200 ctx,
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
201 selectYearWaterlevelsMeasuredSQL,
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
202 isrs.CountryCode,
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
203 isrs.LoCode,
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
204 isrs.FairwaySection,
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
205 isrs.Orc,
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
206 isrs.Hectometre,
2806
c6374c520228 Use between filter to speed up SQL for fetching yearly waterlevels.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2803
diff changeset
207 begin,
c6374c520228 Use between filter to speed up SQL for fetching yearly waterlevels.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2803
diff changeset
208 end,
2803
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
209 )
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
210 if err != nil {
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
211 http.Error(
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
212 rw, fmt.Sprintf("error: %v", err),
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
213 http.StatusInternalServerError)
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
214 return
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
215 }
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
216 defer rows.Close()
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
217
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
218 var values []float64
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
219
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
220 lastDay, lastMonth := -1, -1
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
221
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
222 write := func() error {
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
223 var err error
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
224 if len(values) > 0 {
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
225 mean := stat.Mean(values, nil)
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
226 _, err = fmt.Fprintf(
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
227 rw, "%02d-%02d,%s\n", lastDay, lastMonth,
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
228 float64format(mean))
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
229 values = values[:0]
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
230 }
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
231 return err
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
232 }
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
233
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
234 for rows.Next() {
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
235 var when time.Time
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
236 var value float64
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
237 if err := rows.Scan(&when, &value); err != nil {
5490
5f47eeea988d Use own logging package.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4786
diff changeset
238 log.Errorf("%v", err)
2803
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
239 // Too late for an HTTP error code.
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
240 return
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
241 }
2807
1fa57ad05e1e Calculate the yearly waterlevel over an UTC time interval.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2806
diff changeset
242 when = when.UTC()
2803
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
243 day, month := when.Day(), int(when.Month())
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
244 if day != lastDay || month != lastMonth {
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
245 if err := write(); err != nil {
5490
5f47eeea988d Use own logging package.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4786
diff changeset
246 log.Errorf("%v", err)
2803
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
247 // Too late for an HTTP error code.
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
248 return
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
249 }
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
250 lastDay, lastMonth = day, month
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
251 }
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
252 values = append(values, value)
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
253 }
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
254
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
255 if err := rows.Err(); err != nil {
5490
5f47eeea988d Use own logging package.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4786
diff changeset
256 log.Errorf("%v", err)
2803
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
257 // Too late for an HTTP error code.
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
258 return
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
259 }
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
260
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
261 if err := write(); err != nil {
5490
5f47eeea988d Use own logging package.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4786
diff changeset
262 log.Errorf("%v", err)
2803
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
263 // Too late for an HTTP error code.
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
264 }
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
265 }
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
266
2800
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
267 func longtermWaterlevels(rw http.ResponseWriter, req *http.Request) {
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
268
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
269 gauge := mux.Vars(req)["gauge"]
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
270
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
271 isrs, err := models.IsrsFromString(gauge)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
272 if err != nil {
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
273 http.Error(
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
274 rw, fmt.Sprintf("error: Invalid ISRS code: %v", err),
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
275 http.StatusBadRequest)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
276 return
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
277 }
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
278
4244
4394daeea96a Moved JSONHandler into middleware package.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4243
diff changeset
279 conn := mw.GetDBConn(req)
2800
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
280
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
281 ctx := req.Context()
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
282
2826
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
283 var begin, end pgtype.Timestamp
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
284
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
285 err = conn.QueryRowContext(
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
286 ctx,
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
287 selectAllWaterlevelsMeasuredRangeSQL,
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
288 isrs.CountryCode,
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
289 isrs.LoCode,
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
290 isrs.FairwaySection,
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
291 isrs.Orc,
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
292 isrs.Hectometre,
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
293 ).Scan(&begin, &end)
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
294
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
295 switch {
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
296 case err == sql.ErrNoRows || begin.Status != pgtype.Present || end.Status != pgtype.Present:
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
297 http.NotFound(rw, req)
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
298 return
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
299 case err != nil:
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
300 http.Error(
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
301 rw, fmt.Sprintf("error: %v", err),
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
302 http.StatusInternalServerError)
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
303 return
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
304 }
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
305
2800
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
306 rows, err := conn.QueryContext(
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
307 ctx,
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
308 selectAllWaterlevelsMeasuredSQL,
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
309 isrs.CountryCode,
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
310 isrs.LoCode,
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
311 isrs.FairwaySection,
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
312 isrs.Orc,
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
313 isrs.Hectometre,
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
314 )
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
315 if err != nil {
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
316 http.Error(
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
317 rw, fmt.Sprintf("error: %v", err),
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
318 http.StatusInternalServerError)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
319 return
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
320 }
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
321 defer rows.Close()
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
322
2809
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
323 type result struct {
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
324 day int
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
325 month int
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
326 q25 float64
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
327 median float64
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
328 q75 float64
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
329 mean float64
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
330 min float64
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
331 max float64
2800
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
332 }
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
333
2809
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
334 results := make([]result, 0, 366)
2800
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
335
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
336 start := time.Now()
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
337
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
338 for rows.Next() {
2809
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
339 var r result
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
340 var dayMonth string
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
341 if err := rows.Scan(
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
342 &dayMonth,
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
343 &r.q25,
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
344 &r.median,
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
345 &r.q75,
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
346 &r.mean,
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
347 &r.min,
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
348 &r.max,
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
349 ); err != nil {
2800
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
350 http.Error(
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
351 rw, fmt.Sprintf("error: %v", err),
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
352 http.StatusInternalServerError)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
353 }
2809
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
354 parts := strings.SplitN(dayMonth, ":", 2)
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
355 r.day, _ = strconv.Atoi(parts[0])
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
356 r.month, _ = strconv.Atoi(parts[1])
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
357 results = append(results, r)
2800
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
358 }
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
359
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
360 if err := rows.Err(); err != nil {
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
361 http.Error(
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
362 rw, fmt.Sprintf("error: %v", err),
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
363 http.StatusInternalServerError)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
364 return
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
365 }
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
366
5490
5f47eeea988d Use own logging package.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4786
diff changeset
367 log.Infof("loading entries took %s\n", time.Since(start))
2800
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
368
5490
5f47eeea988d Use own logging package.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4786
diff changeset
369 log.Infof("days found: %d\n", len(results))
2800
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
370
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
371 sort.Slice(results, func(i, j int) bool {
2832
b5555005f51e cosmetics: removed superfluous casts.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2826
diff changeset
372 if d := results[i].month - results[j].month; d != 0 {
2800
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
373 return d < 0
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
374 }
2809
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
375 return results[i].day < results[j].day
2800
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
376 })
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
377
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
378 rw.Header().Add("Content-Type", "text/csv")
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
379
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
380 out := csv.NewWriter(rw)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
381
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
382 record := []string{
2826
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
383 fmt.Sprintf("#interval: %d-%d",
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
384 begin.Time.UTC().Year(),
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
385 end.Time.UTC().Year()),
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
386 "",
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
387 "",
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
388 "",
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
389 "",
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
390 "",
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
391 "",
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
392 }
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
393
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
394 if err := out.Write(record); err != nil {
5490
5f47eeea988d Use own logging package.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4786
diff changeset
395 log.Errorf("%v\n", err)
2826
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
396 // Too late for an HTTP error code.
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
397 return
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
398 }
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
399
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
400 record = []string{
2800
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
401 "#date",
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
402 "#min",
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
403 "#max",
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
404 "#mean",
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
405 "#median",
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
406 "#q25",
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
407 "#q75",
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
408 }
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
409
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
410 if err := out.Write(record); err != nil {
5490
5f47eeea988d Use own logging package.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4786
diff changeset
411 log.Errorf("%v\n", err)
2800
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
412 // Too late for an HTTP error code.
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
413 return
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
414 }
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
415
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
416 for i := range results {
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
417 r := &results[i]
2809
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
418 record[0] = fmt.Sprintf("%02d-%02d", r.day, r.month)
2800
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
419 record[1] = float64format(r.min)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
420 record[2] = float64format(r.max)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
421 record[3] = float64format(r.mean)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
422 record[4] = float64format(r.median)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
423 record[5] = float64format(r.q25)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
424 record[6] = float64format(r.q75)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
425 if err := out.Write(record); err != nil {
5490
5f47eeea988d Use own logging package.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4786
diff changeset
426 log.Errorf("%v\n", err)
2800
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
427 // Too late for an HTTP error code.
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
428 return
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
429 }
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
430 }
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
431
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
432 out.Flush()
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
433 if err := out.Error(); err != nil {
5490
5f47eeea988d Use own logging package.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4786
diff changeset
434 log.Errorf("%v", err)
2800
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
435 // Too late for an HTTP error code.
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
436 return
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
437 }
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
438 }
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
439
3088
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
440 func parseISRS(code string) (*models.Isrs, error) {
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
441 isrs, err := models.IsrsFromString(code)
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
442 if err != nil {
4244
4394daeea96a Moved JSONHandler into middleware package.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4243
diff changeset
443 return nil, mw.JSONError{
3088
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
444 Code: http.StatusBadRequest,
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
445 Message: fmt.Sprintf("error: Invalid ISRS code: %v", err),
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
446 }
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
447 }
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
448 return isrs, nil
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
449 }
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
450
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
451 type observedPredictedValues struct {
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
452 when time.Time
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
453 observed float64
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
454 predicted common.TimedValues
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
455 }
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
456
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
457 func loadNashSutcliffeData(
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
458 ctx context.Context,
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
459 conn *sql.Conn,
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
460 gauge *models.Isrs,
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
461 when time.Time,
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
462 ) ([]observedPredictedValues, error) {
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
463
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
464 var rows *sql.Rows
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
465 var err error
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
466 if rows, err = conn.QueryContext(
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
467 ctx,
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
468 selectPredictedObserveredSQL,
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
469 gauge.CountryCode,
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
470 gauge.LoCode,
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
471 gauge.FairwaySection,
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
472 gauge.Orc,
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
473 gauge.Hectometre,
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
474 when,
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
475 ); err != nil {
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
476 return nil, err
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
477 }
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
478 defer rows.Close()
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
479
3101
41ed69dbf9bb Nash Sutcliffe: Optimization: When append predicted values only accept predictions where the issue date is 24/48/72h in the past of the measure date.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3099
diff changeset
480 acceptedDeltas := []time.Duration{
41ed69dbf9bb Nash Sutcliffe: Optimization: When append predicted values only accept predictions where the issue date is 24/48/72h in the past of the measure date.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3099
diff changeset
481 -time.Hour * 24,
41ed69dbf9bb Nash Sutcliffe: Optimization: When append predicted values only accept predictions where the issue date is 24/48/72h in the past of the measure date.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3099
diff changeset
482 -time.Hour * 48,
41ed69dbf9bb Nash Sutcliffe: Optimization: When append predicted values only accept predictions where the issue date is 24/48/72h in the past of the measure date.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3099
diff changeset
483 -time.Hour * 72,
41ed69dbf9bb Nash Sutcliffe: Optimization: When append predicted values only accept predictions where the issue date is 24/48/72h in the past of the measure date.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3099
diff changeset
484 }
41ed69dbf9bb Nash Sutcliffe: Optimization: When append predicted values only accept predictions where the issue date is 24/48/72h in the past of the measure date.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3099
diff changeset
485
41ed69dbf9bb Nash Sutcliffe: Optimization: When append predicted values only accept predictions where the issue date is 24/48/72h in the past of the measure date.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3099
diff changeset
486 isAccepted := func(observed, predicted time.Time) bool {
41ed69dbf9bb Nash Sutcliffe: Optimization: When append predicted values only accept predictions where the issue date is 24/48/72h in the past of the measure date.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3099
diff changeset
487 for _, delta := range acceptedDeltas {
41ed69dbf9bb Nash Sutcliffe: Optimization: When append predicted values only accept predictions where the issue date is 24/48/72h in the past of the measure date.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3099
diff changeset
488 t := observed.Add(delta)
41ed69dbf9bb Nash Sutcliffe: Optimization: When append predicted values only accept predictions where the issue date is 24/48/72h in the past of the measure date.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3099
diff changeset
489 d := predicted.Sub(t)
41ed69dbf9bb Nash Sutcliffe: Optimization: When append predicted values only accept predictions where the issue date is 24/48/72h in the past of the measure date.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3099
diff changeset
490 if -10*time.Millisecond < d && d < 10*time.Millisecond {
41ed69dbf9bb Nash Sutcliffe: Optimization: When append predicted values only accept predictions where the issue date is 24/48/72h in the past of the measure date.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3099
diff changeset
491 return true
41ed69dbf9bb Nash Sutcliffe: Optimization: When append predicted values only accept predictions where the issue date is 24/48/72h in the past of the measure date.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3099
diff changeset
492 }
41ed69dbf9bb Nash Sutcliffe: Optimization: When append predicted values only accept predictions where the issue date is 24/48/72h in the past of the measure date.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3099
diff changeset
493 }
41ed69dbf9bb Nash Sutcliffe: Optimization: When append predicted values only accept predictions where the issue date is 24/48/72h in the past of the measure date.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3099
diff changeset
494 return false
41ed69dbf9bb Nash Sutcliffe: Optimization: When append predicted values only accept predictions where the issue date is 24/48/72h in the past of the measure date.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3099
diff changeset
495 }
41ed69dbf9bb Nash Sutcliffe: Optimization: When append predicted values only accept predictions where the issue date is 24/48/72h in the past of the measure date.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3099
diff changeset
496
3088
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
497 var (
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
498 hasCurrent bool
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
499 current observedPredictedValues
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
500 values []observedPredictedValues
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
501 )
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
502
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
503 for rows.Next() {
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
504 var (
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
505 measureDate time.Time
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
506 issueDate time.Time
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
507 predicted bool
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
508 value float64
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
509 )
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
510 if err := rows.Scan(
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
511 &measureDate,
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
512 &issueDate,
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
513 &predicted,
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
514 &value,
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
515 ); err != nil {
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
516 return nil, err
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
517 }
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
518 measureDate = measureDate.UTC()
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
519 issueDate = issueDate.UTC()
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
520
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
521 if hasCurrent {
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
522 if !current.when.Equal(measureDate) {
3097
e6ba32b060df Nash Sutcliffe: Treat last prediction date as valid afterwards. Small optimizsations.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3092
diff changeset
523 if !math.IsNaN(current.observed) && len(current.predicted) > 0 {
3088
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
524 values = append(values, current)
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
525 }
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
526 current = observedPredictedValues{
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
527 observed: math.NaN(),
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
528 when: measureDate,
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
529 }
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
530 }
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
531 } else {
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
532 hasCurrent = true
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
533 current = observedPredictedValues{
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
534 observed: math.NaN(),
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
535 when: measureDate,
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
536 }
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
537 }
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
538
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
539 if predicted {
3101
41ed69dbf9bb Nash Sutcliffe: Optimization: When append predicted values only accept predictions where the issue date is 24/48/72h in the past of the measure date.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3099
diff changeset
540 if isAccepted(measureDate, issueDate) {
41ed69dbf9bb Nash Sutcliffe: Optimization: When append predicted values only accept predictions where the issue date is 24/48/72h in the past of the measure date.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3099
diff changeset
541 current.predicted = append(
41ed69dbf9bb Nash Sutcliffe: Optimization: When append predicted values only accept predictions where the issue date is 24/48/72h in the past of the measure date.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3099
diff changeset
542 current.predicted,
41ed69dbf9bb Nash Sutcliffe: Optimization: When append predicted values only accept predictions where the issue date is 24/48/72h in the past of the measure date.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3099
diff changeset
543 common.TimedValue{When: issueDate, Value: value},
41ed69dbf9bb Nash Sutcliffe: Optimization: When append predicted values only accept predictions where the issue date is 24/48/72h in the past of the measure date.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3099
diff changeset
544 )
41ed69dbf9bb Nash Sutcliffe: Optimization: When append predicted values only accept predictions where the issue date is 24/48/72h in the past of the measure date.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3099
diff changeset
545 }
3088
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
546 } else {
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
547 current.observed = value
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
548 }
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
549 }
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
550
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
551 if err := rows.Err(); err != nil {
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
552 return nil, err
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
553 }
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
554
3097
e6ba32b060df Nash Sutcliffe: Treat last prediction date as valid afterwards. Small optimizsations.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3092
diff changeset
555 if hasCurrent && !math.IsNaN(current.observed) && len(current.predicted) > 0 {
3088
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
556 values = append(values, current)
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
557 }
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
558
3097
e6ba32b060df Nash Sutcliffe: Treat last prediction date as valid afterwards. Small optimizsations.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3092
diff changeset
559 // for i := range values {
5490
5f47eeea988d Use own logging package.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4786
diff changeset
560 // log.Debugf("%v %f %d\n", values[i].when, values[i].observed, len(values[i].predicted))
3097
e6ba32b060df Nash Sutcliffe: Treat last prediction date as valid afterwards. Small optimizsations.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3092
diff changeset
561 // if len(values[i].predicted) > 0 {
e6ba32b060df Nash Sutcliffe: Treat last prediction date as valid afterwards. Small optimizsations.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3092
diff changeset
562 // for j := range values[i].predicted {
5490
5f47eeea988d Use own logging package.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4786
diff changeset
563 // log.Debugf("\t%v %f\n", values[i].predicted[j].When, values[i].predicted[j].Value)
3097
e6ba32b060df Nash Sutcliffe: Treat last prediction date as valid afterwards. Small optimizsations.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3092
diff changeset
564 // }
e6ba32b060df Nash Sutcliffe: Treat last prediction date as valid afterwards. Small optimizsations.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3092
diff changeset
565 // }
e6ba32b060df Nash Sutcliffe: Treat last prediction date as valid afterwards. Small optimizsations.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3092
diff changeset
566 // }
e6ba32b060df Nash Sutcliffe: Treat last prediction date as valid afterwards. Small optimizsations.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3092
diff changeset
567
3088
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
568 return values, nil
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
569 }
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
570
4244
4394daeea96a Moved JSONHandler into middleware package.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4243
diff changeset
571 func nashSutcliffe(req *http.Request) (jr mw.JSONResult, err error) {
4243
d776110b4db0 Made the de-serialized input of the JSON handler accessible via the context of the request.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4242
diff changeset
572
2741
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
573 gauge := mux.Vars(req)["gauge"]
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
574
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
575 var isrs *models.Isrs
3088
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
576 if isrs, err = parseISRS(gauge); err != nil {
2741
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
577 return
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
578 }
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
579
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
580 var when time.Time
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
581 if w := req.FormValue("when"); w != "" {
4078
80bdcd137a1d Parse timezones from time inputs and send timezones in results.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3725
diff changeset
582 if when, err = common.ParseTime(w); err != nil {
4244
4394daeea96a Moved JSONHandler into middleware package.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4243
diff changeset
583 err = mw.JSONError{
2741
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
584 Code: http.StatusBadRequest,
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
585 Message: fmt.Sprintf("error: wrong time format: %v", err),
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
586 }
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
587 return
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
588 }
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
589 } else {
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
590 when = time.Now()
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
591 }
3088
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
592 when = when.UTC()
2741
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
593
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
594 ctx := req.Context()
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
595
3088
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
596 var values []observedPredictedValues
2878
4f66a3ba424b Fixed selection for Nash-Sutcliffe coeff calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2861
diff changeset
597
4244
4394daeea96a Moved JSONHandler into middleware package.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4243
diff changeset
598 if values, err = loadNashSutcliffeData(ctx, mw.JSONConn(req), isrs, when); err != nil {
2741
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
599 return
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
600 }
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
601
5490
5f47eeea988d Use own logging package.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4786
diff changeset
602 log.Infof("found %d value(s) for Nash Sutcliffe.\n", len(values))
2878
4f66a3ba424b Fixed selection for Nash-Sutcliffe coeff calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2861
diff changeset
603
2741
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
604 type coeff struct {
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
605 Value float64 `json:"value"`
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
606 Samples int `json:"samples"`
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
607 Hours int `json:"hours"`
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
608 }
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
609
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
610 type coeffs struct {
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
611 When models.ImportTime `json:"when"`
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
612 Coeffs []coeff `json:"coeffs"`
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
613 }
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
614
3091
cec9d4af5f03 Simplified and fixed Nash Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3090
diff changeset
615 var predicted, observed []float64
3088
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
616
2741
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
617 cs := make([]coeff, 3)
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
618 for i := range cs {
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
619 cs[i].Hours = (i + 1) * 24
3092
7dc9660df743 Fixed c&p sign error in Nash Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3091
diff changeset
620 delta := -time.Duration(cs[i].Hours) * time.Hour
3088
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
621
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
622 for j := range values {
3091
cec9d4af5f03 Simplified and fixed Nash Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3090
diff changeset
623 when := values[j].when.Add(delta)
3099
f516ac26f4db "Sharp" match for predicted and measured values in nash sutcliffe.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3097
diff changeset
624 if p, ok := values[j].predicted.Find(when); ok {
3091
cec9d4af5f03 Simplified and fixed Nash Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3090
diff changeset
625 predicted = append(predicted, p)
cec9d4af5f03 Simplified and fixed Nash Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3090
diff changeset
626 observed = append(observed, values[j].observed)
3088
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
627 }
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
628 }
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
629
4087
1562a5fb36dc Handle Nash-Sutcliff more tolerant for NaN/Inf values.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3725
diff changeset
630 cs[i].Value = sanitizeFloat64(common.NashSutcliffe(predicted, observed))
3091
cec9d4af5f03 Simplified and fixed Nash Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3090
diff changeset
631 cs[i].Samples = len(predicted)
3088
09d1ffce3d00 Reworked Nash-Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2913
diff changeset
632
3091
cec9d4af5f03 Simplified and fixed Nash Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3090
diff changeset
633 predicted = predicted[:0]
cec9d4af5f03 Simplified and fixed Nash Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3090
diff changeset
634 observed = observed[:0]
2741
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
635 }
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
636
4244
4394daeea96a Moved JSONHandler into middleware package.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4243
diff changeset
637 jr = mw.JSONResult{
2741
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
638 Result: &coeffs{
3089
813309225e35 Made 'go vet' happy again.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3088
diff changeset
639 When: models.ImportTime{Time: when},
2741
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
640 Coeffs: cs,
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
641 },
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
642 }
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
643 return
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
644 }
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
645
4087
1562a5fb36dc Handle Nash-Sutcliff more tolerant for NaN/Inf values.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3725
diff changeset
646 func sanitizeFloat64(x float64) float64 {
1562a5fb36dc Handle Nash-Sutcliff more tolerant for NaN/Inf values.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3725
diff changeset
647 switch {
1562a5fb36dc Handle Nash-Sutcliff more tolerant for NaN/Inf values.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3725
diff changeset
648 case math.IsNaN(x):
1562a5fb36dc Handle Nash-Sutcliff more tolerant for NaN/Inf values.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3725
diff changeset
649 return 0
1562a5fb36dc Handle Nash-Sutcliff more tolerant for NaN/Inf values.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3725
diff changeset
650 case math.IsInf(x, +1):
1562a5fb36dc Handle Nash-Sutcliff more tolerant for NaN/Inf values.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3725
diff changeset
651 return math.MaxFloat64
1562a5fb36dc Handle Nash-Sutcliff more tolerant for NaN/Inf values.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3725
diff changeset
652 case math.IsInf(x, -1):
1562a5fb36dc Handle Nash-Sutcliff more tolerant for NaN/Inf values.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3725
diff changeset
653 return -math.MaxFloat64
1562a5fb36dc Handle Nash-Sutcliff more tolerant for NaN/Inf values.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3725
diff changeset
654 }
1562a5fb36dc Handle Nash-Sutcliff more tolerant for NaN/Inf values.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3725
diff changeset
655 return x
1562a5fb36dc Handle Nash-Sutcliff more tolerant for NaN/Inf values.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3725
diff changeset
656 }
1562a5fb36dc Handle Nash-Sutcliff more tolerant for NaN/Inf values.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3725
diff changeset
657
2694
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
658 func waterlevels(rw http.ResponseWriter, req *http.Request) {
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
659 gauge := mux.Vars(req)["gauge"]
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
660
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
661 isrs, err := models.IsrsFromString(gauge)
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
662 if err != nil {
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
663 http.Error(
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
664 rw, fmt.Sprintf("error: Invalid ISRS code: %v", err),
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
665 http.StatusBadRequest)
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
666 return
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
667 }
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
668
3725
18777f6df3ef Partially fix waterlevel selection for gauge_measurements(_predictions)
Raimund Renkert <raimund.renkert@intevation.de>
parents: 3302
diff changeset
669 // TODO: FIXME The filter is not correct for predictions!?
3194
eeff2cc4ff9d controllers: re-factored the SQL filter to a tree like structure to be of more general use.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3190
diff changeset
670 filters := filterAnd{
eeff2cc4ff9d controllers: re-factored the SQL filter to a tree like structure to be of more general use.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3190
diff changeset
671 buildFilterTerm(
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3277
diff changeset
672 "location = ($%d::char(2), $%d::char(3), $%d::char(5), $%d::char(5), $%d::int)",
3194
eeff2cc4ff9d controllers: re-factored the SQL filter to a tree like structure to be of more general use.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3190
diff changeset
673 isrs.CountryCode,
eeff2cc4ff9d controllers: re-factored the SQL filter to a tree like structure to be of more general use.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3190
diff changeset
674 isrs.LoCode,
eeff2cc4ff9d controllers: re-factored the SQL filter to a tree like structure to be of more general use.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3190
diff changeset
675 isrs.FairwaySection,
eeff2cc4ff9d controllers: re-factored the SQL filter to a tree like structure to be of more general use.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3190
diff changeset
676 isrs.Orc,
eeff2cc4ff9d controllers: re-factored the SQL filter to a tree like structure to be of more general use.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3190
diff changeset
677 isrs.Hectometre,
eeff2cc4ff9d controllers: re-factored the SQL filter to a tree like structure to be of more general use.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3190
diff changeset
678 ),
eeff2cc4ff9d controllers: re-factored the SQL filter to a tree like structure to be of more general use.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3190
diff changeset
679 &filterOr{
eeff2cc4ff9d controllers: re-factored the SQL filter to a tree like structure to be of more general use.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3190
diff changeset
680 &filterNot{&filterTerm{format: "predicted"}},
eeff2cc4ff9d controllers: re-factored the SQL filter to a tree like structure to be of more general use.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3190
diff changeset
681 buildFilterTerm(
eeff2cc4ff9d controllers: re-factored the SQL filter to a tree like structure to be of more general use.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3190
diff changeset
682 `date_issue = (
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3277
diff changeset
683 SELECT max(date_issue)
3725
18777f6df3ef Partially fix waterlevel selection for gauge_measurements(_predictions)
Raimund Renkert <raimund.renkert@intevation.de>
parents: 3302
diff changeset
684 FROM waterway.gauge_predictions gm
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3277
diff changeset
685 WHERE location = ($%d::char(2), $%d::char(3), $%d::char(5), $%d::char(5), $%d::int))`,
3194
eeff2cc4ff9d controllers: re-factored the SQL filter to a tree like structure to be of more general use.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3190
diff changeset
686 isrs.CountryCode,
eeff2cc4ff9d controllers: re-factored the SQL filter to a tree like structure to be of more general use.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3190
diff changeset
687 isrs.LoCode,
eeff2cc4ff9d controllers: re-factored the SQL filter to a tree like structure to be of more general use.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3190
diff changeset
688 isrs.FairwaySection,
eeff2cc4ff9d controllers: re-factored the SQL filter to a tree like structure to be of more general use.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3190
diff changeset
689 isrs.Orc,
eeff2cc4ff9d controllers: re-factored the SQL filter to a tree like structure to be of more general use.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3190
diff changeset
690 isrs.Hectometre,
eeff2cc4ff9d controllers: re-factored the SQL filter to a tree like structure to be of more general use.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3190
diff changeset
691 ),
eeff2cc4ff9d controllers: re-factored the SQL filter to a tree like structure to be of more general use.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3190
diff changeset
692 },
eeff2cc4ff9d controllers: re-factored the SQL filter to a tree like structure to be of more general use.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3190
diff changeset
693 }
2858
401bca8eaafb Filter predicted values for waterlevels.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2832
diff changeset
694
2694
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
695 if from := req.FormValue("from"); from != "" {
4078
80bdcd137a1d Parse timezones from time inputs and send timezones in results.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3725
diff changeset
696 fromTime, err := common.ParseTime(from)
2694
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
697 if err != nil {
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
698 http.Error(
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
699 rw, fmt.Sprintf("error: Invalid from time: %v", err),
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
700 http.StatusBadRequest)
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
701 return
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
702 }
3194
eeff2cc4ff9d controllers: re-factored the SQL filter to a tree like structure to be of more general use.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3190
diff changeset
703 filters = append(filters, buildFilterTerm("measure_date >= $%d", fromTime))
2694
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
704 }
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
705
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
706 if to := req.FormValue("to"); to != "" {
4078
80bdcd137a1d Parse timezones from time inputs and send timezones in results.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3725
diff changeset
707 toTime, err := common.ParseTime(to)
2694
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
708 if err != nil {
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
709 http.Error(
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
710 rw, fmt.Sprintf("error: Invalid from time: %v", err),
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
711 http.StatusBadRequest)
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
712 return
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
713 }
3194
eeff2cc4ff9d controllers: re-factored the SQL filter to a tree like structure to be of more general use.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3190
diff changeset
714 filters = append(filters, buildFilterTerm("measure_date <= $%d", toTime))
2694
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
715 }
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
716
3194
eeff2cc4ff9d controllers: re-factored the SQL filter to a tree like structure to be of more general use.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3190
diff changeset
717 var stmt strings.Builder
eeff2cc4ff9d controllers: re-factored the SQL filter to a tree like structure to be of more general use.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3190
diff changeset
718 var args []interface{}
eeff2cc4ff9d controllers: re-factored the SQL filter to a tree like structure to be of more general use.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3190
diff changeset
719
eeff2cc4ff9d controllers: re-factored the SQL filter to a tree like structure to be of more general use.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3190
diff changeset
720 stmt.WriteString(selectWaterlevelsSQL)
eeff2cc4ff9d controllers: re-factored the SQL filter to a tree like structure to be of more general use.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3190
diff changeset
721 filters.serialize(&stmt, &args)
eeff2cc4ff9d controllers: re-factored the SQL filter to a tree like structure to be of more general use.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3190
diff changeset
722
4244
4394daeea96a Moved JSONHandler into middleware package.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4243
diff changeset
723 conn := mw.GetDBConn(req)
2694
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
724
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
725 ctx := req.Context()
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
726
3194
eeff2cc4ff9d controllers: re-factored the SQL filter to a tree like structure to be of more general use.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3190
diff changeset
727 rows, err := conn.QueryContext(ctx, stmt.String(), args...)
2694
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
728 if err != nil {
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
729 http.Error(
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
730 rw, fmt.Sprintf("error: %v", err),
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
731 http.StatusInternalServerError)
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
732 return
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
733 }
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
734 defer rows.Close()
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
735
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
736 rw.Header().Add("Content-Type", "text/csv")
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
737
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
738 out := csv.NewWriter(rw)
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
739
2783
2806821cfd63 Added min and max values to CSV output of /api/data/waterlevels/{gauge} .
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2766
diff changeset
740 record := []string{
2806821cfd63 Added min and max values to CSV output of /api/data/waterlevels/{gauge} .
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2766
diff changeset
741 "#date",
2806821cfd63 Added min and max values to CSV output of /api/data/waterlevels/{gauge} .
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2766
diff changeset
742 "#water_level",
2806821cfd63 Added min and max values to CSV output of /api/data/waterlevels/{gauge} .
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2766
diff changeset
743 "#value_min",
2806821cfd63 Added min and max values to CSV output of /api/data/waterlevels/{gauge} .
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2766
diff changeset
744 "#value_max",
2806821cfd63 Added min and max values to CSV output of /api/data/waterlevels/{gauge} .
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2766
diff changeset
745 "#predicted",
2806821cfd63 Added min and max values to CSV output of /api/data/waterlevels/{gauge} .
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2766
diff changeset
746 }
2806821cfd63 Added min and max values to CSV output of /api/data/waterlevels/{gauge} .
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2766
diff changeset
747
2806821cfd63 Added min and max values to CSV output of /api/data/waterlevels/{gauge} .
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2766
diff changeset
748 if err := out.Write(record); err != nil {
5490
5f47eeea988d Use own logging package.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4786
diff changeset
749 log.Errorf("%v", err)
2783
2806821cfd63 Added min and max values to CSV output of /api/data/waterlevels/{gauge} .
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2766
diff changeset
750 // Too late for an HTTP error code.
2806821cfd63 Added min and max values to CSV output of /api/data/waterlevels/{gauge} .
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2766
diff changeset
751 return
2806821cfd63 Added min and max values to CSV output of /api/data/waterlevels/{gauge} .
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2766
diff changeset
752 }
2694
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
753
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
754 for rows.Next() {
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
755 var (
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
756 measureDate time.Time
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
757 waterlevel float64
2783
2806821cfd63 Added min and max values to CSV output of /api/data/waterlevels/{gauge} .
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2766
diff changeset
758 valueMin sql.NullFloat64
2806821cfd63 Added min and max values to CSV output of /api/data/waterlevels/{gauge} .
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2766
diff changeset
759 valueMax sql.NullFloat64
2694
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
760 predicted bool
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
761 )
2783
2806821cfd63 Added min and max values to CSV output of /api/data/waterlevels/{gauge} .
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2766
diff changeset
762 if err := rows.Scan(
2806821cfd63 Added min and max values to CSV output of /api/data/waterlevels/{gauge} .
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2766
diff changeset
763 &measureDate,
2806821cfd63 Added min and max values to CSV output of /api/data/waterlevels/{gauge} .
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2766
diff changeset
764 &waterlevel,
2806821cfd63 Added min and max values to CSV output of /api/data/waterlevels/{gauge} .
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2766
diff changeset
765 &valueMin,
2806821cfd63 Added min and max values to CSV output of /api/data/waterlevels/{gauge} .
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2766
diff changeset
766 &valueMax,
2806821cfd63 Added min and max values to CSV output of /api/data/waterlevels/{gauge} .
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2766
diff changeset
767 &predicted,
2806821cfd63 Added min and max values to CSV output of /api/data/waterlevels/{gauge} .
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2766
diff changeset
768 ); err != nil {
5490
5f47eeea988d Use own logging package.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4786
diff changeset
769 log.Errorf("%v\n", err)
2694
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
770 // Too late for an HTTP error code.
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
771 return
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
772 }
4078
80bdcd137a1d Parse timezones from time inputs and send timezones in results.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3725
diff changeset
773 record[0] = measureDate.Format(common.TimeFormat)
2783
2806821cfd63 Added min and max values to CSV output of /api/data/waterlevels/{gauge} .
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2766
diff changeset
774 record[1] = float64format(waterlevel)
2806821cfd63 Added min and max values to CSV output of /api/data/waterlevels/{gauge} .
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2766
diff changeset
775 record[2] = nullFloat64format(valueMin)
2806821cfd63 Added min and max values to CSV output of /api/data/waterlevels/{gauge} .
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2766
diff changeset
776 record[3] = nullFloat64format(valueMax)
2806821cfd63 Added min and max values to CSV output of /api/data/waterlevels/{gauge} .
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2766
diff changeset
777 record[4] = boolFormat(predicted)
2806821cfd63 Added min and max values to CSV output of /api/data/waterlevels/{gauge} .
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2766
diff changeset
778
2694
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
779 if err := out.Write(record); err != nil {
5490
5f47eeea988d Use own logging package.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4786
diff changeset
780 log.Errorf("%v", err)
2694
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
781 // Too late for an HTTP error code.
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
782 return
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
783 }
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
784 }
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
785
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
786 if err := rows.Err(); err != nil {
5490
5f47eeea988d Use own logging package.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4786
diff changeset
787 log.Errorf("%v", err)
2694
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
788 // Too late for an HTTP error code.
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
789 return
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
790 }
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
791
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
792 out.Flush()
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
793 if err := out.Error(); err != nil {
5490
5f47eeea988d Use own logging package.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4786
diff changeset
794 log.Errorf("%v", err)
2694
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
795 // Too late for an HTTP error code.
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
796 return
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
797 }
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
798 }