Mercurial > gemma

annotate pkg/controllers/gauges.go @ 2806:c6374c520228

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Use between filter to speed up SQL for fetching yearly waterlevels.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Tue, 26 Mar 2019 07:41:53 +0100
parents 46a9a7c1281f
children 1fa57ad05e1e
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 (
2741
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
17 "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
18 "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
19 "fmt"
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 "log"
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
21 "net/http"
2800
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
22 "runtime"
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"
2800
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
25 "sync"
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"
2762
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
29 "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
30
2741
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
31 "gemma.intevation.de/gemma/pkg/common"
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
32 "gemma.intevation.de/gemma/pkg/middleware"
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
33 "gemma.intevation.de/gemma/pkg/models"
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 )
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
35
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
36 const (
2741
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
37 selectPredictedObserveredSQL = `
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
38 SELECT
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
39 a.measure_date AS measure_date,
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
40 a.water_level AS predicted,
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
41 b.water_level AS observed
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
42 FROM waterway.gauge_measurements a JOIN waterway.gauge_measurements b
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
43 ON a.fk_gauge_id = b.fk_gauge_id AND
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
44 a.measure_date = b.measure_date AND
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
45 a.predicted AND NOT b.predicted
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
46 WHERE
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
47 a.fk_gauge_id = (
2763
a06d11d1f0b3 Fixed gauge selection in Nash Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2762
diff changeset
48 $1::char(2),
a06d11d1f0b3 Fixed gauge selection in Nash Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2762
diff changeset
49 $2::char(3),
a06d11d1f0b3 Fixed gauge selection in Nash Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2762
diff changeset
50 $3::char(5),
a06d11d1f0b3 Fixed gauge selection in Nash Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2762
diff changeset
51 $4::char(5),
a06d11d1f0b3 Fixed gauge selection in Nash Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2762
diff changeset
52 $5::int
2741
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
53 ) AND
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
54 a.measure_date BETWEEN
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
55 $6::timestamp AND $6::timestamp - '72hours'::interval
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
56 ORDER BY a.measure_date
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
57 `
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
58
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
59 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
60 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
61 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
62 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
63 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
64 value_max,
2766
1f173d1a731d Fixed broken SQL statement.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2763
diff changeset
65 predicted
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
66 FROM waterway.gauge_measurements
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
67 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
68 `
2800
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
69
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
70 selectAllWaterlevelsMeasuredSQL = `
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
71 SELECT
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
72 measure_date,
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
73 water_level
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
74 FROM waterway.gauge_measurements
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
75 WHERE
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
76 NOT predicted
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
77 AND fk_gauge_id = (
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
78 $1::char(2),
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
79 $2::char(3),
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
80 $3::char(5),
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
81 $4::char(5),
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
82 $5::int
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
83 )
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
84 `
2803
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
85 selectYearWaterlevelsMeasuredSQL = `
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
86 SELECT
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
87 measure_date,
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
88 water_level
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
89 FROM waterway.gauge_measurements
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
90 WHERE
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
91 NOT predicted
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
92 AND fk_gauge_id = (
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
93 $1::char(2),
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
94 $2::char(3),
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
95 $3::char(5),
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
96 $4::char(5),
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
97 $5::int
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
98 )
2806
c6374c520228 Use between filter to speed up SQL for fetching yearly waterlevels.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2803
diff changeset
99 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
100 ORDER BY measure_date
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
101 `
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
102
2762
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
103 selectWaterlevelsMeasuredSQL = `
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
104 SELECT
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
105 measure_date,
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
106 water_level
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
107 FROM waterway.gauge_measurements
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
108 WHERE
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
109 NOT predicted
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
110 AND fk_gauge_id = (
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
111 $1::char(2),
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
112 $2::char(3),
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
113 $3::char(5),
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
114 $4::char(5),
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
115 $5::int
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
116 )
2763
a06d11d1f0b3 Fixed gauge selection in Nash Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2762
diff changeset
117 AND measure_date BETWEEN
a06d11d1f0b3 Fixed gauge selection in Nash Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2762
diff changeset
118 $6::timestamp with time zone AND
a06d11d1f0b3 Fixed gauge selection in Nash Sutcliffe calculation.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2762
diff changeset
119 $7::timestamp with time zone
2762
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
120 ORDER BY measure_date
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
121 `
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
122 )
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
123
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
124 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
125 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
126 }
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
127
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
128 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
129 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
130 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
131 }
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
132 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
133 }
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
134
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
135 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
136 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
137 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
138 }
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
139 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
140 }
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
141
2803
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
142 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
143
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
144 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
145
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
146 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
147 if err != nil {
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
148 http.Error(
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
149 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
150 http.StatusBadRequest)
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
151 return
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
152 }
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
153
2806
c6374c520228 Use between filter to speed up SQL for fetching yearly waterlevels.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2803
diff changeset
154 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
155
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
156 conn := middleware.GetDBConn(req)
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
157
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
158 ctx := req.Context()
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
159
2806
c6374c520228 Use between filter to speed up SQL for fetching yearly waterlevels.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2803
diff changeset
160 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
161 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
162
c6374c520228 Use between filter to speed up SQL for fetching yearly waterlevels.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2803
diff changeset
163 log.Printf("info: begin %s\n", begin)
c6374c520228 Use between filter to speed up SQL for fetching yearly waterlevels.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2803
diff changeset
164 log.Printf("info: end %s\n", end)
c6374c520228 Use between filter to speed up SQL for fetching yearly waterlevels.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2803
diff changeset
165
2803
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
166 rows, err := conn.QueryContext(
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
167 ctx,
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
168 selectYearWaterlevelsMeasuredSQL,
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
169 isrs.CountryCode,
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
170 isrs.LoCode,
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
171 isrs.FairwaySection,
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
172 isrs.Orc,
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
173 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
174 begin,
c6374c520228 Use between filter to speed up SQL for fetching yearly waterlevels.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2803
diff changeset
175 end,
2803
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 if err != nil {
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
178 http.Error(
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
179 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
180 http.StatusInternalServerError)
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
181 return
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
182 }
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
183 defer rows.Close()
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
184
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
185 var values []float64
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
186
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
187 lastDay, lastMonth := -1, -1
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
188
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
189 write := func() error {
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
190 var err error
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
191 if len(values) > 0 {
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
192 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
193 _, err = fmt.Fprintf(
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
194 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
195 float64format(mean))
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
196 values = values[:0]
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
197 }
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
198 return err
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
199 }
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
200
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
201 for rows.Next() {
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
202 var when time.Time
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
203 var value float64
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
204 if err := rows.Scan(&when, &value); err != nil {
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
205 log.Printf("error: %v", err)
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
206 // 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
207 return
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
208 }
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
209 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
210 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
211 if err := write(); err != nil {
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
212 log.Printf("error: %v", err)
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
213 // 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
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 lastDay, lastMonth = day, month
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 values = append(values, value)
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
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
221 if err := rows.Err(); err != nil {
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
222 log.Printf("error: %v", err)
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
223 // 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
224 return
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
225 }
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
226
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
227 if err := write(); err != nil {
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
228 log.Printf("error: %v", err)
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
229 // 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
230 }
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
231 }
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
232
2800
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
233 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
234
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
235 gauge := mux.Vars(req)["gauge"]
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
236
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
237 isrs, err := models.IsrsFromString(gauge)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
238 if err != nil {
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
239 http.Error(
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
240 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
241 http.StatusBadRequest)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
242 return
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
243 }
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
244
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
245 conn := middleware.GetDBConn(req)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
246
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
247 ctx := req.Context()
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
248
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
249 rows, err := conn.QueryContext(
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
250 ctx,
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
251 selectAllWaterlevelsMeasuredSQL,
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
252 isrs.CountryCode,
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
253 isrs.LoCode,
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
254 isrs.FairwaySection,
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
255 isrs.Orc,
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
256 isrs.Hectometre,
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
257 )
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
258 if err != nil {
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
259 http.Error(
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
260 rw, fmt.Sprintf("error: %v", err),
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
261 http.StatusInternalServerError)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
262 return
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
263 }
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
264 defer rows.Close()
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
265
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
266 type dayKey struct {
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
267 month byte
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
268 day byte
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
269 }
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 entries := make(map[dayKey][]float64)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
272
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
273 start := time.Now()
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
274
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
275 for rows.Next() {
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
276 var value float64
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
277 var date time.Time
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
278 if err := rows.Scan(&date, &value); err != nil {
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
279 http.Error(
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
280 rw, fmt.Sprintf("error: %v", err),
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
281 http.StatusInternalServerError)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
282 }
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
283 key := dayKey{
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
284 month: byte(date.Month()),
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
285 day: byte(date.Day()),
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
286 }
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
287 entries[key] = append(entries[key], value)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
288 }
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
289
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
290 if err := rows.Err(); err != nil {
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
291 http.Error(
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
292 rw, fmt.Sprintf("error: %v", err),
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
293 http.StatusInternalServerError)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
294 return
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
295 }
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
296
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
297 log.Printf("info: loading entries took %s\n", time.Since(start))
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
298
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
299 log.Printf("info: days found: %d\n", len(entries))
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
300
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
301 stats := time.Now()
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
302
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
303 type result struct {
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
304 key dayKey
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
305 values []float64
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
306 min float64
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
307 max float64
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
308 mean float64
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
309 median float64
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
310 q25 float64
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
311 q75 float64
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
312 }
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
313
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
314 results := make([]result, len(entries))
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
315
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
316 jobs := make(chan int)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
317
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
318 var wg sync.WaitGroup
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
319
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
320 for i, n := 0, runtime.NumCPU(); i < n; i++ {
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
321 wg.Add(1)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
322 go func() {
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
323 defer wg.Done()
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
324 for job := range jobs {
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
325 r := &results[job]
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
326 if len(r.values) == 0 {
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
327 continue
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
328 }
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
329 sort.Float64s(r.values)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
330 r.min = r.values[0]
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
331 r.max = r.values[len(r.values)-1]
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
332 r.median = r.values[len(r.values)/2]
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
333 r.mean = stat.Mean(r.values, nil)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
334 r.q25 = stat.Quantile(0.25, stat.Empirical, r.values, nil)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
335 r.q75 = stat.Quantile(0.75, stat.Empirical, r.values, nil)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
336 }
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 }
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
339
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
340 var i int
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
341 for k, v := range entries {
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
342 results[i].key = k
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
343 results[i].values = v
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
344 jobs <- i
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
345 i++
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
346 }
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
347 close(jobs)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
348 wg.Wait()
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
349
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
350 sort.Slice(results, func(i, j int) bool {
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
351 ki, kj := results[i].key, results[j].key
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
352 if d := int(ki.month) - int(kj.month); d != 0 {
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
353 return d < 0
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
354 }
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
355 return ki.day < kj.day
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
356 })
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
357
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
358 log.Printf("info: calculating stats took %s\n", time.Since(stats))
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 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
361
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
362 out := csv.NewWriter(rw)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
363
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
364 record := []string{
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
365 "#date",
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
366 "#min",
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
367 "#max",
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
368 "#mean",
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
369 "#median",
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
370 "#q25",
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
371 "#q75",
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
372 }
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
373
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
374 if err := out.Write(record); err != nil {
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
375 log.Printf("error: %v\n", err)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
376 // 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
377 return
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
378 }
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 for i := range results {
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
381 r := &results[i]
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
382 record[0] = fmt.Sprintf("%02d-%02d", r.key.day, r.key.month)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
383 record[1] = float64format(r.min)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
384 record[2] = float64format(r.max)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
385 record[3] = float64format(r.mean)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
386 record[4] = float64format(r.median)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
387 record[5] = float64format(r.q25)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
388 record[6] = float64format(r.q75)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
389 if err := out.Write(record); err != nil {
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
390 log.Printf("error: %v\n", err)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
391 // 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
392 return
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
393 }
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
394 }
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
395
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
396 out.Flush()
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
397 if err := out.Error(); err != nil {
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
398 log.Printf("error: %v", err)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
399 // 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
400 return
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
401 }
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
402 }
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
403
2762
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
404 func averageWaterlevels(rw http.ResponseWriter, req *http.Request) {
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
405 gauge := mux.Vars(req)["gauge"]
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
406
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
407 isrs, err := models.IsrsFromString(gauge)
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
408 if err != nil {
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
409 http.Error(
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
410 rw, fmt.Sprintf("error: Invalid ISRS code: %v", err),
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
411 http.StatusBadRequest)
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
412 return
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
413 }
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
414
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
415 var from, to time.Time
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
416
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
417 if t := req.FormValue("to"); t != "" {
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
418 var err error
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
419 if to, err = time.ParseInLocation(common.DateFormat, t, time.UTC); err != nil {
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
420 http.Error(
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
421 rw, fmt.Sprintf("error: bad from date: %v", err),
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
422 http.StatusBadRequest)
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
423 return
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
424 }
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
425 } else {
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
426 y, m, d := time.Now().Date()
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
427 to = time.Date(y, m, d, 0, 0, 0, 0, time.UTC)
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
428 }
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
429
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
430 if f := req.FormValue("from"); f != "" {
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
431 var err error
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
432 if from, err = time.ParseInLocation(common.DateFormat, f, time.UTC); err != nil {
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
433 http.Error(
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
434 rw, fmt.Sprintf("error: bad from date: %v", err),
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
435 http.StatusBadRequest)
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
436 return
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
437 }
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
438 } else {
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
439 from = to.AddDate(-1, 0, 0)
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
440 }
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
441
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
442 to = to.AddDate(0, 0, 1).Add(-time.Nanosecond)
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
443
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
444 if to.Before(from) {
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
445 from, to = to, from
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
446 }
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
447
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
448 conn := middleware.GetDBConn(req)
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
449
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
450 ctx := req.Context()
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
451
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
452 rows, err := conn.QueryContext(
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
453 ctx,
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
454 selectWaterlevelsMeasuredSQL,
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
455 isrs.CountryCode,
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
456 isrs.LoCode,
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
457 isrs.FairwaySection,
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
458 isrs.Orc,
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
459 isrs.Hectometre,
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
460 from, to,
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
461 )
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
462 if err != nil {
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
463 http.Error(
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
464 rw, fmt.Sprintf("error: %v", err),
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
465 http.StatusInternalServerError)
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
466 return
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
467 }
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
468 defer rows.Close()
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
469
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
470 rw.Header().Add("Content-Type", "text/csv")
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
471
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
472 out := csv.NewWriter(rw)
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
473
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
474 var last time.Time
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
475 var values []float64
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
476
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
477 record := []string{
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
478 "#date",
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
479 "#min",
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
480 "#max",
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
481 "#mean",
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
482 "#median",
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
483 "#q25",
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
484 "#q75",
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
485 }
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
486
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
487 if err := out.Write(record); err != nil {
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
488 log.Printf("error: %v\n", err)
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
489 // Too late for an HTTP error code.
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
490 return
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
491 }
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
492
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
493 write := func() error {
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
494 if len(values) > 0 {
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
495 sort.Float64s(values)
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
496 // date
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
497 record[0] = last.Format(common.DateFormat)
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
498 // min
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
499 record[1] = float64format(values[0])
2762
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
500 // max
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
501 record[2] = float64format(values[len(values)-1])
2762
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
502 // mean
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
503 record[3] = float64format(stat.Mean(values, nil))
2762
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
504 // median
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
505 record[4] = float64format(values[len(values)/2])
2762
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
506 // Q25
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
507 record[5] = float64format(
2762
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
508 stat.Quantile(0.25, stat.Empirical, values, nil))
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
509 // Q75
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
510 record[6] = float64format(
2762
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
511 stat.Quantile(0.75, stat.Empirical, values, nil))
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
512
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
513 err := out.Write(record)
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
514 values = values[:0]
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
515 return err
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
516 }
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
517 return nil
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
518 }
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
519
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
520 for rows.Next() {
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
521 var (
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
522 date time.Time
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
523 value float64
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
524 )
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
525 if err := rows.Scan(&date, &value); err != nil {
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
526 log.Printf("error: %v\n", err)
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
527 // Too late for an HTTP error code.
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
528 return
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
529 }
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
530 oy, om, od := last.Date()
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
531 ny, nm, nd := date.Date()
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
532 if oy != ny || om != nm || od != nd {
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
533 if err := write(); err != nil {
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
534 log.Printf("error: %v\n", err)
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
535 // Too late for an HTTP error code.
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
536 return
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
537 }
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
538 last = date
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
539 } else {
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
540 values = append(values, value)
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
541 }
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
542 }
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
543 write()
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
544
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
545 if err := rows.Err(); err != nil {
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
546 log.Printf("error: %v", err)
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
547 // Too late for an HTTP error code.
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
548 return
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
549 }
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
550
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
551 out.Flush()
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
552 if err := out.Error(); err != nil {
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
553 log.Printf("error: %v", err)
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
554 // Too late for an HTTP error code.
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
555 return
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
556 }
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
557
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
558 }
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
559
2741
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
560 func nashSutcliffe(
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
561 _ interface{},
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
562 req *http.Request,
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
563 conn *sql.Conn,
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
564 ) (jr JSONResult, err error) {
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
565 gauge := mux.Vars(req)["gauge"]
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
566
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
567 var isrs *models.Isrs
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
568 if isrs, err = models.IsrsFromString(gauge); err != nil {
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
569 err = JSONError{
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
570 Code: http.StatusBadRequest,
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
571 Message: fmt.Sprintf("error: Invalid ISRS code: %v", err),
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
572 }
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
573 return
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
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
576 var when time.Time
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
577 if w := req.FormValue("when"); w != "" {
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
578 if when, err = time.Parse(models.ImportTimeFormat, w); err != nil {
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
579 err = JSONError{
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
580 Code: http.StatusBadRequest,
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
581 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
582 }
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
583 return
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
584 }
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
585 } else {
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
586 when = time.Now()
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
587 }
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 ctx := req.Context()
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
590
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
591 var rows *sql.Rows
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
592 if rows, err = conn.QueryContext(
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
593 ctx,
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
594 selectPredictedObserveredSQL,
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
595 isrs.CountryCode,
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
596 isrs.LoCode,
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
597 isrs.FairwaySection,
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
598 isrs.Orc,
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
599 isrs.Hectometre,
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
600 when,
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
601 ); err != nil {
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
602 return
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
603 }
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
604 defer rows.Close()
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
605
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
606 var measurements []common.NSMeasurement
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
607
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
608 for rows.Next() {
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
609 var m common.NSMeasurement
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
610 if err = rows.Scan(
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
611 &m.When,
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
612 &m.Predicted,
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
613 &m.Observed,
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
614 ); err != nil {
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
615 return
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
616 }
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
617 measurements = append(measurements, m)
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
618 }
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
619 if err = rows.Err(); err != nil {
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
620 return
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
621 }
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
622
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
623 type coeff struct {
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
624 Value float64 `json:"value"`
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
625 Samples int `json:"samples"`
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
626 Hours int `json:"hours"`
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
627 }
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
628
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
629 type coeffs struct {
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
630 When models.ImportTime `json:"when"`
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
631 Coeffs []coeff `json:"coeffs"`
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
632 }
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
633
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
634 cs := make([]coeff, 3)
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
635 for i := range cs {
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
636 cs[i].Hours = (i + 1) * 24
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
637 cs[i].Value, cs[i].Samples = common.NashSutcliffe(
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
638 measurements,
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
639 when,
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
640 when.Add(time.Duration(-cs[i].Hours)*time.Hour),
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
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
644 jr = JSONResult{
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
645 Result: &coeffs{
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
646 When: models.ImportTime{when},
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
647 Coeffs: cs,
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
648 },
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
649 }
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
650 return
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
651 }
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
652
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
653 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
654 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
655
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
656 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
657 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
658 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
659 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
660 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
661 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
662 }
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
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 var fb filterBuilder
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 fb.stmt.WriteString(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
666
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 fb.cond(
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 " fk_gauge_id = ($%d::char(2), $%d::char(3), $%d::char(5), $%d::char(5), $%d::int) ",
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
669 isrs.CountryCode,
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
670 isrs.LoCode,
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
671 isrs.FairwaySection,
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
672 isrs.Orc,
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
673 isrs.Hectometre,
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
674 )
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
675
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
676 if from := req.FormValue("from"); from != "" {
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
677 fromTime, err := time.Parse(models.ImportTimeFormat, from)
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
678 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
679 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
680 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
681 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
682 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
683 }
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
684 fb.cond("measure_date >= $%d", fromTime)
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
685 }
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
686
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
687 if to := req.FormValue("to"); to != "" {
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
688 toTime, err := time.Parse(models.ImportTimeFormat, to)
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
689 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
690 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
691 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
692 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
693 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
694 }
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 fb.cond("measure_date <= $%d", toTime)
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
696 }
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
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 conn := middleware.GetDBConn(req)
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
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 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
701
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 rows, err := conn.QueryContext(ctx, fb.stmt.String(), fb.args...)
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
703 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
704 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
705 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
706 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
707 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
708 }
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 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
710
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 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
712
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 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
714
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
715 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
716 "#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
717 "#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
718 "#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
719 "#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
720 "#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
721 }
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
722
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
723 if err := out.Write(record); err != nil {
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
724 log.Printf("error: %v", err)
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
725 // 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
726 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
727 }
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
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 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
730 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
731 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
732 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
733 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
734 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
735 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
736 )
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
737 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
738 &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
739 &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
740 &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
741 &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
742 &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
743 ); err != nil {
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
744 log.Printf("error: %v\n", 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
745 // 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
746 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
747 }
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
748 record[0] = measureDate.Format(models.ImportTimeFormat)
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
749 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
750 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
751 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
752 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
753
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
754 if err := out.Write(record); 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
755 log.Printf("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
756 // 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
757 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
758 }
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
759 }
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
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 if err := rows.Err(); 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
762 log.Printf("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
763 // 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
764 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
765 }
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
766
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
767 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
768 if err := out.Error(); 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
769 log.Printf("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
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 }
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
773 }