Filtering data with CQL spatial operators

ECQL does not only let you create readable and powerful filters on feature attributes; obviously, it also lets you filter out geometric properties.

There are a few spatial operators that you can use in a CQL filter: EQUALS, DISJOINT, INTERSECTS, TOUCHES, CROSSES, WITHIN, CONTAINS, OVERLAPS, RELATE, DWITHIN, and BEYOND. With all these operators at your fingertips, you can really build complex filters.

In this recipe, we will use the BEYOND operator that is the inverse of DWITHIN, which we used in the recipe Using WFS spatial filters. With the filter, we will select the populated places located at least 1,000 km away from Rome, Italy. The result is shown in the following screenshot:

Tip

You can find the full source code for this recipe in the ch01_wmsCQLSpatialFilter.html file.

How to do it…

1. Create a projected table with populated places in PostGIS:

   gisdata=> CREATE TABLE populatedplaceswm AS SELECT name, ST_Transform(geom,3857) AS geom FROM populatedplaces;
   gisdata=> CREATE INDEX populatedplaceswm_0_geom_gist ON populatedplaceswm USING gist(geom);
   

2. Have a look at the latitude and longitude values for Rome using the following lines of code:

   gisdata=> select ST_AsText(geom) from populatedplaces where name = 'Rome';
   -----------------------------------------
    POINT(12.481312562874 41.8979014850989)
   

3. Copy wmsCQLFilter.html to wmsCQLSpatialFilter.html and edit the JavaScript code. Change sld to PointSymbolizer with a red fill for points outside the buffer:

   sld+= '<PointSymbolizer>';
   sld+= '<Graphic>';
   sld+= '<Mark>';
   sld+= '<WellKnownName>circle</WellKnownName>';
   sld+= '<Fill>';
   sld+= '<CssParameter name="fill">#FF0000</CssParameter>';
   sld+= '</Fill>';
   sld+= '</Mark>';
   sld+= '<Size>3</Size>';
   sld+= '</Graphic>';
   sld+= '</PointSymbolizer>';

4. Add an sld2 variable that holds drawing rules for points contained in the buffer area. You can reuse the code for the sld variable, changing the fill color to green:

   sld2+= '<PointSymbolizer>';
   sld2+= '<Graphic>';
   sld2+= '<Mark>';
   sld2+= '<WellKnownName>circle</WellKnownName>';
   sld2+= '<Fill>';
   sld2+= '<CssParameter name="fill">#00FF00</CssParameter>';
   sld2+= '</Fill>';
   sld2+= '</Mark>';
   sld2+= '<Size>3</Size>';
   sld2+= '</Graphic>';
   sld2+= '</PointSymbolizer>';

5. Set the SRS for the map to EPSG:3857:

   map = new OpenLayers.Map({
     div: "myMap",
     allOverlays: true,
     projection: "EPSG:3857",
     maxExtent: new OpenLayers.Bounds(-20037508, -20037508, 20037508, 20037508.34),
     maxResolution: 156543.0339,
     units: 'm',

6. Add a layer with a cql filter for points located outside a 1,000-kilometre circular buffer from Rome:

   new OpenLayers.Layer.WMS("Far away places",
     "http://localhost/geoserver/wms",{
       layers: "NaturalEarth:populatedplaceswm",
       format: "image/png",
       transparent: true,
       cql_filter: "BEYOND(geom,POINT(1389413 5145697),1000000,meters)",
       sld_body: sld,

7. Add a layer with a cql filter for points located inside a 1,000-kilometre circular buffer from Rome:

   new OpenLayers.Layer.WMS("Near places",
     "http://localhost/geoserver/wms",{
       layers: "NaturalEarth:populatedplaceswm",
       format: "image/png",
       transparent: true,
       cql_filter: "DWITHIN(geom,POINT(1389413 5145697),1000000,meters)",
       sld_body: sld2,

8. Save the file and point your browser to it. Your map will show a set of green points around Rome. As shown in the previous image, Rome is surrounded by red points.

How it works…

Using spatial operators in cql_filter is not really different than filtering other attributes. We used a map in a planar coordinate, in this case, the Web Mercator projection, because of an issue within GeoServer. Although you can define units that will be used to calculate distance in the BEYOND and DWITHIN operators, the interpretation depends on the data store (see http://jira.codehaus.org/browse/GEOS-937 for a detailed discussion). In PostGIS, the distance is evaluated according to the default units for the native spatial reference system of the data.

Tip

A spatial reference system (SRS) is a coordinate-based local, regional, or global system used to locate geographical entities. SRS defines a specific map projection as well as transformations between different SRS.

We need to create a new table with points projected in a planar coordinate. Then, we create an OpenLayers map object and set it to EPSG:3857. When using a projected SRS, we also need to set the map extent, resolution, and units:

projection: "EPSG:3857",
maxExtent: new OpenLayers.Bounds(-20037508, -20037508, 20037508, 20037508.34),
maxResolution: 156543.0339,
units: 'm',

We then add two layers pointing to the same feature type, populatedplaceswm, and using two different spatial filters. The first one uses the BEYOND operator, passing it the coordinates of Rome expressed in meters, a 1000-kilometer distance, and the units:

cql_filter: "BEYOND(geom,POINT(1389413 5145697),1000000,meters)",

Note

Although the units are not evaluated when filtering features, the parameter is mandatory. Hence, you have to insert it, but you need to be aware of the native SRS of the data and calculate a proper value for the distance

To override the default rendering of features, we set sld_body for the request to the SLD created in the JavaScript code:

sld_body: sld,

To represent features inside the buffer, we create a similar layer, filtering features with the DWITHIN operator. The syntax is pretty similar to BEYOND; please ensure that you set the same point and distance to build the buffer area:

cql_filter: "DWITHIN(geom,POINT(1389413 5145697),1000000,meters)",

Then, we set a different sld_body value to represent features with a different symbol:

sld_body: sld2,