* * Markus Kottländer * * Fadi Abbud + * * Bernhard Reiter */ import * as d3 from "d3"; import app from "@/main"; @@ -103,7 +115,8 @@ }, templateData: null, templates: [], - defaultTemplate: defaultDiagramTemplate + defaultTemplate: defaultDiagramTemplate, + showNumbers: false }; }, created() { @@ -477,6 +490,28 @@ .attr("fill", (d, i) => { return this.$options.AFDCOLORS[i]; }); + if (this.showNumbers) { + afd + .selectAll("text") + .data([data.above, data.between, data.below]) + .enter() + .append("text") + .attr("fill", "black") + .attr("text-anchor", "middle") + .attr("x", ldcWidth / 2) + .attr("y", (d, i) => { + let h = d; // i == 0 + if (i > 0) { + h += data.above; + } + if (i > 1) { + h += data.between; + } + return yScale(h + 0.8) + this.paddingTop; + }) + .text(d => (d > 0 ? Math.round(d) : "")) + .attr("font-size", "8"); + } }, drawLNWL(data, i, diagram, spaceBetween, widthPerItem, ldcWidth, yScale) { let lnwl = diagram @@ -510,17 +545,24 @@ //d3.event.pageX gives coordinates relative to SVG //dy gives offset of svg on page }) - .attr("height", d => { - return yScale(0) - yScale(d); - }) - .attr("y", d => { - return yScale(d); - }) + .attr("height", d => yScale(0) - yScale(d)) + .attr("y", d => yScale(d)) .attr("transform", `translate(0 ${this.paddingTop})`) .attr("width", ldcWidth) .attr("fill", () => { return this.$options.LWNLCOLORS.LDC; }); + if (this.showNumbers) { + // we do not need to bind data or a datum as the forEach in drawBars + // already brings us only one datum in data + lnwl + .append("text") + .attr("y", yScale(data.ldc + 0.8) + this.paddingTop) + .text(data.ldc) + .attr("text-anchor", "left") + .attr("fill", "black") + .attr("font-size", "8"); + } }, drawScaleLabel({ diagram, dimensions }) { diagram @@ -530,6 +572,7 @@ .attr("x", 0) .attr("y", 0) .attr("dy", "20") + .attr("fill", "black") // translate a few mm to the right to allow for slightly higher letters .attr( "transform", @@ -603,6 +646,9 @@ watch: { fwLNWLData() { this.drawDiagram(); + }, + showNumbers() { + this.drawDiagram(); } }, LEGEND: app.$gettext("Percent"), diff -r 0c5829ff4dea -r 970e90d3d5eb client/src/components/identify/Identify.vue --- a/client/src/components/identify/Identify.vue Mon Sep 09 17:16:41 2019 +0200 +++ b/client/src/components/identify/Identify.vue Mon Sep 09 17:24:07 2019 +0200 @@ -64,15 +64,29 @@ v-if="showGaugeMeta(feature)" class="ml-2 mb-1 text-left d-flex flex-column" > -

- +

+ + +

{{ gmAvailability(feature) }} @@ -227,7 +241,9 @@ data() { return { refGaugeStatus: "", - gaugeStatus: "" + gaugeStatus: "", + gaugeCoeffs: null, + refGaugeCoeffs: null }; }, computed: { @@ -248,10 +264,17 @@ gaugeStatusText() { const nsc24 = this.config.gm_forecast_vs_reality_nsc_24h; const nsc72 = this.config.gm_forecast_vs_reality_nsc_72h; + const coeffWarn = this.gaugeCoeffs ? this.gaugeCoeffs[2].value : ""; + const coeffDanger = this.gaugeCoeffs ? this.gaugeCoeffs[0].value : ""; const messagesPerState = { - OK: this.$gettext("Nash-Sutcliffe") + `>${nsc24} /24h >${nsc72} / 72h`, - WARNING: this.$gettext("Nash-Sutcliffe") + ` < ${nsc72}`, - DANGER: this.$gettext("Nash-Sutcliffe") + ` < ${nsc24}`, + OK: + this.$gettext("Nash-Sutcliffe") + + `(${coeffDanger} >${nsc24} /24h ${coeffWarn} >${nsc72} / 72h)`, + WARNING: + this.$gettext("Nash-Sutcliffe") + ` (${coeffWarn} < ${nsc72} / 72h)`, + DANGER: + this.$gettext("Nash-Sutcliffe") + + ` (${coeffDanger} < ${nsc24} / 72h)`, NEUTRAL: this.$gettext("Nash-Sutcliffe not available") }; return messagesPerState[this.gaugeStatus]; @@ -262,10 +285,19 @@ refGaugeStatusText() { const nsc24 = this.config.gm_forecast_vs_reality_nsc_24h; const nsc72 = this.config.gm_forecast_vs_reality_nsc_72h; + const coeffWarn = this.refGaugeCoeffs ? this.refGaugeCoeffs[2].value : ""; + const coeffDanger = this.refGaugeCoeffs + ? this.refGaugeCoeffs[0].value + : ""; const messagesPerState = { - OK: this.$gettext("Nash-Sutcliffe") + `>${nsc24} /24h >${nsc72} / 72h`, - WARNING: this.$gettext("Nash-Sutcliffe") + ` < ${nsc72}`, - DANGER: this.$gettext("Nash-Sutcliffe") + ` < ${nsc24}`, + OK: + this.$gettext("Nash-Sutcliffe") + + `(${coeffDanger} >${nsc24} /24h ${coeffWarn} >${nsc72} / 72h)`, + WARNING: + this.$gettext("Nash-Sutcliffe") + ` (${coeffWarn} < ${nsc72} / 72h)`, + DANGER: + this.$gettext("Nash-Sutcliffe") + + ` (${coeffDanger} < ${nsc24} / 72h)`, NEUTRAL: this.$gettext("Nash-Sutcliffe not available") }; return messagesPerState[this.refGaugeStatus]; @@ -284,6 +316,7 @@ this.$store .dispatch("gauges/getNashSutcliffeForISRS", isrs) .then(response => { + this.gaugeCoeffs = response.coeffs; this.gaugeStatus = classifications.calcForecastVsRealityForNSC( response ); @@ -297,6 +330,7 @@ this.$store .dispatch("gauges/getNashSutcliffeForISRS", isrs) .then(response => { + this.refGaugeCoeffs = response.coeffs; this.refGaugeStatus = classifications.calcForecastVsRealityForNSC( response ); @@ -335,12 +369,17 @@ forecastAccuracy(feature) { const offset24 = this.config.gm_forecast_offset_24h; const offset72 = this.config.gm_forecast_offset_72h; + const fa3d = feature.get("forecast_accuracy_3d"); + const fa1d = feature.get("forecast_accuracy_1d"); const messagesPerState = { OK: this.$gettext("Highest confidence") + ` <${offset24} cm/24h, <${offset72} cm/72h`, - WARNING: this.$gettext("Confidence per 72h") + ` > ${offset72} cm`, - DANGER: this.$gettext("Confidence per 24h") + ` > ${offset24} cm` + WARNING: + this.$gettext("Confidence per 72h") + + ` (${fa3d} cm > ${offset72} cm)`, + DANGER: + this.$gettext("Confidence per 24h") + ` (${fa1d} cm > ${offset24} cm)` }; return messagesPerState[[classifications.forecastAccuracy(feature)]]; }, @@ -351,13 +390,17 @@ }, recency(feature) { const revisitingFactor = this.config.bn_revtime_multiplier; + const revisitingTime = feature.get("revisiting_time"); const messagesPerState = { - OK: this.$gettext("Data within the revisiting time"), + OK: + this.$gettext("Data within the revisiting time") + + ` (${revisitingTime}d)`, WARNING: this.$gettext("Data within revisiting treshold") + - ` (${revisitingFactor})`, + ` (${revisitingTime} * ${revisitingFactor})`, DANGER: - this.$gettext("Data too old. Treshold:") + ` (${revisitingFactor})` + this.$gettext("Data too old. Treshold:") + + ` (${revisitingTime}d * ${revisitingFactor})` }; return messagesPerState[classifications.surveyRecency(feature)]; }, diff -r 0c5829ff4dea -r 970e90d3d5eb client/src/components/layers/LegendElement.vue --- a/client/src/components/layers/LegendElement.vue Mon Sep 09 17:16:41 2019 +0200 +++ b/client/src/components/layers/LegendElement.vue Mon Sep 09 17:24:07 2019 +0200 @@ -29,9 +29,11 @@ import Feature from "ol/Feature"; import { Vector as VectorLayer } from "ol/layer"; import { Vector as VectorSource } from "ol/source"; +import Polygon from "ol/geom/Polygon"; import LineString from "ol/geom/LineString"; import Point from "ol/geom/Point"; import { HTTP } from "@/lib/http"; +import { Stroke, Style, Fill } from "ol/style"; export default { props: ["layer"], @@ -135,7 +137,8 @@ recreateLayers() { let vector = this.createVectorLayer(); - this.myMap.removeLayer(this.myMap.getLayers()[0]); + this.myMap.removeLayer(this.myMap.getLayers().getArray()[0]); + this.myMap.addLayer(vector); }, initMap() { @@ -155,11 +158,29 @@ }, createVectorLayer() { let mapStyle = this.layer.getStyle(); - - let feature = new Feature({ - geometry: new LineString([[-1, -1], [0, 0], [1, 1]]) - }); - + let feature; + // show bottleneck legend as polygon + if (this.layer.get("id") === "BOTTLENECKS") { + feature = new Feature({ + geometry: new Polygon([ + [[-1.7, -1.2], [-1.7, 0.5], [1.7, 1.2], [1.7, -0.5]] + ]) + }); + mapStyle = new Style({ + stroke: new Stroke({ + color: mapStyle.getStroke().getColor(), + // reduce the stroke width for better layout in map legend. + width: 2 + }), + fill: new Fill({ + color: mapStyle.getFill().getColor() + }) + }); + } else { + feature = new Feature({ + geometry: new LineString([[-1, -1], [0, 0], [1, 1]]) + }); + } // special case if we need to call the style function with a special // parameter or to detect a point layer let legendStyle = this.layer.get("forLegendStyle"); diff -r 0c5829ff4dea -r 970e90d3d5eb client/src/lib/classifications.js --- a/client/src/lib/classifications.js Mon Sep 09 17:16:41 2019 +0200 +++ b/client/src/lib/classifications.js Mon Sep 09 17:24:07 2019 +0200 @@ -12,6 +12,9 @@ * Raimund Renkert * Markus Kottländer */ + +/*eslint no-unused-vars: ["error", { "varsIgnorePattern": "_" }]*/ + import store from "@/store/index"; const getGauge = f => { @@ -32,18 +35,21 @@ }; const calcForecastVsRealityForNSC = nsc => { - if (nsc && nsc.coeffs.reduce((sum, coeff) => sum + coeff.samples, 0)) { + const hasSamples = + nsc && nsc.coeffs.reduce((sum, coeff) => sum + coeff.samples, 0); + if (hasSamples) { + const [nsc24h, _, nsc72h] = nsc.coeffs; // 24h < configured value if ( - nsc.coeffs[0].samples && - nsc.coeffs[0].value < + nsc24h.samples && + nsc24h.value < store.state.application.config.gm_forecast_vs_reality_nsc_24h ) return "DANGER"; // 72h < configured value if ( - nsc.coeffs[2].samples && - nsc.coeffs[2].value < + nsc72h.samples && + nsc72h.value < store.state.application.config.gm_forecast_vs_reality_nsc_72h ) return "WARNING"; diff -r 0c5829ff4dea -r 970e90d3d5eb client/src/main.js --- a/client/src/main.js Mon Sep 09 17:16:41 2019 +0200 +++ b/client/src/main.js Mon Sep 09 17:24:07 2019 +0200 @@ -53,6 +53,7 @@ faBars, faBook, faCaretUp, + faCaretDown, faChartArea, faChartBar, faChartLine, @@ -124,6 +125,7 @@ faBars, faBook, faCaretUp, + faCaretDown, faChartArea, faChartBar, faChartLine, diff -r 0c5829ff4dea -r 970e90d3d5eb client/src/store/fairwayavailability.js --- a/client/src/store/fairwayavailability.js Mon Sep 09 17:16:41 2019 +0200 +++ b/client/src/store/fairwayavailability.js Mon Sep 09 17:24:07 2019 +0200 @@ -178,12 +178,6 @@ setSelectedFairwayAvailability: (state, feature) => { state.selectedFairwayAvailabilityFeature = feature; }, - setFwLNWLData: (state, fwLNWLData) => { - state.fwLNWLData = fwLNWLData; - }, - setCSV: (state, csv) => { - state.csv = csv; - }, addFwLNWLOverviewData: (state, data) => { let existingIndex = state.fwLNWLOverviewData.findIndex( d => d.feature.get("id") === data.feature.get("id") @@ -212,6 +206,37 @@ }); state.fwData = transformAFD(csv); + }, + // See docs/developers.md for an example how to directly + // call this method for testing. + setAvailableFairwayDepthLNWLData: (state, data) => { + state.csv = data; + + data = data.split("\n").filter(d => d); + data.shift(); // remove header line + data = data.map(d => { + let columns = d.split(","); + let result; + if (columns.length === 6) { + result = { + date: columns[0], + ldc: Number(columns[2]), + below: Number(columns[3]), + between: Number(columns[4]), + above: Number(columns[5]) + }; + } else { + result = { + date: columns[0], + ldc: Number(columns[2]), + below: Number(columns[3]), + between: null, + above: Number(columns[4]) + }; + } + return result; + }); + state.fwLNWLData = data; } }, actions: { @@ -306,34 +331,8 @@ }); }, loadAvailableFairwayDepthLNWLDiagram: ({ commit, dispatch }, options) => { - dispatch("loadAvailableFairwayDepthLNWL", options).then(response => { - commit("setCSV", response); - let data = response.split("\n").filter(d => d); - data.shift(); // remove header line - data = data.map(d => { - let columns = d.split(","); - let result; - if (columns.length === 6) { - result = { - date: columns[0], - ldc: Number(columns[2]), - below: Number(columns[3]), - between: Number(columns[4]), - above: Number(columns[5]) - }; - } else { - result = { - date: columns[0], - ldc: Number(columns[2]), - below: Number(columns[3]), - between: null, - above: Number(columns[4]) - }; - } - return result; - }); - commit("setFwLNWLData", data); - return data; + dispatch("loadAvailableFairwayDepthLNWL", options).then(data => { + commit("setAvailableFairwayDepthLNWLData", data); }); }, loadAvailableFairwayDepthLNWLForMap: ({ dispatch }, options) => { diff -r 0c5829ff4dea -r 970e90d3d5eb pkg/controllers/bottlenecks.go --- a/pkg/controllers/bottlenecks.go Mon Sep 09 17:16:41 2019 +0200 +++ b/pkg/controllers/bottlenecks.go Mon Sep 09 17:24:07 2019 +0200 @@ -10,6 +10,7 @@ // // Author(s): // * Sascha L. Teichmann +// * Sascha Wilde package controllers @@ -131,6 +132,43 @@ } } +// According to clarification, it has to be assumed, that at times +// with no data, the best case (which by convention is the highest +// class created by classify()) should be assumed. That is due to the +// fact, that at times where bottlenecks are not a limiting factor on +// the waterway, services don't provide any data for the bottleneck in +// question. +// +// FIXME: A potential improvement could be to intersect the time +// ranges with the time ranges where bottlenecks were "active" (this +// _might_ be derivable from the validity periods in the bottleneck +// data. So it _might_ be possible do detect actual missing data (BN +// valid, but no data from FA service). Anyway, this is left out for +// now, as many clarification regarding the base assumtions would be +// needed and the results still might be unrelyable. +func optimisticPadClassification( + from, to time.Time, + classified []time.Duration, +) []time.Duration { + + var actualDuration time.Duration + for _, v := range classified { + actualDuration += v + } + + // If the actual duration is smaller than the length + // of the classifaction interval extend the + // time spend in the highest class by the difference. + if delta := to.Sub(from) - actualDuration; delta > 0 { + log.Printf("info: time interval: (%v - %v)\n", from, to) + log.Printf("info: found only data for %.2f hours, padding by %.2f hours\n", + actualDuration.Hours(), delta.Hours()) + classified[len(classified)-1] += delta + } + + return classified +} + func (measurements availMeasurements) classify( from, to time.Time, breaks []float64, @@ -158,7 +196,7 @@ }) if idx >= len(measurements) { - return result + return optimisticPadClassification(from, to, result) } // Be safe for interpolation. @@ -173,7 +211,7 @@ p2 := &measurements[i+1] if p1.when.After(to) { - return result + return optimisticPadClassification(from, to, result) } if p2.when.Before(from) { @@ -214,7 +252,7 @@ } } - return result + return optimisticPadClassification(from, to, result) } func orderTime(a, b time.Time) (time.Time, time.Time) { @@ -482,7 +520,13 @@ interval := intervals[mode](from, to) + now := time.Now() for pfrom, pto, label := interval(); label != ""; pfrom, pto, label = interval() { + // Don't interpolate for the future + if now.Sub(pto) < 0 { + pto = now + } + lnwl := ms.classify( pfrom, pto, ldcRefs, @@ -638,7 +682,12 @@ interval := intervals[mode](from, to) + now := time.Now() for pfrom, pto, label := interval(); label != ""; pfrom, pto, label = interval() { + // Don't interpolate for the future + if now.Sub(pto) < 0 { + pto = now + } ldc := ms.classify( pfrom, pto, diff -r 0c5829ff4dea -r 970e90d3d5eb pkg/controllers/stretches.go --- a/pkg/controllers/stretches.go Mon Sep 09 17:16:41 2019 +0200 +++ b/pkg/controllers/stretches.go Mon Sep 09 17:24:07 2019 +0200 @@ -279,7 +279,13 @@ var ldc, breaks []time.Duration + now := time.Now() for _, bn := range loaded { + // Don't interpolate for the future + if now.Sub(res.to) < 0 { + res.to = now + } + l := bn.measurements.classify( res.from, res.to, bn.ldc, @@ -539,7 +545,13 @@ for res := range jobCh { var ldc, breaks []time.Duration + now := time.Now() for _, bn := range loaded { + // Don't interpolate for the future + if now.Sub(res.to) < 0 { + res.to = now + } + l := bn.measurements.classify( res.from, res.to, bn.ldc, diff -r 0c5829ff4dea -r 970e90d3d5eb pkg/geoserver/boot.go --- a/pkg/geoserver/boot.go Mon Sep 09 17:16:41 2019 +0200 +++ b/pkg/geoserver/boot.go Mon Sep 09 17:24:07 2019 +0200 @@ -114,7 +114,7 @@ // Probe workspace. req, err := http.NewRequest( http.MethodGet, - geoURL+"/rest/workspaces/"+workspaceName+".json", + geoURL+"/rest/workspaces/"+workspaceName+".json?quietOnNotFound=true", nil) if err != nil { return err @@ -170,7 +170,7 @@ req, err := http.NewRequest( http.MethodGet, geoURL+"/rest/workspaces/"+workspaceName+ - "/datastores/"+datastoreName+".json", + "/datastores/"+datastoreName+".json?quietOnNotFound=true", nil) if err != nil { return err diff -r 0c5829ff4dea -r 970e90d3d5eb schema/gemma.sql --- a/schema/gemma.sql Mon Sep 09 17:16:41 2019 +0200 +++ b/schema/gemma.sql Mon Sep 09 17:24:07 2019 +0200 @@ -807,8 +807,8 @@ -- Configure primary keys for geoserver views INSERT INTO waterway.gt_pk_metadata VALUES - ('waterway', 'gauges_geoserver', 'location'), - ('waterway', 'distance_marks_geoserver', 'location_code'), + ('waterway', 'gauges_geoserver', 'isrs_code'), + ('waterway', 'distance_marks_geoserver', 'location'), ('waterway', 'distance_marks_ashore_geoserver', 'id'), ('waterway', 'bottlenecks_geoserver', 'id'), ('waterway', 'stretches_geoserver', 'id'), diff -r 0c5829ff4dea -r 970e90d3d5eb schema/geoserver_views.sql --- a/schema/geoserver_views.sql Mon Sep 09 17:16:41 2019 +0200 +++ b/schema/geoserver_views.sql Mon Sep 09 17:24:07 2019 +0200 @@ -55,13 +55,11 @@ CREATE OR REPLACE VIEW waterway.gauges_geoserver AS SELECT - location, isrs_code, objname, geom, applicability_from_km, applicability_to_km, - validity, zero_point, geodref, date_info, @@ -76,7 +74,7 @@ WHERE NOT erased; CREATE OR REPLACE VIEW waterway.distance_marks_geoserver AS - SELECT location_code, + SELECT isrs_asText(location_code) AS location, geom::Geometry(POINT, 4326), related_enc, @@ -99,7 +97,6 @@ b.bottleneck_id, b.objnam, b.nobjnm, - b.stretch, b.area, b.rb, b.lb, @@ -108,7 +105,6 @@ b.limiting, b.date_info, b.source_organization, - g.location AS gauge_isrs_code, g.objname AS gauge_objname, g.reference_water_levels, fal.date_info AS fa_date_info, @@ -155,7 +151,8 @@ max(g.forecast_accuracy_3d) AS forecast_accuracy_3d, max(g.forecast_accuracy_1d) AS forecast_accuracy_1d FROM waterway.stretches s - LEFT JOIN waterway.gauges_geoserver g ON g.location <@ s.stretch + LEFT JOIN waterway.gauges_geoserver g + ON isrs_fromtext(g.isrs_code) <@ s.stretch GROUP BY s.id; CREATE OR REPLACE VIEW waterway.sections_geoserver AS @@ -175,7 +172,8 @@ max(g.forecast_accuracy_3d) AS forecast_accuracy_3d, max(g.forecast_accuracy_1d) AS forecast_accuracy_1d FROM waterway.sections s - LEFT JOIN waterway.gauges_geoserver g ON g.location <@ s.section + LEFT JOIN waterway.gauges_geoserver g + ON isrs_fromtext(g.isrs_code) <@ s.section GROUP BY s.id; CREATE OR REPLACE VIEW waterway.sounding_results_contour_lines_geoserver AS diff -r 0c5829ff4dea -r 970e90d3d5eb schema/updates/1112/01.cleanup_views.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1112/01.cleanup_views.sql Mon Sep 09 17:24:07 2019 +0200 @@ -0,0 +1,130 @@ +DROP VIEW waterway.gauges_geoserver CASCADE; +CREATE VIEW waterway.gauges_geoserver AS + SELECT + isrs_code, + objname, + geom, + applicability_from_km, + applicability_to_km, + zero_point, + geodref, + date_info, + source_organization, + reference_water_levels, + gm_measuredate, + gm_waterlevel, + gm_n_14d, + forecast_accuracy_3d, + forecast_accuracy_1d + FROM waterway.gauges_base_view + WHERE NOT erased; + +DROP VIEW waterway.distance_marks_geoserver; +CREATE VIEW waterway.distance_marks_geoserver AS + SELECT + isrs_asText(location_code) AS location, + geom::Geometry(POINT, 4326), + related_enc, + (location_code).hectometre + FROM waterway.distance_marks_virtual; + +DROP VIEW waterway.bottlenecks_geoserver; +CREATE OR REPLACE VIEW waterway.bottlenecks_geoserver AS + SELECT + b.id, + b.bottleneck_id, + b.objnam, + b.nobjnm, + b.area, + b.rb, + b.lb, + b.responsible_country, + b.revisiting_time, + b.limiting, + b.date_info, + b.source_organization, + g.objname AS gauge_objname, + g.reference_water_levels, + fal.date_info AS fa_date_info, + fal.critical AS fa_critical, + g.gm_measuredate, + g.gm_waterlevel, + g.gm_n_14d, + srl.date_max, + g.forecast_accuracy_3d, + g.forecast_accuracy_1d + FROM waterway.bottlenecks b + LEFT JOIN waterway.gauges_base_view g + ON b.gauge_location = g.location AND g.validity @> current_timestamp + LEFT JOIN (SELECT DISTINCT ON (bottleneck_id) + bottleneck_id, date_info, critical + FROM waterway.fairway_availability + ORDER BY bottleneck_id, date_info DESC) AS fal + ON b.bottleneck_id = fal.bottleneck_id + LEFT JOIN (SELECT DISTINCT ON (bottleneck_id) + bottleneck_id, max(date_info) AS date_max + FROM waterway.sounding_results + GROUP BY bottleneck_id + ORDER BY bottleneck_id DESC) AS srl + ON b.bottleneck_id = srl.bottleneck_id + WHERE b.validity @> current_timestamp; + +CREATE VIEW waterway.stretches_geoserver AS + SELECT + s.id, + s.name, + (s.stretch).lower::varchar as lower, + (s.stretch).upper::varchar as upper, + s.area::Geometry(MULTIPOLYGON, 4326), + s.objnam, + s.nobjnam, + s.date_info, + s.source_organization, + (SELECT string_agg(country_code, ', ') + FROM waterway.stretch_countries + WHERE stretches_id = s.id) AS countries, + s.staging_done, + min(g.gm_measuredate) AS gm_measuredate, + min(g.gm_n_14d) AS gm_n_14d, + max(g.forecast_accuracy_3d) AS forecast_accuracy_3d, + max(g.forecast_accuracy_1d) AS forecast_accuracy_1d + FROM waterway.stretches s + LEFT JOIN waterway.gauges_geoserver g + ON isrs_fromtext(g.isrs_code) <@ s.stretch + GROUP BY s.id; + +CREATE VIEW waterway.sections_geoserver AS + SELECT + s.id, + s.name, + (s.section).lower::varchar as lower, + (s.section).upper::varchar as upper, + s.area::Geometry(MULTIPOLYGON, 4326), + s.objnam, + s.nobjnam, + s.date_info, + s.source_organization, + s.staging_done, + min(g.gm_measuredate) AS gm_measuredate, + min(g.gm_n_14d) AS gm_n_14d, + max(g.forecast_accuracy_3d) AS forecast_accuracy_3d, + max(g.forecast_accuracy_1d) AS forecast_accuracy_1d + FROM waterway.sections s + LEFT JOIN waterway.gauges_geoserver g + ON isrs_fromtext(g.isrs_code) <@ s.section + GROUP BY s.id; + +UPDATE waterway.gt_pk_metadata SET pk_column = 'isrs_code' + WHERE table_schema = 'waterway' + AND table_name = 'gauges_geoserver'; +UPDATE waterway.gt_pk_metadata SET pk_column = 'location' + WHERE table_schema = 'waterway' + AND table_name = 'distance_marks_geoserver'; + +GRANT SELECT ON + waterway.gauges_geoserver, + waterway.distance_marks_geoserver, + waterway.bottlenecks_geoserver, + waterway.stretches_geoserver, + waterway.sections_geoserver + TO waterway_user; diff -r 0c5829ff4dea -r 970e90d3d5eb schema/version.sql