GeoServer Guide to CQL

CQL (Common Query Language) is a plain text query language created by the OGC for the Catalogue Web Services specification.

It is a human-readable query language, and its syntax is very similar to SQL syntax, but with extremely limited functionality. For example, it can’t encode ID filters, and it requires an attribute to be on the left side of any comparison operator.

Because of the mentioned limitations, GeoServer provides an extended version of CQL, called ECQL.

CQL Supported Operators

CQL, compared to SQL or OGC filters, is using a more familiar text-based syntax and is better-suited for manual authoring.

CQL supports following operators:

• Comparison operators: =, <>, >, >=, <, <=
• Id, list and other operators: BETWEEN, BEFORE, AFTER, LIKE, IS, EXISTS, NOT, IN
• Arithmetic expression operators: +, -, *, /
• Geometric operators: EQUALS, DISJOINT, INTERSECTS, TOUCHES, CROSSES, WITHIN, CONTAINS, OVERLAPS, RELATE, DWITHIN, BEYOND

GeoServer ECQL Implementation

ECQL removes the limitations of CQL, providing a more flexible language with stronger similarities with SQL. GeoServer supports the use of both CQL and ECQL in WMS and WFS requests. GeoServer User Manual has listed all available WMS CQL_FILTER vendor parameters that GeoServer supports.

The easiest way to try and play with CQL filters is to open the GeoServer Map Preview for the topp:states layer. Options button at the top of the map preview opens the advanced options toolbar with Filter: CQL box. Again, GeoServer User Manual has some CQL filter examples, like:

• PERSONS > 15000000
• PERSONS BETWEEN 1000000 AND 3000000
• STATE_NAME = 'California'
• STATE_NAME LIKE 'N%'
• MALE > FEMALE
• UNEMPLOY / (EMPLOYED + UNEMPLOY) > 0.07
• STATE_NAME IN ('New York', 'California', 'Montana', 'Texas')
• BBOX(the_geom, -90, 40, -60, 45)

Filtering by Date

However, if you need to filter a table by the column that is of a type date, you could run into problems. Depending on the Database setup and Regional Settings on the server where GeoServer is installed, you could end up with an unexpected date representation. The best practice is to use GeoServer Map Preview and see in which format date is represented when you click on the feature.

For example, if you want to filter data by the year 2016 you could end up with one of the following CQL filters:

DATE_COL AFTER 2016-01-01T00:00:00Z AND DATE_COL BEFORE 2016-12-31T23:59:59Z
DATE_COL > '01.01.2016' AND DATE_COL < '31.12.2016'

If you have two or more layers defined in one WMS request, like this:

http://localhost:8080/geoserver/wms?layers=layer1,layer2,layer3...

And you want to filter only one layer in that group, you must order CQL filters in the same order as the layers are, and use INCLUDE keyword for the layers that you don’t want to filter. For example, to filter only 2nd layer, the WMS request will look like this:

http://localhost:8080/geoserver/wms?layers=layer1,layer2,layer3&cql_filter=INCLUDE;(LAYER2_COL='value');INCLUDE...

Note that the separator between filters is ;, while between the layers is ,.