Database: fix registration of Icegeoid_xxx.gtx files, and add corresponding entries in grid_alternatives.sql

3 files changed, 81 insertions, 10 deletions

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')
diff --git a/data/sql/grid_alternatives.sql b/data/sql/grid_alternatives.sql
index 9bc27ea8..087036de 100644
--- a/data/sql/grid_alternatives.sql
+++ b/data/sql/grid_alternatives.sql
@@ -1744,6 +1744,51 @@ INSERT INTO grid_alternatives(original_grid_name,
                               'proj-datumgrid-europe',
                               NULL, NULL, NULL, NULL);
 
+INSERT INTO grid_alternatives(original_grid_name,
+                              proj_grid_name,
+                              proj_grid_format,
+                              proj_method,
+                              inverse_direction,
+                              package_name,
+                              url, direct_download, open_license, directory)
+                      VALUES ('Icegeoid_ISN2004.gtx',
+                              'Icegeoid_ISN2004.gtx',
+                              'GTX',
+                              'vgridshift',
+                              0,
+                              'proj-datumgrid-europe',
+                              NULL, NULL, NULL, NULL);
+
+INSERT INTO grid_alternatives(original_grid_name,
+                              proj_grid_name,
+                              proj_grid_format,
+                              proj_method,
+                              inverse_direction,
+                              package_name,
+                              url, direct_download, open_license, directory)
+                      VALUES ('Icegeoid_ISN93.gtx',
+                              'Icegeoid_ISN93.gtx',
+                              'GTX',
+                              'vgridshift',
+                              0,
+                              'proj-datumgrid-europe',
+                              NULL, NULL, NULL, NULL);
+
+INSERT INTO grid_alternatives(original_grid_name,
+                              proj_grid_name,
+                              proj_grid_format,
+                              proj_method,
+                              inverse_direction,
+                              package_name,
+                              url, direct_download, open_license, directory)
+                      VALUES ('Icegeoid_ISN2016.gtx',
+                              'Icegeoid_ISN2016.gtx',
+                              'GTX',
+                              'vgridshift',
+                              0,
+                              'proj-datumgrid-europe',
+                              NULL, NULL, NULL, NULL);
+
 -- New Zealand grid shift models.  EPSG names are not for GTX files (at time of writing)
 INSERT INTO grid_alternatives(original_grid_name,
                               proj_grid_name,
diff --git a/data/sql/grid_transformation_custom.sql b/data/sql/grid_transformation_custom.sql
index 97434ad8..05916a39 100644
--- a/data/sql/grid_transformation_custom.sql
+++ b/data/sql/grid_transformation_custom.sql
@@ -65,33 +65,33 @@ INSERT INTO "grid_transformation" VALUES('PROJ','SETA2016','DHDN to ETRS89 (SETA
 -- Iceland
 
 INSERT INTO "grid_transformation" VALUES(
-    'PROJ','EPSG_5323_TO_EPSG_8089','ISN2004 height to vertical datum ISH2004',
+    'PROJ','EPSG_8089_TO_EPSG_5323','ISH2004 height to ISN2004',
     NULL,NULL,
     'PROJ','HEIGHT_TO_GEOGRAPHIC3D','GravityRelatedHeight to Geographic3D',
-    'EPSG','5323', -- source CRS (ISN2004 height)
-    'EPSG','8089', -- target CRS (ISH2004)
+    'EPSG','8089', -- source CRS (ISH2004 height)
+    'EPSG','5323', -- target CRS (ISN2004 geographic 3D)
     'EPSG','1120', -- area of use: Iceland - onshore and offshore
     NULL,
     'EPSG','8666','Geoid (height correction) model file','Icegeoid_ISN2004.gtx',
     NULL,NULL,NULL,NULL,NULL,NULL,NULL,0);
     
 INSERT INTO "grid_transformation" VALUES(
-    'PROJ','EPSG_4945_TO_EPSG_8089','ISN93 height to vertical datum ISH2004',
+    'PROJ','EPSG_8089_TO_EPSG_4945','ISH2004 height to ISN93',
     NULL,NULL,
     'PROJ','HEIGHT_TO_GEOGRAPHIC3D','GravityRelatedHeight to Geographic3D',
-    'EPSG','4945', -- source CRS (ISN93 height)
-    'EPSG','8089', -- target CRS (ISH2004)
+    'EPSG','8089', -- source CRS (ISH2004 height)
+    'EPSG','4945', -- target CRS (ISN93 geographic 3D)
     'EPSG','1120', -- area of use: Iceland - onshore and offshore
     NULL,
     'EPSG','8666','Geoid (height correction) model file','Icegeoid_ISN93.gtx',
     NULL,NULL,NULL,NULL,NULL,NULL,NULL,0);
     
 INSERT INTO "grid_transformation" VALUES(
-    'PROJ','EPSG_8085_TO_EPSG_8089','ISN2016 height to vertical datum ISH2004',
+    'PROJ','EPSG_8089_TO_EPSG_8085','ISH2004 height to ISN2016',
     NULL,NULL,
     'PROJ','HEIGHT_TO_GEOGRAPHIC3D','GravityRelatedHeight to Geographic3D',
-    'EPSG','8085', -- source CRS (ISN2016 height)
-    'EPSG','8089', -- target CRS (ISH2004)
+    'EPSG','8089', -- source CRS (ISH2004 height)
+    'EPSG','8085', -- target CRS (ISN2016 geographic 3D)
     'EPSG','1120', -- area of use: Iceland - onshore and offshore
     NULL,
     'EPSG','8666','Geoid (height correction) model file','Icegeoid_ISN2016.gtx',