Mercurial > gemma

annotate pkg/controllers/gauges.go @ 2858:401bca8eaafb

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Filter predicted values for waterlevels.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Thu, 28 Mar 2019 17:56:14 +0100
parents b5555005f51e
children e6dc09896a4d
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"
2762
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
22 "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
23 "strconv"
2809
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
24 "strings"
2694
0d7a4fdb9e12 Added GET /api/data/waterlevels/{gauge isrs}?from={time_a}&to={time_b} to fetch waterlevels of gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
25 "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
26
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 "github.com/gorilla/mux"
2826
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
28 "github.com/jackc/pgx/pgtype"
2762
f95ec0bb565c Added endpoint to deliver average waterlevels for a given gauge.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2741
diff changeset
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
2826
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
70 selectAllWaterlevelsMeasuredRangeSQL = `
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
71 SELECT
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
72 min(measure_date),
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
73 max(measure_date)
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
74 FROM waterway.gauge_measurements WHERE
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
75 fk_gauge_id = (
2800
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
76 $1::char(2),
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
77 $2::char(3),
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
78 $3::char(5),
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
79 $4::char(5),
2826
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
80 $5::int
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
81 )::isrs
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
82 AND NOT predicted
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
83 AND staging_done
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
84 `
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
85
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
86 selectAllWaterlevelsMeasuredSQL = `
2809
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
87 SELECT
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
88 extract(day from measure_date)::varchar || ':' ||
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
89 extract(month from measure_date)::varchar AS day_month,
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
90 percentile_disc(0.25) within group (order by water_level) AS q25,
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
91 percentile_disc(0.5) within group (order by water_level) AS median,
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
92 percentile_disc(0.75) within group (order by water_level) AS q75,
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
93 avg(water_level) AS mean,
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
94 min(water_level) AS min,
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
95 max(water_level) AS max
2826
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
96 FROM waterway.gauge_measurements WHERE
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
97 fk_gauge_id = (
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
98 $1::char(2),
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
99 $2::char(3),
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
100 $3::char(5),
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
101 $4::char(5),
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
102 $5::int
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
103 )::isrs
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
104 AND NOT predicted
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
105 AND staging_done
2809
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
106 GROUP BY extract(day from measure_date)::varchar || ':' ||
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
107 extract(month from measure_date)::varchar;
2800
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
108 `
2803
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
109 selectYearWaterlevelsMeasuredSQL = `
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
110 SELECT
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
111 measure_date,
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
112 water_level
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
113 FROM waterway.gauge_measurements
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
114 WHERE
2817
ff58440ce809 Added staging_done to the longterm waterlevels selections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2814
diff changeset
115 NOT predicted AND
ff58440ce809 Added staging_done to the longterm waterlevels selections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2814
diff changeset
116 staging_done AND
ff58440ce809 Added staging_done to the longterm waterlevels selections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2814
diff changeset
117 fk_gauge_id = (
2803
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
118 $1::char(2),
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
119 $2::char(3),
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
120 $3::char(5),
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
121 $4::char(5),
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
122 $5::int
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
123 )
2806
c6374c520228 Use between filter to speed up SQL for fetching yearly waterlevels.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2803
diff changeset
124 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
125 ORDER BY measure_date
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
126 `
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
127 )
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
128
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
129 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
130 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
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
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 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
134 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
135 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
136 }
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 ""
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
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 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
141 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
142 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
143 }
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
144 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
145 }
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
146
2803
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
147 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
148
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
149 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
150
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
151 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
152 if err != nil {
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
153 http.Error(
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
154 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
155 http.StatusBadRequest)
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
156 return
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
2806
c6374c520228 Use between filter to speed up SQL for fetching yearly waterlevels.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2803
diff changeset
159 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
160
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
161 conn := middleware.GetDBConn(req)
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
162
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
163 ctx := req.Context()
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
164
2806
c6374c520228 Use between filter to speed up SQL for fetching yearly waterlevels.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2803
diff changeset
165 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
166 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
167
c6374c520228 Use between filter to speed up SQL for fetching yearly waterlevels.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2803
diff changeset
168 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
169 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
170
2803
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
171 rows, err := conn.QueryContext(
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
172 ctx,
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
173 selectYearWaterlevelsMeasuredSQL,
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
174 isrs.CountryCode,
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
175 isrs.LoCode,
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
176 isrs.FairwaySection,
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
177 isrs.Orc,
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
178 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
179 begin,
c6374c520228 Use between filter to speed up SQL for fetching yearly waterlevels.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2803
diff changeset
180 end,
2803
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
181 )
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
182 if err != nil {
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
183 http.Error(
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
184 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
185 http.StatusInternalServerError)
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
186 return
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
187 }
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
188 defer rows.Close()
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
189
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
190 var values []float64
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
191
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
192 lastDay, lastMonth := -1, -1
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
193
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
194 write := func() error {
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
195 var err error
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
196 if len(values) > 0 {
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
197 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
198 _, err = fmt.Fprintf(
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
199 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
200 float64format(mean))
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
201 values = values[:0]
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
202 }
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
203 return err
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
204 }
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
205
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
206 for rows.Next() {
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
207 var when time.Time
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
208 var value float64
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
209 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
210 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
211 // 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
212 return
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
213 }
2807
1fa57ad05e1e Calculate the yearly waterlevel over an UTC time interval.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2806
diff changeset
214 when = when.UTC()
2803
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
215 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
216 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
217 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
218 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
219 // 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
220 return
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
221 }
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
222 lastDay, lastMonth = day, month
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
223 }
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
224 values = append(values, value)
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 := rows.Err(); 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 return
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
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
233 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
234 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
235 // 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
236 }
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
237 }
46a9a7c1281f Added GET /api/data/year-waterlevels/{gauge}/{year}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2800
diff changeset
238
2800
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
239 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
240
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
241 gauge := mux.Vars(req)["gauge"]
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
242
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
243 isrs, err := models.IsrsFromString(gauge)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
244 if err != nil {
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
245 http.Error(
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
246 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
247 http.StatusBadRequest)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
248 return
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
249 }
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
250
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
251 conn := middleware.GetDBConn(req)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
252
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
253 ctx := req.Context()
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
254
2826
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
255 var begin, end pgtype.Timestamp
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
256
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
257 err = conn.QueryRowContext(
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
258 ctx,
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
259 selectAllWaterlevelsMeasuredRangeSQL,
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
260 isrs.CountryCode,
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
261 isrs.LoCode,
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
262 isrs.FairwaySection,
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
263 isrs.Orc,
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
264 isrs.Hectometre,
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
265 ).Scan(&begin, &end)
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
266
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
267 switch {
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
268 case err == sql.ErrNoRows || begin.Status != pgtype.Present || end.Status != pgtype.Present:
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
269 http.NotFound(rw, req)
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
270 return
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
271 case err != nil:
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
272 http.Error(
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
273 rw, fmt.Sprintf("error: %v", err),
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
274 http.StatusInternalServerError)
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
275 return
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
276 }
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
277
2800
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
278 rows, err := conn.QueryContext(
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
279 ctx,
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
280 selectAllWaterlevelsMeasuredSQL,
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
281 isrs.CountryCode,
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
282 isrs.LoCode,
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
283 isrs.FairwaySection,
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
284 isrs.Orc,
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
285 isrs.Hectometre,
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 if err != nil {
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
288 http.Error(
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
289 rw, fmt.Sprintf("error: %v", err),
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
290 http.StatusInternalServerError)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
291 return
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
292 }
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
293 defer rows.Close()
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
294
2809
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
295 type result struct {
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
296 day int
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
297 month int
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
298 q25 float64
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
299 median float64
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
300 q75 float64
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
301 mean float64
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
302 min float64
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
303 max float64
2800
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
304 }
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
305
2809
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
306 results := make([]result, 0, 366)
2800
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
307
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
308 start := time.Now()
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
309
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
310 for rows.Next() {
2809
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
311 var r result
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
312 var dayMonth string
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
313 if err := rows.Scan(
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
314 &dayMonth,
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
315 &r.q25,
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
316 &r.median,
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
317 &r.q75,
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
318 &r.mean,
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
319 &r.min,
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
320 &r.max,
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
321 ); err != nil {
2800
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
322 http.Error(
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
323 rw, fmt.Sprintf("error: %v", err),
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
324 http.StatusInternalServerError)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
325 }
2809
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
326 parts := strings.SplitN(dayMonth, ":", 2)
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
327 r.day, _ = strconv.Atoi(parts[0])
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
328 r.month, _ = strconv.Atoi(parts[1])
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
329 results = append(results, r)
2800
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
330 }
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
331
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
332 if err := rows.Err(); err != nil {
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
333 http.Error(
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
334 rw, fmt.Sprintf("error: %v", err),
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
335 http.StatusInternalServerError)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
336 return
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 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
340
2809
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
341 log.Printf("info: days found: %d\n", len(results))
2800
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
342
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
343 sort.Slice(results, func(i, j int) bool {
2832
b5555005f51e cosmetics: removed superfluous casts.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2826
diff changeset
344 if d := results[i].month - results[j].month; d != 0 {
2800
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
345 return d < 0
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
346 }
2809
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
347 return results[i].day < results[j].day
2800
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
348 })
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 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
351
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
352 out := csv.NewWriter(rw)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
353
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
354 record := []string{
2826
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
355 fmt.Sprintf("#interval: %d-%d",
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
356 begin.Time.UTC().Year(),
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
357 end.Time.UTC().Year()),
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
358 "",
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
359 "",
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
360 "",
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
361 "",
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
362 "",
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
363 "",
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
364 }
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
365
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
366 if err := out.Write(record); err != nil {
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
367 log.Printf("error: %v\n", err)
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
368 // Too late for an HTTP error code.
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
369 return
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
370 }
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
371
f7ae108c2838 longterm waterlevels statistics: Generate a comment line with the interval of years.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2817
diff changeset
372 record = []string{
2800
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
373 "#date",
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
374 "#min",
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
375 "#max",
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
376 "#mean",
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
377 "#median",
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
378 "#q25",
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
379 "#q75",
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
380 }
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
381
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
382 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
383 log.Printf("error: %v\n", err)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
384 // 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
385 return
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
386 }
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
387
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
388 for i := range results {
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
389 r := &results[i]
2809
216bc6394911 Optimized longterm waterlevel statistics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2808
diff changeset
390 record[0] = fmt.Sprintf("%02d-%02d", r.day, r.month)
2800
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
391 record[1] = float64format(r.min)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
392 record[2] = float64format(r.max)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
393 record[3] = float64format(r.mean)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
394 record[4] = float64format(r.median)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
395 record[5] = float64format(r.q25)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
396 record[6] = float64format(r.q75)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
397 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
398 log.Printf("error: %v\n", 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
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
404 out.Flush()
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
405 if err := out.Error(); err != nil {
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
406 log.Printf("error: %v", err)
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
407 // 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
408 return
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
409 }
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
410 }
db1052bc162a Added GET /data/longterm-waterlevels/{gauge}
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2783
diff changeset
411
2741
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
412 func nashSutcliffe(
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
413 _ interface{},
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
414 req *http.Request,
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
415 conn *sql.Conn,
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
416 ) (jr JSONResult, err error) {
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
417 gauge := mux.Vars(req)["gauge"]
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
418
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
419 var isrs *models.Isrs
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
420 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
421 err = JSONError{
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
422 Code: http.StatusBadRequest,
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
423 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
424 }
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
425 return
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
426 }
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
427
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
428 var when time.Time
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
429 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
430 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
431 err = JSONError{
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
432 Code: http.StatusBadRequest,
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
433 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
434 }
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
435 return
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
436 }
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
437 } else {
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
438 when = time.Now()
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
439 }
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
440
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
441 ctx := req.Context()
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
442
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
443 var rows *sql.Rows
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
444 if rows, err = conn.QueryContext(
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
445 ctx,
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
446 selectPredictedObserveredSQL,
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
447 isrs.CountryCode,
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
448 isrs.LoCode,
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
449 isrs.FairwaySection,
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
450 isrs.Orc,
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
451 isrs.Hectometre,
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
452 when,
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
453 ); err != nil {
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
454 return
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
455 }
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
456 defer rows.Close()
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
457
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
458 var measurements []common.NSMeasurement
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
459
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
460 for rows.Next() {
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
461 var m common.NSMeasurement
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
462 if err = rows.Scan(
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
463 &m.When,
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
464 &m.Predicted,
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
465 &m.Observed,
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
466 ); err != nil {
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
467 return
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
468 }
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
469 measurements = append(measurements, m)
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
470 }
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
471 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
472 return
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
473 }
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
474
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
475 type coeff struct {
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
476 Value float64 `json:"value"`
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
477 Samples int `json:"samples"`
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
478 Hours int `json:"hours"`
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
479 }
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
480
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
481 type coeffs struct {
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
482 When models.ImportTime `json:"when"`
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
483 Coeffs []coeff `json:"coeffs"`
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
484 }
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
485
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
486 cs := make([]coeff, 3)
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
487 for i := range cs {
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
488 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
489 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
490 measurements,
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
491 when,
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
492 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
493 )
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
494 }
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
495
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
496 jr = JSONResult{
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
497 Result: &coeffs{
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
498 When: models.ImportTime{when},
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
499 Coeffs: cs,
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
500 },
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
501 }
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
502 return
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
503 }
87aed4f9b1b8 Added calculation of Nash Sutcliffe efficiency coefficents.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2694
diff changeset
504
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
505 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
506 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
507
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
508 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
509 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
510 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
511 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
512 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
513 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
514 }
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
515
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
516 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
517 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
518
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
519 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
520 " 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
521 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
522 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
523 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
524 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
525 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
526 )
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
527
2858
401bca8eaafb Filter predicted values for waterlevels.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2832
diff changeset
528 fb.cond(
401bca8eaafb Filter predicted values for waterlevels.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2832
diff changeset
529 `NOT predicted
401bca8eaafb Filter predicted values for waterlevels.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2832
diff changeset
530 OR (
401bca8eaafb Filter predicted values for waterlevels.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2832
diff changeset
531 date_issue = (
401bca8eaafb Filter predicted values for waterlevels.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2832
diff changeset
532 SELECT max(date_issue) FROM waterway.gauge_measurements
401bca8eaafb Filter predicted values for waterlevels.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2832
diff changeset
533 WHERE fk_gauge_id = ($%d::char(2), $%d::char(3), $%d::char(5), $%d::char(5), $%d::int)
401bca8eaafb Filter predicted values for waterlevels.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2832
diff changeset
534 )
401bca8eaafb Filter predicted values for waterlevels.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2832
diff changeset
535 )`,
401bca8eaafb Filter predicted values for waterlevels.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2832
diff changeset
536 isrs.CountryCode,
401bca8eaafb Filter predicted values for waterlevels.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2832
diff changeset
537 isrs.LoCode,
401bca8eaafb Filter predicted values for waterlevels.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2832
diff changeset
538 isrs.FairwaySection,
401bca8eaafb Filter predicted values for waterlevels.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2832
diff changeset
539 isrs.Orc,
401bca8eaafb Filter predicted values for waterlevels.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2832
diff changeset
540 isrs.Hectometre,
401bca8eaafb Filter predicted values for waterlevels.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2832
diff changeset
541 )
401bca8eaafb Filter predicted values for waterlevels.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2832
diff changeset
542
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
543 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
544 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
545 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
546 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
547 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
548 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
549 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
550 }
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
551 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
552 }
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
553
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
554 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
555 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
556 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
557 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
558 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
559 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
560 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
561 }
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
562 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
563 }
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
564
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
565 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
566
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
567 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
568
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
569 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
570 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
571 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
572 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
573 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
574 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
575 }
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
576 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
577
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
578 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
579
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
580 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
581
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
582 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
583 "#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
584 "#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
585 "#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
586 "#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
587 "#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
588 }
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
589
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
590 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
591 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
592 // 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
593 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
594 }
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
595
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
596 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
597 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
598 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
599 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
600 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
601 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
602 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
603 )
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
604 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
605 &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
606 &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
607 &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
608 &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
609 &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
610 ); 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
611 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
612 // 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
613 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
614 }
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
615 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
616 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
617 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
618 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
619 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
620
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
621 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
622 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
623 // 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
624 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
625 }
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
626 }
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
627
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
628 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
629 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
630 // 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
631 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
632 }
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
633
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
634 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
635 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
636 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
637 // 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
638 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
639 }
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
640 }