Mercurial > gemma

changeset 4578:c657dec6b0fa iso-areas

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Removed old contours from backend and database.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Tue, 08 Oct 2019 12:10:55 +0200
parents 82029885c11b
children 4c56682f83f7
files pkg/controllers/diff.go pkg/imports/isr.go pkg/imports/sr.go schema/default_sysconfig.sql schema/gemma.sql schema/geoserver_views.sql schema/updates/1204/02.delete-contours.sql
diffstat 7 files changed, 10 insertions(+), 176 deletions(-) [+]
line wrap: on
line diff
--- a/pkg/controllers/diff.go	Tue Oct 08 10:37:02 2019 +0200
+++ b/pkg/controllers/diff.go	Tue Oct 08 12:10:55 2019 +0200
@@ -63,29 +63,6 @@
 WHERE m.date_info = $2::date AND s.date_info = $3::date
 RETURNING id
 `
-	insertDiffContourSQL = `
-INSERT INTO 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
-  )
-`
 	insertDiffIsoAreasQL = `
 INSERT INTO caching.sounding_differences_iso_areas (
   sounding_differences_id,
@@ -302,12 +279,6 @@
 
 	log.Printf("info: num heights: %d\n", len(heights))
 
-	var stmt *sql.Stmt
-	if stmt, err = tx.PrepareContext(ctx, insertDiffContourSQL); err != nil {
-		return
-	}
-	defer stmt.Close()
-
 	var isoStmt *sql.Stmt
 	if isoStmt, err = tx.PrepareContext(ctx, insertDiffIsoAreasQL); err != nil {
 		return
@@ -326,19 +297,6 @@
 
 	heights = common.DedupFloat64s(heights)
 
-	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,
-			)
-		}
-	})
-
 	areas := tree.TraceAreas(heights, isoCellSize)
 
 	for i, a := range areas {
--- a/pkg/imports/isr.go	Tue Oct 08 10:37:02 2019 +0200
+++ b/pkg/imports/isr.go	Tue Oct 08 12:10:55 2019 +0200
@@ -53,10 +53,6 @@
 FROM waterway.sounding_results
 ORDER BY bottleneck_id
 `
-	deleteContourLinesSQL = `
-DELETE FROM waterway.sounding_results_contour_lines
-WHERE sounding_result_id = $1
-`
 	deleteIsoAreasSQL = `
 DELETE FROM waterway.sounding_results_iso_areas
 WHERE sounding_result_id = $1
@@ -167,12 +163,6 @@
 	}
 	defer tx.Rollback()
 
-	insertContoursStmt, err := tx.Prepare(insertContourSQL)
-	if err != nil {
-		return err
-	}
-	defer insertContoursStmt.Close()
-
 	insertAreasStmt, err := tx.Prepare(insertIsoAreasSQL)
 	if err != nil {
 		return err
@@ -188,29 +178,11 @@
 		hs := octree.ExtrapolateClassBreaks(heights, tree.Min.Z, tree.Max.Z)
 		hs = common.DedupFloat64s(hs)
 
-		// Delete the old contour lines.
-		if _, err := tx.ExecContext(ctx, deleteContourLinesSQL, sr); err != nil {
-			return err
-		}
-
 		// Delete the old iso areas.
 		if _, err := tx.ExecContext(ctx, deleteIsoAreasSQL, sr); err != nil {
 			return err
 		}
 
-		octree.DoContours(tree, hs, func(res *octree.ContourResult) {
-			if err == nil && len(res.Lines) > 0 {
-				_, err = insertContoursStmt.ExecContext(
-					ctx,
-					sr, res.Height, tree.EPSG,
-					res.Lines.AsWKB2D(),
-					contourTolerance)
-			}
-		})
-		if err != nil {
-			return err
-		}
-
 		// Calculate and store the iso areas.
 		areas := tree.TraceAreas(hs, isoCellSize)
 		for i, a := range areas {
--- a/pkg/imports/sr.go	Tue Oct 08 10:37:02 2019 +0200
+++ b/pkg/imports/sr.go	Tue Oct 08 12:10:55 2019 +0200
@@ -97,10 +97,7 @@
 
 func (srJobCreator) Depends() [2][]string {
 	return [2][]string{
-		{"sounding_results",
-			"sounding_results_contour_lines",
-			"sounding_results_iso_areas",
-		},
+		{"sounding_results", "sounding_results_iso_areas"},
 		{"bottlenecks"},
 	}
 }
@@ -164,32 +161,6 @@
   ST_AsBinary(ST_Buffer(ST_MakeValid(ST_GeomFromWKB($1, $2::integer)), 0.0)),
   ST_AsBinary(ST_Buffer(ST_MakeValid(ST_GeomFromWKB($1, $2::integer)), 0.1))`
 
-	insertContourSQL = `
-INSERT INTO waterway.sounding_results_contour_lines (
-  sounding_result_id,
-  height,
-  lines
-)
-SELECT
-  $1,
-  $2,
-  ST_Transform(
-    ST_Multi(
-      ST_CollectionExtract(
-        ST_SimplifyPreserveTopology(
-          ST_Multi(ST_Collectionextract(
-            ST_MakeValid(ST_GeomFromWKB($4, $3::integer)), 2)),
-          $5
-        ),
-        2
-      )
-    ),
-    4326
-  )
-FROM waterway.sounding_results sr
-WHERE id = $1
-`
-
 	insertIsoAreasSQL = `
 INSERT INTO waterway.sounding_results_iso_areas (
   sounding_result_id,
@@ -900,10 +871,6 @@
 
 	heights = common.DedupFloat64s(heights)
 
-	if err := generateContours(ctx, tx, feedback, tree, heights, id); err != nil {
-		return err
-	}
-
 	return generateIsoAreas(ctx, tx, feedback, tree, heights, id)
 }
 
@@ -967,37 +934,3 @@
 
 	return nil
 }
-
-func generateContours(
-	ctx context.Context,
-	tx *sql.Tx,
-	feedback Feedback,
-	tree *octree.Tree,
-	heights []float64,
-	id int64,
-) error {
-	feedback.Info("Generate contour lines")
-	start := time.Now()
-	defer func() {
-		feedback.Info("Generating contour lines took %v",
-			time.Since(start))
-	}()
-
-	stmt, err := tx.PrepareContext(ctx, insertContourSQL)
-	if err != nil {
-		return err
-	}
-	defer stmt.Close()
-
-	octree.DoContours(tree, heights, func(res *octree.ContourResult) {
-		if err == nil && len(res.Lines) > 0 {
-			_, err = stmt.ExecContext(
-				ctx,
-				id, res.Height, tree.EPSG,
-				res.Lines.AsWKB2D(),
-				contourTolerance)
-		}
-	})
-
-	return err
-}
--- a/schema/default_sysconfig.sql	Tue Oct 08 10:37:02 2019 +0200
+++ b/schema/default_sysconfig.sql	Tue Oct 08 12:10:55 2019 +0200
@@ -30,13 +30,11 @@
     ('waterway.gauges_geoserver'),
     ('waterway.distance_marks_ashore_geoserver'),
     ('waterway.distance_marks_geoserver'),
-    ('waterway.sounding_results_contour_lines_geoserver'),
     ('waterway.bottlenecks_geoserver'),
     ('waterway.bottleneck_overview'),
     ('waterway.waterway_axis'),
     ('waterway.waterway_area'),
-    ('waterway.waterway_profiles'),
-    ('waterway.sounding_differences');
+    ('waterway.waterway_profiles');
 
 --
 -- Settings
--- a/schema/gemma.sql	Tue Oct 08 10:37:02 2019 +0200
+++ b/schema/gemma.sql	Tue Oct 08 12:10:55 2019 +0200
@@ -894,14 +894,6 @@
         UNIQUE (minuend, subtrahend)
     )
 
-    CREATE TABLE sounding_differences_contour_lines (
-        sounding_differences_id int NOT NULL REFERENCES sounding_differences(id)
-                                    ON DELETE CASCADE,
-        height numeric NOT NULL,
-        lines  geography(multilinestring, 4326) NOT NULL,
-        PRIMARY KEY (sounding_differences_id, height)
-    )
-
     CREATE TABLE sounding_differences_iso_areas (
         sounding_differences_id int NOT NULL REFERENCES sounding_differences(id)
                                     ON DELETE CASCADE,
--- a/schema/geoserver_views.sql	Tue Oct 08 10:37:02 2019 +0200
+++ b/schema/geoserver_views.sql	Tue Oct 08 12:10:55 2019 +0200
@@ -182,14 +182,6 @@
             ON isrs_fromtext(g.isrs_code) <@ s.section
     GROUP BY s.id;
 
-CREATE OR REPLACE VIEW waterway.sounding_results_contour_lines_geoserver AS
-    SELECT bottleneck_id,
-        date_info,
-        height,
-        CAST(lines AS geometry(multilinestring, 4326)) AS lines
-    FROM waterway.sounding_results_contour_lines cl
-        JOIN waterway.sounding_results sr ON sr.id = cl.sounding_result_id;
-
 CREATE OR REPLACE VIEW waterway.bottleneck_overview AS
     SELECT
         objnam AS name,
@@ -204,22 +196,3 @@
             GROUP BY bottleneck_id) sr ON sr.bottleneck_id = bn.bottleneck_id
     WHERE bn.validity @> current_timestamp
     ORDER BY objnam;
-
-CREATE OR REPLACE VIEW waterway.sounding_differences AS
-    SELECT
-        sd.id           AS id,
-        bn.objnam       AS objnam,
-        srm.date_info   AS minuend,
-        srs.date_info   AS subtrahend,
-        sdcl.height     AS height,
-        CAST(sdcl.lines AS geometry(multilinestring, 4326)) AS lines
-    FROM caching.sounding_differences sd
-        JOIN caching.sounding_differences_contour_lines sdcl
-            ON sd.id = sdcl.sounding_differences_id
-        JOIN waterway.sounding_results srm
-            ON sd.minuend = srm.id
-        JOIN waterway.sounding_results srs
-            ON sd.subtrahend = srs.id
-        JOIN waterway.bottlenecks bn
-            ON srm.bottleneck_id = bn.bottleneck_id
-                AND srm.date_info::timestamptz <@ bn.validity;
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/schema/updates/1204/02.delete-contours.sql	Tue Oct 08 12:10:55 2019 +0200
@@ -0,0 +1,8 @@
+DELETE FROM sys_admin.published_services WHERE name = 'waterway.sounding_differences'::regclass;
+DELETE FROM sys_admin.published_services WHERE name = 'waterway.sounding_results_contour_lines_geoserver'::regclass;
+
+DROP VIEW waterway.sounding_results_contour_lines_geoserver;
+DROP VIEW waterway.sounding_differences;
+DROP TABLE caching.sounding_differences_contour_lines;
+DROP TABLE waterway.sounding_results_contour_lines;
+