# HG changeset patch
# User Tom Gottfried <tom@intevation.de>
# Date 1541609690 -3600
# Node ID 2e6b47cdb2ca26c89aab368ecf6d2edee7d9cf29
# Parent  42617bba8709ca9215b24c9caa0ac5d988fc3c8e
Store octrees along with sounding results

The extra table with 1:1-relation to sounding results was a
provisorial solution with the intention to make it easily removable.

diff -r 42617bba8709 -r 2e6b47cdb2ca pkg/imports/sr.go
--- a/pkg/imports/sr.go	Wed Nov 07 17:54:31 2018 +0100
+++ b/pkg/imports/sr.go	Wed Nov 07 17:54:50 2018 +0100
@@ -103,15 +103,9 @@
   END + floor((ST_X(ST_Centroid(point_cloud::geometry))+180)/6)::int + 1`
 
 	insertOctreeSQL = `
-INSERT INTO waterway.octrees (
-  sounding_result_id,
-  checksum,
-  octree_index
-) VALUES (
-  $1,
-  $2,
-  $3
-)`
+UPDATE waterway.sounding_results SET
+  octree_checksum = $2, octree_index = $3
+WHERE id = $1`
 
 	insertContourSQL = `
 INSERT INTO waterway.sounding_results_contour_lines (
diff -r 42617bba8709 -r 2e6b47cdb2ca pkg/octree/cache.go
--- a/pkg/octree/cache.go	Wed Nov 07 17:54:31 2018 +0100
+++ b/pkg/octree/cache.go	Wed Nov 07 17:54:50 2018 +0100
@@ -45,19 +45,19 @@
 
 const (
 	fetchOctreeSQL = `
-SELECT checksum, octree_index
-FROM waterway.octrees ot
-JOIN waterway.sounding_results sr ON ot.sounding_result_id = sr.id
-WHERE sr.bottleneck_id = $1 AND sr.date_info = $2::date
+SELECT octree_checksum, octree_index
+FROM waterway.sounding_results
+WHERE bottleneck_id = $1 AND date_info = $2::date
+  AND octree_checksum NOT NULL AND octree_index NOT NULL
 `
 	checkOctreeSQL = `
 SELECT CASE
-  WHEN checksum = $3 THEN NULL
-  ELSE ot.octree_index
+  WHEN octree_checksum = $3 THEN NULL
+  ELSE octree_index
   END
-FROM waterway.octrees ot
-JOIN waterway.sounding_results sr ON ot.sounding_result_id = sr.id
-WHERE sr.bottleneck_id = $1 AND sr.date_info = $2::date
+FROM waterway.sounding_results
+WHERE bottleneck_id = $1 AND date_info = $2::date
+  AND octree_checksum NOT NULL AND octree_index NOT NULL
 `
 )
 
diff -r 42617bba8709 -r 2e6b47cdb2ca schema/gemma.sql
--- a/schema/gemma.sql	Wed Nov 07 17:54:31 2018 +0100
+++ b/schema/gemma.sql	Wed Nov 07 17:54:50 2018 +0100
@@ -416,15 +416,11 @@
         coverage varchar REFERENCES coverage_types,
         depth_reference char(3) NOT NULL REFERENCES depth_references,
         point_cloud geography(MULTIPOINTZ, 4326) NOT NULL,
+        octree_checksum varchar,
+        octree_index bytea,
         staging_done boolean NOT NULL DEFAULT false
     )
 
-    CREATE TABLE octrees (
-        sounding_result_id int NOT NULL UNIQUE REFERENCES sounding_results,
-        checksum varchar NOT NULL,
-        octree_index bytea NOT NULL
-    )
-
     CREATE TABLE sounding_results_contour_lines (
         sounding_result_id int NOT NULL REFERENCES sounding_results,
         height numeric NOT NULL,