Implement distance within for postgres iterator

Note: this could be optimised by converting automatically to a ST_DWithin(...) clause!
qgis · Aug 5, 2021 · 85f67fd · 85f67fd
1 parent a592eb3
commit 85f67fd
Show file tree

Hide file tree

Showing 3 changed files with 102 additions and 64 deletions.
diff --git a/src/providers/postgres/qgspostgresconn.cpp b/src/providers/postgres/qgspostgresconn.cpp
@@ -1060,9 +1060,9 @@ QString QgsPostgresConn::postgisVersion() const
     result = PQexec( QStringLiteral( "SELECT postgis_geos_version(), postgis_proj_version()" ) );
     mGeosAvailable = result.PQntuples() == 1 && !result.PQgetisnull( 0, 0 );
     mProjAvailable = result.PQntuples() == 1 && !result.PQgetisnull( 0, 1 );
-    QgsDebugMsg( QStringLiteral( "geos:%1 proj:%2" )
-                 .arg( mGeosAvailable ? result.PQgetvalue( 0, 0 ) : "none" )
-                 .arg( mProjAvailable ? result.PQgetvalue( 0, 1 ) : "none" ) );
+    QgsDebugMsgLevel( QStringLiteral( "geos:%1 proj:%2" )
+                      .arg( mGeosAvailable ? result.PQgetvalue( 0, 0 ) : "none" )
+                      .arg( mProjAvailable ? result.PQgetvalue( 0, 1 ) : "none" ), 2 );
   }
   else
   {

diff --git a/src/providers/postgres/qgspostgresfeatureiterator.cpp b/src/providers/postgres/qgspostgresfeatureiterator.cpp
@@ -22,6 +22,7 @@
 #include "qgsmessagelog.h"
 #include "qgssettings.h"
 #include "qgsexception.h"
+#include "qgsgeometryengine.h"
 
 #include <QElapsedTimer>
 #include <QObject>
@@ -70,11 +71,32 @@ QgsPostgresFeatureIterator::QgsPostgresFeatureIterator( QgsPostgresFeatureSource
     return;
   }
 
+  bool limitAtProvider = ( mRequest.limit() >= 0 );
+
+  // 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:
+      if ( !mRequest.referenceGeometry().isEmpty() )
+      {
+        mDistanceWithinGeom = mRequest.referenceGeometry();
+        mDistanceWithinEngine.reset( QgsGeometry::createGeometryEngine( mDistanceWithinGeom.constGet() ) );
+        mDistanceWithinEngine->prepareGeometry();
+        limitAtProvider = false;
+      }
+      break;
+  }
+
   mCursorName = mConn->uniqueCursorName();
   QString whereClause;
 
-  bool limitAtProvider = ( mRequest.limit() >= 0 );
-
   bool useFallbackWhereClause = false;
   QString fallbackWhereClause;
 
@@ -246,79 +268,88 @@ bool QgsPostgresFeatureIterator::fetchFeature( QgsFeature &feature )
   if ( mClosed )
     return false;
 
-  if ( mFeatureQueue.empty() && !mLastFetch )
+  while ( true )
   {
+    if ( mFeatureQueue.empty() && !mLastFetch )
+    {
 #if 0 //disabled dynamic queue size
-    QElapsedTimer timer;
-    timer.start();
+      QElapsedTimer timer;
+      timer.start();
 #endif
 
-    QString fetch = QStringLiteral( "FETCH FORWARD %1 FROM %2" ).arg( mFeatureQueueSize ).arg( mCursorName );
-    QgsDebugMsgLevel( QStringLiteral( "fetching %1 features." ).arg( mFeatureQueueSize ), 4 );
-
-    lock();
-    if ( mConn->PQsendQuery( fetch ) == 0 ) // fetch features asynchronously
-    {
-      QgsMessageLog::logMessage( QObject::tr( "Fetching from cursor %1 failed\nDatabase error: %2" ).arg( mCursorName, mConn->PQerrorMessage() ), QObject::tr( "PostGIS" ) );
-    }
-
-    QgsPostgresResult queryResult;
-    for ( ;; )
-    {
-      queryResult = mConn->PQgetResult();
-      if ( !queryResult.result() )
-        break;
+      QString fetch = QStringLiteral( "FETCH FORWARD %1 FROM %2" ).arg( mFeatureQueueSize ).arg( mCursorName );
+      QgsDebugMsgLevel( QStringLiteral( "fetching %1 features." ).arg( mFeatureQueueSize ), 4 );
 
-      if ( queryResult.PQresultStatus() != PGRES_TUPLES_OK )
+      lock();
+      if ( mConn->PQsendQuery( fetch ) == 0 ) // fetch features asynchronously
       {
         QgsMessageLog::logMessage( QObject::tr( "Fetching from cursor %1 failed\nDatabase error: %2" ).arg( mCursorName, mConn->PQerrorMessage() ), QObject::tr( "PostGIS" ) );
-        break;
       }
 
-      int rows = queryResult.PQntuples();
-      if ( rows == 0 )
-        continue;
+      QgsPostgresResult queryResult;
+      for ( ;; )
+      {
+        queryResult = mConn->PQgetResult();
+        if ( !queryResult.result() )
+          break;
 
-      mLastFetch = rows < mFeatureQueueSize;
+        if ( queryResult.PQresultStatus() != PGRES_TUPLES_OK )
+        {
+          QgsMessageLog::logMessage( QObject::tr( "Fetching from cursor %1 failed\nDatabase error: %2" ).arg( mCursorName, mConn->PQerrorMessage() ), QObject::tr( "PostGIS" ) );
+          break;
+        }
 
-      for ( int row = 0; row < rows; row++ )
-      {
-        mFeatureQueue.enqueue( QgsFeature() );
-        getFeature( queryResult, row, mFeatureQueue.back() );
-      } // for each row in queue
-    }
-    unlock();
+        int rows = queryResult.PQntuples();
+        if ( rows == 0 )
+          continue;
+
+        mLastFetch = rows < mFeatureQueueSize;
+
+        for ( int row = 0; row < rows; row++ )
+        {
+          mFeatureQueue.enqueue( QgsFeature() );
+          getFeature( queryResult, row, mFeatureQueue.back() );
+        } // for each row in queue
+      }
+      unlock();
 
 #if 0 //disabled dynamic queue size
-    if ( timer.elapsed() > 500 && mFeatureQueueSize > 1 )
-    {
-      mFeatureQueueSize /= 2;
+      if ( timer.elapsed() > 500 && mFeatureQueueSize > 1 )
+      {
+        mFeatureQueueSize /= 2;
+      }
+      else if ( timer.elapsed() < 50 && mFeatureQueueSize < 10000 )
+      {
+        mFeatureQueueSize *= 2;
+      }
+#endif
     }
-    else if ( timer.elapsed() < 50 && mFeatureQueueSize < 10000 )
+
+    if ( mFeatureQueue.empty() )
     {
-      mFeatureQueueSize *= 2;
+      mLastFetch = true;
+      break;
     }
-#endif
-  }
 
-  if ( mFeatureQueue.empty() )
-  {
-    QgsDebugMsg( QStringLiteral( "Finished after %1 features" ).arg( mFetched ) );
-    close();
+    feature = mFeatureQueue.dequeue();
+    mFetched++;
 
-    mSource->mShared->ensureFeaturesCountedAtLeast( mFetched );
+    geometryToDestinationCrs( feature, mTransform );
+    if ( mDistanceWithinEngine && mDistanceWithinEngine->distance( feature.geometry().constGet() ) > mRequest.distanceWithin() )
+    {
+      continue;
+    }
 
-    return false;
+    feature.setValid( true );
+    feature.setFields( mSource->mFields ); // allow name-based attribute lookups
+    return true;
   }
+  QgsDebugMsgLevel( QStringLiteral( "Finished after %1 features" ).arg( mFetched ), 2 );
+  close();
 
-  feature = mFeatureQueue.dequeue();
-  mFetched++;
-
-  feature.setValid( true );
-  feature.setFields( mSource->mFields ); // allow name-based attribute lookups
-  geometryToDestinationCrs( feature, mTransform );
+  mSource->mShared->ensureFeaturesCountedAtLeast( mFetched );
 
-  return true;
+  return false;
 }
 
 bool QgsPostgresFeatureIterator::nextFeatureFilterExpression( QgsFeature &f )
@@ -552,7 +583,7 @@ QString QgsPostgresFeatureIterator::whereClauseRect()
 
   if ( mSource->mRequestedGeomType != QgsWkbTypes::Unknown && mSource->mRequestedGeomType != mSource->mDetectedGeomType )
   {
-    whereClause += QStringLiteral( " AND %1" ).arg( QgsPostgresConn::postgisTypeFilter( mSource->mGeometryColumn, ( QgsWkbTypes::Type )mSource->mRequestedGeomType, castToGeometry ) );
+    whereClause += QStringLiteral( " AND %1" ).arg( QgsPostgresConn::postgisTypeFilter( mSource->mGeometryColumn, mSource->mRequestedGeomType, castToGeometry ) );
   }
 
   QgsDebugMsgLevel( QStringLiteral( "whereClause = %1" ).arg( whereClause ), 4 );
@@ -563,7 +594,10 @@ QString QgsPostgresFeatureIterator::whereClauseRect()
 
 bool QgsPostgresFeatureIterator::declareCursor( const QString &whereClause, long limit, bool closeOnFail, const QString &orderBy )
 {
-  mFetchGeometry = ( !( mRequest.flags() & QgsFeatureRequest::NoGeometry ) || mFilterRequiresGeometry ) && !mSource->mGeometryColumn.isNull();
+  mFetchGeometry = ( !( mRequest.flags() & QgsFeatureRequest::NoGeometry )
+                     || mFilterRequiresGeometry
+                     || mRequest.spatialFilterType() == Qgis::SpatialFilterType::DistanceWithin )
+                   && !mSource->mGeometryColumn.isNull();
 #if 0
   // TODO: check that all field indexes exist
   if ( !hasAllFields )
@@ -617,7 +651,7 @@ bool QgsPostgresFeatureIterator::declareCursor( const QString &whereClause, long
             // For postgis >= 2.2 Use ST_RemoveRepeatedPoints instead
             // Do it only if threshold is <= 1 pixel to avoid holes in adjacent polygons
             // We should perhaps use it always for Linestrings, even if threshold > 1 ?
-            if ( mRequest.simplifyMethod().threshold() <= 1.0 )
+            if ( mRequest.simplifyMethod().threshold() <= 1.0f )
             {
               simplifyPostgisMethod = QStringLiteral( "st_removerepeatedpoints" );
               postSimplification = true; // Ask to apply a post-filtering simplification
@@ -637,10 +671,10 @@ bool QgsPostgresFeatureIterator::declareCursor( const QString &whereClause, long
           simplifyPostgisMethod = QStringLiteral( "st_simplifypreservetopology" );
         }
       }
-      QgsDebugMsg(
-        QString( "PostGIS Server side simplification : threshold %1 pixels - method %2" )
+      QgsDebugMsgLevel(
+        QStringLiteral( "PostGIS Server side simplification : threshold %1 pixels - method %2" )
         .arg( mRequest.simplifyMethod().threshold() )
-        .arg( simplifyPostgisMethod )
+        .arg( simplifyPostgisMethod ), 3
       );
 
       geom = QStringLiteral( "%1(%2,%3)" )
@@ -750,7 +784,7 @@ bool QgsPostgresFeatureIterator::getFeature( QgsPostgresResult &queryResult, int
       memcpy( &wkbType, featureGeom + 1, sizeof( wkbType ) );
       QgsWkbTypes::Type newType = QgsPostgresConn::wkbTypeFromOgcWkbType( wkbType );
 
-      if ( ( unsigned int )newType != wkbType )
+      if ( static_cast< unsigned int >( newType ) != wkbType )
       {
         // overwrite type
         unsigned int n = newType;
@@ -846,6 +880,7 @@ bool QgsPostgresFeatureIterator::getFeature( QgsPostgresResult &queryResult, int
     case PktFidMap:
     {
       QVariantList primaryKeyVals;
+      primaryKeyVals.reserve( mSource->mPrimaryKeyAttrs.size() );
 
       for ( int idx : std::as_const( mSource->mPrimaryKeyAttrs ) )
       {
@@ -874,7 +909,7 @@ bool QgsPostgresFeatureIterator::getFeature( QgsPostgresResult &queryResult, int
     break;
 
     case PktUnknown:
-      Q_ASSERT( !"FAILURE: cannot get feature with unknown primary key" );
+      Q_ASSERT_X( false, "QgsPostgresFeatureIterator::getFeature", "FAILURE: cannot get feature with unknown primary key" );
       return false;
   }
 

diff --git a/src/providers/postgres/qgspostgresfeatureiterator.h b/src/providers/postgres/qgspostgresfeatureiterator.h
@@ -127,6 +127,9 @@ class QgsPostgresFeatureIterator final: public QgsAbstractFeatureIteratorFromSou
 
     QgsCoordinateTransform mTransform;
     QgsRectangle mFilterRect;
+    QgsGeometry mDistanceWithinGeom;
+    std::unique_ptr< QgsGeometryEngine > mDistanceWithinEngine;
+
 };
 
 #endif // QGSPOSTGRESFEATUREITERATOR_H