Filters and Expressions
[Feature Service]
Use a filter to select a subset of the features in a datastore. Filters are used in calls to MgFeatureService::SelectFeatures() and MgFeatureService::SelectAggregate(). There are two types of filters: basic and spatial. Spatial Filter
A spatial filter relates two geometries by way of a spatial operator. A spatial filter is set by a call to MgFeatureQueryOptions::SetSpatialFilter() or MgFeatureAggregateOptions::SetSpatialFilter(). These methods take 3 arguments: a name which identifies a geometry property of a feature in a datastore, a geometry object, and a spatial operation identifier. The effect of the filter is to select features from the datastore whose geometry property is related according to the spatial operator to the geometry object argument. For example, if the spatial operator is MgFeatureSpatialOperations::Within, and the geometry object's WKT representation is POLYGON((0 0, 2 0, 2 2, 0 2, 0 0)), then the effect of the filter is to select those features which have geometries within this polygon.
Basic Filter
Use non-geometry feature property names, data values, and operators to construct a filter string which is passed as an argument to the MgFeatureQueryOption::SetFilter() or MgFeatureAggregateOptions::SetFilter() method. For example, if FEATID is a feature property name, then the filter "FEATID > 20" selects the features whose FEATID has a value greater than 20.
Expressions
Expressions are subcomponents of a basic filter. One expression might constitute the entire filter, or several expressions can be strung together using operators.
Basic Filter Grammar
<Filter> ::= '(' Filter ')'
| <LogicalOperator>
| <SearchCondition>
<LogicalOperator> ::= <BinaryLogicalOperator>
| <UnaryLogicalOperator>
<BinaryLogicalOperator> ::=
<Filter> <BinaryLogicalOperations> <Filter>
<SearchCondition> ::= <InCondition> | <ComparisonCondition> | <GeometricCondition> | <NullCondition>
<InCondition> ::= <Identifier> IN '('
ValueExpressionCollection ')'
<ValueExpressionCollection> ::= <ValueExpression> | <ValueExpressionCollection> ',' <ValueExpression>
<ComparisonCondition> ::=
<Expression> <ComparisonOperations> <Expression>
<GeometricCondition> ::= <SpatialCondition> | <DistanceCondition>
<NullCondition> ::= <Identifier> NULL
<SpatialCondition> ::= <Identifier> <SpatialOperations> <Expression>
<DistanceCondition> ::= <Identifier> <DistanceOperations> <Expression> <distance>
<UnaryLogicalOperator> ::= NOT <Filter>
<BinaryLogicalOperations> ::= AND | OR
<ComparisionOperations> ::= = | <> | > | >= | < | <= | LIKE
<SpatialOperations> ::= CONTAINS | CROSSES | DISJOINT | EQUALS | INTERSECTS | OVERLAPS | TOUCHES | WITHIN | COVEREDBY | INSIDE
<DistanceOperations> ::= BEYOND | WITHINDISTANCE
<distance> ::= BEYOND | WITHINDISTANCE
Expression Grammar
<Expression> ::= '(' Expression ')'
| <UnaryExpression>
| <BinaryExpression>
| <Function>
| <Identifier>
| <ValueExpression>
<BinaryExpression> ::= <Expression> '+' <Expression> | <Expression> '-' <Expression> | <Expression> '*' <Expression> | <Expression> '/' <Expression>
<ValueExpression> ::= <LiteralValue>
<LiteralValue> ::= <GeometryValue> | <DataValue>
<GeometryValue> ::= GEOMFROMTEXT '(' STRING ')'
<DataValue> ::= TRUE | FALSE | DATETIME | DOUBLE | INTEGER | STRING | BLOB | CLOB | NULL
<Function> ::= <Identifier> '(' <ExpressionCollection>
')'
<ExpressionCollection> ::= | <Expression> | <ExpressionCollection> ',' <Expression>
<Identifier> ::= IDENTIFIER
<UnaryExpression> ::= '-' <Expression>
Operator Precedence
The operator precedence from highest to lowest is: Negate NOT
Multiply Divide
Add Subtract
= <> > >= < <=
AND
OR
Filter and Expression Keywords
The following case-insensitive keywords are reserved in the language. That is, they cannot be used as identifier or function names:
AND BEYOND COMPARE CONTAINS COVEREDBY CROSSES DATA DISJOINT DISTANCE EQUALS FALSE GEOMFROMTEXT IN INSIDE INTERSECTS LIKE NOT NULL OR OVERLAPS RELATE SPATIAL TIME TIMESTAMP TOUCHES TRUE WITHIN WITHINDISTANCE
STRING
Strings are literal constants enclosed in single quotes. If you need to include a single quote character inside a string, you can double the character, for example, 'aaa''bbb'.
IDENTIFIER
An identifier can be any alphanumeric sequence of characters other than a keyword. Identifiers can be enclosed in double quotes to show special characters and white space. If you need to include a double quote character inside an identifier, you can double the character, for example "abc""def".
INTEGER
Integers allow only decimal characters with an optional unary minus sign. Unary plus is not supported. If an integer is out of the 32-bit precision range, it is converted to floating point.
DOUBLE
Floating point numbers have a decimal point, can be signed (-), and include an optional exponent (e{[0-9]}).
DATETIME
Date and time are parsed using the standard literal strings:
DATE 'YYYY-MM-DD'
TIME 'HH:MM:SS[.sss]'
TIMESTAMP 'YYYY-MM-DD HH:MM:SS[.sss]'
CLOB/BLOB
These data types are not currently supported. If you need to support binary input, use parameters.
<Function>
Some functions such as ceil, floor, concat, lower, and upper take as an argument the value of a property from a single feature and return a value related to the property value.
Some functions such as avg, count, max, min, stddev, and sum take as an argument the values of a property from multiple features and return a single value related to the values of the group of property arguments.
| Function | Description |
|---|---|
| Double Avg(n) | Average value of n, ignoring nulls |
| Int64 Ceil(Double) | Smallest integer >= Double |
| String Concat(Str1, Str2) | Concatenates Str1 and Str2 |
| Int64 Count(expression) | Number of features where expression is not null |
| Int64 Floor(Double) | Largest integer <= Double |
| String Lower(Str) | Str with all lowercase letters |
| Double Min(expression) | Minimum value of expression |
| Double Max(expression) | Maximum value of expression |
| Double Stddev(n) | Standard deviation of n, ignoring nulls |
| Double Sum(n) | Sum of values of n |
| String Upper(Str) | Str with all uppercase letters |
Examples
PHP and C# sample code for the setting of filters for select operations is presented. The SQL expression equivalent to the PHP and C# code is also presented.
<?php $queryOptions = new MgFeatureQueryOptions(); $stringCollection = new MgStringCollection(); $wktReaderWriter = new MgWktReaderWriter(); ?>
using OSGeo.MapGuide; private MgFeatureQueryOptions queryOptions; private MgStringCollection stringCollection; private MgWktReaderWriter wktReaderWriter; private String featClassName = "SdfFeatureClass"; private MgResourceIdentifier featureSrcResourceId; private MgFeatureService featureService;
stringCollection = new MgStringCollection(); queryOptions = new MgFeatureQueryOptions(); // the feature source has already been installed in the repository featureSrcResourceId = new MgResourceIdentifier("Library://PlatformApiDocTests/SdfFeatureClass.FeatureSource"); wktReaderWriter = new MgWktReaderWriter();
<Identifier> NULL
SometimesNULL is a string property. If you have not given a value to it when inserting certain features and you apply the filter, sometimesNULL NULL, you select those features. If you have given a value to it when inserting other features and you apply the filter, NOT sometimesNULL NULL, you select those other features.
sqlplus> select sometimesnull from featclass where sometimesnull is null;
<?php
$queryOptions->AddFeatureProperty("sometimesNULL");
$queryOptions->SetFilter("sometimesNULL NULL");
$featureReader = $featureService->SelectFeatures($featureSrcResourceId, $featClassName, $queryOptions);
# process $featureReader
$featureReader->Close();
?>
queryOptions.AddFeatureProperty("SometimesNull");
queryOptions.SetFilter("SometimesNull NULL");
featureReader = featureService.SelectFeatures(featureSrcResourceId, featClassName, queryOptions);
// process featureReader
featureReader.Close();
sqlplus> select sometimesnull from featclass where sometimesnull is not null;
<?php
$queryOptions->AddFeatureProperty("sometimesNULL");
$queryOptions->SetFilter("NOT sometimesNULL NULL");
$featureReader = $featureService->SelectFeatures($featureSrcResourceId, $featClassName, $queryOptions);
# process $featureReader
$featureReader->Close();
?>
queryOptions.AddFeatureProperty("SometimesNull");
queryOptions.SetFilter("NOT SometimesNull NULL");
featureReader = featureService.SelectFeatures(featureSrcResourceId, featClassName, queryOptions);
// process featureReader
featureReader.Close();
<Identifier> LIKE <String>
Identifier is the name of a property whose type is MgPropertyType::String. String contains a pattern. A percent character (%) in a pattern matches zero or more characters. An underscore character (_) matches one character.
Description is a string property. There are 2 features with this property in the datastore, and the contents of the two properties are: "POINT XY (1 1)" and "POLYGON XY ((0 0, 2 0, 2 2, 0 2, 0 0))".
The filter, Description LIKE 'POLYGON', returns "POLYGON XY ((0 0, 2 0, 2 2, 0 2, 0 0))", the filter, NOT Description LIKE 'POLYGON', returns "POINT XY (1 1)", and the filter, Description LIKE 'POL_GON', returns "POLYGON XY ((0 0, 2 0, 2 2, 0 2, 0 0))".
sqlplus> select Description from featclass where Description LIKE 'POLYGON';
$queryOptions->AddFeatureProperty("Description");
$queryOptions->SetFilter("Description LIKE 'POLYGON'");
$featureReader = $featureService->SelectFeatures($featureSrcResourceId, $featClassName, $queryOptions);
# process $featureReader
$featureReader->Close();
queryOptions.AddFeatureProperty("Description");
queryOptions.SetFilter("Description LIKE 'POLYGON'");
featureReader = featureService.SelectFeatures(featureSrcResourceId, featClassName, queryOptions);
// process featureReader
featureReader.Close();
sqlplus> select Description from featclass where Description NOT LIKE 'POLYGON';
$queryOptions->AddFeatureProperty("Description");
$queryOptions->SetFilter("NOT Description LIKE 'POLYGON'");
$featureReader = $featureService->SelectFeatures($featureSrcResourceId, $featClassName, $queryOptions);
# process $featureReader
$featureReader->Close();
queryOptions.AddFeatureProperty("Description");
queryOptions.SetFilter("NOT Description LIKE 'POLYGON'");
featureReader = featureService.SelectFeatures(featureSrcResourceId, featClassName, queryOptions);
// process featureReader
featureReader.Close();
sqlplus> select Description from featclass where Description LIKE 'POL_GON';
$queryOptions->AddFeatureProperty("Description");
$queryOptions->SetFilter("Description LIKE 'POL_GON'");
$featureReader = $featureService->SelectFeatures($featureSrcResourceId, $featClassName, $queryOptions);
# process $featureReader
$featureReader->Close();
queryOptions.AddFeatureProperty("Description");
queryOptions.SetFilter("Description LIKE 'POL_GON'");
featureReader = featureService.SelectFeatures(featureSrcResourceId, featClassName, queryOptions);
// process featureReader
featureReader.Close();
<Identifier> IN ( <ValueExpressionCollection> )
anInt16 is an Int16 property. In one feature instance the value of anInt16 is -7033. If you apply <filter>anInt16 IN ( -5995, -7033 ), you select this feature.
sqlplus> select anInt16 from featclass where anInt16 in ( -5995, -7033 );
<?php
$queryOptions->AddFeatureProperty("anInt16");
$queryOptions->SetFilter("anInt16 IN ( -5995, -7033 )");
$featureReader = $featureService->SelectFeatures($featureSrcResourceId, $featClassName, $queryOptions);
# process $featureReader
$featureReader->Close();
?>
queryOptions.AddFeatureProperty("anInt16");
queryOptions.SetFilter("anInt16 IN ( -5995, -7033 )");
featureReader = featureService.SelectFeatures(featureSrcResourceId, featClassName, queryOptions);
// process featureReader
featureReader.Close();
<Identifier> > <DataValue>
featid is an identity property. If you apply the filter, featid > 20, you select the features whose featid has a value > 20. If you apply the filter, featid > 0 AND featid < 5, you select the features whose featid belongs to { 1, 2, 3, 4}. If you apply the filter, featid < 3 OR featid > 3, you select features whose featid is not 3.
aDateTime is a date property. There is a feature whose aDateTime property has the value 9/20/2005::10:9:34:0. If you apply the filter, aDateTime < '2005-09-21', you select this feature.
sqlplus> select anInt16 from featclass where adatetime < '21-SEP-05';
<?php
$queryOptions->AddFeatureProperty("anInt16");
$queryOptions->SetFilter("aDateTime < '2005-09-21'");
$featureReader = $featureService->SelectFeatures($featureSrcResourceId, $featClassName, $queryOptions);
# process $featureReader
$featureReader->Close();
?>
queryOptions.AddFeatureProperty("anInt16");
queryOptions.SetFilter("aDateTime < '2005-09-21'");
featureReader = featureService.SelectFeatures(featureSrcResourceId, featClassName, queryOptions);
// process featureReader
featureReader.Close();
<Expression> < <DataValue>
anInt16 is an Int16 property. Two features have non-NULL values for this property. One has a value -7033, and the other -5995. If you apply the filter, ( anInt16 + 1000 ) < -5995, you select the feature whose anInt16 property has the value -7033. The parentheses in this filter are optional because operator precedence would dictate that the filter, anInt16 + 1000 < -5995, is equivalent.
sqlplus> select anInt16 from featclass where anInt16 + 1000 < -5995;
<?php
$queryOptions->AddFeatureProperty("anInt16");
$queryOptions->SetFilter("anInt16 + 1000 < -5995");
$featureReader = $featureService->SelectFeatures($featureSrcResourceId, $featClassName, $queryOptions);
# process $featureReader
$featureReader->Close();
?>
queryOptions.AddFeatureProperty("anInt16");
queryOptions.SetFilter("anInt16 + 1000 < -5995");
featureReader = featureService.SelectFeatures(featureSrcResourceId, featClassName, queryOptions);
// process featureReader
featureReader.Close();
<FunctionValue> = <DataValue>
aDouble is a double property. One feature has aDouble property with a value of 8103.08393. If you apply the filter, ceil(aDouble) = 8104, you select this feature.
sqlplus> select aDouble from featclass where ceil(aDouble) = 8104;
<?php
$queryOptions->AddFeatureProperty("aDouble");
$queryOptions->SetFilter("ceil(aDouble) = 8104");
$featureReader = $featureService->SelectFeatures($featureSrcResourceId, $featClassName, $queryOptions);
# process $featureReader
$featureReader->Close();
?>
queryOptions.AddFeatureProperty("aDouble");
queryOptions.SetFilter("ceil(aDouble) = 8104");
featureReader = featureService.SelectFeatures(featureSrcResourceId, featClassName, queryOptions);
// process featureReader
featureReader.Close();
Group Function
aDouble is a double property. sum is a group function. sum(aDouble) sums the values of the aDouble property taken from a group of features.
sqlplus> select sum(aDouble) from featclass;
<?php
$queryOptions->AddComputedProperty("sumDbl", "sum(aDouble)");
$featureReader = $featureService->SelectFeatures($featureSrcResourceId, $featClassName, $queryOptions);
# process $featureReader
$featureReader->Close();
?>
queryOptions.AddComputedProperty("sumDbl", "sum(aDouble)");
featureReader = featureService.SelectFeatures(featureSrcResourceId, featClassName, queryOptions);
// process featureReader
featureReader.Close();
Ordering
aDouble is a double property. anInt32Key is the identity property. The first example returns aDouble values in ascending order, and the second example returns them in descending order.
Ascending
sqlplus> select anint32key,adouble from tuxuniversalclassxy order by adouble ASC;
<?php
$queryOptions->AddFeatureProperty("aDouble");
$queryOptions->AddFeatureProperty("anInt32Key");
$stringCollection->Add("aDouble");
$queryOptions->SetOrderingFilter($stringCollection, MgOrderingOption::Ascending);
$featureReader = $featureService->SelectFeatures($featureSrcResourceId, $featClassName, $queryOptions);
# process $featureReader
$featureReader->Close();
?>
queryOptions.AddFeatureProperty("aDouble");
queryOptions.AddFeatureProperty("anInt32Key");
stringCollection.Add("aDouble");
queryOptions.SetOrderingFilter(stringCollection, MgOrderingOption::Ascending);
featureReader = $featureService.SelectFeatures(featureSrcResourceId, featClassName, queryOptions);
// process $featureReader
featureReader.Close();
Descending
sqlplus> select anint32key,adouble from tuxuniversalclassxy order by adouble DESC;
<?php
$queryOptions->AddFeatureProperty("aDouble");
$queryOptions->AddFeatureProperty("anInt32Key");
$stringCollection->Add("aDouble");
$queryOptions->SetOrderingFilter($stringCollection, MgOrderingOption::Descending);
$featureReader = $featureService->SelectFeatures($featureSrcResourceId, $featClassName, $queryOptions);
# process $featureReader
$featureReader->Close();
?>
queryOptions.AddFeatureProperty("aDouble");
queryOptions.AddFeatureProperty("anInt32Key");
stringCollection.Add("aDouble");
queryOptions.SetOrderingFilter(stringCollection, MgOrderingOption::Descending);
featureReader = featureService.SelectFeatures(featureSrcResourceId, featClassName, queryOptions);
// process $featureReader
featureReader.Close();
Basic Filter OR Spatial Filter
featId is an identity property, and geometry is a geometry property. The feature whose featId value is 0 has a geometry value of POINT(1 1). The feature whose featid value is 1 has a null geometry value. The spatial filter requests features whose geometry intersects with POINT(1 1). The following select operation returns both of these features. The operation can be coded in two ways. The first way uses the SetFilter(), SetSpatialFilter() and SetBinaryOperator() methods, and the second way uses only the SetFilter() method.
sqlplus> select a.featId from featclass a where a.featId = 1 or sdo_relate(a.geometry, MDSYS.SDO_GEOMETRY(2001, NULL, MDSYS.SDO_POINT_TYPE(1,1,NULL), NULL, NULL), 'mask=anyinteract') = 'TRUE';
<?php
$queryOptions->AddFeatureProperty("featId");
$queryOptions->SetFilter("featId = 1");
$queryOptions->SetBinaryOperator(false);
$geometry = $wktReaderWriter->Read("POINT(1 1)");
$queryOptions->SetSpatialFilter("geometry", $geometry, MgFeatureSpatialOperations::Intersects);
$featureReader = $featureService->SelectFeatures($featureSrcResourceId, $featClassName, $queryOptions);
# process $featureReader
$featureReader->Close();
?>
queryOptions.AddFeatureProperty("featId");
queryOptions.SetFilter("featId = 1");
queryOptions.SetBinaryOperator(false);
geometry = wktReaderWriter.Read("POINT(1 1)");
queryOptions.SetSpatialFilter("geometry", geometry, MgFeatureSpatialOperations::Intersects);
featureReader = featureService.SelectFeatures(featureSrcResourceId, featClassName, queryOptions);
// process $featureReader
featureReader.Close();
<?php
$queryOptions->AddFeatureProperty("featId");
$queryOptions->SetFilter("(featId = 1) OR (geometry INTERSECTS GEOMFROMTEXT ( 'POINT(1 1)' )");
$featureReader = $featureService->SelectFeatures($featureSrcResourceId, $featClassName, $queryOptions);
# process $featureReader
$featureReader->Close();
?>
queryOptions.AddFeatureProperty("featId");
geometry = wktReaderWriter.Read("POINT(1 1)");
queryOptions.SetFilter("(featId = 1) OR (geometry INTERSECTS GEOMFROMTEXT ( 'POINT(1 1)' )");
featureReader = featureService.SelectFeatures(featureSrcResourceId, featClassName, queryOptions);
// process $featureReader
featureReader.Close();
Distance Filter
featId is an identity property, and geometry is a geometry property. The feature whose featId value is 0 has a geometry value of POINT(1 1). The distance filter requests features whose geometry is within a distance of 1 from POINT(2 1). The following select operation returns the feature whose featId is 0.
sqlplus> select a.featId from featclass a where sdo_within_distance(a.geometry, MDSYS SDO_GEOMETRY(2001, NULL, MDSYS.SDO_POINT_TYPE(2,1 NULL), NULL, NULL), 'distance=1') = 'TRUE';
<?php
$queryOptions->AddFeatureProperty("featId");
$queryOptions->SetFilter("geometry WITHINDISTANCE GEOMFROMTEXT ('POINT(2 1)') 1");
$featureReader = $featureService->SelectFeatures($featureSrcResourceId, $featClassName, $queryOptions);
# process $featureReader
$featureReader->Close();
?>
queryOptions.AddFeatureProperty("featId");
queryOptions.SetFilter("geometry WITHINDISTANCE GEOMFROMTEXT ('POINT(2 1)') 1");
featureReader = featureService.SelectFeatures(featureSrcResourceId, featClassName, queryOptions);
// process $featureReader
featureReader.Close();