# HG changeset patch
# User Sascha L. Teichmann <sascha.teichmann@intevation.de>
# Date 1567530111 -7200
# Node ID 3d6a2c6b436cd229a888883113b634594b67217d
# Parent  ee4c47c3e7b21292a267e6c5f3a98150e8b6d6c7
shape upload stretch import: Store features in database. Still broken [WIP].

diff -r ee4c47c3e7b2 -r 3d6a2c6b436c pkg/imports/stsh.go
--- a/pkg/imports/stsh.go	Tue Sep 03 17:47:57 2019 +0200
+++ b/pkg/imports/stsh.go	Tue Sep 03 19:01:51 2019 +0200
@@ -24,6 +24,7 @@
 	"path"
 	"path/filepath"
 	"strings"
+	"time"
 
 	shp "github.com/jonas-p/go-shp"
 
@@ -53,6 +54,27 @@
 	}
 }
 
+const (
+	stshInsertSQL = `
+INSERT INTO waterway.stretches (
+  name,
+  stretch,
+  area,
+  objnam,
+  nobjnam,
+  date_info,
+  source_organization
+) VALUES (
+  $1,
+  isrsrange(isrs_fromText($2), isrs_fromText($3)),
+  ST_GeomFromWKB($4, 4326),
+  $5,
+  $6,
+  $7,
+  $8)
+RETURNING id`
+)
+
 func (stshJobCreator) StageDone(
 	ctx context.Context,
 	tx *sql.Tx,
@@ -66,6 +88,10 @@
 	return os.RemoveAll(stsh.Dir)
 }
 
+func fixAttribute(s string) string {
+	return strings.TrimRight(s, "\x00")
+}
+
 func (stsh *StretchShape) Do(
 	ctx context.Context,
 	importID int64,
@@ -73,7 +99,12 @@
 	feedback Feedback,
 ) (interface{}, error) {
 
-	//start := time.Now()
+	start := time.Now()
+	defer func() {
+		feedback.Info("Storing stretches from shape file took %v.",
+			time.Since(start))
+	}()
+
 	zpath := filepath.Join(stsh.Dir, "stretch.zip")
 
 	z, err := zip.OpenReader(zpath)
@@ -107,6 +138,81 @@
 
 	fields := sr.Fields()
 
+	// Map the attribute column indices.
+
+	var (
+		nameIdx      = -1
+		objnamIdx    = -1
+		nobjnamIdx   = -1
+		lowerIdx     = -1
+		upperIdx     = -1
+		sourceIdx    = -1
+		dateInfoIdx  = -1
+		countriesIdx = -1
+	)
+
+	type index struct {
+		name string
+		idx  *int
+	}
+
+	indices := []index{
+		{"name", &nameIdx},
+		{"objnam", &objnamIdx},
+		{"nobjnam", &nobjnamIdx},
+		{"lower", &lowerIdx},
+		{"upper", &upperIdx},
+		{"source", &sourceIdx},
+		{"source", &sourceIdx},
+		{"dateinfo", &dateInfoIdx},
+		{"countries", &countriesIdx},
+	}
+
+nextField:
+	for i := range fields {
+		name := strings.ToLower(fields[i].String())
+		for j := range indices {
+			if name == indices[j].name {
+				*indices[j].idx = i
+				continue nextField
+			}
+		}
+	}
+
+	var missingFields []string
+
+	for i := range indices {
+		if *indices[i].idx == -1 {
+			missingFields = append(missingFields, indices[i].name)
+		}
+	}
+
+	if len(missingFields) > 0 {
+		return nil, fmt.Errorf("missing fields in attributes: %s",
+			strings.Join(missingFields, ", "))
+	}
+
+	// Now we have ensured that all columns are in place
+	// so start extracting data from the shape file.
+
+	tx, err := conn.BeginTx(ctx, nil)
+	if err != nil {
+		return nil, err
+	}
+	defer tx.Rollback()
+
+	insStmt, err := tx.PrepareContext(ctx, stshInsertSQL)
+	if err != nil {
+		return nil, err
+	}
+	defer insStmt.Close()
+
+	insCountryStmt, err := tx.PrepareContext(ctx, stInsertCountrySQL)
+	if err != nil {
+		return nil, err
+	}
+	defer insCountryStmt.Close()
+
 	for sr.Next() {
 
 		_, p := sr.Shape()
@@ -120,17 +226,69 @@
 			continue
 		}
 
-		// Convert to a multi polygon.
-		mp := poly.MultiPolygonGeom()
+		var (
+			name      = fixAttribute(sr.Attribute(nameIdx))
+			objnam    = fixAttribute(sr.Attribute(objnamIdx))
+			nobjnam   = fixAttribute(sr.Attribute(nobjnamIdx))
+			lower     = fixAttribute(sr.Attribute(lowerIdx))
+			upper     = fixAttribute(sr.Attribute(upperIdx))
+			dateInfo  = fixAttribute(sr.Attribute(dateInfoIdx))
+			source    = fixAttribute(sr.Attribute(sourceIdx))
+			countries = fixAttribute(sr.Attribute(countriesIdx))
+		)
 
-		wkb := mp.AsWKB()
+		log.Printf("name     : %+q\n", name)
+		log.Printf("objnam   : %+q\n", objnam)
+		log.Printf("nobjnam  : %+q\n", nobjnam)
+		log.Printf("lower    : %+q\n", lower)
+		log.Printf("upper    : %+q\n", upper)
+		log.Printf("dateinfo : %+q\n", dateInfo)
+		log.Printf("source   : %+q\n", source)
+		log.Printf("countries: %+q\n", countries)
+
+		date, err := common.ParseTime(dateInfo)
+		if err != nil {
+			feedback.Warn("Invalid time value: %v.", err)
+			continue
+		}
 
-		_ = wkb
+		var nobjnamNull sql.NullString
+		if nobjnam != "" {
+			nobjnamNull = sql.NullString{
+				String: nobjnam,
+				Valid:  true,
+			}
+		}
+
+		// Convert to a multi polygon.
+		area := poly.MultiPolygonGeom().AsWKB()
+
+		log.Printf("len geom: %d\n", len(area))
+
+		var id int64
 
-		for k, f := range fields {
-			name := f.String()
-			val := sr.Attribute(k)
-			log.Printf("info: \t%s =  %v\n", name, val)
+		if err := insStmt.QueryRowContext(
+			ctx,
+			name,
+			lower, upper,
+			area,
+			objnam,
+			nobjnamNull,
+			date,
+			source,
+		).Scan(&id); err != nil {
+			return nil, err
+		}
+
+		log.Println("after insert")
+
+		for _, country := range strings.Split(countries, ",") {
+			if country = strings.TrimSpace(country); country == "" {
+				continue
+			}
+			if _, err := insCountryStmt.ExecContext(ctx, country); err != nil {
+				return nil, err
+			}
 		}
 
 		// TODO: Implement me!
@@ -140,5 +298,5 @@
 		return nil, err
 	}
 
-	return nil, nil
+	return nil, errors.New("Not implemented, yet!")
 }
diff -r ee4c47c3e7b2 -r 3d6a2c6b436c pkg/wkb/data.go
--- a/pkg/wkb/data.go	Tue Sep 03 17:47:57 2019 +0200
+++ b/pkg/wkb/data.go	Tue Sep 03 19:01:51 2019 +0200
@@ -48,7 +48,7 @@
 
 	for _, pg := range mpg {
 		binary.Write(buf, binary.LittleEndian, NDR)
-		binary.Write(buf, binary.LittleEndian, uint32(len(mpg)))
+		binary.Write(buf, binary.LittleEndian, uint32(len(pg)))
 
 		for _, r := range pg {
 			binary.Write(buf, binary.LittleEndian, NDR)