Mercurial > gemma

changeset 2912:93fa55bce126

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add utility function to get users area of responsibility
author Tom Gottfried <tom@intevation.de>
date Tue, 02 Apr 2019 19:25:17 +0200
parents a75c546ef498
children cf6f595af2cb
files pkg/imports/dma.go pkg/imports/fd.go pkg/imports/wa.go pkg/imports/wx.go schema/manage_users.sql schema/manage_users_tests.sql schema/run_tests.sh
diffstat 7 files changed, 49 insertions(+), 44 deletions(-) [+]
line wrap: on
line diff
--- a/pkg/imports/dma.go	Tue Apr 02 19:24:08 2019 +0200
+++ b/pkg/imports/dma.go	Tue Apr 02 19:25:17 2019 +0200
@@ -77,22 +77,16 @@
 const (
 	deleteDistanceMarksSQL = `
 WITH resp AS (
-  SELECT best_utm(area) AS t,
-         ST_Transform(area::geometry, best_utm(area)) AS a
-  FROM users.responsibility_areas
-  WHERE country = users.current_user_country()
+  SELECT users.current_user_area_utm() AS a
 )
 DELETE FROM waterway.distance_marks
 WHERE ST_Covers(
   (SELECT a FROM resp),
-  ST_Transform(geom::geometry, (SELECT t FROM resp)))
+  ST_Transform(geom::geometry, (SELECT ST_SRID(a) FROM resp)))
 `
 	insertDistanceMarksSQL = `
 WITH resp AS (
-  SELECT best_utm(area) AS t,
-         ST_Transform(area::geometry, best_utm(area)) AS a
-  FROM users.responsibility_areas
-  WHERE country = users.current_user_country()
+  SELECT users.current_user_area_utm() AS a
 )
 INSERT INTO waterway.distance_marks (geom, catdis)
 SELECT ST_Transform(clipped.geom, 4326)::geography, $3 FROM (
@@ -101,7 +95,7 @@
          (SELECT a FROM resp),
          ST_Transform(
            ST_GeomFromWKB($1, $2::integer),
-           (SELECT t FROM resp)
+           (SELECT ST_SRID(a) FROM resp)
          )
        )
      )).geom AS geom
--- a/pkg/imports/fd.go	Tue Apr 02 19:24:08 2019 +0200
+++ b/pkg/imports/fd.go	Tue Apr 02 19:25:17 2019 +0200
@@ -139,15 +139,12 @@
 
 	deleteFairwayDimensionSQL = `
 WITH resp AS (
-  SELECT best_utm(area) AS t,
-         ST_Transform(area::geometry, best_utm(area)) AS a
-  FROM users.responsibility_areas
-  WHERE country = users.current_user_country()
+  SELECT users.current_user_area_utm() AS a
 )
 DELETE FROM waterway.fairway_dimensions
 WHERE ST_Covers(
   (SELECT a FROM resp),
-  ST_Transform(area::geometry, (SELECT t FROM resp)))
+  ST_Transform(area::geometry, (SELECT ST_SRID(a) FROM resp)))
   AND staging_done
   AND level_of_service = $1::smallint`
 
@@ -155,10 +152,7 @@
 	// avoid errors due to reprojection.
 	insertFairwayDimensionSQL = `
 WITH resp AS (
-  SELECT best_utm(area) AS t,
-         ST_Transform(area::geometry, best_utm(area)) AS a
-  FROM users.responsibility_areas
-  WHERE country = users.current_user_country()
+  SELECT users.current_user_area_utm() AS a
 )
 INSERT INTO waterway.fairway_dimensions (area, level_of_service, min_width, max_width, min_depth, date_info, source_organization)
 SELECT ST_Transform(clipped.geom, 4326)::geography, $3, $4, $5, $6, $7, $8 FROM (
@@ -167,7 +161,7 @@
          (SELECT ST_Buffer(a, -0.0001) FROM resp),
          ST_MakeValid(ST_Transform(
            ST_GeomFromWKB($1, $2::integer),
-           (SELECT t FROM resp)
+           (SELECT ST_SRID(a) FROM resp)
          ))
        )
      )).geom AS geom
--- a/pkg/imports/wa.go	Tue Apr 02 19:24:08 2019 +0200
+++ b/pkg/imports/wa.go	Tue Apr 02 19:25:17 2019 +0200
@@ -81,22 +81,16 @@
 const (
 	deleteWaterwayAreaSQL = `
 WITH resp AS (
-  SELECT best_utm(area) AS t,
-         ST_Transform(area::geometry, best_utm(area)) AS a
-  FROM users.responsibility_areas
-  WHERE country = users.current_user_country()
+  SELECT users.current_user_area_utm() AS a
 )
 DELETE FROM waterway.waterway_area
 WHERE pg_has_role('sys_admin', 'MEMBER')
   OR ST_Covers((SELECT a FROM resp),
-    ST_Transform(area::geometry, (SELECT t FROM resp)))
+    ST_Transform(area::geometry, (SELECT ST_SRID(a) FROM resp)))
 `
 	insertWaterwayAreaSQL = `
 WITH resp AS (
-  SELECT best_utm(area) AS t,
-         ST_Transform(area::geometry, best_utm(area)) AS a
-  FROM users.responsibility_areas
-  WHERE country = users.current_user_country()
+  SELECT users.current_user_area_utm() AS a
 )
 INSERT INTO waterway.waterway_area (area, catccl, dirimp)
 SELECT dmp.geom, $3, $4
@@ -106,7 +100,7 @@
         THEN ST_MakeValid(ST_Transform(new_area,
           best_utm(ST_Transform(new_area, 4326))))
         ELSE ST_Intersection((SELECT a FROM resp),
-          ST_MakeValid(ST_Transform(new_area, (SELECT t FROM resp))))
+          ST_MakeValid(ST_Transform(new_area, (SELECT ST_SRID(a) FROM resp))))
         END,
       3), 4326)) AS dmp
 `
--- a/pkg/imports/wx.go	Tue Apr 02 19:24:08 2019 +0200
+++ b/pkg/imports/wx.go	Tue Apr 02 19:25:17 2019 +0200
@@ -80,15 +80,12 @@
 const (
 	deleteWaterwayAxisSQL = `
 WITH resp AS (
-  SELECT best_utm(area) AS t,
-         ST_Transform(area::geometry, best_utm(area)) AS a
-  FROM users.responsibility_areas
-  WHERE country = users.current_user_country()
+  SELECT users.current_user_area_utm() AS a
 )
 DELETE FROM waterway.waterway_axis
 WHERE pg_has_role('sys_admin', 'MEMBER')
   OR ST_Covers((SELECT a FROM resp),
-    ST_Transform(wtwaxs::geometry, (SELECT t FROM resp)))
+    ST_Transform(wtwaxs::geometry, (SELECT ST_SRID(a) FROM resp)))
 `
 
 	checkCrossingAxisSQL = `
@@ -100,10 +97,7 @@
 
 	insertWaterwayAxisSQL = `
 WITH resp AS (
-  SELECT best_utm(area) AS t,
-         ST_Transform(area::geometry, best_utm(area)) AS a
-  FROM users.responsibility_areas
-  WHERE country = users.current_user_country()
+  SELECT users.current_user_area_utm() AS a
 )
 INSERT INTO waterway.waterway_axis (wtwaxs, objnam, nobjnam)
 SELECT dmp.geom, $3, $4
@@ -113,7 +107,7 @@
         THEN ST_Node(ST_Transform(new_line,
           best_utm(ST_Transform(new_line, 4326))))
         ELSE ST_Intersection((SELECT a FROM resp),
-          ST_Node(ST_Transform(new_line, (SELECT t FROM resp))))
+          ST_Node(ST_Transform(new_line, (SELECT ST_SRID(a) FROM resp))))
         END,
       2), 4326)) AS dmp
 RETURNING id
--- a/schema/manage_users.sql	Tue Apr 02 19:24:08 2019 +0200
+++ b/schema/manage_users.sql	Tue Apr 02 19:25:17 2019 +0200
@@ -4,7 +4,7 @@
 -- SPDX-License-Identifier: AGPL-3.0-or-later
 -- License-Filename: LICENSES/AGPL-3.0.txt
 
--- Copyright (C) 2018 by via donau
+-- Copyright (C) 2018, 2019 by via donau
 --   – Österreichische Wasserstraßen-Gesellschaft mbH
 -- Software engineering by Intevation GmbH
 
@@ -68,6 +68,22 @@
     STABLE PARALLEL SAFE;
 
 
+CREATE OR REPLACE FUNCTION users.current_user_area_utm()
+    RETURNS geometry
+    AS $$
+        DECLARE utm_area geometry;
+        BEGIN
+            SELECT ST_Transform(area::geometry, best_utm(area))
+                INTO STRICT utm_area
+                FROM users.responsibility_areas
+                WHERE country = users.current_user_country();
+            RETURN utm_area;
+        END;
+    $$
+    LANGUAGE plpgsql
+    STABLE PARALLEL SAFE;
+
+
 CREATE OR REPLACE FUNCTION internal.create_user() RETURNS trigger
 AS $$
 BEGIN
--- a/schema/manage_users_tests.sql	Tue Apr 02 19:24:08 2019 +0200
+++ b/schema/manage_users_tests.sql	Tue Apr 02 19:25:17 2019 +0200
@@ -4,7 +4,7 @@
 -- SPDX-License-Identifier: AGPL-3.0-or-later
 -- License-Filename: LICENSES/AGPL-3.0.txt
 
--- Copyright (C) 2018 by via donau
+-- Copyright (C) 2018, 2019 by via donau
 --   – Österreichische Wasserstraßen-Gesellschaft mbH
 -- Software engineering by Intevation GmbH
 
@@ -17,10 +17,23 @@
 
 SET search_path TO public, gemma, gemma_waterway, gemma_fairway;
 
+SET SESSION AUTHORIZATION test_user_at;
+--
+-- Utility functions
+--
+SELECT results_eq($$
+    SELECT ST_SRID(users.current_user_area_utm())
+    $$,
+    $$
+    SELECT best_utm(area)
+        FROM users.responsibility_areas
+        WHERE country = users.current_user_country()
+    $$,
+    'Geometry has SRID corresponding to best_utm()');
+
 --
 -- Role listing
 --
-SET SESSION AUTHORIZATION test_user_at;
 SELECT results_eq($$
     SELECT username FROM users.list_users
     $$,
--- a/schema/run_tests.sh	Tue Apr 02 19:24:08 2019 +0200
+++ b/schema/run_tests.sh	Tue Apr 02 19:25:17 2019 +0200
@@ -28,7 +28,7 @@
     -c 'SET client_min_messages TO WARNING' \
     -c "DROP ROLE IF EXISTS $TEST_ROLES" \
     -f tap_tests_data.sql \
-    -c 'SELECT plan(63)' \
+    -c 'SELECT plan(64)' \
     -f gemma_tests.sql \
     -f isrs_tests.sql \
     -f auth_tests.sql \