--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/pkg/imports/st.go	Fri Jan 18 17:13:12 2019 +0100
@@ -0,0 +1,218 @@
+// 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"
+	"errors"
+	"time"
+
+	"gemma.intevation.de/gemma/pkg/common"
+	"gemma.intevation.de/gemma/pkg/models"
+)
+
+type Stretch struct {
+	Name      string                 `json:"name"`
+	From      models.Isrs            `json:"from"`
+	To        models.Isrs            `json:"to"`
+	ObjNam    string                 `json:"objnam"`
+	NObjNam   *string                `json:"nobjnam"`
+	Source    string                 `json:"source-organization"`
+	Date      models.Date            `json:"date-info"`
+	Countries models.UniqueCountries `json:"countries"`
+}
+
+const STJobKind JobKind = "st"
+
+type stJobCreator struct{}
+
+func init() {
+	RegisterJobCreator(STJobKind, stJobCreator{})
+}
+
+func (stJobCreator) Description() string { return "stretch" }
+
+func (stJobCreator) AutoAccept() bool { return false }
+
+func (stJobCreator) Create(_ JobKind, data string) (Job, error) {
+	st := new(Stretch)
+	if err := common.FromJSONString(data, st); err != nil {
+		return nil, err
+	}
+	return st, nil
+}
+
+func (stJobCreator) Depends() []string {
+	return []string{
+		"stretches",
+	}
+}
+
+const (
+	stDeleteSQL = `
+DELETE FROM waterway.stretches WHERE
+staging_done AND name = (
+  SELECT name
+  FROM waterway.stretches WHERE
+  id = (
+    SELECT key from waterway.track_imports
+    WHERE import_id = $1 AND
+      relation = 'waterway.stretches'::regclass)
+  AND NOT staging_done
+)`
+
+	stStageDoneSQL = `
+UPDATE waterway.stretches SET staging_done = true
+WHERE id IN (
+  SELECT key from waterway.track_imports
+  WHERE import_id = $1 AND
+        relation = 'waterway.stretches'::regclass)`
+
+	stInsertSQL = `
+WITH r AS (
+  SELECT isrsrange(
+    isrs(
+      $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)) AS r
+)
+INSERT INTO waterway.stretches (
+  name,
+  stretch,
+  geom,
+  objnam,
+  nobjnam,
+  date_info
+) VALUES (
+  $11,
+  (SELECT r FROM r),
+  ISRSrange_area(
+    (SELECT r FROM r),
+    (SELECT ST_Union(CAST(area AS geometry))
+      FROM waterway.waterway_area)),
+  $12,
+  $13,
+  $14)
+RETURNING id`
+
+	stInsertCountrySQL = `
+INSERT INTO waterway.stretch_countries (
+  stretches_id,
+  country_code
+) VALUES (
+  $1,
+  $2
+)`
+)
+
+// StageDone moves the imported stretch out of the staging area.
+func (stJobCreator) StageDone(
+	ctx context.Context,
+	tx *sql.Tx,
+	id int64,
+) error {
+	if _, err := tx.ExecContext(ctx, stDeleteSQL, id); err != nil {
+		return err
+	}
+	_, err := tx.ExecContext(ctx, stStageDoneSQL, id)
+	return err
+}
+
+// CleanUp of a stretch import is a NOP.
+func (*Stretch) CleanUp() error { return nil }
+
+// Do executes the actual bottleneck import.
+func (st *Stretch) Do(
+	ctx context.Context,
+	importID int64,
+	conn *sql.Conn,
+	feedback Feedback,
+) (interface{}, error) {
+
+	start := time.Now()
+
+	feedback.Info("Storing stretch '%s'", st.Name)
+
+	if len(st.Countries) == 0 {
+		return nil, errors.New("List of countries is empty")
+	}
+
+	tx, err := conn.BeginTx(ctx, nil)
+	if err != nil {
+		return nil, err
+	}
+	defer tx.Rollback()
+
+	insertCountryStmt, err := tx.PrepareContext(ctx, stInsertCountrySQL)
+	if err != nil {
+		return nil, err
+	}
+
+	var nobjnm sql.NullString
+	if st.NObjNam != nil {
+		nobjnm = sql.NullString{String: *st.NObjNam, Valid: true}
+	}
+
+	var id int64
+	if err := tx.QueryRowContext(
+		ctx,
+		stInsertSQL,
+		st.From.CountryCode,
+		st.From.LoCode,
+		st.From.FairwaySection,
+		st.From.Orc,
+		st.From.Hectometre,
+		st.To.CountryCode,
+		st.To.LoCode,
+		st.To.FairwaySection,
+		st.To.Orc,
+		st.To.Hectometre,
+		st.Name,
+		st.ObjNam,
+		nobjnm,
+		st.Date.Time,
+	).Scan(&err); err != nil {
+		return nil, err
+	}
+
+	// store the associated countries.
+	for _, c := range st.Countries {
+		if _, err := insertCountryStmt.ExecContext(ctx, id, c); err != nil {
+			return nil, err
+		}
+	}
+
+	feedback.Info("Storing stretch '%s' took %s", st.Name, time.Since(start))
+	if err := tx.Commit(); err != nil {
+		return nil, err
+	}
+	feedback.Info("Import of stretch was successful")
+
+	summary := struct {
+		Stretch string `json:"stretch"`
+	}{
+		Stretch: st.Name,
+	}
+
+	return &summary, nil
+}