Merge pull request #2118 from rouault/backport_6_3_fix_2116

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

1 files changed, 27 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];
     }
 }