# HG changeset patch
# User Sascha L. Teichmann <sascha.teichmann@intevation.de>
# Date 1570997755 -7200
# Node ID 8f745c353784a998d010a3cacf4dc4651f72489e
# Parent  66fcd898efd95ed5694a1d3ff2f03dc782c08f31
Finished first version of conversion tool.

diff -r 66fcd898efd9 -r 8f745c353784 cmd/oct2str/main.go
--- a/cmd/oct2str/main.go	Sun Oct 13 19:22:36 2019 +0200
+++ b/cmd/oct2str/main.go	Sun Oct 13 22:15:55 2019 +0200
@@ -15,20 +15,114 @@
 
 import (
 	"context"
+	"crypto/sha1"
 	"database/sql"
+	"encoding/hex"
 	"flag"
 	"log"
 
+	"gemma.intevation.de/gemma/pkg/octree"
 	"github.com/jackc/pgx"
 	"github.com/jackc/pgx/stdlib"
 )
 
-func process(ctx context.Context, conn *sql.Conn) error {
-	// TODO: Implement me!
+const (
+	fetchOneOctreeSQL = `
+SELECT
+  id,
+  octree_index
+FROM waterway.sounding_results
+WHERE mesh_index IS NULL
+LIMIT 1`
+
+	storeSTRTreeSQL = `
+UPDATE waterway.sounding_results
+SET mesh_index = $1, mesh_checksum = $2
+WHERE id = $3`
+)
+
+func process(ctx context.Context, conn *sql.Conn, count int) error {
+
+	fetch, err := conn.PrepareContext(ctx, fetchOneOctreeSQL)
+	if err != nil {
+		return err
+	}
+	defer fetch.Close()
+
+	store, err := conn.PrepareContext(ctx, storeSTRTreeSQL)
+	if err != nil {
+		return err
+	}
+	defer store.Close()
+
+	var next func() bool
+	if count < 0 {
+		next = func() bool { return true }
+	} else {
+		var c int
+		next = func() bool {
+			if c < count {
+				c++
+				return true
+			}
+			return false
+		}
+	}
+
+loop:
+	for next() {
+		switch err := func() error {
+			tx, err := conn.BeginTx(ctx, nil)
+			if err != nil {
+				return err
+			}
+			defer tx.Rollback()
+
+			var id int64
+			var data []byte
+
+			if err = tx.Stmt(fetch).QueryRowContext(ctx).Scan(&id, &data); err != nil {
+				return err
+			}
+
+			otree, err := octree.Deserialize(data)
+			if err != nil {
+				return err
+			}
+
+			unused := otree.FindUnused()
+
+			log.Printf("unused: %d\n", len(unused))
+
+			str := octree.STRTree{Entries: 16}
+			str.BuildWithout(otree.Tin(), unused)
+
+			out, err := str.Bytes()
+			if err != nil {
+				return err
+			}
+			h := sha1.New()
+			h.Write(out)
+			checksum := hex.EncodeToString(h.Sum(nil))
+			log.Printf("hash: %s\n", checksum)
+
+			if _, err := tx.Stmt(store).ExecContext(ctx, out, checksum, id); err != nil {
+				return err
+			}
+
+			return tx.Commit()
+		}(); {
+		case err == sql.ErrNoRows:
+			break loop
+		case err != nil:
+			return err
+		}
+	}
+
 	return nil
 }
 
-func connect(cc pgx.ConnConfig) error {
+func connect(cc pgx.ConnConfig, count int) error {
 	db := stdlib.OpenDB(cc)
 	defer db.Close()
 
@@ -40,7 +134,7 @@
 	}
 	defer conn.Close()
 
-	return process(ctx, conn)
+	return process(ctx, conn, count)
 }
 
 func main() {
@@ -51,6 +145,7 @@
 		password = flag.String("password", "so2Phie4", "database password")
 		port     = flag.Uint("port", 5432, "database port")
 		ssl      = flag.String("ssl", "prefer", "SSL mode")
+		count    = flag.Int("count", -1, "how many sounding results to convert")
 	)
 
 	flag.Parse()
@@ -66,7 +161,7 @@
 	cc.Password = *password
 	cc.Database = *db
 
-	if err := connect(cc); err != nil {
+	if err := connect(cc, *count); err != nil {
 		log.Fatalf("error: %v\n", err)
 	}
 }
diff -r 66fcd898efd9 -r 8f745c353784 pkg/octree/tree.go
--- a/pkg/octree/tree.go	Sun Oct 13 19:22:36 2019 +0200
+++ b/pkg/octree/tree.go	Sun Oct 13 22:15:55 2019 +0200
@@ -52,6 +52,50 @@
 	{0.5, 0.5, 1.0, 1.0},
 }
 
+func (ot *Tree) Tin() *Tin {
+	return &Tin{
+		EPSG:      ot.EPSG,
+		Vertices:  ot.vertices,
+		Triangles: ot.triangles,
+		Min:       ot.Min,
+		Max:       ot.Max,
+	}
+}
+
+func (ot *Tree) FindUnused() map[int32]struct{} {
+
+	used := make(map[int32]struct{}, len(ot.triangles))
+	for i := int32(0); i < int32(len(ot.triangles)); i++ {
+		used[i] = struct{}{}
+	}
+
+	stack := []int32{1}
+
+	for len(stack) > 0 {
+		top := stack[len(stack)-1]
+		stack = stack[:len(stack)-1]
+
+		if top > 0 { // node
+			if index := ot.index[top:]; len(index) > 7 {
+				for _, idx := range index[:8] {
+					if idx != 0 {
+						stack = append(stack, idx)
+					}
+				}
+			}
+		} else { // leaf
+			pos := -top - 1
+			n := ot.index[pos]
+			indices := ot.index[pos+1 : pos+1+n]
+			for _, idx := range indices {
+				delete(used, idx)
+			}
+		}
+	}
+
+	return used
+}
+
 func (ot *Tree) Value(x, y float64) (float64, bool) {
 
 	// out of bounding box