Mercurial > gemma
changeset 3656:2a079d0a71c1
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;