ESRI_WKT ingestion: make sure to identify to non-deprecated EPSG entry when possible (fixes #2116)

2 files changed, 52 insertions, 9 deletions

diff --git a/src/iso19111/factory.cpp b/src/iso19111/factory.cpp
index e3bbcf95..e3aee4b4 100644
--- a/src/iso19111/factory.cpp
+++ b/src/iso19111/factory.cpp
@@ -5343,18 +5343,36 @@ std::string AuthorityFactory::getOfficialNameFromAlias(
         if (res.empty()) {
             return std::string();
         }
-        const auto &row = res.front();
-        outTableName = row[0];
-        outAuthName = row[1];
-        outCode = row[2];
-        sql = "SELECT name FROM \"";
-        sql += replaceAll(outTableName, "\"", "\"\"");
-        sql += "\" WHERE auth_name = ? AND code = ?";
-        res = d->run(sql, {outAuthName, outCode});
+
+        params.clear();
+        sql.clear();
+        bool first = true;
+        for (const auto &row : res) {
+            if (!first)
+                sql += " UNION ALL ";
+            first = false;
+            outTableName = row[0];
+            outAuthName = row[1];
+            outCode = row[2];
+            sql += "SELECT name, ? AS table_name, auth_name, code, deprecated "
+                   "FROM \"";
+            sql += replaceAll(outTableName, "\"", "\"\"");
+            sql += "\" WHERE auth_name = ? AND code = ?";
+            params.emplace_back(outTableName);
+            params.emplace_back(outAuthName);
+            params.emplace_back(outCode);
+        }
+        sql = "SELECT name, table_name, auth_name, code FROM (" + sql +
+              ") x ORDER BY deprecated LIMIT 1";
+        res = d->run(sql, params);
         if (res.empty()) { // shouldn't happen normally
             return std::string();
         }
-        return res.front()[0];
+        const auto &row = res.front();
+        outTableName = row[1];
+        outAuthName = row[2];
+        outCode = row[3];
+        return row[0];
     }
 }
 
diff --git a/test/unit/test_crs.cpp b/test/unit/test_crs.cpp
index e5a974bf..2498c533 100644
--- a/test/unit/test_crs.cpp
+++ b/test/unit/test_crs.cpp
@@ -2472,6 +2472,31 @@ TEST(crs, projectedCRS_identify_db) {
         EXPECT_EQ(res.front().first->getEPSGCode(), 2193);
         EXPECT_EQ(res.front().second, 90);
     }
+    {
+        // Test case of https://github.com/OSGeo/PROJ/issues/2116
+        // The NAD_1983_CSRS_Prince_Edward_Island has entries in the alias
+        // table under the ESRI authority for deprecated EPSG:2292 and
+        // non-deprecated EPSG:2954
+        auto obj = WKTParser().attachDatabaseContext(dbContext).createFromWKT(
+            "PROJCS[\"NAD_1983_CSRS_Prince_Edward_Island\","
+            "GEOGCS[\"GCS_North_American_1983_CSRS\","
+            "DATUM[\"D_North_American_1983_CSRS\","
+            "SPHEROID[\"GRS_1980\",6378137.0,298.257222101]],"
+            "PRIMEM[\"Greenwich\",0.0],UNIT[\"Degree\",0.0174532925199433]],"
+            "PROJECTION[\"Double_Stereographic\"],"
+            "PARAMETER[\"False_Easting\",400000.0],"
+            "PARAMETER[\"False_Northing\",800000.0],"
+            "PARAMETER[\"Central_Meridian\",-63.0],"
+            "PARAMETER[\"Scale_Factor\",0.999912],"
+            "PARAMETER[\"Latitude_Of_Origin\",47.25],UNIT[\"Meter\",1.0]]");
+        auto crs = nn_dynamic_pointer_cast<ProjectedCRS>(obj);
+        ASSERT_TRUE(crs != nullptr);
+        auto factoryAll = AuthorityFactory::create(dbContext, std::string());
+        auto res = crs->identify(factoryAll);
+        ASSERT_EQ(res.size(), 1U);
+        EXPECT_EQ(res.front().first->getEPSGCode(), 2954);
+        EXPECT_EQ(res.front().second, 100);
+    }
 }
 
 // ---------------------------------------------------------------------------