Mercurial > gemma
changeset 1131:2e6b47cdb2ca
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.
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Wed, 07 Nov 2018 17:54:50 +0100 |
| parents | 42617bba8709 |
| children | da0a62fcfd80 |
| files | pkg/imports/sr.go pkg/octree/cache.go schema/gemma.sql |
| diffstat | 3 files changed, 14 insertions(+), 24 deletions(-) [+] |
line wrap: on
line diff
--- 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 (
--- 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 ` )
--- 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,