Database: make sure we can deal with 'Geog3D to Geog2D+GravityRelatedHeight' records as we do with the more common 'Geographic3D to GravityRelatedHeight'

2 files changed, 53 insertions, 3 deletions

diff --git a/data/sql/commit.sql b/data/sql/commit.sql
index d090ccb2..55df332f 100644
--- a/data/sql/commit.sql
+++ b/data/sql/commit.sql
@@ -32,9 +32,20 @@ FOR EACH ROW BEGIN
 
     -- test to check that our custom grid transformation overrides are really needed
     SELECT RAISE(ABORT, 'PROJ grid_transformation defined whereas EPSG has one')
-        WHERE EXISTS (SELECT 1 FROM grid_transformation g1, grid_transformation g2 WHERE
-            lower(g1.grid_name) = lower(g2.grid_name) AND
-            g1.auth_name = 'PROJ' AND g2.auth_name = 'EPSG');
+        WHERE EXISTS (SELECT 1 FROM grid_transformation g1
+                      JOIN grid_transformation g2
+                      ON g1.source_crs_auth_name = g2.source_crs_auth_name
+                      AND g1.source_crs_code = g2.source_crs_code
+                      AND g1.target_crs_auth_name = g2.target_crs_auth_name
+                      AND g1.target_crs_code = g2.target_crs_code
+                      WHERE g1.auth_name = 'PROJ' AND g2.auth_name = 'EPSG')
+        OR EXISTS (SELECT 1 FROM grid_transformation g1
+                      JOIN grid_transformation g2
+                      ON g1.source_crs_auth_name = g2.target_crs_auth_name
+                      AND g1.source_crs_code = g2.target_crs_code
+                      AND g1.target_crs_auth_name = g1.source_crs_auth_name
+                      AND g1.target_crs_code = g1.source_crs_code
+                      WHERE 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');
diff --git a/data/sql/customizations.sql b/data/sql/customizations.sql
index efb0a4bc..815c371a 100644
--- a/data/sql/customizations.sql
+++ b/data/sql/customizations.sql
@@ -171,3 +171,42 @@ INSERT INTO "unit_of_measure" VALUES('PROJ','IN','inch','length',0.0254,'in',0);
 INSERT INTO "unit_of_measure" VALUES('PROJ','US_IN','US survey inch','length',0.025400050800101,'us-in',0);
 INSERT INTO "unit_of_measure" VALUES('PROJ','US_YD','US survey yard','length',0.914401828803658,'us-yd',0);
 INSERT INTO "unit_of_measure" VALUES('PROJ','IND_CH','Indian chain','length',20.11669506,'ind-ch',0);
+
+-- Deal with grid_transformation using EPSG:9635 'Geog3D to Geog2D+GravityRelatedHeight (US .gtx)' method
+-- We derive records using the more classic 'Geographic3D to GravityRelatedHeight' method
+-- We could probably do that at runtime too, but more simple and efficient to create records
+
+INSERT INTO "grid_transformation"
+SELECT
+    'PROJ' AS auth_name,
+    gt.auth_name || '_' || gt.code || '_RESTRICTED_TO_VERTCRS' AS code,
+    gcrs.name || ' to ' || vcrs.name || ' (from ' || gt.name || ')' AS name,
+    NULL AS description,
+    gt.scope,
+    'EPSG' AS method_auth_name,
+    '9665' AS method_code,
+    'Geographic3D to GravityRelatedHeight (gtx)' AS method_name,
+    gt.source_crs_auth_name,
+    gt.source_crs_code,
+    c.vertical_crs_auth_name AS target_crs_auth_name,
+    c.vertical_crs_code AS target_crs_code,
+    gt.area_of_use_auth_name,
+    gt.area_of_use_code,
+    gt.accuracy,
+    gt.grid_param_auth_name,
+    gt.grid_param_code,
+    gt.grid_param_name,
+    gt.grid_name,
+    gt.grid2_param_auth_name,
+    gt.grid2_param_code,
+    gt.grid2_param_name,
+    gt.grid2_name,
+    gt.interpolation_crs_auth_name,
+    gt.interpolation_crs_code,
+    gt.operation_version,
+    gt.deprecated
+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 = '9635' AND gt.deprecated = 0;