diff pkg/imports/wa.go @ 1796:904c7e853adc

Waterway area import: Clip features against responsibility area.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Mon, 14 Jan 2019 16:21:31 +0100
parents 09349ca27dd7
children 1ecfcf46e4da
line wrap: on
line diff
--- a/pkg/imports/wa.go	Mon Jan 14 15:19:16 2019 +0100
+++ b/pkg/imports/wa.go	Mon Jan 14 16:21:31 2019 +0100
@@ -81,14 +81,39 @@
 }
 
 const (
-	deleteWaterwayAreaSQL = `DELETE FROM waterway.waterway_area`
+	deleteWaterwayAreaSQL = `
+WITH resp AS (
+  SELECT best_utm(area::geometry) AS t,
+         ST_Transform(area::geometry, best_utm(area::geometry)) AS a
+  FROM users.responsibility_areas
+  WHERE country = users.current_user_country()
+)
+DELETE FROM waterway.waterway_area
+WHERE ST_Covers(
+  (SELECT a FROM resp),
+  ST_Transform(area::geometry, (SELECT t FROM resp)))
+`
 	insertWaterwayAreaSQL = `
+WITH resp AS (
+  SELECT best_utm(area::geometry) AS t,
+         ST_Transform(area::geometry, best_utm(area::geometry)) AS a
+  FROM users.responsibility_areas
+  WHERE country = users.current_user_country()
+)
 INSERT INTO waterway.waterway_area (area, catccl, dirimp)
-VALUES (
-  ST_Transform(ST_GeomFromWKB($1, $2::integer), 4326)::geography,
-  $3,
-  $4
-)`
+SELECT ST_Transform(clipped.geom, 4326)::geography, $3, $4 FROM (
+    SELECT (ST_Dump(
+       ST_Intersection(
+         (SELECT a FROM resp),
+         ST_Transform(
+           ST_GeomFromWKB($1, $2::integer),
+           (SELECT t FROM resp)
+         )
+       )
+     )).geom AS geom
+  ) AS clipped
+  WHERE clipped.geom IS NOT NULL
+`
 )
 
 // Do executes the actual waterway axis import.