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

3 files changed, 80 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;
diff --git a/test/unit/test_operation.cpp b/test/unit/test_operation.cpp
index 630492e6..6ef6e686 100644
--- a/test/unit/test_operation.cpp
+++ b/test/unit/test_operation.cpp
@@ -4900,6 +4900,33 @@ TEST(operation, vertCRS_to_geogCRS_context) {
             "+step +proj=unitconvert +xy_in=rad +xy_out=deg "
             "+step +proj=axisswap +order=2,1");
     }
+    {
+        // Test actually the database where we derive records using the more
+        // classic 'Geographic3D to GravityRelatedHeight' method from
+        // records using EPSG:9635
+        //'Geog3D to Geog2D+GravityRelatedHeight (US .gtx)' method
+        auto ctxt = CoordinateOperationContext::create(
+            AuthorityFactory::create(DatabaseContext::create(), std::string()),
+            nullptr, 0.0);
+        ctxt->setSpatialCriterion(
+            CoordinateOperationContext::SpatialCriterion::PARTIAL_INTERSECTION);
+        auto list = CoordinateOperationFactory::create()->createOperations(
+            // Baltic 1957 height
+            authFactory->createCoordinateReferenceSystem("8357"),
+            // ETRS89
+            authFactory->createCoordinateReferenceSystem("4937"), ctxt);
+        ASSERT_EQ(list.size(), 1U);
+        EXPECT_EQ(
+            list[0]->exportToPROJString(PROJStringFormatter::create().get()),
+            "+proj=pipeline "
+            "+step +proj=axisswap +order=2,1 "
+            "+step +proj=unitconvert +xy_in=deg +xy_out=rad "
+            "+step +proj=vgridshift "
+            "+grids=sk_gku_Slovakia_ETRS89h_to_Baltic1957.tif "
+            "+multiplier=1 "
+            "+step +proj=unitconvert +xy_in=rad +xy_out=deg "
+            "+step +proj=axisswap +order=2,1");
+    }
 }
 
 // ---------------------------------------------------------------------------