Mercurial > gemma
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.