The Common Query Language (CQL) is used to define expressions and filters in several parts of hale製tudio. It can be used to define conditions on schema elements or to filter instances. hale製tudio also supports the extended CQL (ECQL) syntax as defined by Geotools.
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"
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.
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"
| Type | Example |
|---|---|
| String | 'Yoda' |
| Integer | 12 |
| Floating-point number | 1.234 |
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 |
Following are some example filters. You can test them with the example
project provided in the
Get started with hale製tudio guide, using the
Transformed Data view.
name = 'Yoda'
details.age > (10 + (4 / 2) * 3)
details.age >= 16 AND details.age <= 50
details.age BETWEEN 16 AND 50
name LIKE 'Tom%'
name NOT LIKE '%Snow'
details.address.city IS NULL
details.address.city IS NOT NULL
Following is a list of filter operations that have been tested with hale製tudio:
Checks if a value is equal to another attribute or a literal value.
name = 'Yoda'
details.age = 12
If there are multiple values in an attribute that is checked for equality, it is enough if one of the values is a match.
Checks if a value is not equal to another attribute or a literal value.
name <> 'Yoda'
details.age <> 12
All values must be not equal to the given value for the filter to match.
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.
name IS NULL
id IS NOT NULL
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
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.
Match names that start with Y:
name LIKE 'Y%'
Match names that contain an o or O
name LIKE '%o%' or name LIKE '%O%'
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.
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.
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
If there are multiple values in an attribute that is checked, it is enough if one of the values is a match.
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 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製tudio. It is right now not possible to apply them to parent attributes of geometry attributes directly.
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))
If there are multiple geometries in an attribute that is checked, it is enough if one of the geometries is a match.
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.
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
Checks on attributes with multiple values are not supported. Matches will always fail in that case.
Use
AND
and
OR
to combine filter expressions or
NOT
to negate them.
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
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
These are CQL filter operations that have been verified to not work with hale製tudio:
IS NOT NULL instead if possibleIS NULL instead if possibleCROSSES insteadINTERSECTS insteadMore general information on CQL can be found in the Geotools documentation or the UDig user guide.
