Mercurial > gemma
changeset 720:aeaa2adf5a8b octree
octree: write SQL insert statements for octree indices.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Fri, 21 Sep 2018 17:36:00 +0200 |
parents | d1b60ad2f50d |
children | ca82698349b7 |
files | cmd/tin2octree/main.go schema/gemma.sql |
diffstat | 2 files changed, 69 insertions(+), 15 deletions(-) [+] |
line wrap: on
line diff
--- a/cmd/tin2octree/main.go Fri Sep 21 16:29:38 2018 +0200 +++ b/cmd/tin2octree/main.go Fri Sep 21 17:36:00 2018 +0200 @@ -1,8 +1,13 @@ package main import ( + "bytes" + "crypto/sha1" "database/sql" + "encoding/base64" "flag" + "fmt" + "io" "log" "math" "os" @@ -17,8 +22,8 @@ bottleneck = flag.String("bottleneck", "", "bottleneck id") date = flag.String("date", "", "date info") file = flag.String("file", "", "save to file") - - utm = flag.Bool("utm", false, "fetch in matchin UTM zone") + insert = flag.Bool("insert", false, "write as SQL insert statement") + utm = flag.Bool("utm", true, "fetch in matchin UTM zone") dbhost = flag.String("dbhost", "localhost", "database host") dbport = flag.Uint("dbport", 5432, "database port") @@ -138,26 +143,70 @@ tb := &treeBuilder{t: &t} tb.build() + if *insert { + var w io.Writer + var f *os.File + + if *file != "" { + if f, err = os.Create(*file); err != nil { + log.Fatalf("error: %v\n", err) + } + w = f + } else { + w = os.Stdout + } + + var buf bytes.Buffer + if err := write(&buf, tb); err != nil { + log.Fatalf("error: %v\n", err) + } + data := buf.String() + h := sha1.New() + buf.WriteTo(h) + fmt.Fprintln(w, "BEGIN;") + fmt.Fprintln(w, "INSERT INTO waterway.octrees") + fmt.Fprintf(w, "SELECT sr.id, '%x',\n", h.Sum(nil)) + fmt.Fprint(w, "decode('") + fmt.Fprintf(w, "%s", base64.StdEncoding.EncodeToString([]byte(data))) + fmt.Fprintln(w, "', 'base64')") + fmt.Fprintln(w, "FROM waterway.sounding_results sr") + fmt.Fprintf(w, + "WHERE sr.bottleneck_id = '%s' AND sr.date_info = '%s'::date;\n", + *bottleneck, *date) + fmt.Fprintln(w, "END;") + + if f != nil { + if err := f.Close(); err != nil { + log.Fatalf("error: %v\n", err) + } + } + return + } + if *file != "" { f, err := os.Create(*file) if err != nil { log.Printf("error: %v\n", err) } - out := snappy.NewBufferedWriter(f) - if err := t.Serialize(out); err != nil { - f.Close() - log.Fatalf("error: %v\n", err) + err = write(f, tb) + if err2 := f.Close(); err == nil { + if err != nil { + err = err2 + } } - if err := tb.Serialize(out); err != nil { - f.Close() - log.Fatalf("error: %v\n", err) - } - if err := out.Flush(); err != nil { - f.Close() - log.Fatalf("error: %v\n", err) - } - if err := f.Close(); err != nil { + if err != nil { log.Fatalf("error: %v\n", err) } } } + +func write(w io.Writer, tb *treeBuilder) error { + out := snappy.NewBufferedWriter(w) + if err := tb.t.Serialize(out); err != nil { + return err + } + if err := tb.Serialize(out); err != nil { + return err + } + return out.Flush() +}
--- a/schema/gemma.sql Fri Sep 21 16:29:38 2018 +0200 +++ b/schema/gemma.sql Fri Sep 21 17:36:00 2018 +0200 @@ -406,6 +406,11 @@ CREATE INDEX meshes_gix ON meshes USING gist(geom) + CREATE TABLE octrees ( + sounding_result_id int NOT NULL UNIQUE REFERENCES sounding_results, + checksum varchar NOT NULL, + octree_index bytea NOT NULL + ) -- -- Fairway availability --