Database: refine checks for 'Geog3D to Geog2D+XXX' transformations, and create less synthetic 'Geographic3D to GravityRelatedHeight' synthetic transformations

3 files changed, 10 insertions, 6 deletions

diff --git a/data/sql/commit.sql b/data/sql/commit.sql
index 7b0e15d7..8f647530 100644
--- a/data/sql/commit.sql
+++ b/data/sql/commit.sql
@@ -113,11 +113,11 @@ 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'));
diff --git a/data/sql/customizations.sql b/data/sql/customizations.sql
index 19032e3a..d38c72a2 100644
--- a/data/sql/customizations.sql
+++ b/data/sql/customizations.sql
@@ -343,7 +343,10 @@ FROM grid_transformation gt
 JOIN compound_crs c ON gt.target_crs_code = c.code AND gt.target_crs_auth_name = c.auth_name
 JOIN geodetic_crs gcrs ON gt.source_crs_auth_name = gcrs.auth_name AND gt.source_crs_code = gcrs.code
 JOIN vertical_crs vcrs on vcrs.auth_name = c.vertical_crs_auth_name AND vcrs.code = c.vertical_crs_code
-WHERE method_auth_name = 'EPSG' AND method_code IN ('1088', '1089', '1090', '1091', '1092', '1093', '1094', '1095', '1096', '1097', '1098', '1103') AND gt.deprecated = 0;
+WHERE method_auth_name = 'EPSG' AND method_name LIKE 'Geog3D to Geog2D+%'
+AND NOT EXISTS (SELECT 1 FROM grid_transformation gt2 WHERE gt2.method_name LIKE 'Geographic3D to%' AND gt2.source_crs_auth_name = gt.source_crs_auth_name AND gt2.source_crs_code = gt.source_crs_code AND gt2.target_crs_auth_name = vcrs.auth_name AND gt2.target_crs_code = vcrs.code)
+AND NOT (gt.auth_name == 'EPSG' AND gt.code IN (9659,9661)) -- issue in EPSG v10.017
+AND gt.deprecated = 0;
 
 INSERT INTO "usage"
 SELECT
@@ -358,4 +361,5 @@ SELECT
     u.scope_code
 FROM grid_transformation gt
 JOIN usage u ON u.object_auth_name = gt.auth_name AND u.object_code = gt.code AND u.object_table_name = 'grid_transformation'
-WHERE method_auth_name = 'EPSG' AND method_code IN ('1088', '1089', '1090', '1091', '1092', '1093', '1094', '1095', '1096', '1097', '1098', '1103') AND gt.deprecated = 0;
+WHERE method_auth_name = 'EPSG' AND method_name LIKE 'Geog3D to Geog2D+%'
+AND EXISTS (SELECT 1 FROM grid_transformation gt2 WHERE gt2.auth_name = 'PROJ' AND gt2.code = gt.auth_name || '_' || gt.code || '_RESTRICTED_TO_VERTCRS');
diff --git a/data/sql/metadata.sql b/data/sql/metadata.sql
index b0d14ed5..ef2c8fae 100644
--- a/data/sql/metadata.sql
+++ b/data/sql/metadata.sql
@@ -9,6 +9,6 @@
 INSERT INTO "metadata" VALUES('DATABASE.LAYOUT.VERSION.MAJOR', 1);
 INSERT INTO "metadata" VALUES('DATABASE.LAYOUT.VERSION.MINOR', 0);
 
--- TODO at next update remove exception case for EPSG:9658 and 9660 in customization.sql
+-- TODO at next update remove exception case for EPSG:9658 to 9661 in commit.sql and EPSG:9659 and 9661 in customizations.sql
 INSERT INTO "metadata" VALUES('EPSG.VERSION', 'v10.017');
 INSERT INTO "metadata" VALUES('EPSG.DATE', '2021-04-01');