CQL Filtering in pg_featureserv
The goal of pg_featureserv
is
to provide easy and efficient access to PostGIS from web
clients.
To do this, it uses the emerging OGC API for Features (OAPIF) RESTful protocol. This is a natural fit for systems which need to query and communicate spatial data. The core OAPIF specification provides a basic framework for querying spatial datasets, but it has only limited capability to express filtering subsets of spatial tables.
In particular, it only allows filtering on single attribute values, and it only
supports limited spatial filtering via the bbox
query parameter (in PostGIS
terms, this is equivalent to using the &&
operator with a box2d
).
Of course, PostGIS and PostgresQL provide much more powerful capabilities to
filter data using SQL WHERE
clauses. It would be very nice to be able to use
these via pg_featureserv
. Luckily, the OGC is defining
a way to allow filtering via the
Common Query Language (CQL) which, as the name
suggests, is a close match to SQL filtering capabilities. This is being issued
under the OGC API suite as CQL2 (currently in
draft).
Recently we added pg_featureserv
support for most of CQL2. Here we'll describe
the powerful new capability it provides.
CQL Overview
CQL is a simple language to describe logical expressions. A CQL expression
applies to values provided by feature properties and constants including
numbers, booleans and text values. Values can be combined using the arithmetic
operators +
,-
,*
, /
and %
(modulo). Conditions on values are expressed
using simple comparisons (<
,>
,<=
,>=
,=
,<>
). Other predicates include:
prop IN (val1, val2, ...)
prop BETWEEN val1 AND val2
prop IS [NOT] NULL
prop LIKE | ILIKE pattern
Conditions can be combined with the boolean operators AND
,OR
and NOT
.
You will notice that this is very similar to SQL (probably not a coincidence!). That makes it straightforward to implement, and easy to use for us database people.
CQL also defines syntax for spatial and temporal filters. We'll discuss those in a future blog post.
Filtering with CQL
A CQL expression can be used in a pg_featureserv
request in the filter
parameter.
This is converted to SQL and included in the WHERE
clause of the underlying
database query. This allows the database to use its query planner and any
defined indexes to execute the query efficiently.
Here's an example. We'll query the
Natural Earth admin boundaries
dataset, which we've loaded into PostGIS as a spatial table. (See
this post
for details on how to do this.) We're going to retrieve information about
European countries where the population is 5,000,000 or less. The CQL expression
for this is continent = 'Europe' AND pop_est <= 5000000
.
Here's the query to get this result:
http://localhost:9000/collections/ne.countries/items.json?properties=name,pop_est&filter=continent%20=%20%27Europe%27%20AND%20pop_est%20%3C=%205000000&limit=100
Note: it's a good idea to to URL-encode spaces and special characters.
This returns a GeoJSON response with 25 features:
By using the extension html
instead of json
in the request we can visualize
the result in the pg_featureserv
UI:
More power, fewer functions
One of the cool things about pg_featureserv
and its companion
pg_tileserv
is the ability to
serve data provided by PostgreSQL functions. In a previous post we showed
how to use a function to find countries where the name matches a search string
. Now we can do this more easily and flexibly by using a CQL filter:
http://localhost:9000/collections/ne.countries/items.html?properties=name,pop_est&filter=name%20ILIKE%20%27Mo%25%27
Note that the ILIKE
wildcard must be URL-encoded as %25
.
And the filter can easily include more complex conditions, which is harder to do with a function. But function serving is still powerful for things like generating spatial data and routing.
More to come...
We'll publish a blog post on the spatial filtering capabilities of CQL soon,
along with some other interesting spatial capabilities in pg_featureserv
. CQL
support will be
rolled out in
pg_tileserv
soon as well. This
brings some exciting possibilities for large-scale data visualization!
PostgreSQL provides even more powerful expression capabilities than are
available in CQL. There's things like string concatenation and functions, the
CASE
construct for "computed if", and others. What kinds of things would you
like to see pg_featureserv
support?
Try it!
CQL filtering will be included in the forthcoming pg_featureserv Version 1.3.
But you can try it out now by simply
downloading the latest
build. Let us know what use cases you find
for CQL filtering!