Mercurial > gemma

changeset 2855:8a8a929182f9

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Enable import of axis and area independently from areas of responsibility In preparation of generating the areas of responsibility from data previously imported by a sys_admin! Having the CTE as an item in the FROM-list prevents the SELECT from returning any rows if the CTE returns no rows. Thus, use the CTE only in scalar subqueries that are relevant only if the statement is executed by somebody without sys_admin role. Also introduced an ST_CollectionExtract() in the axis INSERT because here too, the intersection is not guaranteed to have the right dimensionality.
author Tom Gottfried <tom@intevation.de>
date Thu, 28 Mar 2019 17:19:27 +0100
parents 1b5acce6120d
children a2a82e887b18 401bca8eaafb
files pkg/imports/wa.go pkg/imports/wx.go
diffstat 2 files changed, 18 insertions(+), 20 deletions(-) [+]
line wrap: on
line diff
--- a/pkg/imports/wa.go	Thu Mar 28 17:19:07 2019 +0100
+++ b/pkg/imports/wa.go	Thu Mar 28 17:19:27 2019 +0100
@@ -99,17 +99,16 @@
   WHERE country = users.current_user_country()
 )
 INSERT INTO waterway.waterway_area (area, catccl, dirimp)
-SELECT ST_Transform(clipped.geom, 4326)::geography, $3, $4
-  FROM resp,
-    ST_CollectionExtract(ST_MakeValid(ST_Transform(
-      ST_GeomFromWKB($1, $2::integer), t)), 3) AS new_area (new_area),
-    LATERAL (SELECT (ST_Dump(
+SELECT dmp.geom, $3, $4
+  FROM ST_GeomFromWKB($1, $2::integer) AS new_area (new_area),
+    ST_Dump(ST_Transform(ST_CollectionExtract(
       CASE WHEN pg_has_role('sys_admin', 'MEMBER')
-        THEN new_area
-        ELSE ST_Intersection(a, new_area)
-        END
-      )).geom AS geom
-    ) AS clipped
+        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))))
+        END,
+      3), 4326)) AS dmp
 `
 )
 
--- a/pkg/imports/wx.go	Thu Mar 28 17:19:07 2019 +0100
+++ b/pkg/imports/wx.go	Thu Mar 28 17:19:27 2019 +0100
@@ -106,17 +106,16 @@
   WHERE country = users.current_user_country()
 )
 INSERT INTO waterway.waterway_axis (wtwaxs, objnam, nobjnam)
-SELECT ST_Transform(clipped.geom, 4326)::geography, $3, $4
-  FROM resp,
-    ST_Node(ST_Transform(
-      ST_GeomFromWKB($1, $2::integer), t)) AS new_line (new_line),
-    LATERAL (SELECT (ST_Dump(
+SELECT dmp.geom, $3, $4
+  FROM ST_GeomFromWKB($1, $2::integer) AS new_line (new_line),
+    ST_Dump(ST_Transform(ST_CollectionExtract(
       CASE WHEN pg_has_role('sys_admin', 'MEMBER')
-        THEN new_line
-        ELSE ST_Intersection(a, new_line)
-        END
-      )).geom AS geom
-    ) AS clipped
+        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))))
+        END,
+      2), 4326)) AS dmp
 RETURNING id
 `
 )