Mercurial > gemma

changeset 3656:2a079d0a71c1

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Ensure sounding results are associated to matching bottleneck version
author Tom Gottfried <tom@intevation.de>
date Thu, 13 Jun 2019 19:13:42 +0200
parents fb8a53c7c6d3
children 7126fdc5779a
files pkg/controllers/diff.go pkg/controllers/surveys.go pkg/imports/bn.go pkg/imports/sr.go pkg/models/sr.go schema/gemma.sql schema/geoserver_views.sql
diffstat 7 files changed, 50 insertions(+), 22 deletions(-) [+]
line wrap: on
line diff
--- a/pkg/controllers/diff.go	Thu Jun 13 11:20:17 2019 +0200
+++ b/pkg/controllers/diff.go	Thu Jun 13 19:13:42 2019 +0200
@@ -36,7 +36,7 @@
 const (
 	diffIDSQL = `
 SELECT sd.id FROM
-  caching.sounding_differences sd JOIN 
+  caching.sounding_differences sd JOIN
   waterway.sounding_results srm ON sd.minuend = srm.id JOIN
   waterway.sounding_results srs ON sd.subtrahend = srs.id
 WHERE srm.bottleneck_id = srs.bottleneck_id AND
--- a/pkg/controllers/surveys.go	Thu Jun 13 11:20:17 2019 +0200
+++ b/pkg/controllers/surveys.go	Thu Jun 13 19:13:42 2019 +0200
@@ -34,7 +34,7 @@
 FROM waterway.bottlenecks AS b
   JOIN waterway.gauges AS g
     ON b.gauge_location = g.location AND b.gauge_validity = g.validity
-  JOIN waterway.sounding_results AS s ON b.id = s.bottleneck_id
+  JOIN waterway.sounding_results AS s ON b.bottleneck_id = s.bottleneck_id
   LEFT JOIN waterway.gauges_reference_water_levels AS r
     ON s.depth_reference = r.depth_reference
       AND g.location = r.location AND g.validity = r.validity
--- a/pkg/imports/bn.go	Thu Jun 13 11:20:17 2019 +0200
+++ b/pkg/imports/bn.go	Thu Jun 13 19:13:42 2019 +0200
@@ -107,6 +107,14 @@
 )
 RETURNING id`
 
+	moveSRSQL = `
+UPDATE waterway.sounding_results
+-- Associate measurements to matching bottleneck version
+SET bottleneck_validity = $2
+WHERE bottleneck_id = $1
+  AND CAST(date_info AS timestamptz) <@ CAST($2 AS tstzrange)
+`
+
 	fixBNValiditySQL = `
 UPDATE waterway.bottlenecks SET
    -- Set enddate of old entry to new startdate in case of overlap:
@@ -265,7 +273,7 @@
 
 	feedback.Info("Found %d bottlenecks for import", len(bns))
 
-	var hasStmt, insertStmt, fixValidityStmt, updateStmt,
+	var hasStmt, insertStmt, moveSRStmt, fixValidityStmt, updateStmt,
 		deleteMaterialStmt, insertMaterialStmt, trackStmt *sql.Stmt
 
 	for _, x := range []struct {
@@ -274,6 +282,7 @@
 	}{
 		{hasBottleneckSQL, &hasStmt},
 		{insertBottleneckSQL, &insertStmt},
+		{moveSRSQL, &moveSRStmt},
 		{fixBNValiditySQL, &fixValidityStmt},
 		{updateBottleneckSQL, &updateStmt},
 		{deleteBottleneckMaterialSQL, &deleteMaterialStmt},
@@ -294,7 +303,7 @@
 	for _, bn := range bns {
 		if err := storeBottleneck(
 			ctx, importID, conn, feedback, bn, &nids, tolerance,
-			hasStmt, insertStmt, fixValidityStmt, updateStmt,
+			hasStmt, insertStmt, moveSRStmt, fixValidityStmt, updateStmt,
 			deleteMaterialStmt, insertMaterialStmt, trackStmt); err != nil {
 			return nil, err
 		}
@@ -321,7 +330,7 @@
 	bn *ifbn.BottleNeckType,
 	nids *[]string,
 	tolerance float64,
-	hasStmt, insertStmt, fixValidityStmt, updateStmt,
+	hasStmt, insertStmt, moveSRStmt, fixValidityStmt, updateStmt,
 	deleteMaterialStmt, insertMaterialStmt, trackStmt *sql.Stmt,
 ) error {
 	feedback.Info("Processing %s (%s)", bn.OBJNAM, bn.Bottleneck_id)
@@ -503,6 +512,18 @@
 		}
 	}
 
+	// Move sounding results to new matching bottleneck version, if applicable
+	if _, err = tx.StmtContext(ctx, moveSRStmt).ExecContext(ctx,
+		bn.Bottleneck_id,
+		&validity,
+	); err != nil {
+		feedback.Warn(handleError(err).Error())
+		if err2 := tx.Rollback(); err2 != nil {
+			return err2
+		}
+		return nil
+	}
+
 	// Set end of validity of old version to start of new version
 	// in case of overlap
 	if _, err = tx.StmtContext(ctx, fixValidityStmt).ExecContext(ctx,
--- a/pkg/imports/sr.go	Thu Jun 13 11:20:17 2019 +0200
+++ b/pkg/imports/sr.go	Thu Jun 13 19:13:42 2019 +0200
@@ -32,7 +32,6 @@
 	"strings"
 	"time"
 
-	"github.com/jackc/pgx"
 	shp "github.com/jonas-p/go-shp"
 
 	"gemma.intevation.de/gemma/pkg/common"
@@ -110,12 +109,13 @@
 	insertHullSQL = `
 INSERT INTO waterway.sounding_results (
   bottleneck_id,
+  bottleneck_validity,
   date_info,
   depth_reference,
   area
 ) SELECT
-  (SELECT id FROM waterway.bottlenecks WHERE objnam = $1
-     AND validity @> CAST($2 AS timestamptz)),
+  bottleneck_id,
+  validity,
   $2::date,
   $3,
   (SELECT
@@ -124,6 +124,8 @@
     ELSE
       ST_Transform(ST_GeomFromWKB($5, $6::integer), 4326)::geography
     END)
+FROM waterway.bottlenecks
+WHERE objnam = $1 AND validity @> CAST($2 AS timestamptz)
 RETURNING
   id,
   ST_X(ST_Centroid(area::geometry)),
@@ -419,15 +421,11 @@
 	)
 	xyz, boundary = nil, nil // not need from now on.
 	feedback.Info("Calculating hull took %s.", time.Since(start))
-	if err != nil {
-		if e, isPgErr := err.(pgx.PgError); isPgErr &&
-			e.Code == notNullViolation &&
-			e.SchemaName == "waterway" &&
-			e.TableName == "sounding_results" &&
-			e.ColumnName == "bottleneck_id" {
-			return nil, fmt.Errorf(
-				"No bottleneck matching given name and time available")
-		}
+	switch {
+	case err == sql.ErrNoRows:
+		return nil, fmt.Errorf(
+			"No bottleneck matching given name and time available")
+	case err != nil:
 		return nil, err
 	}
 	feedback.Info("Best suited UTM EPSG: %d", epsg)
--- a/pkg/models/sr.go	Thu Jun 13 11:20:17 2019 +0200
+++ b/pkg/models/sr.go	Thu Jun 13 19:13:42 2019 +0200
@@ -51,7 +51,7 @@
 
 	checkBottleneckDateUniqueSQL = `
 SELECT true FROM waterway.sounding_results sr JOIN
-  waterway.bottlenecks bn ON sr.bottleneck_id = bn.id
+  waterway.bottlenecks bn ON sr.bottleneck_id = bn.bottleneck_id
 WHERE bn.objnam = $1 AND sr.date_info = $2`
 )
 
--- a/schema/gemma.sql	Thu Jun 13 11:20:17 2019 +0200
+++ b/schema/gemma.sql	Thu Jun 13 19:13:42 2019 +0200
@@ -489,6 +489,7 @@
         id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
         bottleneck_id varchar NOT NULL,
         validity tstzrange NOT NULL CHECK (NOT isempty(validity)),
+        UNIQUE (bottleneck_id, validity),
         EXCLUDE USING GiST (bottleneck_id WITH =, validity WITH &&)
             DEFERRABLE INITIALLY DEFERRED,
         gauge_location isrs NOT NULL,
@@ -535,8 +536,15 @@
 
     CREATE TABLE sounding_results (
         id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
-        bottleneck_id int NOT NULL REFERENCES bottlenecks(id),
+        bottleneck_id varchar NOT NULL,
+        bottleneck_validity tstzrange NOT NULL,
+        CONSTRAINT bottleneck_key
+            FOREIGN KEY (bottleneck_id, bottleneck_validity)
+                REFERENCES bottlenecks (bottleneck_id, validity)
+                ON UPDATE CASCADE,
         date_info date NOT NULL,
+        CHECK (tstzrange(date_info::timestamptz,
+            date_info::timestamptz + '1 d'::interval) && bottleneck_validity),
         UNIQUE (bottleneck_id, date_info),
         area geography(POLYGON, 4326) NOT NULL
             CHECK(ST_IsValid(CAST(area AS geometry))),
--- a/schema/geoserver_views.sql	Thu Jun 13 11:20:17 2019 +0200
+++ b/schema/geoserver_views.sql	Thu Jun 13 19:13:42 2019 +0200
@@ -135,7 +135,7 @@
         LEFT JOIN fairway_availability_latest fal
             ON b.id = fal.bottleneck_id
         LEFT JOIN sounding_result_latest srl
-            ON b.id = srl.bottleneck_id
+            ON b.bottleneck_id = srl.bottleneck_id
     WHERE NOT b.erased;
 
 CREATE OR REPLACE VIEW waterway.stretches_geoserver AS
@@ -200,7 +200,7 @@
     FROM waterway.bottlenecks bn LEFT JOIN (
         SELECT bottleneck_id, max(date_info) AS current
             FROM waterway.sounding_results
-            GROUP BY bottleneck_id) sr ON sr.bottleneck_id = bn.id
+            GROUP BY bottleneck_id) sr ON sr.bottleneck_id = bn.bottleneck_id
     WHERE NOT bn.erased
     ORDER BY objnam;
 
@@ -220,4 +220,5 @@
         JOIN waterway.sounding_results srs
             ON sd.subtrahend = srs.id
         JOIN waterway.bottlenecks bn
-            ON srm.bottleneck_id = bn.id;
+            ON srm.bottleneck_id = bn.bottleneck_id
+                AND srm.bottleneck_validity = bn.validity;