Mercurial > gemma
changeset 2976:ac5ba5a0e963 unified_import
merge with default
| author | Thomas Junk <thomas.junk@intevation.de> |
|---|---|
| date | Tue, 09 Apr 2019 08:51:02 +0200 |
| parents | 2a29bf8776d0 (current diff) 7c301ff449bc (diff) |
| children | ab26fb7a76f6 |
| files | |
| diffstat | 7 files changed, 505 insertions(+), 155 deletions(-) [+] |
line wrap: on
line diff
--- a/client/src/components/Maplayer.vue Mon Apr 08 17:17:01 2019 +0200 +++ b/client/src/components/Maplayer.vue Tue Apr 09 08:51:02 2019 +0200 @@ -49,7 +49,6 @@ name: "maplayer", data() { return { - projection: "EPSG:3857", splitscreen: false }; }, @@ -77,25 +76,28 @@ methods: { buildVectorLoader( featureRequestOptions, - endpoint, vectorSource, + bboxStrategyDisabled, featurePostProcessor ) { // build a function to be used for VectorSource.setLoader() // make use of WFS().writeGetFeature to build the request // and use our HTTP library to actually do it - // NOTE: a) the geometryName has to be given in featureRequestOptions, - // because we want to load depending on the bbox - // b) the VectorSource has to have the option strategy: bbox - featureRequestOptions["outputFormat"] = "application/json"; - var loader = function(extent, resolution, projection) { - featureRequestOptions["bbox"] = extent; - featureRequestOptions["srsName"] = projection.getCode(); - var featureRequest = new WFS().writeGetFeature(featureRequestOptions); - // DEBUG console.log(featureRequest); + // NOTE: the geometryName has to be given in featureRequestOptions if + // bboxStrategy (default) is used + featureRequestOptions.featureNS = "gemma"; + featureRequestOptions.featurePrefix = "gemma"; + featureRequestOptions.outputFormat = "application/json"; + return (extent, resolution, projection) => { + if (!bboxStrategyDisabled) { + featureRequestOptions.bbox = extent; + } + featureRequestOptions.srsName = projection.getCode(); HTTP.post( - endpoint, - new XMLSerializer().serializeToString(featureRequest), + "/internal/wfs", + new XMLSerializer().serializeToString( + new WFS().writeGetFeature(featureRequestOptions) + ), { headers: { "X-Gemma-Auth": localStorage.getItem("token"), @@ -104,27 +106,18 @@ } ) .then(response => { - var features = new GeoJSON().readFeatures( + const features = new GeoJSON().readFeatures( JSON.stringify(response.data) ); if (featurePostProcessor) { features.map(f => featurePostProcessor(f)); } vectorSource.addFeatures(features); - // console.log( - // "loaded", - // features.length, - // featureRequestOptions.featureTypes, - // "features" - // ); - // DEBUG console.log("loaded ", features, "for", vectorSource); - // eslint-disable-next-line }) .catch(() => { vectorSource.removeLoadedExtent(extent); }); }; - return loader; }, updateBottleneckFilter(bottleneck_id, datestr) { const exists = bottleneck_id != "does_not_exist"; @@ -183,7 +176,7 @@ center: [this.extent.lon, this.extent.lat], minZoom: 5, // restrict zooming out to ~size of Europe for width 1000px zoom: this.extent.zoom, - projection: this.projection + projection: "EPSG:3857" }) }); map.on("moveend", event => { @@ -228,58 +221,46 @@ // TODO make display of layers more dynamic, e.g. from a list - // load different fairway dimension layers (level of service) - [ - "FAIRWAYDIMENSIONSLOS1", - "FAIRWAYDIMENSIONSLOS2", - "FAIRWAYDIMENSIONSLOS3" - ].forEach((los, i) => { - // loading the full WFS layer without bboxStrategy - var source = this.layers[los].getSource(); - var loader = function() { - var featureRequest = new WFS().writeGetFeature({ - srsName: "EPSG:3857", - featureNS: "gemma", - featurePrefix: "gemma", + this.layers.FAIRWAYDIMENSIONSLOS1.getSource().setLoader( + this.buildVectorLoader( + { featureTypes: ["fairway_dimensions"], - outputFormat: "application/json", - filter: equalTo("level_of_service", i + 1) - }); + filter: equalTo("level_of_service", 1) + }, + this.layers.FAIRWAYDIMENSIONSLOS1.getSource(), + true + ) + ); - featureRequest["outputFormat"] = "application/json"; - // NOTE: loading the full fairway_dimensions makes sure - // that all are available for the intersection with the profile - HTTP.post( - "/internal/wfs", - new XMLSerializer().serializeToString(featureRequest), - { - headers: { - "X-Gemma-Auth": localStorage.getItem("token"), - "Content-type": "text/xml; charset=UTF-8" - } - } - ).then(response => { - source.addFeatures( - new GeoJSON().readFeatures(JSON.stringify(response.data)) - ); - // would scale to the extend of all resulting features - // this.openLayersMap.getView().fit(vectorSrc.getExtent()); - }); - }; + this.layers.FAIRWAYDIMENSIONSLOS2.getSource().setLoader( + this.buildVectorLoader( + { + featureTypes: ["fairway_dimensions"], + filter: equalTo("level_of_service", 2) + }, + this.layers.FAIRWAYDIMENSIONSLOS2.getSource(), + true + ) + ); - this.layers[los].getSource().setLoader(loader); - }); + this.layers.FAIRWAYDIMENSIONSLOS3.getSource().setLoader( + this.buildVectorLoader( + { + featureTypes: ["fairway_dimensions"], + filter: equalTo("level_of_service", 3) + }, + this.layers.FAIRWAYDIMENSIONSLOS3.getSource(), + true + ) + ); // load following layers with bboxStrategy (using our request builder) this.layers.WATERWAYAREA.getSource().setLoader( this.buildVectorLoader( { - featureNS: "gemma", - featurePrefix: "gemma", featureTypes: ["waterway_area"], geometryName: "area" }, - "/internal/wfs", this.layers.WATERWAYAREA.getSource() ) ); @@ -287,12 +268,9 @@ this.layers.WATERWAYAXIS.getSource().setLoader( this.buildVectorLoader( { - featureNS: "gemma", - featurePrefix: "gemma", featureTypes: ["waterway_axis"], geometryName: "wtwaxs" }, - "/internal/wfs", this.layers.WATERWAYAXIS.getSource() ) ); @@ -300,12 +278,9 @@ this.layers.WATERWAYPROFILES.getSource().setLoader( this.buildVectorLoader( { - featureNS: "gemma", - featurePrefix: "gemma", featureTypes: ["waterway_profiles"], geometryName: "geom" }, - "/internal/wfs", this.layers.WATERWAYPROFILES.getSource() ) ); @@ -313,12 +288,9 @@ this.layers.DISTANCEMARKS.getSource().setLoader( this.buildVectorLoader( { - featureNS: "gemma", - featurePrefix: "gemma", featureTypes: ["distance_marks_ashore_geoserver"], geometryName: "geom" }, - "/internal/wfs", this.layers.DISTANCEMARKS.getSource() ) ); @@ -326,12 +298,9 @@ this.layers.DISTANCEMARKSAXIS.getSource().setLoader( this.buildVectorLoader( { - featureNS: "gemma", - featurePrefix: "gemma", featureTypes: ["distance_marks_geoserver"], geometryName: "geom" }, - "/internal/wfs", this.layers.DISTANCEMARKSAXIS.getSource() ) ); @@ -339,12 +308,9 @@ this.layers.GAUGES.getSource().setLoader( this.buildVectorLoader( { - featureNS: "gemma", - featurePrefix: "gemma", featureTypes: ["gauges_geoserver"], geometryName: "geom" }, - "/internal/wfs", this.layers.GAUGES.getSource() ) ); @@ -352,12 +318,9 @@ this.layers.STRETCHES.getSource().setLoader( this.buildVectorLoader( { - featureNS: "gemma", - featurePrefix: "gemma", featureTypes: ["stretches_geoserver"], geometryName: "area" }, - "/internal/wfs", this.layers.STRETCHES.getSource(), f => { if (f.getId() === this.selectedStretchId) { @@ -371,12 +334,9 @@ this.layers.BOTTLENECKSTATUS.getSource().setLoader( this.buildVectorLoader( { - featureNS: "gemma", - featurePrefix: "gemma", featureTypes: ["bottlenecks_geoserver"], geometryName: "area" }, - "/internal/wfs", this.layers.BOTTLENECKSTATUS.getSource() ) ); @@ -384,12 +344,9 @@ this.layers.BOTTLENECKS.getSource().setLoader( this.buildVectorLoader( { - featureNS: "gemma", - featurePrefix: "gemma", featureTypes: ["bottlenecks_geoserver"], geometryName: "area" }, - "/internal/wfs", this.layers.BOTTLENECKS.getSource() ) ); @@ -422,8 +379,6 @@ console.log(error); }); - // so none is shown - this.updateBottleneckFilter("does_not_exist", "1999-10-01"); this.$store.dispatch("map/disableIdentifyTool"); this.$store.dispatch("map/enableIdentifyTool"); }
--- a/client/src/components/Pdftool.vue Mon Apr 08 17:17:01 2019 +0200 +++ b/client/src/components/Pdftool.vue Tue Apr 09 08:51:02 2019 +0200 @@ -791,7 +791,7 @@ if ( this.selectedBottleneck && this.selectedSurvey && - this.BOTTLENECKISOLINE.getVisible() + this.layers.BOTTLENECKISOLINE.getVisible() ) { let survey = this.selectedSurvey;
--- a/client/src/components/layers/Layerselect.vue Mon Apr 08 17:17:01 2019 +0200 +++ b/client/src/components/layers/Layerselect.vue Tue Apr 09 08:51:02 2019 +0200 @@ -15,10 +15,10 @@ {{ label }} </label> </div> - <div v-if="layer.getVisible() && isBottleneckIsolineLayer"> + <div v-if="layer.getVisible() && layer === layers.BOTTLENECKISOLINE"> <img class="rounded my-1 d-block" :src="isolinesLegendImgDataURL" /> </div> - <div v-if="layer.getVisible() && isBottleneckDifferences"> + <div v-if="layer.getVisible() && layer === layers.DIFFERENCES"> <img class="rounded my-1 d-block" :src="differencesLegendImgDataURL" /> </div> </div> @@ -40,12 +40,13 @@ * * Bernhard Reiter <bernhard.reiter@intevation.de> */ import { HTTP } from "@/lib/http"; +import { displayError } from "@/lib/errors"; import { mapState } from "vuex"; export default { props: ["layer"], components: { - LegendElement: () => import("./LegendElement.vue") + LegendElement: () => import("./LegendElement") }, computed: { ...mapState("map", [ @@ -53,12 +54,6 @@ "isolinesLegendImgDataURL", "differencesLegendImgDataURL" ]), - isBottleneckIsolineLayer() { - return this.layer == this.layers.BOTTLENECKISOLINE; - }, - isBottleneckDifferences() { - return this.layer == this.layers.DIFFERENCES; - }, label() { return this.$gettext(this.layer.get("label")); } @@ -66,46 +61,45 @@ methods: { visibilityToggled() { this.layer.setVisible(!this.layer.getVisible()); + }, + loadLegendImage(layer, storeTarget) { + HTTP.get( + `/internal/wms?REQUEST=GetLegendGraphic&VERSION=1.0.0&FORMAT=image/png&WIDTH=20&HEIGHT=20&LAYER=${layer}&legend_options=columns:4;fontAntiAliasing:true`, + { + headers: { + Accept: "image/png", + "X-Gemma-Auth": localStorage.getItem("token") + }, + responseType: "blob" + } + ) + .then(response => { + const reader = new FileReader(); + reader.onload = event => { + this.$store.commit("map/" + storeTarget, event.target.result); + }; + reader.readAsDataURL(response.data); + }) + .catch(error => { + displayError({ + title: this.$gettext("Backend Error"), + message: `${error.response.status}: ${error.response.statusText}` + }); + }); } }, created() { - // fetch legend image for bottleneck isolines - // directly read it as dataURL so it is reusable later - if (this.isBottleneckIsolineLayer) { - const src = - "/internal/wms?REQUEST=GetLegendGraphic&VERSION=1.0.0&FORMAT=image/png&WIDTH=20&HEIGHT=20&LAYER=sounding_results_contour_lines_geoserver&legend_options=columns:4;fontAntiAliasing:true"; - HTTP.get(src, { - headers: { - Accept: "image/png", - "X-Gemma-Auth": localStorage.getItem("token") - }, - responseType: "blob" - }).then(response => { - var that = this; - const reader = new FileReader(); - reader.onload = function() { - that.$store.commit("map/isolinesLegendImgDataURL", this.result); - }; - reader.readAsDataURL(response.data); - }); + if (this.layer === this.layers.BOTTLENECKISOLINE) { + this.loadLegendImage( + "sounding_results_contour_lines_geoserver", + "isolinesLegendImgDataURL" + ); } - if (this.isBottleneckDifferences) { - const src = - "/internal/wms?REQUEST=GetLegendGraphic&VERSION=1.0.0&FORMAT=image/png&WIDTH=20&HEIGHT=20&LAYER=sounding_differences&legend_options=columns:4;fontAntiAliasing:true"; - HTTP.get(src, { - headers: { - Accept: "image/png", - "X-Gemma-Auth": localStorage.getItem("token") - }, - responseType: "blob" - }).then(response => { - var that = this; - const reader = new FileReader(); - reader.onload = function() { - that.$store.commit("map/differencesLegendImgDataURL", this.result); - }; - reader.readAsDataURL(response.data); - }); + if (this.layer === this.layers.DIFFERENCES) { + this.loadLegendImage( + "sounding_differences", + "differencesLegendImgDataURL" + ); } } };
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/pkg/controllers/bottlenecks.go Tue Apr 09 08:51:02 2019 +0200 @@ -0,0 +1,379 @@ +// This is Free Software under GNU Affero General Public License v >= 3.0 +// without warranty, see README.md and license for details. +// +// SPDX-License-Identifier: AGPL-3.0-or-later +// License-Filename: LICENSES/AGPL-3.0.txt +// +// Copyright (C) 2019 by via donau +// – Österreichische Wasserstraßen-Gesellschaft mbH +// Software engineering by Intevation GmbH +// +// Author(s): +// * Sascha L. Teichmann <sascha.teichmann@intevation.de> + +package controllers + +import ( + "database/sql" + "fmt" + "log" + "net/http" + "sort" + "strconv" + "strings" + "time" + + "gemma.intevation.de/gemma/pkg/common" + "github.com/gorilla/mux" +) + +const ( + selectAvailableDepthSQL = ` +WITH data AS ( + SELECT + efa.measure_date, + efa.available_depth_value, + efa.water_level_value + FROM waterway.effective_fairway_availability efa + JOIN waterway.fairway_availability fa + ON efa.fairway_availability_id = fa.id + JOIN waterway.bottlenecks bn + ON fa.bottleneck_id = bn.id + WHERE + bn.objnam = $1 AND + efa.level_of_service = $2 AND + efa.measure_type = 'Measured' AND + efa.available_depth_value IS NOT NULL AND + efa.water_level_value IS NOT NULL +), +before AS ( + SELECT * FROM data WHERE measure_date < $3 + ORDER BY measure_date DESC LIMIT 1 +), +inside AS ( + SELECT * FROM data WHERE measure_date BETWEEN $3 AND $4 +), +after AS ( + SELECT * FROM data WHERE measure_date > $4 + ORDER BY measure_date LIMIT 1 +) +SELECT * FROM before +UNION ALL +SELECT * FROM inside +UNION ALL +SELECT * FROM after +ORDER BY measure_date +` + + selectGaugeLevelsSQL = ` +SELECT + grwl.depth_reference, + grwl.value +FROM waterway.gauges_reference_water_levels grwl JOIN + waterway.bottlenecks bns + ON bns.fk_g_fid = grwl.gauge_id +WHERE bns.objnam = $1 AND ( + grwl.depth_reference like 'HDC%' OR + grwl.depth_reference like 'LDC%' OR + grwl.depth_reference like 'MW%' +) +` +) + +type ( + availReferenceValue struct { + level int + value int + } + + availMeasurement struct { + when time.Time + depth int + value int + } +) + +func min(a, b int) int { + if a < b { + return a + } + return b +} + +func max(a, b int) int { + if a > b { + return a + } + return b +} + +func interpolate( + m1, m2 *availMeasurement, + diff time.Duration, +) int { + tdiff := m2.when.Sub(m1.when) + + // f(0) = m1.value + // f(tdiff) = m2.value + // f(x) = m*x + b + // m1.value = m*0 + b <=> b = m1.value + // m2.value = m*tdiff + b <=> m = (m2.value - b)/tdiff + // f(diff) = diff*(m2.value - m1.value)/tdiff + m1.value + + return int(diff*time.Duration(m2.value-m1.value)/tdiff) + m1.value +} + +func classifyAvailMeasurements( + from, to time.Time, + measurements []availMeasurement, + classes []availReferenceValue, +) []time.Duration { + + results := make([]time.Duration, len(classes)+2) + + if from.Before(measurements[0].when) { + results[len(results)-1] = measurements[0].when.Sub(from) + from = measurements[0].when + } + + if to.After(measurements[len(measurements)-1].when) { + results[len(results)-1] += to.Sub(measurements[len(measurements)-1].when) + to = measurements[len(measurements)-1].when + } + + for i := 0; i < len(measurements)-1; i++ { + p1 := &measurements[i] + p2 := &measurements[i+1] + tdiff := p2.when.Sub(p1.when) + if tdiff <= 0 { + continue + } + + if from.After(p2.when) || to.Before(p1.when) { + continue + } + + if from.After(p1.when) { + tdiff2 := from.Sub(p1.when) + vf := interpolate(p1, p2, tdiff2) + p1 = &availMeasurement{when: from, value: vf} + tdiff = p2.when.Sub(from) + } + + if to.Before(p2.when) { + tdiff2 := p2.when.Sub(to) + vt := interpolate(p1, p2, tdiff2) + p2 = &availMeasurement{when: to, value: vt} + tdiff = p2.when.Sub(p1.when) + } + + if max(p1.value, p2.value) <= classes[0].value { + results[0] += tdiff + continue + } + if min(p1.value, p2.value) > classes[len(classes)-1].value { + results[len(results)-2] += tdiff + continue + } + + // TODO: Do the real classes. + } + + return results +} + +func bottleneckAvailabilty( + _ interface{}, + req *http.Request, + conn *sql.Conn, +) (jr JSONResult, err error) { + bn := mux.Vars(req)["objnam"] + + if bn == "" { + err = JSONError{ + Code: http.StatusBadRequest, + Message: "Missing objnam of bottleneck", + } + return + } + + ctx := req.Context() + + loadReferenceValues := func() ([]availReferenceValue, error) { + rows, err := conn.QueryContext(ctx, selectGaugeLevelsSQL, bn) + if err != nil { + return nil, err + } + defer rows.Close() + + var levels []availReferenceValue + + loop: + for rows.Next() { + var what string + var value int + if err := rows.Scan(&what, &value); err != nil { + return nil, err + } + var level int + switch { + case strings.HasPrefix(what, "LDC"): + level = 0 + case strings.HasPrefix(what, "MW"): + level = 1 + case strings.HasPrefix(what, "HDC"): + level = 2 + default: + return nil, fmt.Errorf("Unexpected reference level type '%s'", what) + } + for i := range levels { + if levels[i].level == level { + levels[i].value = value + continue loop + } + } + levels = append(levels, availReferenceValue{level: level, value: value}) + } + + if err := rows.Err(); err != nil { + return nil, err + } + + sort.Slice(levels, func(i, j int) bool { return levels[i].level < levels[j].level }) + + return levels, nil + } + + var refVals []availReferenceValue + if refVals, err = loadReferenceValues(); err != nil { + return + } + + if len(refVals) == 0 { + err = JSONError{ + Code: http.StatusNotFound, + Message: "No gauge reference values found for bottleneck", + } + } + + var from, to time.Time + + if f := req.FormValue("from"); f != "" { + if from, err = time.Parse(common.TimeFormat, f); err != nil { + err = JSONError{ + Code: http.StatusBadRequest, + Message: fmt.Sprintf("Invalid time format for 'from' field: %v", err), + } + return + } + from = from.UTC() + } else { + from = time.Now().AddDate(-1, 0, 0).UTC() + } + + if t := req.FormValue("to"); t != "" { + if to, err = time.Parse(common.TimeFormat, t); err != nil { + err = JSONError{ + Code: http.StatusBadRequest, + Message: fmt.Sprintf("Invalid time format for 'from' field: %v", err), + } + return + } + to = to.UTC() + } else { + to = from.AddDate(1, 0, 0).UTC() + } + + if to.Before(from) { + to, from = from, to + } + + log.Printf("info: time interval: (%v - %v)\n", from, to) + + var los int + if l := req.FormValue("los"); l != "" { + if los, err = strconv.Atoi(l); err != nil { + err = JSONError{ + Code: http.StatusBadRequest, + Message: fmt.Sprintf("Invalid value for field 'los': %v", err), + } + return + } + } else { + los = 1 + } + + loadDepthValues := func() ([]availMeasurement, error) { + + rows, err := conn.QueryContext( + ctx, selectAvailableDepthSQL, bn, los, from, to) + if err != nil { + return nil, err + } + defer rows.Close() + + var ms []availMeasurement + + for rows.Next() { + var m availMeasurement + if err := rows.Scan(&m.when, &m.depth, &m.value); err != nil { + return nil, err + } + m.when = m.when.UTC() + ms = append(ms, m) + } + + if err := rows.Err(); err != nil { + return nil, err + } + + return ms, nil + } + + var ms []availMeasurement + + if ms, err = loadDepthValues(); err != nil { + return + } + + if len(ms) == 0 { + err = JSONError{ + Code: http.StatusNotFound, + Message: "No available fairway depth values found", + } + return + } + + // TODO: Calculate the ranges. + + type outputLevel struct { + Level string `json:"level"` + Value int `json:"value"` + } + + type output struct { + Levels []outputLevel `json:"levels"` + } + + out := output{} + + for i := range refVals { + var level string + switch refVals[i].level { + case 0: + level = "LDC" + case 1: + level = "MW" + case 2: + level = "HDC" + } + out.Levels = append(out.Levels, outputLevel{ + Level: level, + Value: refVals[i].value, + }) + } + + jr = JSONResult{Result: &out} + + return +}
--- a/pkg/controllers/routes.go Mon Apr 08 17:17:01 2019 +0200 +++ b/pkg/controllers/routes.go Tue Apr 09 08:51:02 2019 +0200 @@ -298,6 +298,9 @@ })).Methods(http.MethodPut) // Handler to serve data to the client. + api.Handle("/data/bottleneck/availability/{objnam}", any(&JSONHandler{ + Handle: bottleneckAvailabilty, + })).Methods(http.MethodGet) api.Handle("/data/waterlevels/{gauge}", any( middleware.DBConn(http.HandlerFunc(waterlevels)))).Methods(http.MethodGet)
--- a/pkg/imports/sr.go Mon Apr 08 17:17:01 2019 +0200 +++ b/pkg/imports/sr.go Tue Apr 09 08:51:02 2019 +0200 @@ -431,18 +431,32 @@ var hasNegZ bool + const maxWarnings = 100 + var warnings int + + warn := func(format string, args ...interface{}) { + if warnings++; warnings <= maxWarnings { + feedback.Warn(format, args...) + } + } + defer func() { + if warnings > maxWarnings { + feedback.Warn("Too many warnings. %d ignored.", warnings-maxWarnings) + } + }() + for line := 1; s.Scan(); line++ { text := s.Text() var p octree.Vertex // fmt.Sscanf(text, "%f,%f,%f") is 4 times slower. idx := strings.IndexByte(text, ',') if idx == -1 { - feedback.Warn("format error in line %d", line) + warn("format error in line %d", line) continue } var err error if p.X, err = strconv.ParseFloat(text[:idx], 64); err != nil { - feedback.Warn("format error in line %d: %v", line, err) + warn("format error in line %d: %v", line, err) continue } text = text[idx+1:] @@ -451,19 +465,19 @@ continue } if p.Y, err = strconv.ParseFloat(text[:idx], 64); err != nil { - feedback.Warn("format error in line %d: %v", line, err) + warn("format error in line %d: %v", line, err) continue } text = text[idx+1:] if p.Z, err = strconv.ParseFloat(text, 64); err != nil { - feedback.Warn("format error in line %d: %v", line, err) + warn("format error in line %d: %v", line, err) continue } if p.Z < 0 { p.Z = -p.Z if !hasNegZ { hasNegZ = true - feedback.Warn("Negative Z value found: Using -Z") + warn("Negative Z value found: Using -Z") } } mpz = append(mpz, p)
--- a/pkg/models/sr.go Mon Apr 08 17:17:01 2019 +0200 +++ b/pkg/models/sr.go Tue Apr 09 08:51:02 2019 +0200 @@ -36,7 +36,12 @@ const ( checkDepthReferenceSQL = ` -SELECT true FROM depth_references WHERE depth_reference = $1` +SELECT EXISTS(SELECT 1 + FROM waterway.bottlenecks bn + JOIN waterway.gauges g ON g.location = bn.fk_g_fid + JOIN waterway.gauges_reference_water_levels rl ON rl.gauge_id = g.location + WHERE bn.objnam = $1 + AND rl.depth_reference = $2)` checkBottleneckSQL = ` SELECT true FROM waterway.bottlenecks WHERE objnam = $1` @@ -61,18 +66,6 @@ var b bool err := conn.QueryRowContext(ctx, - checkDepthReferenceSQL, - m.DepthReference).Scan(&b) - switch { - case err == sql.ErrNoRows: - errs = append(errs, fmt.Errorf("unknown depth reference '%s'", m.DepthReference)) - case err != nil: - errs = append(errs, err) - case !b: - errs = append(errs, errors.New("unexpected depth reference")) - } - - err = conn.QueryRowContext(ctx, checkBottleneckSQL, m.Bottleneck).Scan(&b) switch { @@ -85,6 +78,18 @@ } err = conn.QueryRowContext(ctx, + checkDepthReferenceSQL, + m.Bottleneck, + m.DepthReference).Scan(&b) + switch { + case !b: + errs = append(errs, + fmt.Errorf("unknown depth reference '%s'", m.DepthReference)) + case err != nil: + errs = append(errs, err) + } + + err = conn.QueryRowContext(ctx, checkBottleneckDateUniqueSQL, m.Bottleneck, m.Date.Time).Scan(&b) switch {