Mercurial > gemma

annotate cmd/octree2contour/store.go @ 925:15bf101e1522

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Send 2D X/Y vertices to the database directly instead of sending 3D data and dropping the Z value afterwards.
author Sascha L. Teichmann <teichmann@intevation.de>
date Sat, 06 Oct 2018 13:34:51 +0200
parents 52cb0b82b490
children
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 "database/sql"
732
39b5cf9a6b8f Store sounding results contour lines into right table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 727
diff changeset
5 "time"
694
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
6
727
41c8dc61f38f Moved octree loading stuff to octree package.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 726
diff changeset
7 "gemma.intevation.de/gemma/pkg/octree"
694
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
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
10 type result struct {
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
11 h float64
727
41c8dc61f38f Moved octree loading stuff to octree package.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 726
diff changeset
12 lines octree.MultiLineStringZ
694
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
13 }
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
14
732
39b5cf9a6b8f Store sounding results contour lines into right table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 727
diff changeset
15 const (
39b5cf9a6b8f Store sounding results contour lines into right table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 727
diff changeset
16 deleteSQL = `
735
83a9f04f1759 Spell table names correctly.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 732
diff changeset
17 DELETE FROM waterway.sounding_results_contour_lines
732
39b5cf9a6b8f Store sounding results contour lines into right table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 727
diff changeset
18 WHERE sounding_result_id IN (SELECT id
39b5cf9a6b8f Store sounding results contour lines into right table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 727
diff changeset
19 FROM waterway.sounding_results
39b5cf9a6b8f Store sounding results contour lines into right table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 727
diff changeset
20 WHERE bottleneck_id = $1 AND date_info = $2)
694
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
21 `
732
39b5cf9a6b8f Store sounding results contour lines into right table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 727
diff changeset
22 insertSQL = `
735
83a9f04f1759 Spell table names correctly.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 732
diff changeset
23 INSERT INTO waterway.sounding_results_contour_lines
736
34ea9d5df653 Fixed problem with unique constraint for countour lines.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 735
diff changeset
24 (sounding_result_id, height, lines)
732
39b5cf9a6b8f Store sounding results contour lines into right table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 727
diff changeset
25 SELECT
742
fb9faf2c4f60 Clip contour lines against bounding area of sounding result.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 736
diff changeset
26 sr.id,
732
39b5cf9a6b8f Store sounding results contour lines into right table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 727
diff changeset
27 $1,
742
fb9faf2c4f60 Clip contour lines against bounding area of sounding result.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 736
diff changeset
28 ST_Transform(
920
862cc5ba4d1d Merge contour lines
Tom Gottfried <tom@intevation.de>
parents: 919
diff changeset
29 ST_Multi(
862cc5ba4d1d Merge contour lines
Tom Gottfried <tom@intevation.de>
parents: 919
diff changeset
30 ST_CollectionExtract(
919
271561dce2e6 Store contour lines in 2D
Tom Gottfried <tom@intevation.de>
parents: 742
diff changeset
31 ST_Intersection(
271561dce2e6 Store contour lines in 2D
Tom Gottfried <tom@intevation.de>
parents: 742
diff changeset
32 ST_Transform(sr.area::geometry, $3::integer),
921
52cb0b82b490 Simplify contour lines on storage
Tom Gottfried <tom@intevation.de>
parents: 920
diff changeset
33 ST_SimplifyPreserveTopology(
925
15bf101e1522 Send 2D X/Y vertices to the database directly instead of sending 3D data and dropping the Z value afterwards.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 921
diff changeset
34 ST_LineMerge(ST_GeomFromWKB($2, $3::integer)),
921
52cb0b82b490 Simplify contour lines on storage
Tom Gottfried <tom@intevation.de>
parents: 920
diff changeset
35 $6
52cb0b82b490 Simplify contour lines on storage
Tom Gottfried <tom@intevation.de>
parents: 920
diff changeset
36 )
920
862cc5ba4d1d Merge contour lines
Tom Gottfried <tom@intevation.de>
parents: 919
diff changeset
37 ),
862cc5ba4d1d Merge contour lines
Tom Gottfried <tom@intevation.de>
parents: 919
diff changeset
38 2
862cc5ba4d1d Merge contour lines
Tom Gottfried <tom@intevation.de>
parents: 919
diff changeset
39 )
919
271561dce2e6 Store contour lines in 2D
Tom Gottfried <tom@intevation.de>
parents: 742
diff changeset
40 ),
271561dce2e6 Store contour lines in 2D
Tom Gottfried <tom@intevation.de>
parents: 742
diff changeset
41 4326
271561dce2e6 Store contour lines in 2D
Tom Gottfried <tom@intevation.de>
parents: 742
diff changeset
42 )
742
fb9faf2c4f60 Clip contour lines against bounding area of sounding result.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 736
diff changeset
43 FROM waterway.sounding_results sr
732
39b5cf9a6b8f Store sounding results contour lines into right table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 727
diff changeset
44 WHERE bottleneck_id = $4 AND date_info = $5
39b5cf9a6b8f Store sounding results contour lines into right table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 727
diff changeset
45 `
39b5cf9a6b8f Store sounding results contour lines into right table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 727
diff changeset
46 )
694
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
47
732
39b5cf9a6b8f Store sounding results contour lines into right table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 727
diff changeset
48 func store(
39b5cf9a6b8f Store sounding results contour lines into right table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 727
diff changeset
49 all []result, epsg uint32,
39b5cf9a6b8f Store sounding results contour lines into right table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 727
diff changeset
50 bottleneck string, date time.Time,
921
52cb0b82b490 Simplify contour lines on storage
Tom Gottfried <tom@intevation.de>
parents: 920
diff changeset
51 tol float64,
732
39b5cf9a6b8f Store sounding results contour lines into right table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 727
diff changeset
52 ) error {
694
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 run(func(db *sql.DB) error {
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 tx, err := db.Begin()
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
57 if err != nil {
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
58 return err
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 defer tx.Rollback()
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
61
732
39b5cf9a6b8f Store sounding results contour lines into right table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 727
diff changeset
62 if _, err := tx.Exec(deleteSQL, bottleneck, date); err != nil {
39b5cf9a6b8f Store sounding results contour lines into right table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 727
diff changeset
63 return err
39b5cf9a6b8f Store sounding results contour lines into right table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 727
diff changeset
64 }
39b5cf9a6b8f Store sounding results contour lines into right table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 727
diff changeset
65
694
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
66 stmt, err := tx.Prepare(insertSQL)
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
67 if err != nil {
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
68 return err
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
69 }
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
70
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
71 for _, r := range all {
732
39b5cf9a6b8f Store sounding results contour lines into right table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 727
diff changeset
72 if _, err := stmt.Exec(
925
15bf101e1522 Send 2D X/Y vertices to the database directly instead of sending 3D data and dropping the Z value afterwards.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 921
diff changeset
73 r.h, r.lines.AsWKB2D(), epsg,
732
39b5cf9a6b8f Store sounding results contour lines into right table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 727
diff changeset
74 bottleneck, date,
921
52cb0b82b490 Simplify contour lines on storage
Tom Gottfried <tom@intevation.de>
parents: 920
diff changeset
75 tol,
732
39b5cf9a6b8f Store sounding results contour lines into right table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 727
diff changeset
76 ); err != nil {
694
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
77 return err
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
78 }
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
79 }
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
80
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
81 return tx.Commit()
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
82 })
a9783d8f74ed octree: Store contour lines into postgres/postgis.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
83 }