From 3c202610d35ee76b960d41cb69434c1353d78096 Mon Sep 17 00:00:00 2001
From: Even Rouault <even.rouault@spatialys.com>
Date: Thu, 1 Apr 2021 23:21:35 +0200
Subject: Merge pull request #2627 from
 rouault/improve_grid_transformation_check
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

Database: refine checks for 'Geog3D to Geog2D+XXX' transformations, …
---
 data/sql/commit.sql | 23 ++++++++++++++++++-----
 1 file changed, 18 insertions(+), 5 deletions(-)

(limited to 'data/sql/commit.sql')

diff --git a/data/sql/commit.sql b/data/sql/commit.sql
index 3394c756..d9adffef 100644
--- a/data/sql/commit.sql
+++ b/data/sql/commit.sql
@@ -118,21 +118,33 @@ FOR EACH ROW BEGIN
                       g.method_name LIKE 'Geographic3D to GravityRelatedHeight%' AND
                       g.target_crs_auth_name || g.target_crs_code NOT IN
                       (SELECT auth_name || code FROM vertical_crs));
-    SELECT RAISE(ABORT, 'One grid_transformation with Geographic3D to GravityRelatedHeight has not its source_crs in geodetic_crs table with type = ''geographic 3D''')
+    SELECT RAISE(ABORT, 'One grid_transformation with Geographic3D to GravityRelatedHeight or Geog3D to Geog2D+XXX has not its source_crs in geodetic_crs table with type = ''geographic 3D''')
         WHERE EXISTS (SELECT * FROM grid_transformation g WHERE
                       g.deprecated = 0 AND
-                      g.method_name LIKE 'Geographic3D to GravityRelatedHeight%' AND
-                      NOT (g.auth_name = 'EPSG' AND g.code IN (9658,9660)) AND -- those are wrongly registered as they refer to a Geog2D CRS in EPGS v10.017. Reported to EPSG
+                      (g.method_name LIKE 'Geographic3D to %' OR g.method_name LIKE 'Geog3D to %') AND
+                      NOT (g.auth_name = 'EPSG' AND g.code IN (9658,9659,9660,9661)) AND -- those are wrongly registered as they refer to a Geog2D CRS in EPGS v10.017. Reported to EPSG
                       g.source_crs_auth_name || g.source_crs_code NOT IN
                       (SELECT auth_name || code FROM geodetic_crs
                        WHERE type = 'geographic 3D'));
 
+    -- check that grids with 'Vertical Offset by Grid Interpolation' methods are properly registered
+    SELECT RAISE(ABORT, 'One grid_transformation with Vertical Offset by Grid Interpolation has not its source_crs in vertical_crs table')
+        WHERE EXISTS (SELECT * FROM grid_transformation g WHERE
+                      g.method_name LIKE 'Vertical Offset by Grid Interpolation%' AND
+                      g.source_crs_auth_name || g.source_crs_code NOT IN
+                      (SELECT auth_name || code FROM vertical_crs));
+    SELECT RAISE(ABORT, 'One grid_transformation with Vertical Offset by Grid Interpolation has not its target_crs in vertical_crs table')
+        WHERE EXISTS (SELECT * FROM grid_transformation g WHERE
+                      g.method_name LIKE 'Vertical Offset by Grid Interpolation%' AND
+                      g.target_crs_auth_name || g.target_crs_code NOT IN
+                      (SELECT auth_name || code FROM vertical_crs));
+
     -- check that transformations intersect the area of use of their source/target CRS
     -- EPSG, ESRI and IGNF have cases where this does not hold.
     SELECT RAISE(ABORT, 'The area of use of at least one coordinate_operation does not intersect the one of its source CRS')
         WHERE EXISTS (SELECT * FROM coordinate_operation_view v, crs_view c, usage vu, extent ve, usage cu, extent ce WHERE
                       v.deprecated = 0 AND
-                      v.auth_name NOT IN ('EPSG', 'ESRI', 'IGNF') AND
+                      (v.table_name = 'grid_transformation' OR v.auth_name NOT IN ('EPSG', 'ESRI', 'IGNF')) AND
                       v.source_crs_auth_name = c.auth_name AND
                       v.source_crs_code = c.code AND
                       vu.object_table_name = v.table_name AND
@@ -149,7 +161,8 @@ FOR EACH ROW BEGIN
     SELECT RAISE(ABORT, 'The area of use of at least one coordinate_operation does not intersect the one of its target CRS')
         WHERE EXISTS (SELECT * FROM coordinate_operation_view v, crs_view c, usage vu, extent ve, usage cu, extent ce WHERE
                       v.deprecated = 0 AND
-                      v.auth_name NOT IN ('EPSG', 'ESRI', 'IGNF') AND
+                      ((v.table_name = 'grid_transformation' AND NOT (v.auth_name = 'IGNF' AND v.code = 'TSG1185'))
+                       OR v.auth_name NOT IN ('EPSG', 'ESRI', 'IGNF')) AND
                       v.target_crs_auth_name = c.auth_name AND
                       v.target_crs_code = c.code AND
                       vu.object_table_name = v.table_name AND
-- 
cgit v1.2.3

