Skip to content

Commit

Permalink
[FEATURE] add field calculator functions (implements #3177)
Browse files Browse the repository at this point in the history 
- add support for functions with 2 or three arguments
- add function atan2(y,x)
- add length(string) to determine string length
- add replace(string,from,to) to do string replaces
- add substr(string,from,length) to retrieve substrings
- add (preliminary) online help to field calculator with a listing of operations.


git-svn-id: http://svn.osgeo.org/qgis/trunk/qgis@14533 c8812cc2-4d05-0410-92ff-de0c093fc19c
  • Loading branch information
jef committed Nov 8, 2010
1 parent a0671f1 commit 8fb07b0
Show file tree
Hide file tree
Showing 8 changed files with 170 additions and 36 deletions.
71 changes: 71 additions & 0 deletions resources/context_help/QgsFieldCalculator-en_US
View file
@@ -0,0 +1,71 @@
<h3>Field Calculator</h3>
The field calculator allows you to update fields with expressions.

<h4>Supported Operations</h4>

<table border=1>
<tr>
<th>Operation</th>
<th>Description</th>
</tr>
<tr>
<td>
<tt>column_name</tt><br>
<tt>"column_name"</tt>
</td>
<td>value of field <tt>column_name</tt></td>
</tr>
<tr><td>'string'</td><td>literal string value</td></tr>
<tr><td><i>number</i></td><td>number</td></tr>
<tr><td>NULL</td><td>null value</td></tr>
<tr><td><tt>a</tt> OR <tt>b</tt></td><td><tt>a</tt> or <tt>b</tt> are true.</td></tr>
<tr><td><tt>a</tt> AND <tt>b</tt></td><td><tt>a</tt> and <tt>b</tt> are true.</td></tr>
<tr><td>NOT <tt>a</tt></td><td>inverted boolean value of <tt>a</tt></td></tr>
<tr><td><tt>a</tt> IS NULL</td><td><tt>a</tt> has no value</td></tr>
<tr><td><tt>a</tt> IS NOT NULL</td><td><tt>a</tt> has <tt>a</tt> value</td></tr>
<tr><td><tt>a</tt> IN ( value, [, value] )</td><td><tt>a</tt> is one of the listed values</td></tr>
<tr><td><tt>a</tt> NOT IN ( value, [, value] )</td><td><tt>a</tt> is not one of the listed values</td></tr>
<tr><td><tt>a</tt> = <tt>b</tt></td><td><tt>a</tt> and <tt>b</tt> are equal</td><tr>
<tr>
<td>
<tt>a</tt> != <tt>b</tt><br>
<tt>a</tt> &lt;&gt; <tt>b</tt>
</td>
<td><tt>a</tt> and <tt>b</tt> are not equal</td>
</tr>
<tr><td><tt>a</tt> &lt;= <tt>b</tt></td><td><tt>a</tt> is less or equal <tt>b</tt></td></tr>
<tr><td><tt>a</tt> &gt;= <tt>b</tt></td><td><tt>a</tt> is greater or equal <tt>b</tt></td></tr>
<tr><td><tt>a</tt> &gt; <tt>b</tt></td><td><tt>a</tt> is greater than <tt>b</tt></td></tr>
<tr><td><tt>a</tt> &lt; <tt>b</tt></td><td><tt>a</tt> is less than <tt>b</tt></td></tr>
<tr><td><tt>a</tt> ~ <tt>b</tt></td></td><td><tt>a</tt> matches regular expression <tt>b</tt></td></tr>
<tr><td><tt>a</tt> LIKE <tt>b</tt></td><td><tt>a</tt> is like <tt>b</tt></td></tr>
<tr><td><tt>a</tt> ILIKE <tt>b</tt></td><td><tt>a</tt> is like <tt>b</tt> (case insensitive)</td></tr>
<tr><td>sqrt(<tt>a</tt>)</td><td>square root</td></tr>
<tr><td>sin(<tt>a</tt>)</td><td>sinus of <tt>a</tt></td></tr>
<tr><td>cos(<tt>a</tt>)</td><td>cosinus of <tt>b</tt></td></tr>
<tr><td>tan(<tt>a</tt>)</td><td>tangens of <tt>a</tt></td></tr>
<tr><td>asin(<tt>a</tt>)</td><td>arcussinus of <tt>a</tt></td></tr>
<tr><td>acos(<tt>a</tt>)</td><td>arcuscosinus of <tt>a</tt></td></tr>
<tr><td>atan(<tt>a</tt>)</td><td>arcustangens of <tt>a</tt></td></tr>
<tr><td>to int(<tt>a</tt>)</td><td>convert string <tt>a</tt> to integer</td></tr>
<tr><td>to real(<tt>a</tt>)</td><td>convert string <tt>a</tt> to real</td></tr>
<tr><td>to string(<tt>a</tt>)</td><td>convert number <tt>a</tt> to string</td></tr>
<tr><td>lower(<tt>a</tt>)</td><td>convert string <tt>a</tt> to lower case</td></tr>
<tr><td>upper(<tt>a</tt>)</td><td>convert string <tt>a</tt> to upper case</td></tr>
<tr><td>length(<tt>a</tt>)</td><td>length of string <tt>a</tt></td></tr>
<tr><td>atan2(y,x)</td><td>arcustangens of y/x using the signs of the two arguments to determine the quadrant of the result.</td></tr>
<tr><td>replace(<tt>a</tt>,replacethis,withthat)</td><td>replace replacethis with withthat in string <tt>a</tt></td></td>
<tr><td>substr(<tt>a</tt>,from,len)</td><td>len characters of string <tt>a</tt> starting from from (first character index is 1)</td></td>
<tr><td><tt>a</tt> || <tt>b</tt></td><td>concatenate strings <tt>a</tt> and <tt>b</tt></td></tr>
<tr><td>$rownum</td><td>number current row</td></tr>
<tr><td>$area</td><td>area of polygon</td></tr>
<tr><td>$length</td><td>area of line</td></tr>
<tr><td>$id</td><td>feature id</td></tr>
<tr><td><tt>a</tt> ^ <tt>b</tt></td><td><tt>a</tt> raised to the power of <tt>b</tt></td></tr>
<tr><td><tt>a</tt> * <tt>b</tt></td><td><tt>a</tt> multiplied by <tt>b</tt></td></tr>
<tr><td><tt>a</tt> * <tt>b</tt></td><td><tt>a</tt> divided by <tt>b</tt></td></tr>
<tr><td><tt>a</tt> + <tt>b</tt></td><td><tt>a</tt> plus <tt>b</tt></td></tr>
<tr><td><tt>a</tt> - <tt>b</tt></td><td><tt>a</tt> minus <tt>b</tt></td></tr>
<tr><td>+<tt>a</tt></td><td>positive sign</td></tr>
<tr><td>-<tt>a</tt></td><td>negative value of <tt>a</tt></td></tr>
</table>
2 changes: 0 additions & 2 deletions src/app/qgsfieldcalculator.cpp
View file
Expand Up @@ -36,8 +36,6 @@ QgsFieldCalculator::QgsFieldCalculator( QgsVectorLayer* vl ): QDialog(), mVector
mOuputFieldWidthSpinBox->setValue( 10 );
mOutputFieldPrecisionSpinBox->setValue( 3 );



//disable ok button until there is text for output field and expression
mButtonBox->button( QDialogButtonBox::Ok )->setEnabled( false );

Expand Down
3 changes: 3 additions & 0 deletions src/app/qgsfieldcalculator.h
View file
Expand Up @@ -17,6 +17,7 @@
#define QGSFIELDCALCULATOR_H

#include "ui_qgsfieldcalculatorbase.h"
#include "qgscontexthelp.h"

class QgsVectorLayer;

Expand Down Expand Up @@ -61,6 +62,8 @@ class QgsFieldCalculator: public QDialog, private Ui::QgsFieldCalculatorBase
void on_mExpressionTextEdit_textChanged();
void on_mOutputFieldTypeComboBox_activated( int index );

void on_mButtonBox_helpRequested() { QgsContextHelp::run( metaObject()->className() ); }

private:
//default constructor forbidden
QgsFieldCalculator();
Expand Down
30 changes: 18 additions & 12 deletions src/core/qgssearchstringlexer.ll
View file
Expand Up @@ -83,18 +83,24 @@ string "'"{str_char}*"'"
"LIKE" { yylval.op = QgsSearchTreeNode::opLike; return COMPARISON; }
"ILIKE" { yylval.op = QgsSearchTreeNode::opILike; return COMPARISON; }
"sqrt" { yylval.op = QgsSearchTreeNode::opSQRT; return FUNCTION;}
"sin" { yylval.op = QgsSearchTreeNode::opSIN; return FUNCTION;}
"cos" { yylval.op = QgsSearchTreeNode::opCOS; return FUNCTION;}
"tan" { yylval.op = QgsSearchTreeNode::opTAN; return FUNCTION;}
"asin" { yylval.op = QgsSearchTreeNode::opASIN; return FUNCTION;}
"acos" { yylval.op = QgsSearchTreeNode::opACOS; return FUNCTION;}
"atan" { yylval.op = QgsSearchTreeNode::opATAN; return FUNCTION;}
"to int" { yylval.op = QgsSearchTreeNode::opTOINT; return FUNCTION;}
"to real" { yylval.op = QgsSearchTreeNode::opTOREAL; return FUNCTION;}
"to string" { yylval.op = QgsSearchTreeNode::opTOSTRING; return FUNCTION;}
"lower" { yylval.op = QgsSearchTreeNode::opLOWER; return FUNCTION;}
"upper" { yylval.op = QgsSearchTreeNode::opUPPER; return FUNCTION;}
"sqrt" { yylval.op = QgsSearchTreeNode::opSQRT; return FUNCTION1;}
"sin" { yylval.op = QgsSearchTreeNode::opSIN; return FUNCTION1;}
"cos" { yylval.op = QgsSearchTreeNode::opCOS; return FUNCTION1;}
"tan" { yylval.op = QgsSearchTreeNode::opTAN; return FUNCTION1;}
"asin" { yylval.op = QgsSearchTreeNode::opASIN; return FUNCTION1;}
"acos" { yylval.op = QgsSearchTreeNode::opACOS; return FUNCTION1;}
"atan" { yylval.op = QgsSearchTreeNode::opATAN; return FUNCTION1;}
"to int" { yylval.op = QgsSearchTreeNode::opTOINT; return FUNCTION1;}
"to real" { yylval.op = QgsSearchTreeNode::opTOREAL; return FUNCTION1;}
"to string" { yylval.op = QgsSearchTreeNode::opTOSTRING; return FUNCTION1;}
"lower" { yylval.op = QgsSearchTreeNode::opLOWER; return FUNCTION1;}
"upper" { yylval.op = QgsSearchTreeNode::opUPPER; return FUNCTION1;}
"length" { yylval.op = QgsSearchTreeNode::opSTRLEN; return FUNCTION1;}
"atan2" { yylval.op = QgsSearchTreeNode::opATAN2; return FUNCTION2;}
"replace" { yylval.op = QgsSearchTreeNode::opREPLACE; return FUNCTION3;}
"substr" { yylval.op = QgsSearchTreeNode::opSUBSTR; return FUNCTION3;}
"||" { return CONCAT; }
Expand Down
25 changes: 22 additions & 3 deletions src/core/qgssearchstringparser.yy
View file
Expand Up @@ -61,7 +61,9 @@ void addToTmpNodes(QgsSearchTreeNode* node);

%token <number> NUMBER
%token <op> COMPARISON
%token <op> FUNCTION
%token <op> FUNCTION1
%token <op> FUNCTION2
%token <op> FUNCTION3
%token CONCAT
%token IS
%token IN
Expand Down Expand Up @@ -135,11 +137,28 @@ comp_predicate:

scalar_exp_list:
scalar_exp_list ',' scalar_exp { $$ = $1; $1->append($3); joinTmpNodes($1,$1,$3); }
| scalar_exp { $$ = new QgsSearchTreeNode( QgsSearchTreeNode::tNodeList ); $$->append($1); joinTmpNodes($$,$1,0); }
| scalar_exp
{
$$ = new QgsSearchTreeNode( QgsSearchTreeNode::tNodeList );
$$->append($1);
joinTmpNodes($$,$1,0);
}
;

scalar_exp:
FUNCTION '(' scalar_exp ')' { $$ = new QgsSearchTreeNode($1, $3, 0); joinTmpNodes($$, $3, 0);}
FUNCTION1 '(' scalar_exp ')' { $$ = new QgsSearchTreeNode($1, $3, 0); joinTmpNodes($$, $3, 0); }
| FUNCTION2 '(' scalar_exp ',' scalar_exp ')' { $$ = new QgsSearchTreeNode($1, $3, $5); joinTmpNodes($$, $3, $5); }
| FUNCTION3 '(' scalar_exp ',' scalar_exp ',' scalar_exp ')'
{
QgsSearchTreeNode *args = new QgsSearchTreeNode( QgsSearchTreeNode::tNodeList );
args->append($3);
args->append($5);
args->append($7);

$$ = new QgsSearchTreeNode($1, args, 0);
joinTmpNodes($$, $3, $5);
joinTmpNodes($$, $$, $7);
}
| scalar_exp '^' scalar_exp { $$ = new QgsSearchTreeNode(QgsSearchTreeNode::opPOW, $1, $3); joinTmpNodes($$,$1,$3); }
| scalar_exp '*' scalar_exp { $$ = new QgsSearchTreeNode(QgsSearchTreeNode::opMUL, $1, $3); joinTmpNodes($$,$1,$3); }
| scalar_exp '/' scalar_exp { $$ = new QgsSearchTreeNode(QgsSearchTreeNode::opDIV, $1, $3); joinTmpNodes($$,$1,$3); }
Expand Down
69 changes: 51 additions & 18 deletions src/core/qgssearchtreenode.cpp
View file
Expand Up @@ -115,8 +115,10 @@ QgsSearchTreeNode::QgsSearchTreeNode( const QgsSearchTreeNode& node )
else
mRight = NULL;

foreach( QgsSearchTreeNode *lnode, node.mNodeList )
mNodeList.append( new QgsSearchTreeNode( *lnode ) );
foreach( QgsSearchTreeNode * lnode, node.mNodeList )
{
mNodeList.append( new QgsSearchTreeNode( *lnode ) );
}

init();
}
Expand Down Expand Up @@ -209,7 +211,8 @@ QString QgsSearchTreeNode::makeSearchString()
if ( mOp == opSQRT || mOp == opSIN || mOp == opCOS || mOp == opTAN ||
mOp == opASIN || mOp == opACOS || mOp == opATAN ||
mOp == opTOINT || mOp == opTOREAL || mOp == opTOSTRING ||
mOp == opLOWER || mOp == opUPPER )
mOp == opLOWER || mOp == opUPPER || mOp == opSTRLEN ||
mOp == opATAN2 || mOp == opREPLACE || mOp == opSUBSTR )
{
// functions
switch ( mOp )
Expand All @@ -226,6 +229,10 @@ QString QgsSearchTreeNode::makeSearchString()
case opTOSTRING: str += "to string"; break;
case opLOWER: str += "lower"; break;
case opUPPER: str += "upper"; break;
case opATAN2: str += "atan2"; break;
case opSTRLEN: str += "length"; break;
case opREPLACE: str += "replace"; break;
case opSUBSTR: str += "substr"; break;
default: str += "?";
}
// currently all functions take one parameter
Expand Down Expand Up @@ -306,7 +313,7 @@ QString QgsSearchTreeNode::makeSearchString()
else if ( mType == tNodeList )
{
QStringList items;
foreach( QgsSearchTreeNode *node, mNodeList )
foreach( QgsSearchTreeNode * node, mNodeList )
{
items << node->makeSearchString();
}
Expand Down Expand Up @@ -461,7 +468,7 @@ bool QgsSearchTreeNode::checkAgainst( const QgsFieldMap& fields, QgsFeature &f )
return false;
}

foreach( QgsSearchTreeNode *node, mRight->mNodeList )
foreach( QgsSearchTreeNode * node, mRight->mNodeList )
{
if ( !getValue( value2, node, fields, f ) )
{
Expand All @@ -480,7 +487,6 @@ bool QgsSearchTreeNode::checkAgainst( const QgsFieldMap& fields, QgsFeature &f )

return mOp == opNOTIN;
}
break;

case opRegexp:
case opLike:
Expand Down Expand Up @@ -545,7 +551,7 @@ bool QgsSearchTreeNode::getValue( QgsSearchTreeValue& value,
value = node->valueAgainst( fields, f );
if ( value.isError() )
{
switch (( int )value.number() )
switch (( int ) value.number() )
{
case 1:
mError = QObject::tr( "Referenced column wasn't found: %1" ).arg( value.string() );
Expand Down Expand Up @@ -587,7 +593,6 @@ QgsSearchTreeValue QgsSearchTreeNode::valueAgainst( const QgsFieldMap& fields, Q

switch ( mType )
{

case tNumber:
QgsDebugMsgLevel( "number: " + QString::number( mNumber ), 2 );
return QgsSearchTreeValue( mNumber );
Expand Down Expand Up @@ -637,13 +642,23 @@ QgsSearchTreeValue QgsSearchTreeNode::valueAgainst( const QgsFieldMap& fields, Q
// arithmetic operators
case tOperator:
{
QgsSearchTreeValue value1, value2;
QgsSearchTreeValue value1, value2, value3;
if ( mLeft )
{
if ( !getValue( value1, mLeft, fields, f ) ) return value1;
if ( mLeft->type() != tNodeList )
{
if ( !getValue( value1, mLeft, fields, f ) ) return value1;
}
else
{
if ( mLeft->mNodeList.size() > 0 && !getValue( value1, mLeft->mNodeList[0], fields, f ) ) return value1;
if ( mLeft->mNodeList.size() > 1 && !getValue( value2, mLeft->mNodeList[1], fields, f ) ) return value2;
if ( mLeft->mNodeList.size() > 2 && !getValue( value3, mLeft->mNodeList[2], fields, f ) ) return value3;
}
}
if ( mRight )
{
Q_ASSERT( !mLeft || mLeft->type() != tNodeList );
if ( !getValue( value2, mRight, fields, f ) ) return value2;
}

Expand Down Expand Up @@ -716,6 +731,23 @@ QgsSearchTreeValue QgsSearchTreeNode::valueAgainst( const QgsFieldMap& fields, Q
}
}

// string operations
switch ( mOp )
{
case opLOWER:
return QgsSearchTreeValue( value1.string().toLower() );
case opUPPER:
return QgsSearchTreeValue( value1.string().toUpper() );
case opSTRLEN:
return QgsSearchTreeValue( value1.string().length() );
case opREPLACE:
return QgsSearchTreeValue( value1.string().replace( value2.string(), value3.string() ) );
case opSUBSTR:
return QgsSearchTreeValue( value1.string().mid( value2.number() - 1, value3.number() ) );
default:
break;
}

// for other operators, convert strings to numbers if needed
double val1, val2;
if ( value1.isNumeric() )
Expand All @@ -740,8 +772,6 @@ QgsSearchTreeValue QgsSearchTreeNode::valueAgainst( const QgsFieldMap& fields, Q
return QgsSearchTreeValue( 2, "" ); // division by zero
else
return QgsSearchTreeValue( val1 / val2 );
default:
return QgsSearchTreeValue( 3, QString::number( mOp ) ); // unknown operator
case opPOW:
if (( val1 == 0 && val2 < 0 ) || ( val2 < 0 && ( val2 - floor( val2 ) ) > 0 ) )
{
Expand All @@ -762,16 +792,17 @@ QgsSearchTreeValue QgsSearchTreeNode::valueAgainst( const QgsFieldMap& fields, Q
return QgsSearchTreeValue( acos( val1 ) );
case opATAN:
return QgsSearchTreeValue( atan( val1 ) );
case opATAN2:
return QgsSearchTreeValue( atan2( val1, val2 ) );
case opTOINT:
return QgsSearchTreeValue( int( val1 ) );
case opTOREAL:
return QgsSearchTreeValue( val1 );
case opTOSTRING:
return QgsSearchTreeValue( QString::number( val1 ) );
case opLOWER:
return QgsSearchTreeValue( value1.string().toLower() );
case opUPPER:
return QgsSearchTreeValue( value1.string().toUpper() );

default:
return QgsSearchTreeValue( 3, QString::number( mOp ) ); // unknown operator
}
}

Expand Down Expand Up @@ -806,8 +837,10 @@ void QgsSearchTreeNode::append( QgsSearchTreeNode *node )

void QgsSearchTreeNode::append( QList<QgsSearchTreeNode *> nodes )
{
foreach( QgsSearchTreeNode *node, nodes )
mNodeList.append( node );
foreach( QgsSearchTreeNode * node, nodes )
{
mNodeList.append( node );
}
}

int QgsSearchTreeValue::compare( QgsSearchTreeValue& value1, QgsSearchTreeValue& value2, Qt::CaseSensitivity cs )
Expand Down
4 changes: 4 additions & 0 deletions src/core/qgssearchtreenode.h
View file
Expand Up @@ -74,6 +74,7 @@ class CORE_EXPORT QgsSearchTreeNode
opASIN,
opACOS,
opATAN,
opATAN2,

// conversion
opTOINT,
Expand Down Expand Up @@ -106,6 +107,9 @@ class CORE_EXPORT QgsSearchTreeNode
opCONCAT,
opLOWER,
opUPPER,
opREPLACE,
opSTRLEN,
opSUBSTR,

opROWNUM
};
Expand Down
2 changes: 1 addition & 1 deletion src/ui/qgsfieldcalculatorbase.ui
View file
Expand Up @@ -305,7 +305,7 @@
<enum>Qt::Horizontal</enum>
</property>
<property name="standardButtons">
<set>QDialogButtonBox::Cancel|QDialogButtonBox::Ok</set>
<set>QDialogButtonBox::Cancel|QDialogButtonBox::Help|QDialogButtonBox::Ok</set>
</property>
</widget>
</item>
Expand Down

0 comments on commit 8fb07b0

Please sign in to comment.