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!
Related Articles
- Postgres Partitioning with a Default Partition
16 min read
- Iceberg ahead! Analyzing Shipping Data in Postgres
8 min read
- PostGIS Day 2024 Summary
8 min read
- Crunchy Data Warehouse: Postgres with Iceberg for High Performance Analytics
8 min read
- Loading the World! OpenStreetMap Import In Under 4 Hours
6 min read