CQL and ECQL

CQL (OGC Common Query Language) is a query language created by OGC for the Catalogue WebServices specification. Unlike the OGC Filter specification, CQL is plain text, human readable, and thus well suited for manual construction as opposed to machine generation. However CQL has some serious limitations, for example it cannot encode id filters and requires an attribute to be on the left side of any comparison operator. ECQL removes such limitations making for a more flexible language with stronger similarities with SQL.

GeoServer supports the use of both CQL and ECQL in WMS and WFS requests, as well as in dynamic symbolizers. When the documentation refers to CQL you can rest assured ECQL syntax can be used as well (and if not, please report that as a bug).

This tutorial introduces the language by example. If you need a full reference instead have a look at the ECQL BNF definition on the GeoTools site.

Getting started

All the following examples are going to use the topp:states sample layer shipped with GeoServer, and will use the CQL_FILTER vendor parameter to show how the CQL filters alter the map appearance. The easiest way to follow the tutorial is to open your GeoServer map preview, click on the options button at the top of the map preview, in order to open the advanced options toolbar, and enter the filter in the CQL box.

../../_images/gettingStarted1.png

topp:states preview with advanced toolbar open.

The attributes we’ll be using in the filters are those included in the layer itself. This is an example of attribute names and values for the state of Colorado:

Attribute states.6
STATE_NAME Colorado
STATE_FIPS 08
SUB_REGION Mtn
STATE_ABBR CO
LAND_KM 268659.501
WATER_KM 960.364
PERSONS 3294394.0
FAMILIES 854214.0
HOUSHOLD 1282489.0
MALE 1631295.0
FEMALE 1663099.0
WORKERS 1233023.0
DRVALONE 1216639.0
CARPOOL 210274.0
PUBTRANS 46983.0
EMPLOYED 1633281.0
UNEMPLOY 99438.0
SERVICE 421079.0
MANUAL 181760.0
P_MALE 0.495
P_FEMALE 0.505
SAMP_POP 512677.0

Simple comparisons

Let’s get started with the simplest example. In CQL basic arithmetic and comparisons do look exactly like plain text. The filter PERSONS > 15000000 will extract only states that do have more than 15 million inhabitants:

../../_images/more15M.png

PERSONS > 15000000

To check a range of values a between filter can be used instead: PERSONS BETWEEN 1000000 AND 3000000:

../../_images/between.png

PERSONS BETWEEN 1000000 AND 3000000

Comparing with text is similar. In order to get only the state of California, the filter will be STATE_NAME = 'California'. More complex text comparisons are available using LIKE comparisons. STATE_NAME LIKE 'N%' will extract all states starting with an N.

../../_images/startn.png

STATE_NAME LIKE ‘N%’

It is also possible to compare two attributes with each other. MALE > FEMALE selects the states in which the male population surpasses the female one (a rare occurrence):

../../_images/malefemale.png

MALE > FEMALE

It is also possible to make simple math expressions using the +, -, *, / operators. The following filter UNEMPLOY / (EMPLOYED + UNEMPLOY) > 0.07 selects all states whose unemployment ratio is above 7% (remember the sample data is very old, don’t draw any conclusion from the results)

../../_images/employ.png

UNEMPLOY / (EMPLOYED + UNEMPLOY) > 0.07

Id and list comparisons

If we want to extract only the states with a certain feature id we’ll use the IN filter without specifying any attribute, as in IN ('states.1', 'states.12'):

../../_images/idfilter.png

IN (‘states.1’, ‘states.12’)

If instead we want to extract the states whose name is in a given list we can use the IN filter specifying an attribute name, like in STATE_NAME IN ('New York', 'California', 'Montana', 'Texas'):

../../_images/statenames.png

STATE_NAME IN (‘New York’, ‘California’, ‘Montana’, ‘Texas’)

Calling filter functions

CQL/ECQL can call any of the filter functions available in GeoServer.

For example, say we want to find all states whose name contains an “m”, regardless of wheter it’s a capital one, or not. We can call the strToLowerCase to turn all the state names to lowercase and then use a like comparison: strToLowerCase(STATE_NAME) like '%m%':

../../_images/mstates.png

strToLowerCase(STATE_NAME) like ‘%m%’

Geometric filters

CQL provides a full set of geometric filter capabilities. Say, for example, you want to display only the states that do cross the (-90,40,-60,45) bounding box. The filter will be BBOX(the_geom, -90, 40, -60, 45)

../../_images/bbox.png

BBOX(the_geom, -90, 40, -60, 45)

Conversely we can filter out all of the states that are overlapping that bounding box with the following filter DISJOINT(the_geom, POLYGON((-90 40, -90 45, -60 45, -60 40, -90 40))):

../../_images/disjoint.png

DISJOINT(the_geom, POLYGON((-90 40, -90 45, -60 45, -60 40, -90 40)))