[postgres] Handoff distance within queries to backend to use ST_DWithin

wherever it's safe to do so
qgis · Aug 5, 2021 · d01fb8f · d01fb8f
1 parent 26010fb
commit d01fb8f
Showing 1 changed file with 30 additions and 19 deletions.
diff --git a/src/providers/postgres/qgspostgresfeatureiterator.cpp b/src/providers/postgres/qgspostgresfeatureiterator.cpp
@@ -73,38 +73,49 @@ QgsPostgresFeatureIterator::QgsPostgresFeatureIterator( QgsPostgresFeatureSource
 
   bool limitAtProvider = ( mRequest.limit() >= 0 );
 
+  mCursorName = mConn->uniqueCursorName();
+  QString whereClause;
+
+  bool useFallbackWhereClause = false;
+  QString fallbackWhereClause;
+
+  if ( !mFilterRect.isNull() && !mSource->mGeometryColumn.isNull() )
+  {
+    whereClause = whereClauseRect();
+  }
+
   // prepare spatial filter geometries for optimal speed
-  // TODO -- if mTransform is short circuited (i.e. requesting features in same CRS as database layer),
-  // then we should avoid the local distance check and instead add a ST_DWithin(...) clause to the
-  // SQL query
   switch ( mRequest.spatialFilterType() )
   {
     case Qgis::SpatialFilterType::NoFilter:
     case Qgis::SpatialFilterType::BoundingBox:
       break;
 
     case Qgis::SpatialFilterType::DistanceWithin:
+      // we only need to test the distance locally if we are transforming features on QGIS side, otherwise
+      // we use ST_DWithin on the postgres backend instead
       if ( !mRequest.referenceGeometry().isEmpty() )
       {
-        mDistanceWithinGeom = mRequest.referenceGeometry();
-        mDistanceWithinEngine.reset( QgsGeometry::createGeometryEngine( mDistanceWithinGeom.constGet() ) );
-        mDistanceWithinEngine->prepareGeometry();
-        limitAtProvider = false;
+        if ( !mTransform.isShortCircuited() || mSource->mSpatialColType != SctGeometry )
+        {
+          mDistanceWithinGeom = mRequest.referenceGeometry();
+          mDistanceWithinEngine.reset( QgsGeometry::createGeometryEngine( mDistanceWithinGeom.constGet() ) );
+          mDistanceWithinEngine->prepareGeometry();
+          limitAtProvider = false;
+        }
+        else
+        {
+          // we can safely hand this off to the backend to evaluate, so that it will nicely handle it within the query planner!
+          whereClause = QgsPostgresUtils::andWhereClauses( whereClause, QStringLiteral( "ST_DWithin(%1,ST_GeomFromText('%2',%3),%4)" ).arg(
+                          QgsPostgresConn::quotedIdentifier( mSource->mGeometryColumn ),
+                          mRequest.referenceGeometry().asWkt(),
+                          mSource->mRequestedSrid.isEmpty() ? mSource->mDetectedSrid : mSource->mRequestedSrid )
+                        .arg( mRequest.distanceWithin() ) );
+        }
       }
       break;
   }
 
-  mCursorName = mConn->uniqueCursorName();
-  QString whereClause;
-
-  bool useFallbackWhereClause = false;
-  QString fallbackWhereClause;
-
-  if ( !mFilterRect.isNull() && !mSource->mGeometryColumn.isNull() )
-  {
-    whereClause = whereClauseRect();
-  }
-
   if ( !mSource->mSqlWhereClause.isEmpty() )
   {
     whereClause = QgsPostgresUtils::andWhereClauses( whereClause, '(' + mSource->mSqlWhereClause + ')' );
@@ -596,7 +607,7 @@ bool QgsPostgresFeatureIterator::declareCursor( const QString &whereClause, long
 {
   mFetchGeometry = ( !( mRequest.flags() & QgsFeatureRequest::NoGeometry )
                      || mFilterRequiresGeometry
-                     || mRequest.spatialFilterType() == Qgis::SpatialFilterType::DistanceWithin )
+                     || ( mRequest.spatialFilterType() == Qgis::SpatialFilterType::DistanceWithin && !mTransform.isShortCircuited() ) )
                    && !mSource->mGeometryColumn.isNull();
 #if 0
   // TODO: check that all field indexes exist