CQL Filter

The Common Query Language (CQL) is used to define expressions and filters in several parts of hale studio. It can be used to define conditions on schema elements or to filter instances. hale studio also supports the extended CQL (ECQL) syntax as defined by Geotools.

Property references

In a filter, you can refer to a property value by the property name. If you want to refer to a nested property, use a dot to separate the property names on the property's path. Encapsulating a property path in double quotes ensures that it is correctly recognized, even if a property name happens to be equal to a reserved expression in CQL.

For this example schema...

...the following are valid references to a property:

name

"id"

details.address.city

"details.age"

Groups

If a schema contains groups, you can ignore them in your filter. Just treat the group's properties as if they were directly associated to the group's parent.

Namespaces

Stating the namespace associated to a property name is optional, though it may be useful to differentiate between properties with the same local name. A namespace is specified by encapsulating it in curly brackets and using it as a prefix for the corresponding property name. When specifying a namespace it is mandatory to use double quotes for the property reference. Following is an example:

"geometry.{http://www.opengis.net/gml}Point"

Literals

Type Example
String 'Yoda'
Integer 12
Floating-point number 1.234

Filter functions

For more complex filters you can use one or a combination of multiple functions. A selection of available functions is provided below. For the full list of functions, see GeoServer's function reference, but note that not all of the functions listed there are necessarily supported in a filter.

When defining a filter, replace all arguments with either a property name or a lexical expression (e.g. 'John' or 47). Also note, that all expressions must evaluate to true or false, so you may have to add "= true" or "= false" after the function call, e.g. strMatches(name, 'Tom.*') = true.

Function with arguments Description
Comparison functions
equalTo(a, b) Can be used to compare two values a and b
greaterThan(x, y) True if x > y. Can be used for numbers and strings.
greaterEqualThan(x, y) True if x >= y. Can be used for numbers and strings.
isLike(string, pattern) True if string matches the specified pattern. For the syntax of the pattern specification, see the documentation of the Java Pattern class.
isNull(obj) True if the parameter obj is null.
lessThan(x, y) True if x < y. Can be used for numbers and strings.
lessEqualThan(x, y) True if x <= y. Can be used for numbers and strings.
not(bool) True if the expression bool is false.
notEqualTo(a, b) True if a and b are not equal.
String functions
strStartsWith(string, prefix) True if string starts with prefix.
strEndsWith(string, suffix) True if string ends with suffix.
strEqualsIgnoreCase(a, b) True if a and b are equal ignoring case.
strLength(string) Returns the length of string.
strToUpperCase(string) Returns the upper case version of string.
strToLowerCase(string) Returns the lower case version of string.
strMatches(string, pattern) True if string matches the specified pattern. For the syntax of the pattern specification, see the documentation of the Java Pattern class.
Spatial relation and geometric functions
area(geom) Returns the area of the given geometry.
difference(geom1, geom2) Returns the dimension of the given geometry.
dimension(geom) Returns the dimension of the given geometry.
distance(geom1, geom2) Returns the euclidian distance between the given geometries.
geometryType(geom) Returns the type of the given geometry as a string (one of Point, MultiPoint, LineString, LinearRing, MultiLineString, Polygon, MultiPolygon or GeometryCollection)
dimension(geom) Returns the dimension of the given geometry.
geomLength(geom) Returns the length of the given geometry
intersection(geom1, geom2) Returns the intersection of the given geometries
isClosed(line) Returns true if the LineString line is a closed ring
isEmpty(geom) Returns true if the geometry does not contain any point
isRing(line) Returns true if the LineString line is a closed ring
isSimple(line) Returns true if the LineString line intersects itself only at boundary points
isValid(geom) Returns true if geom is topologically valid
union(geom1, geom2) Returns the union of the given geometries

Example filters

Following are some example filters. You can test them with the example project provided in the command linkGet started with hale studio guide, using the Transformed Data view.

Comparisons

name = 'Yoda'

details.age > (10 + (4 / 2) * 3)

details.age >= 16 AND details.age <= 50

details.age BETWEEN 16 AND 50

Text

name LIKE 'Tom%'

name NOT LIKE '%Snow'

Null

details.address.city IS NULL

details.address.city IS NOT NULL

Supported filter operations

Following is a list of filter operations that have been tested with hale:

Equal (=)

Checks if a value is equal to another attribute or a literal value.

Examples

name = 'Yoda'

details.age = 12

Behavior for multiple values

If there are multiple values in an attribute that is checked for equality, it is enough if one of the values is a match.

Not equal (<>)

Checks if a value is not equal to another attribute or a literal value.

Examples

name <> 'Yoda'

details.age <> 12

Behavior for multiple values

All values must be not equal to the given value for the filter to match.

IS NULL

Checks if a value does not exist or if the value is the special null value. Using IS NOT NULL you can check for inverse condition that states if an attribute exists and has a non-null value.

Examples

name IS NULL

id IS NOT NULL

Behavior for multiple values

If there are multiple values for an attribute, IS NULL will never match, as a list of values is always treated as non-null, even if all the values are null.

LIKE

LIKE lets you compare part of a string value. With LIKE you can check if an attribute value starts, ends or contains a string literal. The percent sign serves as a wildcard.

Examples

Match names that start with Y:

name LIKE 'Y%'

Match names that contain an o or O

name LIKE '%o%' or name LIKE '%O%'

Behavior for multiple values

If there are multiple values in an attribute that is checked if it contains a string, it is enough if one of the values is a match.

Comparisons (BETWEEN,<,>,...)

BETWEEN allows you to determine if a value is in a certain range. Comparison operators like less than ( < ) and greater than ( > ) allow you to check a value in relation to another one.

Examples

Match everyone older than 100:

details.age > 100

Match everyone that is between 20 and 30:

details.age BETWEEN 20 AND 30

Match everyone that is 10 or younger:

details.age <= 10

Behavior for multiple values

If there are multiple values in an attribute that is checked, it is enough if one of the values is a match.

Behavior for Strings

The comparison operations can also be used for Strings, but ordering is case sensitive and not based on alphabetical order. Thus it is rarely useful.

Spatial operations (CONTAINS,BBOX,...)

Spatial operations perform checks on geometries. Filters are not aware of any Spatial Reference System, thus currently the checks are performed on the geometries as-is. As a consequence any geometry literals defined must rely on source data having a specific reference system that is previously known.

Note that spatial CQL filters only work on attributes that are marked as geometry attributes in hale. It is right now not possible to apply them to parent attributes of geometry attributes directly.

Examples

CONTAINS(areaAttr, POINT(2 0))

Note that the notation for BBOX first expects the ordinates of one corner of the bounding box, then the other corner:

BBOX(area, 10, 10, 20, 20)

INTERSECTS(area, LINESTRING(2 -1, 2 2))

Behavior for multiple values

If there are multiple geometries in an attribute that is checked, it is enough if one of the geometries is a match.

Time comparisons (BEFORE,AFTER,DURING,TEQUALS)

Time comparisons allow comparing attributes with date and time literals.

Please note that these time comparisons can only be reliably applied on attributes that have a binding of an Date/Time data type.

Examples

Test if the value of attribute date is after a certain point in time:

date AFTER 2006-11-30T01:30:00Z

Test if the value of attribute date is before a certain point in time:

date BEFORE 2016-11-30T01:30:00Z

Test if the value of attribute date is during a certain period of time:

date DURING 2006-11-30T01:30:00Z/2016-11-30T01:30:00Z

Test if the value of attribute date is equal to a certain point in time:

date TEQUALS 2012-12-01T12:00:00Z

Behavior for multiple values

Checks on attributes with multiple values are not supported. Matches will always fail in that case.

Composition and negation

Use AND and OR to combine filter expressions or NOT to negate them.

Examples

Select persons whose name starts with Y and are at least 16 years old:

name like 'Y%' AND details.age >= 16

Select persons that don't match the previous filter:

NOT(name like 'Y%' AND details.age >= 16)

Select persons that are younger than 10 or older than 50 years:

details.age < 10 OR details.age > 50

Filter functions

Use ECQL filter functions to create more complex filters.

Match all instances where the value in name starts with 'Tom':

strMatches(name, 'Tom.*') = true

Match all instances where the total area of the geometry in the_geom is greater than 9000:

area(the_geom) > 9000

Unsupported operations

These are CQL filter operations that have been verified to not work with hale:

More general information on CQL can be found in the Geotools documentation or the UDig user guide.



What is an Instance?

Contexts


Schema elements