[Feature][PostgreSQL] Compile expression functions

qgis · Jan 2, 2017 · 735b1c3 · 735b1c3
1 parent cd970f2
commit 735b1c3
Show file tree

Hide file tree

Showing 5 changed files with 228 additions and 2 deletions.
diff --git a/src/core/qgssqlexpressioncompiler.cpp b/src/core/qgssqlexpressioncompiler.cpp
@@ -319,13 +319,60 @@ QgsSqlExpressionCompiler::Result QgsSqlExpressionCompiler::compileNode( const Qg
     }
 
     case QgsExpression::ntFunction:
+    {
+      const QgsExpression::NodeFunction* n = static_cast<const QgsExpression::NodeFunction*>( node );
+      QgsExpression::Function* fd = QgsExpression::Functions()[n->fnIndex()];
+
+      // get sql function to compile node expression
+      QString nd = sqlFunctionFromFunctionName( fd->name() );
+      // if no sql function the node can't be compiled
+      if ( nd.isEmpty() )
+        return Fail;
+
+      // compile arguments
+      QStringList args;
+      Result inResult = Complete;
+      Q_FOREACH ( const QgsExpression::Node* ln, n->args()->list() )
+      {
+        QString s;
+        Result r = compileNode( ln, s );
+        if ( r == Complete || r == Partial )
+        {
+          args << s;
+          if ( r == Partial )
+            inResult = Partial;
+        }
+        else
+          return r;
+      }
+
+      // update arguments to be adapted to SQL function
+      args = sqlArgumentsFromFunctionName( fd->name(), args );
+
+      // build result
+      result = QStringLiteral( "%1(%2)" ).arg( nd, args.join( ',' ) );
+      return inResult == Partial ? Partial : Complete;
+    }
+
     case QgsExpression::ntCondition:
       break;
   }
 
   return Fail;
 }
 
+QString QgsSqlExpressionCompiler::sqlFunctionFromFunctionName( const QString& fnName ) const
+{
+  Q_UNUSED( fnName );
+  return QString();
+}
+
+QStringList QgsSqlExpressionCompiler::sqlArgumentsFromFunctionName( const QString& fnName, const QStringList& fnArgs ) const
+{
+  Q_UNUSED( fnName );
+  return QStringList( fnArgs );
+}
+
 bool QgsSqlExpressionCompiler::nodeIsNullLiteral( const QgsExpression::Node* node ) const
 {
   if ( node->nodeType() != QgsExpression::ntLiteral )

diff --git a/src/core/qgssqlexpressioncompiler.h b/src/core/qgssqlexpressioncompiler.h
@@ -93,6 +93,21 @@ class CORE_EXPORT QgsSqlExpressionCompiler
      */
     virtual Result compileNode( const QgsExpression::Node* node, QString& str );
 
+    /** Return the SQL function for the expression function.
+     * Derived classes should override this to help compile functions
+     * @param fnName expression function name
+     * @returns the SQL function name
+     */
+    virtual QString sqlFunctionFromFunctionName( const QString& fnName ) const;
+
+    /** Return the Arguments for SQL function for the expression function.
+     * Derived classes should override this to help compile functions
+     * @param fnName expression function name
+     * @param fnArgs arguments from expression
+     * @returns the arguments updated for SQL Function
+     */
+    virtual QStringList sqlArgumentsFromFunctionName( const QString& fnName, const QStringList& fnArgs ) const;
+
     QString mResult;
     QgsFields mFields;
 

diff --git a/src/providers/postgres/qgspostgresexpressioncompiler.cpp b/src/providers/postgres/qgspostgresexpressioncompiler.cpp
@@ -18,6 +18,12 @@
 
 QgsPostgresExpressionCompiler::QgsPostgresExpressionCompiler( QgsPostgresFeatureSource* source )
     : QgsSqlExpressionCompiler( source->mFields )
+    , mGeometryColumn( source->mGeometryColumn )
+    , mSpatialColType( source->mSpatialColType )
+    , mDetectedGeomType( source->mDetectedGeomType )
+    , mRequestedGeomType( source->mRequestedGeomType )
+    , mRequestedSrid( source->mRequestedSrid )
+    , mDetectedSrid( source->mDetectedSrid )
 {
 }
 
@@ -32,3 +38,150 @@ QString QgsPostgresExpressionCompiler::quotedValue( const QVariant& value, bool&
   return QgsPostgresConn::quotedValue( value );
 }
 
+static const QMap<QString, QString>& functionNamesSqlFunctionsMap()
+{
+  static QMap<QString, QString> fnNames;
+  if ( fnNames.isEmpty() )
+  {
+    fnNames =
+    {
+      { "sqrt", "sqrt" },
+      { "radians", "radians" },
+      { "degrees", "degrees" },
+      { "abs", "abs" },
+      { "cos", "cos" },
+      { "sin", "sin" },
+      { "tan", "tan" },
+      { "acos", "acos" },
+      { "asin", "asin" },
+      { "atan", "atan" },
+      { "atan2", "atan2" },
+      { "exp", "exp" },
+      { "ln", "ln" },
+      { "log", "log" },
+      { "log10", "log" },
+      { "round", "round" },
+      { "floor", "floor" },
+      { "ceil", "ceil" },
+      { "pi", "pi" },
+      // geometry functions
+      //{ "azimuth", "ST_Azimuth" },
+      { "x", "ST_X" },
+      { "y", "ST_Y" },
+      //{ "z", "ST_Z" },
+      //{ "m", "ST_M" },
+      { "x_min", "ST_XMin" },
+      { "y_min", "ST_YMin" },
+      { "x_max", "ST_XMax" },
+      { "y_max", "ST_YMax" },
+      { "area", "ST_Area" },
+      { "perimeter", "ST_Perimeter" },
+      { "relate", "ST_Relate" },
+      { "disjoint", "ST_Disjoint" },
+      { "intersects", "ST_Intersects" },
+      //{ "touches", "ST_Touches" },
+      { "crosses", "ST_Crosses" },
+      { "contains", "ST_Contains" },
+      { "overlaps", "ST_Overlaps" },
+      { "within", "ST_Within" },
+      { "translate", "ST_Translate" },
+      { "buffer", "ST_Buffer" },
+      { "centroid", "ST_Centroid" },
+      { "point_on_surface", "ST_PointOnSurface" },
+      //{ "reverse", "ST_Reverse" },
+      //{ "is_closed", "ST_IsClosed" },
+      //{ "convex_hull", "ST_ConvexHull" },
+      //{ "difference", "ST_Difference" },
+      { "distance", "ST_Distance" },
+      //{ "intersection", "ST_Intersection" },
+      //{ "sym_difference", "ST_SymDifference" },
+      //{ "combine", "ST_Union" },
+      //{ "union", "ST_Union" },
+      { "geom_from_wkt", "ST_GeomFromText" },
+      { "geom_from_gml", "ST_GeomFromGML" }
+    };
+  }
+  return fnNames;
+}
+
+QString QgsPostgresExpressionCompiler::sqlFunctionFromFunctionName( const QString& fnName ) const
+{
+  return functionNamesSqlFunctionsMap().value( fnName, QString() );
+}
+
+QStringList QgsPostgresExpressionCompiler::sqlArgumentsFromFunctionName( const QString& fnName, const QStringList& fnArgs ) const
+{
+  QStringList args( fnArgs );
+  if ( fnName == "geom_from_wkt" )
+  {
+    args << ( mRequestedSrid.isEmpty() ? mDetectedSrid : mRequestedSrid );
+  }
+  else if ( fnName == "geom_from_gml" )
+  {
+    args << ( mRequestedSrid.isEmpty() ? mDetectedSrid : mRequestedSrid );
+  }
+  else if ( fnName == "x" || fnName == "y" )
+  {
+    args = QStringList( QStringLiteral( "ST_Centroid(%1)" ).arg( args[0] ) );
+  }
+  else if ( fnName == "buffer" && args.length() == 2 )
+  {
+    args << "8";
+  }
+  // x and y functions have to be adapted
+  return args;
+}
+
+QgsSqlExpressionCompiler::Result QgsPostgresExpressionCompiler::compileNode( const QgsExpression::Node* node, QString& result )
+{
+  switch ( node->nodeType() )
+  {
+    case QgsExpression::ntFunction:
+    {
+      const QgsExpression::NodeFunction* n = static_cast<const QgsExpression::NodeFunction*>( node );
+
+      QgsExpression::Function* fd = QgsExpression::Functions()[n->fnIndex()];
+      if ( fd->name() == "$geometry" )
+      {
+        result = quotedIdentifier( mGeometryColumn );
+        return Complete;
+      }
+      /*
+       * These methods are tricky
+       * QGIS expression versions of these return ellipsoidal measurements
+       * based on the project settings, and also convert the result to the
+       * units specified in project properties.
+      else if ( fd->name() == "$area" )
+      {
+        result = QStringLiteral( "ST_Area(%1)" ).arg( quotedIdentifier( mGeometryColumn ) );
+        return Complete;
+      }
+      else if ( fd->name() == "$length" )
+      {
+        result = QStringLiteral( "ST_Length(%1)" ).arg( quotedIdentifier( mGeometryColumn ) );
+        return Complete;
+      }
+      else if ( fd->name() == "$perimeter" )
+      {
+        result = QStringLiteral( "ST_Perimeter(%1)" ).arg( quotedIdentifier( mGeometryColumn ) );
+        return Complete;
+      }
+      else if ( fd->name() == "$x" )
+      {
+        result = QStringLiteral( "ST_X(%1)" ).arg( quotedIdentifier( mGeometryColumn ) );
+        return Complete;
+      }
+      else if ( fd->name() == "$y" )
+      {
+        result = QStringLiteral( "ST_Y(%1)" ).arg( quotedIdentifier( mGeometryColumn ) );
+        return Complete;
+      }
+      */
+    }
+
+    default:
+      return QgsSqlExpressionCompiler::compileNode( node, result );
+  }
+
+  return Fail;
+}
diff --git a/src/providers/postgres/qgspostgresexpressioncompiler.h b/src/providers/postgres/qgspostgresexpressioncompiler.h
@@ -18,6 +18,7 @@
 
 #include "qgssqlexpressioncompiler.h"
 #include "qgsexpression.h"
+#include "qgspostgresconn.h"
 #include "qgspostgresfeatureiterator.h"
 
 class QgsPostgresExpressionCompiler : public QgsSqlExpressionCompiler
@@ -30,6 +31,16 @@ class QgsPostgresExpressionCompiler : public QgsSqlExpressionCompiler
 
     virtual QString quotedIdentifier( const QString& identifier ) override;
     virtual QString quotedValue( const QVariant& value, bool& ok ) override;
+    virtual Result compileNode( const QgsExpression::Node* node, QString& str ) override;
+    virtual QString sqlFunctionFromFunctionName( const QString& fnName ) const override;
+    virtual QStringList sqlArgumentsFromFunctionName( const QString& fnName, const QStringList& fnArgs ) const override;
+
+    QString mGeometryColumn;
+    QgsPostgresGeometryColumnType mSpatialColType;
+    QgsWkbTypes::Type mDetectedGeomType;
+    QgsWkbTypes::Type mRequestedGeomType;
+    QString mRequestedSrid;
+    QString mDetectedSrid;
 };
 
 #endif // QGSPOSTGRESEXPRESSIONCOMPILER_H
diff --git a/tests/src/python/test_provider_postgres.py b/tests/src/python/test_provider_postgres.py
@@ -94,7 +94,7 @@ def disableCompiler(self):
         QSettings().setValue('/qgis/compileExpressions', False)
 
     def uncompiledFilters(self):
-        return set(['intersects($geometry,geom_from_wkt( \'Polygon ((-72.2 66.1, -65.2 66.1, -65.2 72.0, -72.2 72.0, -72.2 66.1))\'))'])
+        return set([])
 
     def partiallyCompiledFilters(self):
         return set([])
@@ -664,7 +664,7 @@ def disableCompiler(self):
         QSettings().setValue('/qgis/compileExpressions', False)
 
     def uncompiledFilters(self):
-        return set(['intersects($geometry,geom_from_wkt( \'Polygon ((-72.2 66.1, -65.2 66.1, -65.2 72.0, -72.2 72.0, -72.2 66.1))\'))'])
+        return set([])
 
     def partiallyCompiledFilters(self):
         return set([])