Mercurial > gemma
changeset 3171:c8ded555c2a8
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,