# HG changeset patch # User Sascha L. Teichmann # Date 1625701563 -7200 # Node ID c9da747d41092a9e06132b837235efb6f21de120 # Parent 851c14d5768092eca57d4914122000884943bc0f Extended ISR import job to handle marking scans as well. diff -r 851c14d57680 -r c9da747d4109 pkg/imports/isr.go --- a/pkg/imports/isr.go Thu Jul 08 00:14:58 2021 +0200 +++ b/pkg/imports/isr.go Thu Jul 08 01:46:03 2021 +0200 @@ -16,10 +16,11 @@ import ( "context" "database/sql" + "log" "time" - "gemma.intevation.de/gemma/pkg/common" "gemma.intevation.de/gemma/pkg/mesh" + "gemma.intevation.de/gemma/pkg/models" ) // IsoRefresh is an import job to refresh the pre-calculated @@ -52,23 +53,40 @@ const ( fetchSoundingResultsIDsSQL = ` -SELECT bottleneck_id, id +SELECT + bottleneck_id, + id, + surtyp FROM waterway.sounding_results -ORDER BY bottleneck_id -` +ORDER BY bottleneck_id` + deleteIsoAreasSQL = ` DELETE FROM waterway.sounding_results_iso_areas -WHERE sounding_result_id = $1 -` +WHERE sounding_result_id = $1` + + fetchMarkingPointsSQL = ` +SELECT ST_AsBinary(ST_Transform(points, 4326)) +FROM waterway.sounding_results_marking_points +WHERE sounding_result_id = $1` + + deleteMarkingPointsSQL = ` +DELETE FROM waterway.sounding_results_marking_points +WHERE sounding_result_id = $1` ) // CleanUp of a iso refresh import is a NOP. func (isr *IsoRefresh) CleanUp() error { return nil } -type bottleneckSoundingResults struct { - bn string - srs []int64 -} +type ( + scanResult struct { + id int64 + surveyType models.SurveyType + } + bottleneckSoundingResults struct { + bn string + srs []scanResult + } +) func fetchBottleneckResults( ctx context.Context, @@ -81,33 +99,32 @@ } defer rows.Close() - var ids []bottleneckSoundingResults + var bsrs []bottleneckSoundingResults for rows.Next() { - var bn string - var sr int64 - if err := rows.Scan(&bn, &sr); err != nil { + var ( + bn string + id int64 + st string + ) + if err := rows.Scan(&bn, &id, &st); err != nil { return nil, err } - if len(ids) > 0 { - if ids[len(ids)-1].bn != bn { - ids = append(ids, bottleneckSoundingResults{ - bn: bn, - srs: []int64{sr}, - }) - } else { - ids[len(ids)-1].srs = append(ids[len(ids)-1].srs, sr) - } + sr := scanResult{id: id, surveyType: models.SurveyType(st)} + + if l := len(bsrs); l == 0 || bsrs[l-1].bn != bn { + bsrs = append(bsrs, bottleneckSoundingResults{ + bn: bn, + srs: []scanResult{sr}, + }) } else { - ids = []bottleneckSoundingResults{ - {bn: bn, srs: []int64{sr}}, - } + bsrs[l-1].srs = append(bsrs[l-1].srs, sr) } } if err := rows.Err(); err != nil { return nil, err } - return ids, nil + return bsrs, nil } // Do executes the actual refreshing of the iso areas. @@ -167,50 +184,130 @@ } defer tx.Rollback() - insertAreasStmt, err := tx.Prepare(insertIsoAreasSQL) - if err != nil { - return err + var ( + insertAreasStmt, + deleteAreasStmt, + fetchMarkingPointsStmt, + deleteMarkingPointsStmt, + insertMarkingPointsStmt *sql.Stmt + ) + + for _, x := range []struct { + stmt **sql.Stmt + query string + }{ + {&insertAreasStmt, insertIsoAreasSQL}, + {&deleteAreasStmt, deleteIsoAreasSQL}, + {&fetchMarkingPointsStmt, fetchMarkingPointsSQL}, + {&deleteMarkingPointsStmt, deleteMarkingPointsSQL}, + {&insertMarkingPointsStmt, insertMarkingPointsSQL}, + } { + if *x.stmt, err = tx.PrepareContext(ctx, x.query); err != nil { + return err + } + defer (*x.stmt).Close() } - defer insertAreasStmt.Close() // For all sounding results in bottleneck. for _, sr := range bn.srs { - tree, err := mesh.FetchMeshDirectly(ctx, tx, sr) - if err != nil { - return err - } - hs := heights.ExtrapolateClassBreaks(tree.Min().Z, tree.Max().Z) - hs = common.DedupFloat64s(hs) + switch sr.surveyType { + case models.SurveyTypeMarking: + + // Read all points back in. + + var points mesh.MultiPointZ - // Delete the old iso areas. - if _, err := tx.ExecContext(ctx, deleteIsoAreasSQL, sr); err != nil { - return err - } + if err := func() error { + rows, err := fetchMarkingPointsStmt.QueryContext(ctx, sr.id) + if err != nil { + return err + } + defer rows.Close() - // Calculate and store the iso areas. - box := mesh.Box2D{ - X1: tree.Min().X, - Y1: tree.Min().Y, - X2: tree.Max().X, - Y2: tree.Max().Y, - } + for rows.Next() { + var buf []byte + if err := rows.Scan(&buf); err != nil { + return err + } + var npoints mesh.MultiPointZ + if err := npoints.FromWKB(buf); err != nil { + return err + } + points = append(points, npoints...) + } + return rows.Err() + }(); err != nil { + return err + } - raster := mesh.NewRaster(box, isoCellSize) - raster.Rasterize(tree.Value) - areas := raster.Trace(hs) - - for i, a := range areas { - if len(a) == 0 { - continue - } - if _, err := insertAreasStmt.ExecContext( - ctx, - sr, hs[i], tree.EPSG(), - a.AsWKB(), - contourTolerance, - ); err != nil { + // Delete old points + if _, err := deleteMarkingPointsStmt.ExecContext(ctx, sr.id); err != nil { return err } + + // Re-classify points. + classes := heights.Classify(points.All()) + + // Should not happen ... Z values over the top. + if n := len(classes) - 1; n > 1 && len(classes[n]) > 0 { + // Place the over the top values to the class below. + classes[n-1] = append(classes[n-1], classes[n]...) + classes[n] = nil + classes = classes[:n] + } + + // Re-insert points + for i, class := range classes { + // Ignore empty classes + if len(class) == 0 { + continue + } + if _, err := insertMarkingPointsStmt.ExecContext( + ctx, sr.id, heights[i], 4326, class.AsWKB(), + ); err != nil { + return err + } + } + + case models.SurveyTypeMultiBeam, models.SurveyTypeSingleBeam: + tree, err := mesh.FetchMeshDirectly(ctx, tx, sr.id) + if err != nil { + return err + } + hs := heights.ExtrapolateClassBreaks(tree.Min().Z, tree.Max().Z).Dedup() + + // Delete the old iso areas. + if _, err := deleteAreasStmt.ExecContext(ctx, sr); err != nil { + return err + } + + // Calculate and store the iso areas. + box := mesh.Box2D{ + X1: tree.Min().X, + Y1: tree.Min().Y, + X2: tree.Max().X, + Y2: tree.Max().Y, + } + + raster := mesh.NewRaster(box, isoCellSize) + raster.Rasterize(tree.Value) + areas := raster.Trace(hs) + + for i, a := range areas { + if len(a) == 0 { + continue + } + if _, err := insertAreasStmt.ExecContext( + ctx, + sr, hs[i], tree.EPSG(), + a.AsWKB(), + contourTolerance, + ); err != nil { + return err + } + } + default: + log.Printf("error: unknown survey type '%s'\n", sr.surveyType) } } diff -r 851c14d57680 -r c9da747d4109 pkg/imports/sr.go --- a/pkg/imports/sr.go Thu Jul 08 00:14:58 2021 +0200 +++ b/pkg/imports/sr.go Thu Jul 08 01:46:03 2021 +0200 @@ -998,8 +998,9 @@ continue } log.Printf("debug: class %d: %d\n", i, len(class)) - _, err := stmt.ExecContext(ctx, id, heights[i], epsg, class.AsWKB()) - if err != nil { + if _, err := stmt.ExecContext( + ctx, id, heights[i], epsg, class.AsWKB(), + ); err != nil { return err } }