Database: add a same_source_target_crs column to supersession table

This is in preparation for EPSG 9.8.11 import that supersedes a number of
grid transformation for US transformations, but the superseded and replacement
transformations don't operate on the same (source_crs, target_crs), which is
a bit weird. So in that situation, ignores the supersession.

1 files changed, 12 insertions, 7 deletions

diff --git a/src/iso19111/factory.cpp b/src/iso19111/factory.cpp
index 3901e183..9388b196 100644
--- a/src/iso19111/factory.cpp
+++ b/src/iso19111/factory.cpp
@@ -3587,7 +3587,8 @@ AuthorityFactory::createFromCoordinateReferenceSystemCodes(
               "ss.superseded_table_name = cov.table_name AND "
               "ss.superseded_auth_name = cov.auth_name AND "
               "ss.superseded_code = cov.code AND "
-              "ss.superseded_table_name = ss.replacement_table_name "
+              "ss.superseded_table_name = ss.replacement_table_name AND "
+              "ss.same_source_target_crs = 1 "
               "WHERE ";
     } else {
         sql = "SELECT source_crs_auth_name, source_crs_code, "
@@ -3920,12 +3921,14 @@ AuthorityFactory::createFromCRSCodesWithIntermediates(
         "ss1.superseded_table_name = v1.table_name AND "
         "ss1.superseded_auth_name = v1.auth_name AND "
         "ss1.superseded_code = v1.code AND "
-        "ss1.superseded_table_name = ss1.replacement_table_name "
+        "ss1.superseded_table_name = ss1.replacement_table_name AND "
+        "ss1.same_source_target_crs = 1 "
         "LEFT JOIN supersession ss2 ON "
         "ss2.superseded_table_name = v2.table_name AND "
         "ss2.superseded_auth_name = v2.auth_name AND "
         "ss2.superseded_code = v2.code AND "
-        "ss2.superseded_table_name = ss2.replacement_table_name ");
+        "ss2.superseded_table_name = ss2.replacement_table_name AND "
+        "ss2.same_source_target_crs = 1 ");
     const std::string joinArea(
         (discardSuperseded ? joinSupersession : std::string()) +
         "JOIN area a1 ON v1.area_of_use_auth_name = a1.auth_name "
@@ -4529,10 +4532,11 @@ AuthorityFactory::createBetweenGeodeticCRSWithDatumBasedIntermediates(
 
     const auto filterOutSuperseded = [&](SQLResultSet &&resultSet) {
         std::set<std::pair<std::string, std::string>> setTransf;
-        std::string findSupersededSql("SELECT superseded_table_name, "
-                                      "superseded_auth_name, superseded_code, "
-                                      "replacement_auth_name, replacement_code "
-                                      "FROM supersession WHERE ");
+        std::string findSupersededSql(
+            "SELECT superseded_table_name, "
+            "superseded_auth_name, superseded_code, "
+            "replacement_auth_name, replacement_code "
+            "FROM supersession WHERE same_source_target_crs = 1 AND (");
         bool findSupersededFirstWhere = true;
         ListOfParams findSupersededParams;
 
@@ -4583,6 +4587,7 @@ AuthorityFactory::createBetweenGeodeticCRSWithDatumBasedIntermediates(
             setTransf.insert(
                 std::pair<std::string, std::string>(auth_name2, code2));
         }
+        findSupersededSql += ')';
 
         std::map<std::string, std::vector<std::pair<std::string, std::string>>>
             mapSupersession;