Mercurial > gemma

annotate cmd/octree2contour/store.go @ 720:aeaa2adf5a8b octree

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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 c0bba602b60e
children 5af9ab39e715
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
694
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
1 package main
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
2
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
3 import (
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
4 "bytes"
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
5 "database/sql"
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
6 "encoding/binary"
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
7 "math"
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
8 )
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
9
718
c0bba602b60e octree: define a type for LineStringZ.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 694
diff changeset
10 type (
c0bba602b60e octree: define a type for LineStringZ.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 694
diff changeset
11 lineStringZ []vertex
c0bba602b60e octree: define a type for LineStringZ.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 694
diff changeset
12 multiLineStringZ []lineStringZ
c0bba602b60e octree: define a type for LineStringZ.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 694
diff changeset
13 )
694
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
14
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
15 const (
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
16 wkbNDR byte = 1
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
17 wkbPointZ uint32 = 1000 + 1
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
18 wkbLineStringZ uint32 = 1000 + 2
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
19 wkbMultiLineStringZ uint32 = 1000 + 5
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
20 )
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
21
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
22 type result struct {
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
23 h float64
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
24 lines multiLineStringZ
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
25 }
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
26
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
27 const insertSQL = `
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
28 INSERT INTO waterway.contour_lines (height, geom)
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
29 VALUES ($1, ST_Transform(
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
30 ST_SetSRID(ST_GeomFromWKB($2), $3), 4326)::geography)
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
31 `
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
32
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
33 func store(all []result, epsg uint32) error {
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
34
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
35 return run(func(db *sql.DB) error {
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
36
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
37 tx, err := db.Begin()
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
38 if err != nil {
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
39 return err
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
40 }
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
41 defer tx.Rollback()
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
42
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
43 stmt, err := tx.Prepare(insertSQL)
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
44 if err != nil {
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
45 return err
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
46 }
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
47
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
48 for _, r := range all {
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
49 if _, err := stmt.Exec(r.h, r.lines.asWKB(), epsg); err != nil {
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
50 return err
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
51 }
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
52 }
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
53
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
54 return tx.Commit()
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
55 })
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
56 }
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
57
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
58 func (mls multiLineStringZ) asWKB() []byte {
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
59
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
60 var buf bytes.Buffer
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
61
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
62 binary.Write(&buf, binary.LittleEndian, wkbNDR)
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
63 binary.Write(&buf, binary.LittleEndian, wkbMultiLineStringZ)
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
64 binary.Write(&buf, binary.LittleEndian, uint32(len(mls)))
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
65
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
66 for _, ml := range mls {
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
67 binary.Write(&buf, binary.LittleEndian, wkbNDR)
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
68 binary.Write(&buf, binary.LittleEndian, wkbLineStringZ)
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
69 binary.Write(&buf, binary.LittleEndian, uint32(len(ml)))
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
70 for _, p := range ml {
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
71 binary.Write(&buf, binary.LittleEndian, math.Float64bits(p.x))
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
72 binary.Write(&buf, binary.LittleEndian, math.Float64bits(p.y))
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
73 binary.Write(&buf, binary.LittleEndian, math.Float64bits(p.z))
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
74 }
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
75 }
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
76
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
77 return buf.Bytes()
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
78 }