Mercurial > gemma

comparison cmd/octree2contour/store.go @ 732:39b5cf9a6b8f

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Store sounding results contour lines into right table.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Sun, 23 Sep 2018 19:51:02 +0200
parents 41c8dc61f38f
children 83a9f04f1759
comparison
equal deleted inserted replaced
731:4d6329afc1ea 732:39b5cf9a6b8f
1 package main 1 package main
2 2
3 import ( 3 import (
4 "database/sql" 4 "database/sql"
5 "time"
5 6
6 "gemma.intevation.de/gemma/pkg/octree" 7 "gemma.intevation.de/gemma/pkg/octree"
7 ) 8 )
8 9
9 type result struct { 10 type result struct {
10 h float64 11 h float64
11 lines octree.MultiLineStringZ 12 lines octree.MultiLineStringZ
12 } 13 }
13 14
14 const insertSQL = ` 15 const (
15 INSERT INTO waterway.contour_lines (height, geom) 16 deleteSQL = `
16 VALUES ($1, ST_Transform( 17 DELETE FROM waterway.sounding_results_lines
17 ST_SetSRID(ST_GeomFromWKB($2), $3), 4326)::geography) 18 WHERE sounding_result_id IN (SELECT id
19 FROM waterway.sounding_results
20 WHERE bottleneck_id = $1 AND date_info = $2)
18 ` 21 `
22 insertSQL = `
23 INSERT INTO waterway.sounding_results_lines
24 SELECT
25 sounding_result_id,
26 $1,
27 ST_Transform(ST_SetSRID(ST_GeomFromWKB($2), $3), 4326)::geography
28 FROM waterway.sounding_results
29 WHERE bottleneck_id = $4 AND date_info = $5
30 `
31 )
19 32
20 func store(all []result, epsg uint32) error { 33 func store(
34 all []result, epsg uint32,
35 bottleneck string, date time.Time,
36 ) error {
21 37
22 return run(func(db *sql.DB) error { 38 return run(func(db *sql.DB) error {
23 39
24 tx, err := db.Begin() 40 tx, err := db.Begin()
25 if err != nil { 41 if err != nil {
26 return err 42 return err
27 } 43 }
28 defer tx.Rollback() 44 defer tx.Rollback()
29 45
46 if _, err := tx.Exec(deleteSQL, bottleneck, date); err != nil {
47 return err
48 }
49
30 stmt, err := tx.Prepare(insertSQL) 50 stmt, err := tx.Prepare(insertSQL)
31 if err != nil { 51 if err != nil {
32 return err 52 return err
33 } 53 }
34 54
35 for _, r := range all { 55 for _, r := range all {
36 if _, err := stmt.Exec(r.h, r.lines.AsWKB(), epsg); err != nil { 56 if _, err := stmt.Exec(
57 r.h, r.lines.AsWKB(), epsg,
58 bottleneck, date,
59 ); err != nil {
37 return err 60 return err
38 } 61 }
39 } 62 }
40 63
41 return tx.Commit() 64 return tx.Commit()