# HG changeset patch
# User Tom Gottfried <tom@intevation.de>
# Date 1553697592 -3600
# Node ID 1b6840093eacfad2a1202a06ef5217042cd2261b
# Parent  b5555005f51e92920d0c5c93173f0c41120c101d
Prevent calculation of wrong UTM zones

Using geography as input data type will ensure only lon/lat
coordinates are fed into the calculation.

diff -r b5555005f51e -r 1b6840093eac pkg/imports/dma.go
--- a/pkg/imports/dma.go	Wed Mar 27 15:37:36 2019 +0100
+++ b/pkg/imports/dma.go	Wed Mar 27 15:39:52 2019 +0100
@@ -77,8 +77,8 @@
 const (
 	deleteDistanceMarksSQL = `
 WITH resp AS (
-  SELECT best_utm(area::geometry) AS t,
-         ST_Transform(area::geometry, best_utm(area::geometry)) AS a
+  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()
 )
@@ -89,8 +89,8 @@
 `
 	insertDistanceMarksSQL = `
 WITH resp AS (
-  SELECT best_utm(area::geometry) AS t,
-         ST_Transform(area::geometry, best_utm(area::geometry)) AS a
+  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()
 )
diff -r b5555005f51e -r 1b6840093eac pkg/imports/fd.go
--- a/pkg/imports/fd.go	Wed Mar 27 15:37:36 2019 +0100
+++ b/pkg/imports/fd.go	Wed Mar 27 15:39:52 2019 +0100
@@ -139,8 +139,8 @@
 
 	deleteFairwayDimensionSQL = `
 WITH resp AS (
-  SELECT best_utm(area::geometry) AS t,
-         ST_Transform(area::geometry, best_utm(area::geometry)) AS a
+  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()
 )
@@ -155,8 +155,8 @@
 	// avoid errors due to reprojection.
 	insertFairwayDimensionSQL = `
 WITH resp AS (
-  SELECT best_utm(area::geometry) AS t,
-         ST_Transform(area::geometry, best_utm(area::geometry)) AS a
+  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()
 )
diff -r b5555005f51e -r 1b6840093eac pkg/imports/sr.go
--- a/pkg/imports/sr.go	Wed Mar 27 15:37:36 2019 +0100
+++ b/pkg/imports/sr.go	Wed Mar 27 15:39:52 2019 +0100
@@ -124,8 +124,8 @@
   id,
   ST_X(ST_Centroid(area::geometry)),
   ST_Y(ST_Centroid(area::geometry)),
-  best_utm(area::geometry),
-  ST_AsBinary(ST_Transform(area::geometry, best_utm(area::geometry)))
+  best_utm(area),
+  ST_AsBinary(ST_Transform(area::geometry, best_utm(area)))
 `
 
 	reprojectPointsSQL = `
diff -r b5555005f51e -r 1b6840093eac pkg/imports/wa.go
--- a/pkg/imports/wa.go	Wed Mar 27 15:37:36 2019 +0100
+++ b/pkg/imports/wa.go	Wed Mar 27 15:39:52 2019 +0100
@@ -81,8 +81,8 @@
 const (
 	deleteWaterwayAreaSQL = `
 WITH resp AS (
-  SELECT best_utm(area::geometry) AS t,
-         ST_Transform(area::geometry, best_utm(area::geometry)) AS a
+  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()
 )
@@ -93,8 +93,8 @@
 `
 	insertWaterwayAreaSQL = `
 WITH resp AS (
-  SELECT best_utm(area::geometry) AS t,
-         ST_Transform(area::geometry, best_utm(area::geometry)) AS a
+  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()
 )
diff -r b5555005f51e -r 1b6840093eac pkg/imports/wx.go
--- a/pkg/imports/wx.go	Wed Mar 27 15:37:36 2019 +0100
+++ b/pkg/imports/wx.go	Wed Mar 27 15:39:52 2019 +0100
@@ -80,8 +80,8 @@
 const (
 	deleteWaterwayAxisSQL = `
 WITH resp AS (
-  SELECT best_utm(area::geometry) AS t,
-         ST_Transform(area::geometry, best_utm(area::geometry)) AS a
+  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()
 )
@@ -100,8 +100,8 @@
 
 	insertWaterwayAxisSQL = `
 WITH resp AS (
-  SELECT best_utm(area::geometry) AS t,
-         ST_Transform(area::geometry, best_utm(area::geometry)) AS a
+  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()
 )
diff -r b5555005f51e -r 1b6840093eac schema/geo_functions.sql
--- a/schema/geo_functions.sql	Wed Mar 27 15:37:36 2019 +0100
+++ b/schema/geo_functions.sql	Wed Mar 27 15:39:52 2019 +0100
@@ -4,19 +4,20 @@
 -- 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
 
 -- Author(s):
 --  * Sascha L. Teichmann <sascha.teichmann@intevation.de>
+--  * Tom Gottfried <tom@intevation.de>
 
-CREATE OR REPLACE FUNCTION best_utm(g geometry) RETURNS integer AS
+CREATE OR REPLACE FUNCTION best_utm(g geography) RETURNS integer AS
 $$
 DECLARE
   center geometry;
 BEGIN
-  SELECT ST_Centroid(g) INTO center;
+  SELECT CAST(ST_Centroid(g) AS geometry) INTO center;
   RETURN
     CASE WHEN ST_Y(center) > 0 THEN
     32600