Mercurial > gemma

changeset 4922:9bd6a0ca63ea fairway-marks-import

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add import for fairway marks of type BOYLAT
author Tom Gottfried <tom@intevation.de>
date Fri, 14 Feb 2020 12:14:36 +0100
parents a9fac2394238
children 4fda1249f6e8
files pkg/controllers/routes.go pkg/imports/fm_boylat.go pkg/imports/modelconvert.go schema/gemma.sql
diffstat 4 files changed, 279 insertions(+), 1 deletions(-) [+]
line wrap: on
line diff
--- a/pkg/controllers/routes.go	Fri Feb 14 11:07:38 2020 +0100
+++ b/pkg/controllers/routes.go	Fri Feb 14 12:14:36 2020 +0100
@@ -245,6 +245,7 @@
 		"sec", "dsec", "dst", "dsr",
 		"fm_bcnlat",
 		"fm_boycar",
+		"fm_boylat",
 	}, "|")
 
 	api.Handle("/imports/{kind:"+kinds+"}", waterwayAdmin(&mw.JSONHandler{
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/pkg/imports/fm_boylat.go	Fri Feb 14 12:14:36 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 <tom.gottfried@intevation.de>
+
+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
+}
--- a/pkg/imports/modelconvert.go	Fri Feb 14 11:07:38 2020 +0100
+++ b/pkg/imports/modelconvert.go	Fri Feb 14 12:14:36 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{
--- a/schema/gemma.sql	Fri Feb 14 11:07:38 2020 +0100
+++ b/schema/gemma.sql	Fri Feb 14 12:14:36 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)
+        ))
 ;