Mercurial > gemma

annotate pkg/models/sr.go @ 4611:b5aa1eb83bb0 geoserver_sql_views

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add possibility to configure SRS for GeoServer SQL view Automatic detection of spatial reference system for SQL views in GeoServer does not always find the correct SRS.
author Tom Gottfried <tom@intevation.de>
date Fri, 06 Sep 2019 11:58:03 +0200
parents 98e2041c7ebf
children 85f19e924a43
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
1224
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
1 // This is Free Software under GNU Affero General Public License v >= 3.0
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
2 // without warranty, see README.md and license for details.
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
3 //
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
4 // SPDX-License-Identifier: AGPL-3.0-or-later
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
5 // License-Filename: LICENSES/AGPL-3.0.txt
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
6 //
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
7 // Copyright (C) 2018 by via donau
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
8 // – Österreichische Wasserstraßen-Gesellschaft mbH
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
9 // Software engineering by Intevation GmbH
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
10 //
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
11 // Author(s):
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
12 // * Sascha L. Teichmann <sascha.teichmann@intevation.de>
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
13 // * Bernhard E. Reiter <bernhard.reiter@intevation.de>
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
14
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
15 package models
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
16
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
17 import (
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
18 "context"
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
19 "database/sql"
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
20 "encoding/json"
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
21 "errors"
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
22 "fmt"
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
23 "io"
2345
df6383831ad3 Sounding result import: Be more verbose if sounding result already exists for a bottleneck/date.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1900
diff changeset
24
df6383831ad3 Sounding result import: Be more verbose if sounding result already exists for a bottleneck/date.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1900
diff changeset
25 "gemma.intevation.de/gemma/pkg/common"
1224
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
26 )
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
27
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
28 type (
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
29 SoundingResultMeta struct {
1900
6a67cd819e93 To prepare stretch import made some model data types re-usable.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1572
diff changeset
30 Date Date `json:"date"`
6a67cd819e93 To prepare stretch import made some model data types re-usable.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1572
diff changeset
31 Bottleneck string `json:"bottleneck"`
6a67cd819e93 To prepare stretch import made some model data types re-usable.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1572
diff changeset
32 EPSG uint `json:"epsg"`
6a67cd819e93 To prepare stretch import made some model data types re-usable.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1572
diff changeset
33 DepthReference string `json:"depth-reference"`
3585
479da494bc09 SR import: access beam-type parameter supplied by client. TODO: Implement single beam code path.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3302
diff changeset
34 SingleBeam bool `json:"single-beam"`
3945
3bdbaf1b282a SR import: Negate the Z values in the XYZ files if the flag `negate-z` is set.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3895
diff changeset
35 NegateZ bool `json:"negate-z,omitempty"`
1224
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
36 }
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
37 )
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
38
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
39 const (
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
40 checkDepthReferenceSQL = `
2961
5bc941d9ec43 Fix checking of depth reference for sounding result
Tom Gottfried <tom@intevation.de>
parents: 2345
diff changeset
41 SELECT EXISTS(SELECT 1
5bc941d9ec43 Fix checking of depth reference for sounding result
Tom Gottfried <tom@intevation.de>
parents: 2345
diff changeset
42 FROM waterway.bottlenecks bn
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 2961
diff changeset
43 JOIN waterway.gauges g
4055
98e2041c7ebf Adapted sr-import to new historization model.
Sascha Wilde <wilde@intevation.de>
parents: 3945
diff changeset
44 ON bn.gauge_location = g.location AND $3::timestamptz <@ g.validity
3645
02951a62e8c6 'Historicise' bottlenecks on import
Tom Gottfried <tom@intevation.de>
parents: 3585
diff changeset
45 JOIN waterway.gauges_reference_water_levels rl
02951a62e8c6 'Historicise' bottlenecks on import
Tom Gottfried <tom@intevation.de>
parents: 3585
diff changeset
46 ON g.location = rl.location AND g.validity = rl.validity
2961
5bc941d9ec43 Fix checking of depth reference for sounding result
Tom Gottfried <tom@intevation.de>
parents: 2345
diff changeset
47 WHERE bn.objnam = $1
5bc941d9ec43 Fix checking of depth reference for sounding result
Tom Gottfried <tom@intevation.de>
parents: 2345
diff changeset
48 AND rl.depth_reference = $2)`
1224
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
49
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
50 checkBottleneckSQL = `
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
51 SELECT true FROM waterway.bottlenecks WHERE objnam = $1`
1297
1c0c9190fcf2 Import sounding result: If uploading a zip with a meta.json which has
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1224
diff changeset
52
1c0c9190fcf2 Import sounding result: If uploading a zip with a meta.json which has
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1224
diff changeset
53 checkBottleneckDateUniqueSQL = `
1c0c9190fcf2 Import sounding result: If uploading a zip with a meta.json which has
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1224
diff changeset
54 SELECT true FROM waterway.sounding_results sr JOIN
3656
2a079d0a71c1 Ensure sounding results are associated to matching bottleneck version
Tom Gottfried <tom@intevation.de>
parents: 3645
diff changeset
55 waterway.bottlenecks bn ON sr.bottleneck_id = bn.bottleneck_id
1297
1c0c9190fcf2 Import sounding result: If uploading a zip with a meta.json which has
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1224
diff changeset
56 WHERE bn.objnam = $1 AND sr.date_info = $2`
1224
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
57 )
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
58
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
59 func (m *SoundingResultMeta) Decode(r io.Reader) error {
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
60 err := json.NewDecoder(r).Decode(m)
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
61 if err == nil && m.EPSG == 0 {
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
62 m.EPSG = WGS84
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
63 }
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
64 return err
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
65 }
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
66
1328
d753ce6cf588 To make golint happier made context.Context to be the first argument in all calls.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1297
diff changeset
67 func (m *SoundingResultMeta) Validate(ctx context.Context, conn *sql.Conn) []error {
1224
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
68
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
69 var errs []error
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
70
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
71 var b bool
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
72 err := conn.QueryRowContext(ctx,
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
73 checkBottleneckSQL,
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
74 m.Bottleneck).Scan(&b)
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
75 switch {
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
76 case err == sql.ErrNoRows:
1330
785f14264426 Follow the rules for error messages to make golint happy.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1328
diff changeset
77 errs = append(errs, fmt.Errorf("unknown bottleneck '%s'", m.Bottleneck))
1224
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
78 case err != nil:
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
79 errs = append(errs, err)
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
80 case !b:
1331
275ead19b9fd More on following the rules for error messages to make golint happy.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1330
diff changeset
81 errs = append(errs, errors.New("unexpected bottleneck"))
1224
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
82 }
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
83
3895
07b2df71bb4a Allow 'ZPG' as a depth reference system in meta.json files.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3656
diff changeset
84 if m.DepthReference != "ZPG" {
07b2df71bb4a Allow 'ZPG' as a depth reference system in meta.json files.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3656
diff changeset
85 err = conn.QueryRowContext(ctx,
07b2df71bb4a Allow 'ZPG' as a depth reference system in meta.json files.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3656
diff changeset
86 checkDepthReferenceSQL,
07b2df71bb4a Allow 'ZPG' as a depth reference system in meta.json files.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3656
diff changeset
87 m.Bottleneck,
4055
98e2041c7ebf Adapted sr-import to new historization model.
Sascha Wilde <wilde@intevation.de>
parents: 3945
diff changeset
88 m.DepthReference,
98e2041c7ebf Adapted sr-import to new historization model.
Sascha Wilde <wilde@intevation.de>
parents: 3945
diff changeset
89 m.Date.Time).Scan(&b)
3895
07b2df71bb4a Allow 'ZPG' as a depth reference system in meta.json files.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3656
diff changeset
90 switch {
07b2df71bb4a Allow 'ZPG' as a depth reference system in meta.json files.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3656
diff changeset
91 case !b:
07b2df71bb4a Allow 'ZPG' as a depth reference system in meta.json files.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3656
diff changeset
92 errs = append(errs,
07b2df71bb4a Allow 'ZPG' as a depth reference system in meta.json files.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3656
diff changeset
93 fmt.Errorf("unknown depth reference '%s'", m.DepthReference))
07b2df71bb4a Allow 'ZPG' as a depth reference system in meta.json files.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3656
diff changeset
94 case err != nil:
07b2df71bb4a Allow 'ZPG' as a depth reference system in meta.json files.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3656
diff changeset
95 errs = append(errs, err)
07b2df71bb4a Allow 'ZPG' as a depth reference system in meta.json files.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3656
diff changeset
96 }
2961
5bc941d9ec43 Fix checking of depth reference for sounding result
Tom Gottfried <tom@intevation.de>
parents: 2345
diff changeset
97 }
5bc941d9ec43 Fix checking of depth reference for sounding result
Tom Gottfried <tom@intevation.de>
parents: 2345
diff changeset
98
5bc941d9ec43 Fix checking of depth reference for sounding result
Tom Gottfried <tom@intevation.de>
parents: 2345
diff changeset
99 err = conn.QueryRowContext(ctx,
1297
1c0c9190fcf2 Import sounding result: If uploading a zip with a meta.json which has
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1224
diff changeset
100 checkBottleneckDateUniqueSQL,
1c0c9190fcf2 Import sounding result: If uploading a zip with a meta.json which has
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1224
diff changeset
101 m.Bottleneck, m.Date.Time).Scan(&b)
1c0c9190fcf2 Import sounding result: If uploading a zip with a meta.json which has
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1224
diff changeset
102 switch {
1c0c9190fcf2 Import sounding result: If uploading a zip with a meta.json which has
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1224
diff changeset
103 case err == sql.ErrNoRows: // good! -> unique.
1c0c9190fcf2 Import sounding result: If uploading a zip with a meta.json which has
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1224
diff changeset
104 case err != nil:
1c0c9190fcf2 Import sounding result: If uploading a zip with a meta.json which has
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1224
diff changeset
105 errs = append(errs, err)
1c0c9190fcf2 Import sounding result: If uploading a zip with a meta.json which has
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1224
diff changeset
106 case b:
1c0c9190fcf2 Import sounding result: If uploading a zip with a meta.json which has
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1224
diff changeset
107 errs = append(errs,
2345
df6383831ad3 Sounding result import: Be more verbose if sounding result already exists for a bottleneck/date.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1900
diff changeset
108 fmt.Errorf("sounding result for date %s already exists at bottleneck '%s'",
df6383831ad3 Sounding result import: Be more verbose if sounding result already exists for a bottleneck/date.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1900
diff changeset
109 m.Date.Time.Format(common.DateFormat),
df6383831ad3 Sounding result import: Be more verbose if sounding result already exists for a bottleneck/date.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1900
diff changeset
110 m.Bottleneck))
1297
1c0c9190fcf2 Import sounding result: If uploading a zip with a meta.json which has
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1224
diff changeset
111 }
1c0c9190fcf2 Import sounding result: If uploading a zip with a meta.json which has
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1224
diff changeset
112
1224
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
113 return errs
bc4b642c8d04 Started with an upload sounding result to temp upload area.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
114 }