Mercurial > gemma

changeset 3171:c8ded555c2a8

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Sections import: Added a sections import. Derived from the stretches import w/o the countries.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Mon, 06 May 2019 16:36:03 +0200
parents 5c8ecab9f2d4
children 975efa874acf
files pkg/controllers/routes.go pkg/imports/modelconvert.go pkg/imports/sec.go pkg/models/imports.go schema/gemma.sql
diffstat 5 files changed, 323 insertions(+), 0 deletions(-) [+]
line wrap: on
line diff
--- a/pkg/controllers/routes.go	Mon May 06 16:18:41 2019 +0200
+++ b/pkg/controllers/routes.go	Mon May 06 16:36:03 2019 +0200
@@ -234,6 +234,7 @@
 	kinds := strings.Join([]string{
 		"bn", "gm", "fa", "wx", "wa",
 		"wg", "dmv", "fd", "dma",
+		"sec",
 	}, "|")
 
 	api.Handle("/imports/{kind:"+kinds+"}", waterwayAdmin(&JSONHandler{
--- a/pkg/imports/modelconvert.go	Mon May 06 16:18:41 2019 +0200
+++ b/pkg/imports/modelconvert.go	Mon May 06 16:36:03 2019 +0200
@@ -28,6 +28,7 @@
 	FDJobKind:  func() interface{} { return new(models.FairwayDimensionImport) },
 	DMAJobKind: func() interface{} { return new(models.DistanceMarksAshoreImport) },
 	STJobKind:  func() interface{} { return new(models.StretchImport) },
+	SECJobKind: func() interface{} { return new(models.SectionImport) },
 }
 
 func ImportModelForJobKind(kind JobKind) func() interface{} {
@@ -144,6 +145,20 @@
 			Countries: sti.Countries,
 		}
 	},
+
+	SECJobKind: func(input interface{}) interface{} {
+		seci := input.(*models.SectionImport)
+		return &Section{
+			Name:      seci.Name,
+			From:      seci.From,
+			To:        seci.To,
+			Tolerance: seci.Tolerance,
+			ObjNam:    seci.ObjNam,
+			NObjNam:   seci.NObjNam,
+			Source:    seci.Source,
+			Date:      seci.Date,
+		}
+	},
 }
 
 func nilString(s *string) string {
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/pkg/imports/sec.go	Mon May 06 16:36:03 2019 +0200
@@ -0,0 +1,210 @@
+// 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) 2018 by via donau
+//   – Österreichische Wasserstraßen-Gesellschaft mbH
+// Software engineering by Intevation GmbH
+//
+// Author(s):
+//  * Sascha L. Teichmann <sascha.teichmann@intevation.de>
+
+package imports
+
+import (
+	"context"
+	"database/sql"
+	"time"
+
+	"gemma.intevation.de/gemma/pkg/models"
+)
+
+type Section struct {
+	Name      string      `json:"name"`
+	From      models.Isrs `json:"from"`
+	To        models.Isrs `json:"to"`
+	Tolerance float32     `json:"tolerance"`
+	ObjNam    string      `json:"objnam"`
+	NObjNam   *string     `json:"nobjnam"`
+	Source    string      `json:"source-organization"`
+	Date      models.Date `json:"date-info"`
+}
+
+const SECJobKind JobKind = "sec"
+
+type secJobCreator struct{}
+
+func init() {
+	RegisterJobCreator(SECJobKind, secJobCreator{})
+}
+
+func (secJobCreator) Description() string { return "section" }
+
+func (secJobCreator) AutoAccept() bool { return false }
+
+func (secJobCreator) Create() Job { return new(Section) }
+
+func (secJobCreator) Depends() []string {
+	return []string{
+		"sections",
+	}
+}
+
+const (
+	secDeleteSQL = `
+DELETE FROM waterway.sections WHERE
+staging_done AND name = (
+  SELECT name
+  FROM waterway.sections WHERE
+  id = (
+    SELECT key from import.track_imports
+    WHERE import_id = $1 AND
+      relation = 'waterway.sections'::regclass)
+  AND NOT staging_done
+)`
+
+	secStageDoneSQL = `
+UPDATE waterway.sections SET staging_done = true
+WHERE id IN (
+  SELECT key from import.track_imports
+  WHERE import_id = $1 AND
+        relation = 'waterway.sections'::regclass)`
+
+	secInsertSQL = `
+WITH r AS (
+  SELECT isrsrange(
+    least(($1::char(2),
+           $2::char(3),
+           $3::char(5),
+           $4::char(5),
+           $5::int)::isrs,
+          ($6::char(2),
+           $7::char(3),
+           $8::char(5),
+           $9::char(5),
+           $10::int)::isrs),
+    greatest(($1::char(2),
+              $2::char(3),
+              $3::char(5),
+              $4::char(5),
+              $5::int)::isrs,
+             ($6::char(2),
+              $7::char(3),
+              $8::char(5),
+              $9::char(5),
+              $10::int)::isrs)
+    ) AS r),
+axs AS (
+  SELECT ISRSrange_axis((SELECT r FROM r), $16::double precision) AS axs)
+INSERT INTO waterway.sections (
+  name,
+  stretch,
+  area,
+  objnam,
+  nobjnam,
+  date_info,
+  source_organization
+) VALUES (
+  $11,
+  (SELECT r FROM r),
+  ST_Transform(ISRSrange_area(
+      (SELECT axs FROM axs),
+      (SELECT ST_Buffer(axs, 10000) FROM axs)),
+    4326),
+  $12,
+  $13,
+  $14,
+  $15)
+RETURNING id`
+)
+
+// StageDone moves the imported stretch out of the staging area.
+func (secJobCreator) StageDone(
+	ctx context.Context,
+	tx *sql.Tx,
+	id int64,
+) error {
+	if _, err := tx.ExecContext(ctx, secDeleteSQL, id); err != nil {
+		return err
+	}
+	_, err := tx.ExecContext(ctx, secStageDoneSQL, id)
+	return err
+}
+
+// CleanUp of a stretch import is a NOP.
+func (*Section) CleanUp() error { return nil }
+
+// Do executes the actual stretch import.
+func (sec *Section) Do(
+	ctx context.Context,
+	importID int64,
+	conn *sql.Conn,
+	feedback Feedback,
+) (interface{}, error) {
+
+	start := time.Now()
+
+	if sec.Date.Time.IsZero() {
+		sec.Date = models.Date{Time: start}
+	}
+
+	feedback.Info("Storing section '%s'", sec.Name)
+
+	tx, err := conn.BeginTx(ctx, nil)
+	if err != nil {
+		return nil, err
+	}
+	defer tx.Rollback()
+
+	var nobjnm sql.NullString
+	if sec.NObjNam != nil {
+		nobjnm = sql.NullString{String: *sec.NObjNam, Valid: true}
+	}
+
+	feedback.Info("Section from %s to %s.", sec.From.String(), sec.To.String())
+	feedback.Info("Tolerance used to snap waterway axis: %g", sec.Tolerance)
+
+	var id int64
+	if err := tx.QueryRowContext(
+		ctx,
+		secInsertSQL,
+		sec.From.CountryCode,
+		sec.From.LoCode,
+		sec.From.FairwaySection,
+		sec.From.Orc,
+		sec.From.Hectometre,
+		sec.To.CountryCode,
+		sec.To.LoCode,
+		sec.To.FairwaySection,
+		sec.To.Orc,
+		sec.To.Hectometre,
+		sec.Name,
+		sec.ObjNam,
+		nobjnm,
+		sec.Date.Time,
+		sec.Source,
+		sec.Tolerance,
+	).Scan(&id); err != nil {
+		return nil, handleError(err)
+	}
+
+	if err := track(ctx, tx, importID, "waterway.sections", id); err != nil {
+		return nil, err
+	}
+
+	feedback.Info("Storing section '%s' took %s", sec.Name, time.Since(start))
+	if err := tx.Commit(); err != nil {
+		return nil, err
+	}
+	feedback.Info("Import of section was successful")
+
+	summary := struct {
+		Section string `json:"section"`
+	}{
+		Section: sec.Name,
+	}
+
+	return &summary, nil
+}
--- a/pkg/models/imports.go	Mon May 06 16:18:41 2019 +0200
+++ b/pkg/models/imports.go	Mon May 06 16:36:03 2019 +0200
@@ -107,6 +107,19 @@
 		Date      Date            `json:"date-info"`
 		Countries UniqueCountries `json:"countries"`
 	}
+
+	SectionImport struct {
+		EmailType
+
+		Name      string  `json:"name"`
+		From      Isrs    `json:"from"`
+		To        Isrs    `json:"to"`
+		Tolerance float32 `json:"tolerance"`
+		ObjNam    string  `json:"objnam"`
+		NObjNam   *string `json:"nobjnam"`
+		Source    string  `json:"source-organization"`
+		Date      Date    `json:"date-info"`
+	}
 )
 
 func (cui *ConfigurableURLImport) MarshalAttributes(attrs common.Attributes) error {
@@ -294,3 +307,69 @@
 	}
 	return nil
 }
+
+func (seci *SectionImport) MarshalAttributes(attrs common.Attributes) error {
+	if err := seci.EmailType.MarshalAttributes(attrs); err != nil {
+		return err
+	}
+	attrs.Set("name", seci.Name)
+	attrs.Set("from", seci.From.String())
+	attrs.Set("to", seci.To.String())
+	attrs.Set("objnam", seci.ObjNam)
+	if seci.NObjNam != nil {
+		attrs.Set("nobjnam", *seci.NObjNam)
+	}
+	attrs.Set("source-organization", seci.Source)
+	attrs.SetDate("date-info", seci.Date.Time)
+
+	return nil
+}
+
+func (seci *SectionImport) UnmarshalAttributes(attrs common.Attributes) error {
+	if err := seci.EmailType.UnmarshalAttributes(attrs); err != nil {
+		return err
+	}
+	name, found := attrs.Get("name")
+	if !found {
+		return errors.New("missing 'name' attribute")
+	}
+	seci.Name = name
+	from, found := attrs.Get("from")
+	if !found {
+		return errors.New("missing 'from' attribute")
+	}
+	f, err := IsrsFromString(from)
+	if err != nil {
+		return err
+	}
+	seci.From = *f
+	to, found := attrs.Get("to")
+	if !found {
+		return errors.New("missing 'to' attribute")
+	}
+	t, err := IsrsFromString(to)
+	if err != nil {
+		return err
+	}
+	seci.To = *t
+	objnam, found := attrs.Get("objnam")
+	if !found {
+		return errors.New("missing 'objnam' attribute")
+	}
+	seci.ObjNam = objnam
+	nobjnam, found := attrs.Get("nobjnam")
+	if found {
+		seci.NObjNam = &nobjnam
+	}
+	source, found := attrs.Get("source-organization")
+	if !found {
+		return errors.New("missing 'source' attribute")
+	}
+	seci.Source = source
+	date, found := attrs.Date("date-info")
+	if !found {
+		return errors.New("missing 'date-info' attribute")
+	}
+	seci.Date = Date{date}
+	return nil
+}
--- a/schema/gemma.sql	Mon May 06 16:18:41 2019 +0200
+++ b/schema/gemma.sql	Mon May 06 16:36:03 2019 +0200
@@ -406,6 +406,24 @@
         UNIQUE(stretches_id, country_code)
     )
 
+    -- Like stretches without the countries
+    CREATE TABLE sections (
+        id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
+        name varchar NOT NULL,
+        stretch isrsrange NOT NULL,
+        area geography(MULTIPOLYGON, 4326) NOT NULL
+            CHECK(ST_IsValid(CAST(area AS geometry))),
+        objnam varchar NOT NULL,
+        nobjnam varchar,
+        date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
+        source_organization varchar NOT NULL,
+        staging_done boolean NOT NULL DEFAULT false,
+        UNIQUE(name, staging_done)
+    )
+    CREATE TRIGGER sections_date_info
+        BEFORE UPDATE ON stretches
+        FOR EACH ROW EXECUTE PROCEDURE update_date_info()
+
     CREATE TABLE waterway_profiles (
         id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
         location isrs NOT NULL REFERENCES distance_marks_virtual,