changeset 2583:5c6a7e69b02d

Morphological differences: Store calculated iso-lines in database.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Mon, 11 Mar 2019 16:42:38 +0100
parents ab819b1b748b
children 0c400404a237
files cmd/octreediff/main.go pkg/controllers/diff.go
diffstat 2 files changed, 92 insertions(+), 44 deletions(-) [+]
line wrap: on
line diff
--- a/cmd/octreediff/main.go	Mon Mar 11 16:36:04 2019 +0100
+++ b/cmd/octreediff/main.go	Mon Mar 11 16:42:38 2019 +0100
@@ -65,47 +65,6 @@
     4326
   )
 `
-	insertContourSQLClipped = `
-WITH joined AS (
-  SELECT
-    sr.area    AS area,
-    sr.date_info AS date_info
-  FROM waterway.sounding_results sr JOIN
-     waterway.bottlenecks bn ON sr.bottleneck_id = bn.id
-  WHERE bn.bottleneck_id = $1
-),
-inter AS (
-  SELECT
-  ST_Buffer(
-    ST_intersection(
-      (SELECT ST_Transform(area::geometry, $2::int) FROM joined WHERE date_info = $3::date),
-      (SELECT ST_Transform(area::geometry, $2::int) FROM joined WHERE date_info = $4::date)
-    ),
-    0.001) AS area
-)
-INSERT INTO diff_contour_lines (
-  height,
-  lines
-)
-SELECT
-  $5,
-  ST_Transform(
-    ST_Multi(
-	  ST_intersection(
-		  ST_CollectionExtract(
-			ST_SimplifyPreserveTopology(
-			  ST_Multi(ST_Collectionextract(
-				ST_MakeValid(ST_GeomFromWKB($6, $2::integer)), 2)),
-			  $7
-			),
-			2
-		  ),
-		area
-	  )
-	),
-    4326
-  )
-  FROM inter`
 )
 
 func check(err error) {
--- a/pkg/controllers/diff.go	Mon Mar 11 16:36:04 2019 +0100
+++ b/pkg/controllers/diff.go	Mon Mar 11 16:42:38 2019 +0100
@@ -29,6 +29,11 @@
 )
 
 const (
+	contourTolerance = 0.1
+	contourStep      = 0.1
+)
+
+const (
 	diffIDSQL = `
 SELECT sd.id FROM
   caching.sounding_differences sd JOIN 
@@ -40,6 +45,41 @@
   srs.date_info = $2::date AND
   srm.date_info = $3::date AND
 `
+	insertDiffSQL = `
+WITH soundings AS (
+  SELECT sr.id AS id, sr.date_info AS date_info FROM
+  waterway.sounding_results sr JOIN
+  waterway.bottlenecks bns ON sr.bottleneck_id = bns.id
+  WHERE bns.objnam = $1
+)
+INSERT INTO caching.sounding_differences (minuend, subtrahend)
+SELECT m.id, s.id FROM soundings m, soundings s
+WHERE m.date_info = $2::date AND s.date_info = $3::date
+RETURNING id
+`
+	insertDiffContourSQL = `
+INSERT INTO caching.caching.sounding_differences_contour_lines (
+  sounding_differences_id,
+  height,
+  lines
+)
+SELECT
+  $5,
+  $4,
+  ST_Transform(
+    ST_Multi(
+      ST_CollectionExtract(
+        ST_SimplifyPreserveTopology(
+          ST_Multi(ST_Collectionextract(
+            ST_MakeValid(ST_GeomFromWKB($1, $2::integer)), 2)),
+          $3
+        ),
+        2
+      )
+    ),
+    4326
+  )
+`
 )
 
 // Only allow three diffence calculation at once.
@@ -52,7 +92,8 @@
 	conn *sql.Conn,
 ) (jr JSONResult, err error) {
 
-	start := time.Now()
+	begin := time.Now()
+	start := begin
 
 	dci := input.(models.DiffCalculationInput)
 
@@ -209,9 +250,12 @@
 
 	log.Printf("info: min/max: %f %f\n", tree.Min.Z, tree.Max.Z)
 
-	heights := octree.SampleDiffHeights(tree.Min.Z, tree.Max.Z, 0.1)
+	heights := octree.SampleDiffHeights(
+		tree.Min.Z, tree.Max.Z, contourStep)
 	log.Printf("info: num heights: %d\n", len(heights))
 
+	start = time.Now()
+
 	// XXX: Maybe we should start this transaction earlier!?
 	var tx *sql.Tx
 	if tx, err = conn.BeginTx(ctx, nil); err != nil {
@@ -219,7 +263,52 @@
 	}
 	defer tx.Rollback()
 
-	// TODO: Implement me!
+	var stmt *sql.Stmt
+	if stmt, err = tx.PrepareContext(ctx, insertDiffContourSQL); err != nil {
+		return
+	}
+	defer stmt.Close()
+
+	if err = tx.QueryRowContext(
+		ctx,
+		insertDiffSQL,
+		dci.Bottleneck,
+		dci.Minuend.Time,
+		dci.Subtrahend.Time,
+	).Scan(&id); err != nil {
+		return
+	}
 
+	octree.DoContours(tree, heights, func(res *octree.ContourResult) {
+		if err == nil && len(res.Lines) > 0 {
+			_, err = stmt.ExecContext(
+				ctx,
+				res.Lines.AsWKB2D(),
+				minuendTree.EPSG,
+				contourTolerance,
+				res.Height,
+				id,
+			)
+		}
+	})
+
+	log.Printf("info: calculating and storing iso lines took %v\n",
+		time.Since(start))
+
+	if err != nil {
+		return
+	}
+
+	if err = tx.Commit(); err != nil {
+		log.Printf("info: difference calculation failed after %v\n",
+			time.Since(begin))
+		return
+	}
+	log.Printf("info: difference calculation succeed after %v\n",
+		time.Since(begin))
+
+	jr = JSONResult{
+		Result: map[string]int64{"id": id},
+	}
 	return
 }