Export to ESRI WKT: make it use verbatim WKT definition from the database when possible

6 files changed, 229 insertions, 71 deletions

diff --git a/include/proj/io.hpp b/include/proj/io.hpp
index 5fb12a76..ba7e9f53 100644
--- a/include/proj/io.hpp
+++ b/include/proj/io.hpp
@@ -134,6 +134,14 @@ using DatabaseContextNNPtr = util::nn<DatabaseContextPtr>;
 
 // ---------------------------------------------------------------------------
 
+class WKTNode;
+/** Unique pointer of WKTNode. */
+using WKTNodePtr = std::unique_ptr<WKTNode>;
+/** Non-null unique pointer of WKTNode. */
+using WKTNodeNNPtr = util::nn<WKTNodePtr>;
+
+// ---------------------------------------------------------------------------
+
 class WKTFormatter;
 /** WKTFormatter unique pointer. */
 using WKTFormatterPtr = std::unique_ptr<WKTFormatter>;
@@ -308,6 +316,8 @@ class PROJ_GCC_DLL WKTFormatter {
 
     PROJ_INTERNAL const DatabaseContextPtr &databaseContext() const;
 
+    PROJ_INTERNAL void ingestWKTNode(const WKTNodeNNPtr &node);
+
     //! @endcond
 
   protected:
@@ -555,12 +565,6 @@ class PROJ_GCC_DLL IPROJStringExportable {
 
 // ---------------------------------------------------------------------------
 
-class WKTNode;
-/** Unique pointer of WKTNode. */
-using WKTNodePtr = std::unique_ptr<WKTNode>;
-/** Non-null unique pointer of WKTNode. */
-using WKTNodeNNPtr = util::nn<WKTNodePtr>;
-
 /** \brief Node in the tree-splitted WKT representation.
  */
 class PROJ_GCC_DLL WKTNode {
@@ -724,6 +728,9 @@ class PROJ_GCC_DLL DatabaseContext {
     PROJ_INTERNAL bool isKnownName(const std::string &name,
                                    const std::string &tableName) const;
 
+    PROJ_INTERNAL std::string getTextDefinition(const std::string &tableName,
+                                                const std::string &authName,
+                                                const std::string &code) const;
     //! @endcond
 
   protected:
diff --git a/src/crs.cpp b/src/crs.cpp
index 699af09a..31682644 100644
--- a/src/crs.cpp
+++ b/src/crs.cpp
@@ -2415,6 +2415,63 @@ const cs::CartesianCSNNPtr &ProjectedCRS::coordinateSystem() PROJ_CONST_DEFN {
 void ProjectedCRS::_exportToWKT(io::WKTFormatter *formatter) const {
     const bool isWKT2 = formatter->version() == io::WKTFormatter::Version::WKT2;
 
+    const auto &l_identifiers = identifiers();
+    // Try to perfectly round-trip ESRI projectedCRS if the current object
+    // perfectly matches the database definition
+    const auto &dbContext = formatter->databaseContext();
+
+    auto l_name = nameStr();
+    std::string l_alias;
+    if (formatter->useESRIDialect() && dbContext) {
+        l_alias = dbContext->getAliasFromOfficialName(l_name, "projected_crs",
+                                                      "ESRI");
+    }
+
+    if (!isWKT2 && formatter->useESRIDialect() && !l_identifiers.empty() &&
+        *(l_identifiers[0]->codeSpace()) == "ESRI" && dbContext) {
+        try {
+            const auto definition = dbContext->getTextDefinition(
+                "projected_crs", "ESRI", l_identifiers[0]->code());
+            if (starts_with(definition, "PROJCS")) {
+                auto crsFromFromDef = io::WKTParser()
+                                          .attachDatabaseContext(dbContext)
+                                          .createFromWKT(definition);
+                if (_isEquivalentTo(
+                        dynamic_cast<IComparable *>(crsFromFromDef.get()),
+                        util::IComparable::Criterion::EQUIVALENT)) {
+                    formatter->ingestWKTNode(
+                        io::WKTNode::createFrom(definition));
+                    return;
+                }
+            }
+        } catch (const std::exception &) {
+        }
+    } else if (!isWKT2 && formatter->useESRIDialect() && !l_alias.empty()) {
+        try {
+            auto res =
+                io::AuthorityFactory::create(NN_NO_CHECK(dbContext), "ESRI")
+                    ->createObjectsFromName(
+                        l_alias,
+                        {io::AuthorityFactory::ObjectType::PROJECTED_CRS},
+                        false);
+            if (res.size() == 1) {
+                const auto definition = dbContext->getTextDefinition(
+                    "projected_crs", "ESRI",
+                    res.front()->identifiers()[0]->code());
+                if (starts_with(definition, "PROJCS")) {
+                    if (_isEquivalentTo(
+                            dynamic_cast<IComparable *>(res.front().get()),
+                            util::IComparable::Criterion::EQUIVALENT)) {
+                        formatter->ingestWKTNode(
+                            io::WKTNode::createFrom(definition));
+                        return;
+                    }
+                }
+            }
+        } catch (const std::exception &) {
+        }
+    }
+
     const auto &l_coordinateSystem = d->coordinateSystem();
     const auto &axisList = l_coordinateSystem->axisList();
 
@@ -2433,7 +2490,7 @@ void ProjectedCRS::_exportToWKT(io::WKTFormatter *formatter) const {
 
     if (!isWKT2 && !formatter->useESRIDialect() &&
         starts_with(nameStr(), "Popular Visualisation CRS / Mercator")) {
-        formatter->startNode(io::WKTConstants::PROJCS, !identifiers().empty());
+        formatter->startNode(io::WKTConstants::PROJCS, !l_identifiers.empty());
         formatter->addQuotedString(nameStr());
         formatter->setTOWGS84Parameters({0, 0, 0, 0, 0, 0, 0});
         baseCRS()->_exportToWKT(formatter);
@@ -2473,21 +2530,13 @@ void ProjectedCRS::_exportToWKT(io::WKTFormatter *formatter) const {
 
     formatter->startNode(isWKT2 ? io::WKTConstants::PROJCRS
                                 : io::WKTConstants::PROJCS,
-                         !identifiers().empty());
-    auto l_name = nameStr();
+                         !l_identifiers.empty());
+
     if (formatter->useESRIDialect()) {
-        bool aliasFound = false;
-        const auto &dbContext = formatter->databaseContext();
-        if (dbContext) {
-            auto l_alias = dbContext->getAliasFromOfficialName(
-                l_name, "projected_crs", "ESRI");
-            if (!l_alias.empty()) {
-                l_name = l_alias;
-                aliasFound = true;
-            }
-        }
-        if (!aliasFound) {
+        if (l_alias.empty()) {
             l_name = io::WKTFormatter::morphNameToESRI(l_name);
+        } else {
+            l_name = l_alias;
         }
     }
     if (!isWKT2 && !formatter->useESRIDialect() && isDeprecated()) {
diff --git a/src/factory.cpp b/src/factory.cpp
index f58b66a0..91d0c478 100644
--- a/src/factory.cpp
+++ b/src/factory.cpp
@@ -852,6 +852,29 @@ DatabaseContext::getAliasFromOfficialName(const std::string &officialName,
     return res[0][0];
 }
 
+// ---------------------------------------------------------------------------
+
+/** \brief Return the 'text_definition' column of a table for an object
+ *
+ * @param tableName Table name/category.
+ * @param authName Authority name of the object.
+ * @param code Code of the object
+ * @return Text definition (or empty)
+ * @throw FactoryException
+ */
+std::string DatabaseContext::getTextDefinition(const std::string &tableName,
+                                               const std::string &authName,
+                                               const std::string &code) const {
+    std::string sql("SELECT text_definition FROM \"");
+    sql += replaceAll(tableName, "\"", "\"\"");
+    sql += "\" WHERE auth_name = ? AND code = ?";
+    auto res = d->run(sql, {authName, code});
+    if (res.empty()) {
+        return std::string();
+    }
+    return res[0][0];
+}
+
 //! @endcond
 
 // ---------------------------------------------------------------------------
@@ -3807,6 +3830,11 @@ AuthorityFactory::createObjectsFromName(
             break;
         }
     }
+    if (res.empty() && !deprecated) {
+        return createObjectsFromName(searchedName + " (deprecated)",
+                                     allowedObjectTypes, approximateMatch,
+                                     limitResultCount);
+    }
 
     auto sortLambda = [](const common::IdentifiedObjectNNPtr &a,
                          const common::IdentifiedObjectNNPtr &b) {
diff --git a/src/io.cpp b/src/io.cpp
index e8dbd147..11e4748e 100644
--- a/src/io.cpp
+++ b/src/io.cpp
@@ -373,7 +373,7 @@ void WKTFormatter::startNode(const std::string &keyword, bool hasId) {
     if (!d->stackHasChild_.empty()) {
         d->startNewChild();
     } else if (!d->result_.empty()) {
-        d->result_ += ",";
+        d->result_ += ',';
         if (d->params_.multiLine_ && !keyword.empty()) {
             d->addNewLine();
         }
@@ -390,7 +390,7 @@ void WKTFormatter::startNode(const std::string &keyword, bool hasId) {
 
     if (!keyword.empty()) {
         d->result_ += keyword;
-        d->result_ += "[";
+        d->result_ += '[';
     }
     d->indentLevel_++;
     d->stackHasChild_.push_back(false);
@@ -428,7 +428,7 @@ void WKTFormatter::endNode() {
     d->stackEmptyKeyword_.pop_back();
     d->stackHasChild_.pop_back();
     if (!emptyKeyword)
-        d->result_ += "]";
+        d->result_ += ']';
 }
 
 // ---------------------------------------------------------------------------
@@ -443,7 +443,7 @@ WKTFormatter &WKTFormatter::simulCurNodeHasId() {
 void WKTFormatter::Private::startNewChild() {
     assert(!stackHasChild_.empty());
     if (stackHasChild_.back()) {
-        result_ += ",";
+        result_ += ',';
     }
     stackHasChild_.back() = true;
 }
@@ -456,9 +456,9 @@ void WKTFormatter::addQuotedString(const char *str) {
 
 void WKTFormatter::addQuotedString(const std::string &str) {
     d->startNewChild();
-    d->result_ += "\"";
+    d->result_ += '"';
     d->result_ += replaceAll(str, "\"", "\"\"");
-    d->result_ += "\"";
+    d->result_ += '"';
 }
 
 // ---------------------------------------------------------------------------
@@ -719,6 +719,20 @@ std::string WKTFormatter::morphNameToESRI(const std::string &name) {
     return ret;
 }
 
+// ---------------------------------------------------------------------------
+
+void WKTFormatter::ingestWKTNode(const WKTNodeNNPtr &node) {
+    startNode(node->value(), true);
+    for (const auto &child : node->children()) {
+        if (!child->children().empty()) {
+            ingestWKTNode(child);
+        } else {
+            add(child->value());
+        }
+    }
+    endNode();
+}
+
 #ifdef unused
 // ---------------------------------------------------------------------------
 
@@ -991,7 +1005,7 @@ WKTNodeNNPtr WKTNode::createFrom(const std::string &wkt, size_t indexStart,
             if (!inString) {
                 inString = true;
                 closingStringMarker = endPrintedQuote;
-                value += "\"";
+                value += '"';
                 i += 2;
                 continue;
             }
@@ -1000,7 +1014,7 @@ WKTNodeNNPtr WKTNode::createFrom(const std::string &wkt, size_t indexStart,
                    wkt.substr(i, 3) == endPrintedQuote) {
             inString = false;
             closingStringMarker.clear();
-            value += "\"";
+            value += '"';
             i += 2;
             continue;
         }
@@ -1069,7 +1083,7 @@ static std::string escapeIfQuotedString(const std::string &str) {
     if (str.size() > 2 && str[0] == '"' && str.back() == '"') {
         std::string res("\"");
         res += replaceAll(str.substr(1, str.size() - 2), "\"", "\"\"");
-        res += "\"";
+        res += '"';
         return res;
     } else {
         return str;
@@ -1088,7 +1102,7 @@ std::string WKTNode::toString() const {
         bool first = true;
         for (auto &child : d->children_) {
             if (!first) {
-                str += ",";
+                str += ',';
             }
             first = false;
             str += child->toString();
@@ -3308,11 +3322,10 @@ ConversionNNPtr WKTParser::Private::buildProjectionStandard(
 
 // ---------------------------------------------------------------------------
 
-static ProjectedCRSNNPtr createPseudoMercator(PropertyMap &props) {
+static ProjectedCRSNNPtr createPseudoMercator(const PropertyMap &props) {
     auto conversion = Conversion::createPopularVisualisationPseudoMercator(
         PropertyMap().set(IdentifiedObject::NAME_KEY, "unnamed"), Angle(0),
         Angle(0), Length(0), Length(0));
-    props.set(IdentifiedObject::NAME_KEY, "WGS 84 / Pseudo-Mercator");
     return ProjectedCRS::create(
         props, GeographicCRS::EPSG_4326, conversion,
         CartesianCS::createEastingNorthing(UnitOfMeasure::METRE));
@@ -3330,33 +3343,59 @@ WKTParser::Private::buildProjectedCRS(const WKTNodeNNPtr &node) {
         ThrowMissing(WKTConstants::CONVERSION);
     }
 
+    auto &baseGeodCRSNode =
+        nodeP->lookForChild(WKTConstants::BASEGEODCRS,
+                            WKTConstants::BASEGEOGCRS, WKTConstants::GEOGCS);
+    if (isNull(baseGeodCRSNode)) {
+        throw ParsingException(
+            "Missing BASEGEODCRS / BASEGEOGCRS / GEOGCS node");
+    }
+    auto baseGeodCRS = buildGeodeticCRS(baseGeodCRSNode);
+
     auto props = buildProperties(node);
 
+    const std::string projCRSName = stripQuotes(nodeP->children()[0]);
+    if (esriStyle_ && dbContext_) {
+        // It is likely that the ESRI definition of EPSG:32661 (UPS North) &
+        // EPSG:32761 (UPS South) uses the easting-northing order, instead
+        // of the EPSG northing-easting order
+        // so don't substitue names to avoid confusion.
+        if (projCRSName == "UPS_North") {
+            props.set(IdentifiedObject::NAME_KEY, "WGS 84 / UPS North (E,N)");
+        } else if (projCRSName == "UPS_South") {
+            props.set(IdentifiedObject::NAME_KEY, "WGS 84 / UPS South (E,N)");
+        } else {
+            std::string outTableName;
+            std::string authNameFromAlias;
+            std::string codeFromAlias;
+            auto authFactory = AuthorityFactory::create(NN_NO_CHECK(dbContext_),
+                                                        std::string());
+            auto officialName = authFactory->getOfficialNameFromAlias(
+                projCRSName, "projected_crs", "ESRI", outTableName,
+                authNameFromAlias, codeFromAlias);
+            if (!officialName.empty()) {
+                props.set(IdentifiedObject::NAME_KEY, officialName);
+            }
+        }
+    }
+
     if (isNull(conversionNode) && hasWebMercPROJ4String(node, projectionNode)) {
+        toWGS84Parameters_.clear();
         return createPseudoMercator(props);
     }
 
-    const std::string projectedCRSName = stripQuotes(nodeP->children()[0]);
     // WGS_84_Pseudo_Mercator: Particular case for corrupted ESRI WKT generated
     // by older GDAL versions
     // https://trac.osgeo.org/gdal/changeset/30732
     // WGS_1984_Web_Mercator: deprecated ESRI:102113
-    if (metadata::Identifier::isEquivalentName(projectedCRSName.c_str(),
+    if (metadata::Identifier::isEquivalentName(projCRSName.c_str(),
                                                "WGS_84_Pseudo_Mercator") ||
-        metadata::Identifier::isEquivalentName(projectedCRSName.c_str(),
+        metadata::Identifier::isEquivalentName(projCRSName.c_str(),
                                                "WGS_1984_Web_Mercator")) {
+        toWGS84Parameters_.clear();
         return createPseudoMercator(props);
     }
 
-    auto &baseGeodCRSNode =
-        nodeP->lookForChild(WKTConstants::BASEGEODCRS,
-                            WKTConstants::BASEGEOGCRS, WKTConstants::GEOGCS);
-    if (isNull(baseGeodCRSNode)) {
-        throw ParsingException(
-            "Missing BASEGEODCRS / BASEGEOGCRS / GEOGCS node");
-    }
-    auto baseGeodCRS = buildGeodeticCRS(baseGeodCRSNode);
-
     auto linearUnit = buildUnitInSubNode(node, UnitOfMeasure::Type::LINEAR);
     auto angularUnit = baseGeodCRS->coordinateSystem()->axisList()[0]->unit();
 
@@ -3445,32 +3484,6 @@ WKTParser::Private::buildProjectedCRS(const WKTNodeNNPtr &node) {
         ThrowNotExpectedCSType("Cartesian");
     }
 
-    if (esriStyle_ && dbContext_) {
-        auto projCRSName = stripQuotes(nodeP->children()[0]);
-
-        // It is likely that the ESRI definition of EPSG:32661 (UPS North) &
-        // EPSG:32761 (UPS South) uses the easting-northing order, instead
-        // of the EPSG northing-easting order
-        // so don't substitue names to avoid confusion.
-        if (projCRSName == "UPS_North") {
-            props.set(IdentifiedObject::NAME_KEY, "WGS 84 / UPS North (E,N)");
-        } else if (projCRSName == "UPS_South") {
-            props.set(IdentifiedObject::NAME_KEY, "WGS 84 / UPS South (E,N)");
-        } else {
-            std::string outTableName;
-            std::string authNameFromAlias;
-            std::string codeFromAlias;
-            auto authFactory = AuthorityFactory::create(NN_NO_CHECK(dbContext_),
-                                                        std::string());
-            auto officialName = authFactory->getOfficialNameFromAlias(
-                projCRSName, "projected_crs", "ESRI", outTableName,
-                authNameFromAlias, codeFromAlias);
-            if (!officialName.empty()) {
-                props.set(IdentifiedObject::NAME_KEY, officialName);
-            }
-        }
-    }
-
     addExtensionProj4ToProp(nodeP, props);
 
     return ProjectedCRS::create(props, baseGeodCRS, conversion,
@@ -5274,7 +5287,7 @@ void PROJStringFormatter::addParam(const char *paramName,
     std::string paramValue;
     for (size_t i = 0; i < vals.size(); ++i) {
         if (i > 0) {
-            paramValue += ",";
+            paramValue += ',';
         }
         paramValue += formatToString(vals[i]);
     }
diff --git a/test/unit/test_crs.cpp b/test/unit/test_crs.cpp
index 74f85d50..3f044de0 100644
--- a/test/unit/test_crs.cpp
+++ b/test/unit/test_crs.cpp
@@ -1685,6 +1685,60 @@ TEST(crs, projectedCRS_as_WKT1_ESRI) {
 
 // ---------------------------------------------------------------------------
 
+TEST(crs, projectedCRS_with_ESRI_code_as_WKT1_ESRI) {
+    auto dbContext = DatabaseContext::create();
+    auto crs = AuthorityFactory::create(dbContext, "ESRI")
+                   ->createProjectedCRS("102113");
+
+    // Comes literally from the text_definition column of
+    // projected_crs table
+    auto esri_wkt =
+        "PROJCS[\"WGS_1984_Web_Mercator\","
+        "GEOGCS[\"GCS_WGS_1984_Major_Auxiliary_Sphere\","
+        "DATUM[\"D_WGS_1984_Major_Auxiliary_Sphere\","
+        "SPHEROID[\"WGS_1984_Major_Auxiliary_Sphere\",6378137.0,0.0]],"
+        "PRIMEM[\"Greenwich\",0.0],UNIT[\"Degree\",0.0174532925199433]],"
+        "PROJECTION[\"Mercator\"],PARAMETER[\"False_Easting\",0.0],"
+        "PARAMETER[\"False_Northing\",0.0],PARAMETER[\"Central_Meridian\",0.0],"
+        "PARAMETER[\"Standard_Parallel_1\",0.0],UNIT[\"Meter\",1.0]]";
+
+    EXPECT_EQ(
+        crs->exportToWKT(
+            WKTFormatter::create(WKTFormatter::Convention::WKT1_ESRI, dbContext)
+                .get()),
+        esri_wkt);
+}
+
+// ---------------------------------------------------------------------------
+
+TEST(crs, projectedCRS_from_WKT1_ESRI_as_WKT1_ESRI) {
+    auto dbContext = DatabaseContext::create();
+    // Comes literally from the text_definition column of
+    // projected_crs table
+    auto esri_wkt =
+        "PROJCS[\"WGS_1984_Web_Mercator\","
+        "GEOGCS[\"GCS_WGS_1984_Major_Auxiliary_Sphere\","
+        "DATUM[\"D_WGS_1984_Major_Auxiliary_Sphere\","
+        "SPHEROID[\"WGS_1984_Major_Auxiliary_Sphere\",6378137.0,0.0]],"
+        "PRIMEM[\"Greenwich\",0.0],UNIT[\"Degree\",0.0174532925199433]],"
+        "PROJECTION[\"Mercator\"],PARAMETER[\"False_Easting\",0.0],"
+        "PARAMETER[\"False_Northing\",0.0],PARAMETER[\"Central_Meridian\",0.0],"
+        "PARAMETER[\"Standard_Parallel_1\",0.0],UNIT[\"Meter\",1.0]]";
+
+    auto obj =
+        WKTParser().attachDatabaseContext(dbContext).createFromWKT(esri_wkt);
+    auto crs = nn_dynamic_pointer_cast<ProjectedCRS>(obj);
+    ASSERT_TRUE(crs != nullptr);
+
+    EXPECT_EQ(
+        crs->exportToWKT(
+            WKTFormatter::create(WKTFormatter::Convention::WKT1_ESRI, dbContext)
+                .get()),
+        esri_wkt);
+}
+
+// ---------------------------------------------------------------------------
+
 TEST(crs, projectedCRS_as_PROJ_string) {
     auto crs = createProjected();
     EXPECT_EQ(crs->exportToPROJString(PROJStringFormatter::create().get()),
diff --git a/test/unit/test_factory.cpp b/test/unit/test_factory.cpp
index 20869d91..7cdb0b40 100644
--- a/test/unit/test_factory.cpp
+++ b/test/unit/test_factory.cpp
@@ -2703,6 +2703,13 @@ TEST(factory, createObjectsFromName) {
                   .size(),
               1);
 
+    // Deprecated object (but without explicit deprecated)
+    EXPECT_EQ(
+        factoryEPSG
+            ->createObjectsFromName("NAD27(CGQ77) / SCoPQ zone 2", {}, false, 2)
+            .size(),
+        1);
+
     const auto types = std::vector<AuthorityFactory::ObjectType>{
         AuthorityFactory::ObjectType::PRIME_MERIDIAN,
         AuthorityFactory::ObjectType::ELLIPSOID,