Mercurial > gemma
annotate pkg/controllers/surveys.go @ 5520:05db984d3db1
Improve performance of bottleneck area calculation
Avoid buffer calculations by replacing them with simple distance comparisons
and calculate the boundary of the result geometry only once per iteration.
In some edge cases with very large numbers of iterations, this reduced
the runtime of a bottleneck import by a factor of more than twenty.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Thu, 21 Oct 2021 19:50:39 +0200 |
parents | fc01cdaf056d |
children | 3b842e951317 |
rev | line source |
---|---|
1017
a244b18cb916
Added GNU Affero General Public License.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
825
diff
changeset
|
1 // This is Free Software under GNU Affero General Public License v >= 3.0 |
a244b18cb916
Added GNU Affero General Public License.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
825
diff
changeset
|
2 // without warranty, see README.md and license for details. |
a244b18cb916
Added GNU Affero General Public License.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
825
diff
changeset
|
3 // |
a244b18cb916
Added GNU Affero General Public License.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
825
diff
changeset
|
4 // SPDX-License-Identifier: AGPL-3.0-or-later |
a244b18cb916
Added GNU Affero General Public License.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
825
diff
changeset
|
5 // License-Filename: LICENSES/AGPL-3.0.txt |
a244b18cb916
Added GNU Affero General Public License.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
825
diff
changeset
|
6 // |
2129
8f6345ad5f13
Extend /surveys/ endpoint to include reference gauge
Bernhard Reiter <bernhard@intevation.de>
parents:
1572
diff
changeset
|
7 // Copyright (C) 2018, 2019 by via donau |
1017
a244b18cb916
Added GNU Affero General Public License.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
825
diff
changeset
|
8 // – Österreichische Wasserstraßen-Gesellschaft mbH |
a244b18cb916
Added GNU Affero General Public License.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
825
diff
changeset
|
9 // Software engineering by Intevation GmbH |
a244b18cb916
Added GNU Affero General Public License.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
825
diff
changeset
|
10 // |
a244b18cb916
Added GNU Affero General Public License.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
825
diff
changeset
|
11 // Author(s): |
a244b18cb916
Added GNU Affero General Public License.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
825
diff
changeset
|
12 // * Sascha Wilde <sascha.wilde@intevation.de> |
1317
5443f5c9154c
Added missing authors names in Go files.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1017
diff
changeset
|
13 // * Sascha L. Teichmann <sascha.teichmann@intevation.de> |
2129
8f6345ad5f13
Extend /surveys/ endpoint to include reference gauge
Bernhard Reiter <bernhard@intevation.de>
parents:
1572
diff
changeset
|
14 // * Bernhard Reiter <bernhard.reiter@intevation.de> |
1017
a244b18cb916
Added GNU Affero General Public License.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
825
diff
changeset
|
15 |
812
98a7776100fb
Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
16 package controllers |
98a7776100fb
Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
17 |
98a7776100fb
Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
18 import ( |
98a7776100fb
Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
19 "database/sql" |
98a7776100fb
Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
20 "net/http" |
98a7776100fb
Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
21 |
4244
4394daeea96a
Moved JSONHandler into middleware package.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4243
diff
changeset
|
22 "github.com/gorilla/mux" |
4394daeea96a
Moved JSONHandler into middleware package.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4243
diff
changeset
|
23 |
812
98a7776100fb
Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
24 "gemma.intevation.de/gemma/pkg/models" |
4244
4394daeea96a
Moved JSONHandler into middleware package.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4243
diff
changeset
|
25 |
4394daeea96a
Moved JSONHandler into middleware package.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4243
diff
changeset
|
26 mw "gemma.intevation.de/gemma/pkg/middleware" |
812
98a7776100fb
Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
27 ) |
98a7776100fb
Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
28 |
98a7776100fb
Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
29 const ( |
1572
056a86b24be2
Made bottleneck primary key an int. Attention: This may break something!
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1332
diff
changeset
|
30 listSurveysSQL = ` |
4782
fc082c611b8b
api/surveys list all existing surveys (no filter by gauge validity)
Sascha Wilde <wilde@intevation.de>
parents:
4244
diff
changeset
|
31 SELECT DISTINCT |
1572
056a86b24be2
Made bottleneck primary key an int. Attention: This may break something!
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1332
diff
changeset
|
32 s.bottleneck_id, |
2129
8f6345ad5f13
Extend /surveys/ endpoint to include reference gauge
Bernhard Reiter <bernhard@intevation.de>
parents:
1572
diff
changeset
|
33 s.date_info::text, |
2146
7267f8168176
Extending endpoint /surveys to return depth_reference
Bernhard Reiter <bernhard@intevation.de>
parents:
2129
diff
changeset
|
34 s.depth_reference, |
4891
e68220372832
List surveys even if mandatory base data is missing.
Sascha Wilde <wilde@intevation.de>
parents:
4782
diff
changeset
|
35 COALESCE(g.objname, 'ERROR: MISSING GAUGE') AS gauge_objname, |
5405
5e7fca6ef935
When listing surveys tell survey type as well.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4891
diff
changeset
|
36 r.value AS waterlevel_value, |
5406 | 37 COALESCE(s.surtyp, 'ERROR: MISSING SURVEY TYPE') AS surtype |
3302
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
2317
diff
changeset
|
38 FROM waterway.bottlenecks AS b |
4050
02d5c2e17eb8
Adapted surveys back end to new historization model.
Sascha Wilde <wilde@intevation.de>
parents:
3656
diff
changeset
|
39 JOIN waterway.sounding_results AS s ON b.bottleneck_id = s.bottleneck_id |
4891
e68220372832
List surveys even if mandatory base data is missing.
Sascha Wilde <wilde@intevation.de>
parents:
4782
diff
changeset
|
40 LEFT JOIN waterway.gauges AS g |
4050
02d5c2e17eb8
Adapted surveys back end to new historization model.
Sascha Wilde <wilde@intevation.de>
parents:
3656
diff
changeset
|
41 ON b.gauge_location = g.location AND s.date_info::timestamptz <@ g.validity |
3302
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
2317
diff
changeset
|
42 LEFT JOIN waterway.gauges_reference_water_levels AS r |
3645
02951a62e8c6
'Historicise' bottlenecks on import
Tom Gottfried <tom@intevation.de>
parents:
3302
diff
changeset
|
43 ON s.depth_reference = r.depth_reference |
02951a62e8c6
'Historicise' bottlenecks on import
Tom Gottfried <tom@intevation.de>
parents:
3302
diff
changeset
|
44 AND g.location = r.location AND g.validity = r.validity |
4782
fc082c611b8b
api/surveys list all existing surveys (no filter by gauge validity)
Sascha Wilde <wilde@intevation.de>
parents:
4244
diff
changeset
|
45 WHERE b.objnam = $1` |
812
98a7776100fb
Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
46 ) |
98a7776100fb
Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
47 |
4244
4394daeea96a
Moved JSONHandler into middleware package.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4243
diff
changeset
|
48 func listSurveys(req *http.Request) (jr mw.JSONResult, err error) { |
812
98a7776100fb
Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
49 |
98a7776100fb
Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
50 bottleneckName := mux.Vars(req)["bottleneck"] |
98a7776100fb
Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
51 |
98a7776100fb
Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
52 var rows *sql.Rows |
98a7776100fb
Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
53 |
4244
4394daeea96a
Moved JSONHandler into middleware package.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4243
diff
changeset
|
54 rows, err = mw.JSONConn(req).QueryContext(req.Context(), listSurveysSQL, bottleneckName) |
812
98a7776100fb
Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
55 if err != nil { |
98a7776100fb
Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
56 return |
98a7776100fb
Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
57 } |
98a7776100fb
Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
58 defer rows.Close() |
98a7776100fb
Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
59 |
2152
8132434f4093
Surveys controller: Fixed nullable water level.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2149
diff
changeset
|
60 surveys := []*models.Survey{} |
812
98a7776100fb
Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
61 |
2149
ed0ee54fc49e
Extend /surveys endpoint to include waterlevel_value
Bernhard Reiter <bernhard@intevation.de>
parents:
2146
diff
changeset
|
62 // as we do not use the values here, we could simply the code here |
ed0ee54fc49e
Extend /surveys endpoint to include waterlevel_value
Bernhard Reiter <bernhard@intevation.de>
parents:
2146
diff
changeset
|
63 // to work without an explicit mdels/surverys.go |
ed0ee54fc49e
Extend /surveys endpoint to include waterlevel_value
Bernhard Reiter <bernhard@intevation.de>
parents:
2146
diff
changeset
|
64 // (like done in controllers/search.go) |
812
98a7776100fb
Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
65 for rows.Next() { |
98a7776100fb
Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
66 var survey models.Survey |
5405
5e7fca6ef935
When listing surveys tell survey type as well.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4891
diff
changeset
|
67 var surType string |
2152
8132434f4093
Surveys controller: Fixed nullable water level.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2149
diff
changeset
|
68 var level sql.NullInt64 |
812
98a7776100fb
Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
69 if err = rows.Scan( |
1332
447db2bce052
Renamed BottleneckId to BottleneckID to make golint happy.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1317
diff
changeset
|
70 &survey.BottleneckID, |
812
98a7776100fb
Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
71 &survey.DateInfo, |
2146
7267f8168176
Extending endpoint /surveys to return depth_reference
Bernhard Reiter <bernhard@intevation.de>
parents:
2129
diff
changeset
|
72 &survey.DepthReference, |
2129
8f6345ad5f13
Extend /surveys/ endpoint to include reference gauge
Bernhard Reiter <bernhard@intevation.de>
parents:
1572
diff
changeset
|
73 &survey.ReferenceGauge, |
2152
8132434f4093
Surveys controller: Fixed nullable water level.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2149
diff
changeset
|
74 &level, |
5405
5e7fca6ef935
When listing surveys tell survey type as well.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4891
diff
changeset
|
75 &surType, |
812
98a7776100fb
Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
76 ); err != nil { |
98a7776100fb
Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
77 return |
98a7776100fb
Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
78 } |
5462
fc01cdaf056d
Cosmetics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5459
diff
changeset
|
79 switch { |
fc01cdaf056d
Cosmetics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5459
diff
changeset
|
80 case level.Valid: |
2152
8132434f4093
Surveys controller: Fixed nullable water level.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2149
diff
changeset
|
81 survey.WaterLevelValue = &level.Int64 |
5462
fc01cdaf056d
Cosmetics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5459
diff
changeset
|
82 case survey.DepthReference == "ZPG": |
fc01cdaf056d
Cosmetics.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5459
diff
changeset
|
83 survey.WaterLevelValue = new(int64) |
2152
8132434f4093
Surveys controller: Fixed nullable water level.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2149
diff
changeset
|
84 } |
5405
5e7fca6ef935
When listing surveys tell survey type as well.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4891
diff
changeset
|
85 survey.SurveyType = models.SurveyType(surType) |
2152
8132434f4093
Surveys controller: Fixed nullable water level.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2149
diff
changeset
|
86 surveys = append(surveys, &survey) |
812
98a7776100fb
Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
87 } |
98a7776100fb
Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
88 |
825
a5452a001b46
Added missing error check in surveys controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
812
diff
changeset
|
89 if err = rows.Err(); err != nil { |
a5452a001b46
Added missing error check in surveys controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
812
diff
changeset
|
90 return |
a5452a001b46
Added missing error check in surveys controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
812
diff
changeset
|
91 } |
a5452a001b46
Added missing error check in surveys controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
812
diff
changeset
|
92 |
4244
4394daeea96a
Moved JSONHandler into middleware package.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4243
diff
changeset
|
93 jr = mw.JSONResult{ |
812
98a7776100fb
Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
94 Result: struct { |
2152
8132434f4093
Surveys controller: Fixed nullable water level.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2149
diff
changeset
|
95 Surveys []*models.Survey `json:"surveys"` |
812
98a7776100fb
Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
96 }{surveys}, |
98a7776100fb
Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
97 } |
98a7776100fb
Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
98 return |
98a7776100fb
Added end point lo list available surveys for a given bottleneck.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
99 } |