From e7ef9b626ecabfe6bc5ff3793a9565426470a4a3 Mon Sep 17 00:00:00 2001
From: Even Rouault <even.rouault@spatialys.com>
Date: Sat, 21 Dec 2019 02:13:07 +0100
Subject: Database: fix registration of Icegeoid_xxx.gtx files, and add
 corresponding entries in grid_alternatives.sql

---
 data/sql/commit.sql | 28 +++++++++++++++++++++++++++-
 1 file changed, 27 insertions(+), 1 deletion(-)

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

diff --git a/data/sql/commit.sql b/data/sql/commit.sql
index 4b17ea2b..d7bc7b4a 100644
--- a/data/sql/commit.sql
+++ b/data/sql/commit.sql
@@ -37,7 +37,33 @@ FOR EACH ROW BEGIN
             g1.auth_name = 'PROJ' AND g2.auth_name = 'EPSG');
 
     SELECT RAISE(ABORT, 'Arg! there is now a EPSG:102100 object. Hack in createFromUserInput() will no longer work')
-        WHERE EXISTS(SELECT 1 FROM crs_view WHERE auth_name == 'EPSG' AND code = '102100');
+        WHERE EXISTS(SELECT 1 FROM crs_view WHERE auth_name = 'EPSG' AND code = '102100');
+
+    -- check that grids with HEIGHT_TO_GEOGRAPHIC3D method are properly registered
+    SELECT RAISE(ABORT, 'One grid_transformation with HEIGHT_TO_GEOGRAPHIC3D has not its source_crs in vertical_crs table')
+        WHERE EXISTS (SELECT * FROM grid_transformation g WHERE
+                      g.method_code = 'HEIGHT_TO_GEOGRAPHIC3D' 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 HEIGHT_TO_GEOGRAPHIC3D has not its target_crs in geodetic_crs table with type = ''geographic 3D''')
+        WHERE EXISTS (SELECT * FROM grid_transformation g WHERE
+                      g.method_code = 'HEIGHT_TO_GEOGRAPHIC3D' AND
+                      g.target_crs_auth_name || g.target_crs_code NOT IN
+                      (SELECT auth_name || code FROM geodetic_crs
+                       WHERE type = 'geographic 3D'));
+
+    -- check that grids with Geographic3D to GravityRelatedHeight method are properly registered
+    SELECT RAISE(ABORT, 'One grid_transformation with Geographic3D to GravityRelatedHeight has not its target_crs in vertical_crs table')
+        WHERE EXISTS (SELECT * FROM grid_transformation g WHERE
+                      g.method_code 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''')
+        WHERE EXISTS (SELECT * FROM grid_transformation g WHERE
+                      g.method_code LIKE 'Geographic3D to GravityRelatedHeight%' AND
+                      g.source_crs_auth_name || g.source_crs_code NOT IN
+                      (SELECT auth_name || code FROM geodetic_crs
+                       WHERE type = 'geographic 3D'));
 
     -- check geoid_model table
     SELECT RAISE(ABORT, 'missing GEOID99 in geoid_model')
-- 
cgit v1.2.3

