Add tracing framework, and improve createOperations() performance

1 files changed, 411 insertions, 87 deletions

diff --git a/src/iso19111/factory.cpp b/src/iso19111/factory.cpp
index 44e44dbe..307c3a07 100644
--- a/src/iso19111/factory.cpp
+++ b/src/iso19111/factory.cpp
@@ -43,6 +43,7 @@
 #include "proj/internal/internal.hpp"
 #include "proj/internal/io_internal.hpp"
 #include "proj/internal/lru_cache.hpp"
+#include "proj/internal/tracing.hpp"
 
 #include <cmath>
 #include <cstdlib>
@@ -186,6 +187,12 @@ struct DatabaseContext::Private {
     void cache(const std::string &code,
                const datum::GeodeticReferenceFrameNNPtr &datum);
 
+    datum::EllipsoidPtr
+        // cppcheck-suppress functionStatic
+        getEllipsoidFromCache(const std::string &code);
+    // cppcheck-suppress functionStatic
+    void cache(const std::string &code, const datum::EllipsoidNNPtr &ellipsoid);
+
     datum::PrimeMeridianPtr
         // cppcheck-suppress functionStatic
         getPrimeMeridianFromCache(const std::string &code);
@@ -244,6 +251,7 @@ struct DatabaseContext::Private {
     static constexpr size_t CACHE_SIZE = 128;
     LRUCacheOfObjects cacheUOM_{CACHE_SIZE};
     LRUCacheOfObjects cacheCRS_{CACHE_SIZE};
+    LRUCacheOfObjects cacheEllipsoid_{CACHE_SIZE};
     LRUCacheOfObjects cacheGeodeticDatum_{CACHE_SIZE};
     LRUCacheOfObjects cachePrimeMeridian_{CACHE_SIZE};
     LRUCacheOfObjects cacheCS_{CACHE_SIZE};
@@ -252,6 +260,8 @@ struct DatabaseContext::Private {
         cacheCRSToCrsCoordOp_{CACHE_SIZE};
     lru11::Cache<std::string, GridInfoCache> cacheGridInfo_{CACHE_SIZE};
 
+    std::map<std::string, std::vector<std::string>> cacheAllowedAuthorities_{};
+
     static void insertIntoCache(LRUCacheOfObjects &cache,
                                 const std::string &code,
                                 const util::BaseObjectPtr &obj);
@@ -408,6 +418,22 @@ void DatabaseContext::Private::cache(
 
 // ---------------------------------------------------------------------------
 
+datum::EllipsoidPtr
+DatabaseContext::Private::getEllipsoidFromCache(const std::string &code) {
+    util::BaseObjectPtr obj;
+    getFromCache(cacheEllipsoid_, code, obj);
+    return std::static_pointer_cast<datum::Ellipsoid>(obj);
+}
+
+// ---------------------------------------------------------------------------
+
+void DatabaseContext::Private::cache(const std::string &code,
+                                     const datum::EllipsoidNNPtr &ellps) {
+    insertIntoCache(cacheEllipsoid_, code, ellps.as_nullable());
+}
+
+// ---------------------------------------------------------------------------
+
 datum::PrimeMeridianPtr
 DatabaseContext::Private::getPrimeMeridianFromCache(const std::string &code) {
     util::BaseObjectPtr obj;
@@ -830,6 +856,25 @@ SQLResultSet DatabaseContext::Private::run(const std::string &sql,
         nBindField++;
     }
 
+#ifdef TRACE_DATABASE
+    size_t nPos = 0;
+    std::string sqlSubst(sql);
+    for (const auto &param : parameters) {
+        nPos = sqlSubst.find('?', nPos);
+        assert(nPos != std::string::npos);
+        std::string strValue;
+        if (param.type() == SQLValues::Type::STRING) {
+            strValue = '\'' + param.stringValue() + '\'';
+        } else {
+            strValue = toString(param.doubleValue());
+        }
+        sqlSubst =
+            sqlSubst.substr(0, nPos) + strValue + sqlSubst.substr(nPos + 1);
+        nPos += strValue.size();
+    }
+    logTrace(sqlSubst, "DATABASE");
+#endif
+
     SQLResultSet result;
     const int column_count = sqlite3_column_count(stmt);
     while (true) {
@@ -1118,32 +1163,42 @@ std::string DatabaseContext::getTextDefinition(const std::string &tableName,
 std::vector<std::string> DatabaseContext::getAllowedAuthorities(
     const std::string &sourceAuthName,
     const std::string &targetAuthName) const {
-    auto res = d->run(
+
+    const auto key(sourceAuthName + targetAuthName);
+    auto hit = d->cacheAllowedAuthorities_.find(key);
+    if (hit != d->cacheAllowedAuthorities_.end()) {
+        return hit->second;
+    }
+
+    auto sqlRes = d->run(
         "SELECT allowed_authorities FROM authority_to_authority_preference "
         "WHERE source_auth_name = ? AND target_auth_name = ?",
         {sourceAuthName, targetAuthName});
-    if (res.empty()) {
-        res = d->run(
+    if (sqlRes.empty()) {
+        sqlRes = d->run(
             "SELECT allowed_authorities FROM authority_to_authority_preference "
             "WHERE source_auth_name = ? AND target_auth_name = 'any'",
             {sourceAuthName});
     }
-    if (res.empty()) {
-        res = d->run(
+    if (sqlRes.empty()) {
+        sqlRes = d->run(
             "SELECT allowed_authorities FROM authority_to_authority_preference "
             "WHERE source_auth_name = 'any' AND target_auth_name = ?",
             {targetAuthName});
     }
-    if (res.empty()) {
-        res = d->run(
+    if (sqlRes.empty()) {
+        sqlRes = d->run(
             "SELECT allowed_authorities FROM authority_to_authority_preference "
             "WHERE source_auth_name = 'any' AND target_auth_name = 'any'",
             {});
     }
-    if (res.empty()) {
+    if (sqlRes.empty()) {
+        d->cacheAllowedAuthorities_[key] = std::vector<std::string>();
         return std::vector<std::string>();
     }
-    return split(res.front()[0], ',');
+    auto res = split(sqlRes.front()[0], ',');
+    d->cacheAllowedAuthorities_[key] = res;
+    return res;
 }
 
 // ---------------------------------------------------------------------------
@@ -1209,8 +1264,8 @@ struct AuthorityFactory::Private {
         return AuthorityFactory::create(context_, auth_name);
     }
 
-    bool rejectOpDueToMissingGrid(const operation::CoordinateOperationNNPtr &op,
-                                  bool discardIfMissingGrid);
+    bool
+    rejectOpDueToMissingGrid(const operation::CoordinateOperationNNPtr &op);
 
     UnitOfMeasure createUnitOfMeasure(const std::string &auth_name,
                                       const std::string &code);
@@ -1337,12 +1392,10 @@ util::PropertyMap AuthorityFactory::Private::createProperties(
 // ---------------------------------------------------------------------------
 
 bool AuthorityFactory::Private::rejectOpDueToMissingGrid(
-    const operation::CoordinateOperationNNPtr &op, bool discardIfMissingGrid) {
-    if (discardIfMissingGrid) {
-        for (const auto &gridDesc : op->gridsNeeded(context())) {
-            if (!gridDesc.available) {
-                return true;
-            }
+    const operation::CoordinateOperationNNPtr &op) {
+    for (const auto &gridDesc : op->gridsNeeded(context())) {
+        if (!gridDesc.available) {
+            return true;
         }
     }
     return false;
@@ -1542,7 +1595,7 @@ AuthorityFactory::createExtent(const std::string &code) const {
         if (row[1].empty()) {
             auto extent = metadata::Extent::create(
                 util::optional<std::string>(name), {}, {}, {});
-            d->context()->d->cache(code, extent);
+            d->context()->d->cache(cacheKey, extent);
             return extent;
         }
         double south_lat = c_locale_stod(row[1]);
@@ -1557,7 +1610,7 @@ AuthorityFactory::createExtent(const std::string &code) const {
             std::vector<metadata::GeographicExtentNNPtr>{bbox},
             std::vector<metadata::VerticalExtentNNPtr>(),
             std::vector<metadata::TemporalExtentNNPtr>());
-        d->context()->d->cache(code, extent);
+        d->context()->d->cache(cacheKey, extent);
         return extent;
 
     } catch (const std::exception &ex) {
@@ -1755,6 +1808,13 @@ AuthorityFactory::identifyBodyFromSemiMajorAxis(double semi_major_axis,
 
 datum::EllipsoidNNPtr
 AuthorityFactory::createEllipsoid(const std::string &code) const {
+    const auto cacheKey(d->authority() + code);
+    {
+        auto ellps = d->context()->d->getEllipsoidFromCache(cacheKey);
+        if (ellps) {
+            return NN_NO_CHECK(ellps);
+        }
+    }
     auto res = d->runWithCodeParam(
         "SELECT ellipsoid.name, ellipsoid.semi_major_axis, "
         "ellipsoid.uom_auth_name, ellipsoid.uom_code, "
@@ -1783,16 +1843,22 @@ AuthorityFactory::createEllipsoid(const std::string &code) const {
         auto uom = d->createUnitOfMeasure(uom_auth_name, uom_code);
         auto props = d->createProperties(code, name, deprecated, nullptr);
         if (!inv_flattening_str.empty()) {
-            return datum::Ellipsoid::createFlattenedSphere(
+            auto ellps = datum::Ellipsoid::createFlattenedSphere(
                 props, common::Length(semi_major_axis, uom),
                 common::Scale(c_locale_stod(inv_flattening_str)), body);
+            d->context()->d->cache(cacheKey, ellps);
+            return ellps;
         } else if (semi_major_axis_str == semi_minor_axis_str) {
-            return datum::Ellipsoid::createSphere(
+            auto ellps = datum::Ellipsoid::createSphere(
                 props, common::Length(semi_major_axis, uom), body);
+            d->context()->d->cache(cacheKey, ellps);
+            return ellps;
         } else {
-            return datum::Ellipsoid::createTwoAxis(
+            auto ellps = datum::Ellipsoid::createTwoAxis(
                 props, common::Length(semi_major_axis, uom),
                 common::Length(c_locale_stod(semi_minor_axis_str), uom), body);
+            d->context()->d->cache(cacheKey, ellps);
+            return ellps;
         }
     } catch (const std::exception &ex) {
         throw buildFactoryException("ellipsoid", code, ex);
@@ -2112,10 +2178,14 @@ crs::GeodeticCRSNNPtr
 AuthorityFactory::createGeodeticCRS(const std::string &code,
                                     bool geographicOnly) const {
     const auto cacheKey(d->authority() + code);
-    auto crs = std::dynamic_pointer_cast<crs::GeodeticCRS>(
-        d->context()->d->getCRSFromCache(cacheKey));
+    auto crs = d->context()->d->getCRSFromCache(cacheKey);
     if (crs) {
-        return NN_NO_CHECK(crs);
+        auto geogCRS = std::dynamic_pointer_cast<crs::GeodeticCRS>(crs);
+        if (geogCRS) {
+            return NN_NO_CHECK(geogCRS);
+        }
+        throw NoSuchAuthorityCodeException("geodeticCRS not found",
+                                           d->authority(), code);
     }
     std::string sql("SELECT name, type, coordinate_system_auth_name, "
                     "coordinate_system_code, datum_auth_name, datum_code, "
@@ -2153,7 +2223,9 @@ AuthorityFactory::createGeodeticCRS(const std::string &code,
                 pj_add_type_crs_if_needed(text_definition), d->context());
             auto geodCRS = util::nn_dynamic_pointer_cast<crs::GeodeticCRS>(obj);
             if (geodCRS) {
-                return cloneWithProps(NN_NO_CHECK(geodCRS), props);
+                auto crsRet = cloneWithProps(NN_NO_CHECK(geodCRS), props);
+                d->context()->d->cache(cacheKey, crsRet);
+                return crsRet;
             }
 
             auto boundCRS = dynamic_cast<const crs::BoundCRS *>(obj.get());
@@ -2213,6 +2285,16 @@ AuthorityFactory::createGeodeticCRS(const std::string &code,
 
 crs::VerticalCRSNNPtr
 AuthorityFactory::createVerticalCRS(const std::string &code) const {
+    const auto cacheKey(d->authority() + code);
+    auto crs = d->context()->d->getCRSFromCache(cacheKey);
+    if (crs) {
+        auto projCRS = std::dynamic_pointer_cast<crs::VerticalCRS>(crs);
+        if (projCRS) {
+            return NN_NO_CHECK(projCRS);
+        }
+        throw NoSuchAuthorityCodeException("verticalCRS not found",
+                                           d->authority(), code);
+    }
     auto res = d->runWithCodeParam(
         "SELECT name, coordinate_system_auth_name, "
         "coordinate_system_code, datum_auth_name, datum_code, "
@@ -2243,8 +2325,10 @@ AuthorityFactory::createVerticalCRS(const std::string &code) const {
 
         auto verticalCS = util::nn_dynamic_pointer_cast<cs::VerticalCS>(cs);
         if (verticalCS) {
-            return crs::VerticalCRS::create(props, datum,
-                                            NN_NO_CHECK(verticalCS));
+            auto crsRet =
+                crs::VerticalCRS::create(props, datum, NN_NO_CHECK(verticalCS));
+            d->context()->d->cache(cacheKey, crsRet);
+            return crsRet;
         }
         throw FactoryException("unsupported CS type for verticalCRS: " +
                                cs->getWKT2Type(true));
@@ -2380,6 +2464,16 @@ AuthorityFactory::createConversion(const std::string &code) const {
 
 crs::ProjectedCRSNNPtr
 AuthorityFactory::createProjectedCRS(const std::string &code) const {
+    const auto cacheKey(d->authority() + code);
+    auto crs = d->context()->d->getCRSFromCache(cacheKey);
+    if (crs) {
+        auto projCRS = std::dynamic_pointer_cast<crs::ProjectedCRS>(crs);
+        if (projCRS) {
+            return NN_NO_CHECK(projCRS);
+        }
+        throw NoSuchAuthorityCodeException("projectedCRS not found",
+                                           d->authority(), code);
+    }
     auto res = d->runWithCodeParam(
         "SELECT name, coordinate_system_auth_name, "
         "coordinate_system_code, geodetic_crs_auth_name, geodetic_crs_code, "
@@ -2422,9 +2516,11 @@ AuthorityFactory::createProjectedCRS(const std::string &code) const {
                                   common::IdentifiedObject::NAME_KEY, name),
                               conv->method(), conv->parameterValues())
                         : conv;
-                return crs::ProjectedCRS::create(props, projCRS->baseCRS(),
-                                                 newConv,
-                                                 projCRS->coordinateSystem());
+                auto crsRet = crs::ProjectedCRS::create(
+                    props, projCRS->baseCRS(), newConv,
+                    projCRS->coordinateSystem());
+                d->context()->d->cache(cacheKey, crsRet);
+                return crsRet;
             }
 
             auto boundCRS = dynamic_cast<const crs::BoundCRS *>(obj.get());
@@ -2464,8 +2560,10 @@ AuthorityFactory::createProjectedCRS(const std::string &code) const {
 
         auto cartesianCS = util::nn_dynamic_pointer_cast<cs::CartesianCS>(cs);
         if (cartesianCS) {
-            return crs::ProjectedCRS::create(props, baseCRS, conv,
-                                             NN_NO_CHECK(cartesianCS));
+            auto crsRet = crs::ProjectedCRS::create(props, baseCRS, conv,
+                                                    NN_NO_CHECK(cartesianCS));
+            d->context()->d->cache(cacheKey, crsRet);
+            return crsRet;
         }
         throw FactoryException("unsupported CS type for projectedCRS: " +
                                cs->getWKT2Type(true));
@@ -3309,6 +3407,14 @@ AuthorityFactory::createFromCoordinateReferenceSystemCodes(
  * missing grids should be removed from the result set.
  * @param discardSuperseded Whether cordinate operations that are superseded
  * (but not deprecated) should be removed from the result set.
+ * @param tryReverseOrder whether to search in the reverse order too (and thus
+ * inverse results found that way)
+ * @param reportOnlyIntersectingTransformations if intersectingExtent1 and
+ * intersectingExtent2 should be honored in a strict way.
+ * @param intersectingExtent1 Optional extent that the resulting operations
+ * must intersect.
+ * @param intersectingExtent2 Optional extent that the resulting operations
+ * must intersect.
  * @return list of coordinate operations
  * @throw NoSuchAuthorityCodeException
  * @throw FactoryException
@@ -3319,7 +3425,10 @@ AuthorityFactory::createFromCoordinateReferenceSystemCodes(
     const std::string &sourceCRSAuthName, const std::string &sourceCRSCode,
     const std::string &targetCRSAuthName, const std::string &targetCRSCode,
     bool usePROJAlternativeGridNames, bool discardIfMissingGrid,
-    bool discardSuperseded) const {
+    bool discardSuperseded, bool tryReverseOrder,
+    bool reportOnlyIntersectingTransformations,
+    const metadata::ExtentPtr &intersectingExtent1,
+    const metadata::ExtentPtr &intersectingExtent2) const {
 
     auto cacheKey(d->authority());
     cacheKey += sourceCRSAuthName.empty() ? "{empty}" : sourceCRSAuthName;
@@ -3329,6 +3438,24 @@ AuthorityFactory::createFromCoordinateReferenceSystemCodes(
     cacheKey += (usePROJAlternativeGridNames ? '1' : '0');
     cacheKey += (discardIfMissingGrid ? '1' : '0');
     cacheKey += (discardSuperseded ? '1' : '0');
+    cacheKey += (tryReverseOrder ? '1' : '0');
+    cacheKey += (reportOnlyIntersectingTransformations ? '1' : '0');
+    for (const auto &extent : {intersectingExtent1, intersectingExtent2}) {
+        if (extent) {
+            const auto &geogExtent = extent->geographicElements();
+            if (geogExtent.size() == 1) {
+                auto bbox =
+                    dynamic_cast<const metadata::GeographicBoundingBox *>(
+                        geogExtent[0].get());
+                if (bbox) {
+                    cacheKey += toString(bbox->southBoundLatitude());
+                    cacheKey += toString(bbox->westBoundLongitude());
+                    cacheKey += toString(bbox->northBoundLatitude());
+                    cacheKey += toString(bbox->eastBoundLongitude());
+                }
+            }
+        }
+    }
 
     std::vector<operation::CoordinateOperationNNPtr> list;
 
@@ -3337,31 +3464,35 @@ AuthorityFactory::createFromCoordinateReferenceSystemCodes(
     }
 
     if (!targetCRSAuthName.empty()) {
-        // Look-up first for conversion which is the most precise.
-        std::string sql("SELECT conversion_auth_name, "
-                        "geodetic_crs_auth_name, geodetic_crs_code FROM "
-                        "projected_crs WHERE auth_name = ? AND code = ?");
-        auto params = ListOfParams{targetCRSAuthName, targetCRSCode};
-        auto res = d->run(sql, params);
-        if (!res.empty()) {
-            const auto &row = res.front();
-            bool ok = row[1] == sourceCRSAuthName && row[2] == sourceCRSCode;
-            if (ok && d->hasAuthorityRestriction()) {
-                ok = row[0] == d->authority();
-            }
-            if (ok) {
-                auto targetCRS = d->createFactory(targetCRSAuthName)
-                                     ->createProjectedCRS(targetCRSCode);
+        auto targetFactory = d->createFactory(targetCRSAuthName);
+        try {
+            auto targetCRS = targetFactory->createProjectedCRS(targetCRSCode);
+            const auto &baseIds = targetCRS->baseCRS()->identifiers();
+            if (sourceCRSAuthName.empty() ||
+                (!baseIds.empty() &&
+                 *(baseIds.front()->codeSpace()) == sourceCRSAuthName &&
+                 baseIds.front()->code() == sourceCRSCode)) {
+                bool ok = true;
                 auto conv = targetCRS->derivingConversion();
-                list.emplace_back(conv);
-                d->context()->d->cache(cacheKey, list);
-                return list;
+                if (d->hasAuthorityRestriction()) {
+                    ok = *(conv->identifiers().front()->codeSpace()) ==
+                         d->authority();
+                }
+                if (ok) {
+                    list.emplace_back(conv);
+                    d->context()->d->cache(cacheKey, list);
+                    return list;
+                }
             }
+        } catch (const std::exception &) {
         }
     }
     std::string sql;
     if (discardSuperseded) {
-        sql = "SELECT cov.auth_name, cov.code, cov.table_name, "
+        sql = "SELECT source_crs_auth_name, source_crs_code, "
+              "target_crs_auth_name, target_crs_code, "
+              "cov.auth_name, cov.code, cov.table_name, "
+              "area.south_lat, area.west_lon, area.north_lat, area.east_lon, "
               "ss.replacement_auth_name, ss.replacement_code FROM "
               "coordinate_operation_view cov JOIN area "
               "ON cov.area_of_use_auth_name = area.auth_name AND "
@@ -3373,22 +3504,65 @@ AuthorityFactory::createFromCoordinateReferenceSystemCodes(
               "ss.superseded_table_name = ss.replacement_table_name "
               "WHERE ";
     } else {
-        sql = "SELECT cov.auth_name, cov.code, cov.table_name FROM "
+        sql = "SELECT source_crs_auth_name, source_crs_code, "
+              "target_crs_auth_name, target_crs_code, "
+              "cov.auth_name, cov.code, cov.table_name, "
+              "area.south_lat, area.west_lon, area.north_lat, area.east_lon "
+              "FROM "
               "coordinate_operation_view cov JOIN area "
               "ON cov.area_of_use_auth_name = area.auth_name AND "
               "cov.area_of_use_code = area.code "
               "WHERE ";
     }
     ListOfParams params;
-    if (!sourceCRSAuthName.empty()) {
-        sql += "source_crs_auth_name = ? AND source_crs_code = ? AND ";
-        params.emplace_back(sourceCRSAuthName);
-        params.emplace_back(sourceCRSCode);
-    }
-    if (!targetCRSAuthName.empty()) {
-        sql += "target_crs_auth_name = ? AND target_crs_code = ? AND ";
-        params.emplace_back(targetCRSAuthName);
-        params.emplace_back(targetCRSCode);
+    if (!sourceCRSAuthName.empty() && !targetCRSAuthName.empty()) {
+        if (tryReverseOrder) {
+            sql += "((source_crs_auth_name = ? AND source_crs_code = ? AND "
+                   "target_crs_auth_name = ? AND target_crs_code = ?) OR "
+                   "(source_crs_auth_name = ? AND source_crs_code = ? AND "
+                   "target_crs_auth_name = ? AND target_crs_code = ?)) AND ";
+            params.emplace_back(sourceCRSAuthName);
+            params.emplace_back(sourceCRSCode);
+            params.emplace_back(targetCRSAuthName);
+            params.emplace_back(targetCRSCode);
+            params.emplace_back(targetCRSAuthName);
+            params.emplace_back(targetCRSCode);
+            params.emplace_back(sourceCRSAuthName);
+            params.emplace_back(sourceCRSCode);
+        } else {
+            sql += "source_crs_auth_name = ? AND source_crs_code = ? AND "
+                   "target_crs_auth_name = ? AND target_crs_code = ? AND ";
+            params.emplace_back(sourceCRSAuthName);
+            params.emplace_back(sourceCRSCode);
+            params.emplace_back(targetCRSAuthName);
+            params.emplace_back(targetCRSCode);
+        }
+    } else if (!sourceCRSAuthName.empty()) {
+        if (tryReverseOrder) {
+            sql += "((source_crs_auth_name = ? AND source_crs_code = ?) OR "
+                   "(target_crs_auth_name = ? AND target_crs_code = ?)) AND ";
+            params.emplace_back(sourceCRSAuthName);
+            params.emplace_back(sourceCRSCode);
+            params.emplace_back(sourceCRSAuthName);
+            params.emplace_back(sourceCRSCode);
+        } else {
+            sql += "source_crs_auth_name = ? AND source_crs_code = ? AND ";
+            params.emplace_back(sourceCRSAuthName);
+            params.emplace_back(sourceCRSCode);
+        }
+    } else if (!targetCRSAuthName.empty()) {
+        if (tryReverseOrder) {
+            sql += "((source_crs_auth_name = ? AND source_crs_code = ?) OR "
+                   "(target_crs_auth_name = ? AND target_crs_code = ?)) AND ";
+            params.emplace_back(targetCRSAuthName);
+            params.emplace_back(targetCRSCode);
+            params.emplace_back(targetCRSAuthName);
+            params.emplace_back(targetCRSCode);
+        } else {
+            sql += "target_crs_auth_name = ? AND target_crs_code = ? AND ";
+            params.emplace_back(targetCRSAuthName);
+            params.emplace_back(targetCRSCode);
+        }
     }
     sql += "cov.deprecated = 0";
     if (d->hasAuthorityRestriction()) {
@@ -3402,16 +3576,25 @@ AuthorityFactory::createFromCoordinateReferenceSystemCodes(
     std::set<std::pair<std::string, std::string>> setTransf;
     if (discardSuperseded) {
         for (const auto &row : res) {
-            const auto &auth_name = row[0];
-            const auto &code = row[1];
+            const auto &auth_name = row[4];
+            const auto &code = row[5];
             setTransf.insert(
                 std::pair<std::string, std::string>(auth_name, code));
         }
     }
+
+    // Do a pass to determine if there are transformations that intersect
+    // intersectingExtent1 & intersectingExtent2
+    std::vector<bool> intersectingTransformations;
+    intersectingTransformations.resize(res.size());
+    bool hasIntersectingTransformations = false;
+    size_t i = 0;
     for (const auto &row : res) {
+        size_t thisI = i;
+        ++i;
         if (discardSuperseded) {
-            const auto &replacement_auth_name = row[3];
-            const auto &replacement_code = row[4];
+            const auto &replacement_auth_name = row[11];
+            const auto &replacement_code = row[12];
             if (!replacement_auth_name.empty() &&
                 setTransf.find(std::pair<std::string, std::string>(
                     replacement_auth_name, replacement_code)) !=
@@ -3422,12 +3605,77 @@ AuthorityFactory::createFromCoordinateReferenceSystemCodes(
             }
         }
 
-        const auto &auth_name = row[0];
-        const auto &code = row[1];
-        const auto &table_name = row[2];
+        bool intersecting = true;
+        try {
+            double south_lat = c_locale_stod(row[7]);
+            double west_lon = c_locale_stod(row[8]);
+            double north_lat = c_locale_stod(row[9]);
+            double east_lon = c_locale_stod(row[10]);
+            auto transf_extent = metadata::Extent::createFromBBOX(
+                west_lon, south_lat, east_lon, north_lat);
+
+            for (const auto &extent :
+                 {intersectingExtent1, intersectingExtent2}) {
+                if (extent) {
+                    if (!transf_extent->intersects(NN_NO_CHECK(extent))) {
+                        intersecting = false;
+                        break;
+                    }
+                }
+            }
+        } catch (const std::exception &) {
+        }
+
+        intersectingTransformations[thisI] = intersecting;
+        if (intersecting)
+            hasIntersectingTransformations = true;
+    }
+
+    // If there are intersecting transformations, then only report those ones
+    // If there are no intersecting transformations, report all of them
+    // This is for the "projinfo -s EPSG:32631 -t EPSG:2171" use case where we
+    // still want to be able to use the Pulkovo datum shift if EPSG:32631
+    // coordinates are used
+    i = 0;
+    for (const auto &row : res) {
+        size_t thisI = i;
+        ++i;
+        if ((hasIntersectingTransformations ||
+             reportOnlyIntersectingTransformations) &&
+            !intersectingTransformations[thisI]) {
+            continue;
+        }
+        if (discardSuperseded) {
+            const auto &replacement_auth_name = row[11];
+            const auto &replacement_code = row[12];
+            if (!replacement_auth_name.empty() &&
+                setTransf.find(std::pair<std::string, std::string>(
+                    replacement_auth_name, replacement_code)) !=
+                    setTransf.end()) {
+                // Skip transformations that are superseded by others that got
+                // returned in the result set.
+                continue;
+            }
+        }
+
+        const auto &source_crs_auth_name = row[0];
+        const auto &source_crs_code = row[1];
+        const auto &target_crs_auth_name = row[2];
+        const auto &target_crs_code = row[3];
+        const auto &auth_name = row[4];
+        const auto &code = row[5];
+        const auto &table_name = row[6];
         auto op = d->createFactory(auth_name)->createCoordinateOperation(
             code, true, usePROJAlternativeGridNames, table_name);
-        if (!d->rejectOpDueToMissingGrid(op, discardIfMissingGrid)) {
+        if (tryReverseOrder &&
+            (!sourceCRSAuthName.empty()
+                 ? (source_crs_auth_name != sourceCRSAuthName ||
+                    source_crs_code != sourceCRSCode)
+                 : (target_crs_auth_name != targetCRSAuthName ||
+                    target_crs_code != targetCRSCode))) {
+            op = op->inverse();
+        }
+        if (!discardIfMissingGrid || !d->rejectOpDueToMissingGrid(op)) {
             list.emplace_back(op);
         }
     }
@@ -3524,6 +3772,13 @@ static bool useIrrelevantPivot(const operation::CoordinateOperationNNPtr &op,
  * used as potential intermediate CRS. If the list is empty, the database will
  * be used to find common CRS in operations involving both the source and
  * target CRS.
+ * @param allowedIntermediateObjectType Restrict the type of the intermediate
+ * object considered.
+ * Only ObjectType::CRS and ObjectType::GEOGRAPHIC_CRS supported currently
+ * @param intersectingExtent1 Optional extent that the resulting operations
+ * must intersect.
+ * @param intersectingExtent2 Optional extent that the resulting operations
+ * must intersect.
  * @return list of coordinate operations
  * @throw NoSuchAuthorityCodeException
  * @throw FactoryException
@@ -3536,7 +3791,10 @@ AuthorityFactory::createFromCRSCodesWithIntermediates(
     bool usePROJAlternativeGridNames, bool discardIfMissingGrid,
     bool discardSuperseded,
     const std::vector<std::pair<std::string, std::string>>
-        &intermediateCRSAuthCodes) const {
+        &intermediateCRSAuthCodes,
+    ObjectType allowedIntermediateObjectType,
+    const metadata::ExtentPtr &intersectingExtent1,
+    const metadata::ExtentPtr &intersectingExtent2) const {
 
     std::vector<operation::CoordinateOperationNNPtr> listTmp;
 
@@ -3617,18 +3875,55 @@ AuthorityFactory::createFromCRSCodesWithIntermediates(
         joinArea +
         "WHERE v1.source_crs_auth_name = ? AND v1.source_crs_code = ? "
         "AND v2.target_crs_auth_name = ? AND v2.target_crs_code = ? ");
+    if (allowedIntermediateObjectType == ObjectType::GEOGRAPHIC_CRS) {
+        sql += "AND EXISTS(SELECT 1 FROM geodetic_crs x WHERE "
+               "x.auth_name = v1.target_crs_auth_name AND "
+               "x.code = v1.target_crs_code AND "
+               "x.type IN ('geographic 2D', 'geographic 3D')) ";
+    }
     auto params = ListOfParams{sourceCRSAuthName, sourceCRSCode,
                                targetCRSAuthName, targetCRSCode};
-
     std::string additionalWhere(
         "AND v1.deprecated = 0 AND v2.deprecated = 0 "
         "AND intersects_bbox(south_lat1, west_lon1, north_lat1, east_lon1, "
-        "south_lat2, west_lon2, north_lat2, east_lon2) == 1 ");
+        "south_lat2, west_lon2, north_lat2, east_lon2) = 1 ");
     if (d->hasAuthorityRestriction()) {
         additionalWhere += "AND v1.auth_name = ? AND v2.auth_name = ? ";
         params.emplace_back(d->authority());
         params.emplace_back(d->authority());
     }
+    for (const auto &extent : {intersectingExtent1, intersectingExtent2}) {
+        if (extent) {
+            const auto &geogExtent = extent->geographicElements();
+            if (geogExtent.size() == 1) {
+                auto bbox =
+                    dynamic_cast<const metadata::GeographicBoundingBox *>(
+                        geogExtent[0].get());
+                if (bbox) {
+                    const double south_lat = bbox->southBoundLatitude();
+                    const double west_lon = bbox->westBoundLongitude();
+                    const double north_lat = bbox->northBoundLatitude();
+                    const double east_lon = bbox->eastBoundLongitude();
+                    if (south_lat != -90.0 || west_lon != -180.0 ||
+                        north_lat != 90.0 || east_lon != 180.0) {
+                        additionalWhere +=
+                            "AND intersects_bbox(south_lat1, "
+                            "west_lon1, north_lat1, east_lon1, ?, ?, ?, ?) AND "
+                            "intersects_bbox(south_lat2, west_lon2, "
+                            "north_lat2, east_lon2, ?, ?, ?, ?) ";
+                        params.emplace_back(south_lat);
+                        params.emplace_back(west_lon);
+                        params.emplace_back(north_lat);
+                        params.emplace_back(east_lon);
+                        params.emplace_back(south_lat);
+                        params.emplace_back(west_lon);
+                        params.emplace_back(north_lat);
+                        params.emplace_back(east_lon);
+                    }
+                }
+            }
+        }
+    }
     std::string intermediateWhere =
         buildIntermediateWhere(intermediateCRSAuthCodes, "target", "source");
     for (const auto &pair : intermediateCRSAuthCodes) {
@@ -3721,6 +4016,12 @@ AuthorityFactory::createFromCRSCodesWithIntermediates(
           joinArea +
           "WHERE v1.source_crs_auth_name = ? AND v1.source_crs_code = ? "
           "AND v2.source_crs_auth_name = ? AND v2.source_crs_code = ? ";
+    if (allowedIntermediateObjectType == ObjectType::GEOGRAPHIC_CRS) {
+        sql += "AND EXISTS(SELECT 1 FROM geodetic_crs x WHERE "
+               "x.auth_name = v1.target_crs_auth_name AND "
+               "x.code = v1.target_crs_code AND "
+               "x.type IN ('geographic 2D', 'geographic 3D')) ";
+    }
     intermediateWhere =
         buildIntermediateWhere(intermediateCRSAuthCodes, "target", "target");
     res = d->run(sql + additionalWhere + intermediateWhere + orderBy, params);
@@ -3762,6 +4063,12 @@ AuthorityFactory::createFromCRSCodesWithIntermediates(
           joinArea +
           "WHERE v1.target_crs_auth_name = ? AND v1.target_crs_code = ? "
           "AND v2.target_crs_auth_name = ? AND v2.target_crs_code = ? ";
+    if (allowedIntermediateObjectType == ObjectType::GEOGRAPHIC_CRS) {
+        sql += "AND EXISTS(SELECT 1 FROM geodetic_crs x WHERE "
+               "x.auth_name = v1.source_crs_auth_name AND "
+               "x.code = v1.source_crs_code AND "
+               "x.type IN ('geographic 2D', 'geographic 3D')) ";
+    }
     intermediateWhere =
         buildIntermediateWhere(intermediateCRSAuthCodes, "source", "source");
     res = d->run(sql + additionalWhere + intermediateWhere + orderBy, params);
@@ -3803,6 +4110,12 @@ AuthorityFactory::createFromCRSCodesWithIntermediates(
           joinArea +
           "WHERE v1.target_crs_auth_name = ? AND v1.target_crs_code = ? "
           "AND v2.source_crs_auth_name = ? AND v2.source_crs_code = ? ";
+    if (allowedIntermediateObjectType == ObjectType::GEOGRAPHIC_CRS) {
+        sql += "AND EXISTS(SELECT 1 FROM geodetic_crs x WHERE "
+               "x.auth_name = v1.source_crs_auth_name AND "
+               "x.code = v1.source_crs_code AND "
+               "x.type IN ('geographic 2D', 'geographic 3D')) ";
+    }
     intermediateWhere =
         buildIntermediateWhere(intermediateCRSAuthCodes, "source", "target");
     res = d->run(sql + additionalWhere + intermediateWhere + orderBy, params);
@@ -3840,7 +4153,7 @@ AuthorityFactory::createFromCRSCodesWithIntermediates(
 
     std::vector<operation::CoordinateOperationNNPtr> list;
     for (const auto &op : listTmp) {
-        if (!d->rejectOpDueToMissingGrid(op, discardIfMissingGrid)) {
+        if (!discardIfMissingGrid || !d->rejectOpDueToMissingGrid(op)) {
             list.emplace_back(op);
         }
     }
@@ -4239,15 +4552,18 @@ AuthorityFactory::createObjectsFromName(
     }
 
     std::string sql(
-        "SELECT table_name, auth_name, code, name FROM object_view WHERE "
-        "deprecated = ? AND ");
-    ListOfParams params{deprecated ? 1.0 : 0.0};
+        "SELECT table_name, auth_name, code, name, deprecated FROM object_view "
+        "WHERE ");
+    if (deprecated) {
+        sql += "deprecated = 1 AND ";
+    }
+    ListOfParams params;
     if (!approximateMatch) {
         sql += "name LIKE ? AND ";
         params.push_back(searchedNameWithoutDeprecated);
     }
     if (d->hasAuthorityRestriction()) {
-        sql += " auth_name = ? AND ";
+        sql += "auth_name = ? AND ";
         params.emplace_back(d->authority());
     }
 
@@ -4352,7 +4668,7 @@ AuthorityFactory::createObjectsFromName(
             sql += ')';
         }
     }
-    sql += " ORDER BY length(name), name";
+    sql += " ORDER BY deprecated, length(name), name";
     if (limitResultCount > 0 &&
         limitResultCount <
             static_cast<size_t>(std::numeric_limits<int>::max()) &&
@@ -4374,9 +4690,13 @@ AuthorityFactory::createObjectsFromName(
             auto sqlRes = d->run(sql, params);
             for (const auto &row : sqlRes) {
                 const auto &name = row[3];
+                const auto &deprecatedStr = row[4];
                 const auto canonicalizedName(
                     metadata::Identifier::canonicalizeName(name));
-                mapCanonicalizeGRFName[canonicalizedName].push_back(row);
+                auto &v = mapCanonicalizeGRFName[canonicalizedName];
+                if (deprecatedStr == "0" || v.empty() || v.front()[4] == "1") {
+                    v.push_back(row);
+                }
             }
         }
         auto iter = mapCanonicalizeGRFName.find(canonicalizedSearchedName);
@@ -4424,6 +4744,8 @@ AuthorityFactory::createObjectsFromName(
         }
     } else {
         auto sqlRes = d->run(sql, params);
+        bool isFirst = true;
+        bool firstIsDeprecated = false;
         for (const auto &row : sqlRes) {
             const auto &name = row[3];
             if (approximateMatch) {
@@ -4443,6 +4765,14 @@ AuthorityFactory::createObjectsFromName(
             const auto &table_name = row[0];
             const auto &auth_name = row[1];
             const auto &code = row[2];
+            const auto &deprecatedStr = row[4];
+            if (isFirst) {
+                firstIsDeprecated = deprecatedStr == "1";
+                isFirst = false;
+            }
+            if (deprecatedStr == "1" && !res.empty() && !firstIsDeprecated) {
+                break;
+            }
             auto factory = d->createFactory(auth_name);
             if (table_name == "prime_meridian") {
                 res.emplace_back(factory->createPrimeMeridian(code));
@@ -4477,12 +4807,6 @@ AuthorityFactory::createObjectsFromName(
         }
     }
 
-    if (res.empty() && !deprecated) {
-        return createObjectsFromName(searchedName + " (deprecated)",
-                                     allowedObjectTypes, approximateMatch,
-                                     limitResultCount);
-    }
-
     auto sortLambda = [](const common::IdentifiedObjectNNPtr &a,
                          const common::IdentifiedObjectNNPtr &b) {
         const auto &aName = a->nameStr();