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
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
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 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
% (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
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
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
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:
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
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:
Note that the
ILIKE wildcard must be URL-encoded as
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
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
March 7, 2022 •More by this author