# HG changeset patch # User Tom Gottfried # Date 1581678998 -3600 # Node ID 4fda1249f6e8abf335b16363ea785ff9fe825396 # Parent 9bd6a0ca63eace4a38b778db54e05a20945bb9ec# Parent 6129f7bb5b772fea2b8b82c3f921dfe6cf394524 Merged diff -r 6129f7bb5b77 -r 4fda1249f6e8 pkg/controllers/routes.go --- a/pkg/controllers/routes.go Fri Feb 14 12:14:58 2020 +0100 +++ b/pkg/controllers/routes.go Fri Feb 14 12:16:38 2020 +0100 @@ -245,6 +245,7 @@ "sec", "dsec", "dst", "dsr", "fm_bcnlat", "fm_boycar", + "fm_boylat", }, "|") api.Handle("/imports/{kind:"+kinds+"}", waterwayAdmin(&mw.JSONHandler{ diff -r 6129f7bb5b77 -r 4fda1249f6e8 pkg/imports/fm_boylat.go --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/pkg/imports/fm_boylat.go Fri Feb 14 12:16:38 2020 +0100 @@ -0,0 +1,244 @@ +// 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) 2020 by via donau +// – Österreichische Wasserstraßen-Gesellschaft mbH +// Software engineering by Intevation GmbH +// +// Author(s): +// * Tom Gottfried + +package imports + +import ( + "context" + "database/sql" + "time" + + "gemma.intevation.de/gemma/pkg/pgxutils" +) + +// Boylat is an import job to import +// fairway marks of type BOYLAT in form of point geometries +// and attribute data from a WFS service. +type Boylat struct { + FairwayMarks +} + +// Description gives a short info about relevant facts of this import. +func (boylat *Boylat) Description() (string, error) { + return boylat.URL + "|" + boylat.FeatureType, nil +} + +// BOYLATJobKind is the import queue type identifier. +const BOYLATJobKind JobKind = "fm_boylat" + +type boylatJobCreator struct{} + +func init() { + RegisterJobCreator(BOYLATJobKind, boylatJobCreator{}) +} + +func (boylatJobCreator) Description() string { return "fairway marks boylat" } + +func (boylatJobCreator) AutoAccept() bool { return true } + +func (boylatJobCreator) Create() Job { return new(Boylat) } + +func (boylatJobCreator) Depends() [2][]string { + return [2][]string{ + {"fairway_marks_boylat"}, + {}, + } +} + +// StageDone is a NOP for fairway marks imports. +func (boylatJobCreator) StageDone(context.Context, *sql.Tx, int64) error { + return nil +} + +// CleanUp for fairway marks imports is a NOP. +func (*Boylat) CleanUp() error { return nil } + +type boylatProperties struct { + fairwayMarksProperties + Colour *string `json:"hydro_colour"` + Colpat *string `json:"hydro_colpat"` + Conrad *int `json:"hydro_conrad"` + HydroMarsys *int64 `json:"hydro_marsys,omitempty"` + IENCMarsys *int64 `json:"ienc_marsys,omitempty"` + Boyshp *int `json:"hydro_boyshp"` + HydroCatlam *int64 `json:"hydro_catlam,omitempty"` + IENCCatlam *int64 `json:"ienc_catlam,omitempty"` +} + +type boylatFeaturetype struct { + geom pointSlice + props *boylatProperties +} + +const ( + insertBOYLATSQL = ` +with a as ( + select users.current_user_area_utm() AS a +) +INSERT INTO waterway.fairway_marks_boylat ( + geom, + datsta, + datend, + persta, + perend, + objnam, + nobjnm, + inform, + ninfom, + scamin, + picrep, + txtdsc, + sordat, + sorind, + colour, + colpat, + conrad, + marsys, + boyshp, + catlam +) +SELECT newfm, $3, $4, $5, $6, $7, $8, $9, + $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21 + FROM ST_Transform(ST_GeomFromWKB($1, $2::integer), 4326) AS newfm (newfm) + WHERE pg_has_role('sys_admin', 'MEMBER') + OR ST_Intersects((select a from a), + ST_Transform(newfm, (select ST_SRID(a) from a))) +ON CONFLICT ( + CAST((geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + 0, colour, colpat, conrad, marsys, boyshp, catlam + ) AS waterway.fairway_marks_boylat) + ) + DO NOTHING +RETURNING id +` +) + +// Do executes the actual import. +func (fm *Boylat) Do( + ctx context.Context, + importID int64, + conn *sql.Conn, + feedback Feedback, +) (interface{}, error) { + + start := time.Now() + + feedback.Info("Import fairway marks of type BOYLAT") + + fms, epsg, err := getFMFeatures( + feedback, + fm.FairwayMarks, + func() interface{} { return new(boylatProperties) }, + func(p pointSlice, props interface{}) interface{} { + return &boylatFeaturetype{p, props.(*boylatProperties)} + }, + ) + if err != nil { + return nil, err + } + + tx, err := conn.BeginTx(ctx, nil) + if err != nil { + return nil, err + } + defer tx.Rollback() + + insertStmt, err := tx.PrepareContext(ctx, insertBOYLATSQL) + if err != nil { + return nil, err + } + defer insertStmt.Close() + + savepoint := Savepoint(ctx, tx, "feature") + + var ( + outsideOrDup int + features int + ) + for _, fm := range fms { + + f := fm.(*boylatFeaturetype) + + var marsys sql.NullInt64 + if f.props.HydroMarsys != nil { + marsys = sql.NullInt64{Int64: *f.props.HydroMarsys, Valid: true} + } else if f.props.IENCMarsys != nil { + marsys = sql.NullInt64{Int64: *f.props.IENCMarsys, Valid: true} + } + + var catlam sql.NullInt64 + if f.props.HydroCatlam != nil { + catlam = sql.NullInt64{Int64: *f.props.HydroCatlam, Valid: true} + } else if f.props.IENCCatlam != nil { + catlam = sql.NullInt64{Int64: *f.props.IENCCatlam, Valid: true} + } + + var fmid int64 + err := savepoint(func() error { + err := insertStmt.QueryRowContext( + ctx, + f.geom.asWKB(), + epsg, + f.props.Datsta, + f.props.Datend, + f.props.Persta, + f.props.Perend, + f.props.Objnam, + f.props.Nobjnm, + f.props.Inform, + f.props.Ninfom, + f.props.Scamin, + f.props.Picrep, + f.props.Txtdsc, + f.props.Sordat, + f.props.Sorind, + f.props.Colour, + f.props.Colpat, + f.props.Conrad, + marsys, + f.props.Boyshp, + catlam, + ).Scan(&fmid) + return err + }) + switch { + case err == sql.ErrNoRows: + outsideOrDup++ + // ignore -> filtered by responsibility_areas + case err != nil: + feedback.Error(pgxutils.ReadableError{Err: err}.Error()) + default: + features++ + } + } + + if outsideOrDup > 0 { + feedback.Info( + "Features outside responsibility area and duplicates: %d", + outsideOrDup) + } + + if features == 0 { + err := UnchangedError("no valid new features found") + return nil, err + } + + if err = tx.Commit(); err == nil { + feedback.Info("Storing %d features took %s", + features, time.Since(start)) + } + + return nil, err +} diff -r 6129f7bb5b77 -r 4fda1249f6e8 pkg/imports/modelconvert.go --- a/pkg/imports/modelconvert.go Fri Feb 14 12:14:58 2020 +0100 +++ b/pkg/imports/modelconvert.go Fri Feb 14 12:16:38 2020 +0100 @@ -29,6 +29,7 @@ DMAJobKind: func() interface{} { return new(models.DistanceMarksAshoreImport) }, BCNLATJobKind: func() interface{} { return new(models.FairwayMarksImport) }, BOYCARJobKind: func() interface{} { return new(models.FairwayMarksImport) }, + BOYLATJobKind: func() interface{} { return new(models.FairwayMarksImport) }, STJobKind: func() interface{} { return new(models.StretchImport) }, SECJobKind: func() interface{} { return new(models.SectionImport) }, DSECJobKind: func() interface{} { return new(models.SectionDelete) }, @@ -160,6 +161,17 @@ } }, + BOYLATJobKind: func(input interface{}) interface{} { + fmi := input.(*models.FairwayMarksImport) + return &FairwayMarks{ + URL: fmi.URL, + FeatureType: fmi.FeatureType, + SortBy: nilString(fmi.SortBy), + User: nilString(fmi.User), + Password: nilString(fmi.Password), + } + }, + STJobKind: func(input interface{}) interface{} { sti := input.(*models.StretchImport) return &Stretch{ diff -r 6129f7bb5b77 -r 4fda1249f6e8 schema/gemma.sql --- a/schema/gemma.sql Fri Feb 14 12:14:58 2020 +0100 +++ b/schema/gemma.sql Fri Feb 14 12:16:38 2020 +0100 @@ -886,7 +886,7 @@ PRIMARY KEY (fm_bcnlat_id, dirimp) ) - -- Additional attributes for IENC features BOYCAR + -- Additional attributes for IENC feature BOYCAR CREATE TABLE fairway_marks_boycar ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, colour varchar, @@ -905,6 +905,27 @@ 0, colour, colpat, conrad, marsys, boyshp ) AS fairway_marks_boycar) )) + + -- Additional attributes for IENC feature BOYLAT + CREATE TABLE fairway_marks_boylat ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + colour varchar, + colpat varchar, + conrad int, + marsys int, + boyshp int, + catlam int + ) INHERITS (fairway_marks) + -- Prevent identical entries using composite type comparison + -- (i.e. considering two NULL values in a field equal): + CREATE UNIQUE INDEX fairway_marks_boylat_distinct_rows + ON fairway_marks_boylat + ((CAST((geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + 0, colour, colpat, conrad, marsys, boyshp, catlam + ) AS fairway_marks_boylat) + )) ;