Using PostGIS Functions in pg_featureserv
In our last blog post about pg_featureserv, we showed how it can publish spatial datasets and access them via simple web requests. In this post, we’re going to discuss how publishing PostgreSQL/PostGIS functions via pg_featureserv provides even more flexible access to your data.
Do More with Functions
A powerful feature of PostgreSQL is the ability to create user-defined functions. Functions let you wrap complex logic within a simple interface: passing in arguments, and getting back a set of values as output.
In the same way that pg_tileserv can use functions to generate vector tiles, pg_featureserv can use functions to generate data. Publishing functions allows executing any kind of data processing that Postgres and PostGIS can perform, and returning either spatial or non-spatial results (as GeoJSON, or plain JSON, respectively).
You could use functions to:
- Query a spatial database table or view with custom SQL (which can include more complex spatial or attribute filters than the API provides, joins to other tables, or aggregation, etc.)
- Query a non-spatial table or view to return data objects or a summary record
- Generate spatial data controlled by a set of parameters
And that’s just a few to begin with. Functions make pg_featureserv's API more robust, and in a way that you have a lot of control over. Let’s look at a few examples of spatial and non-spatial functions (and how to tell which is which).
Example: Query Countries by Name
We'll start out with a basic function that returns a table containing a geometry column, so we consider this a spatial function. This will return output in GeoJSON format. We're using a database loaded with the Admin 0 - Countries data set from Natural Earth to demonstrate how to use such a function.
CREATE OR REPLACE FUNCTION postgisftw.countries_name(
name_prefix text DEFAULT 'A')
RETURNS TABLE(name text, abbrev text, continent text, geom geometry)
AS $$
BEGIN
RETURN QUERY
SELECT t.name::text,
t.abbrev::text,
t.continent::text,
t.geom
FROM ne.admin_0_countries t
WHERE t.name ILIKE name_prefix || '%';
END;
$$
LANGUAGE 'plpgsql' STABLE PARALLEL SAFE;
COMMENT ON FUNCTION postgisftw.countries_name IS 'Filters the countries table by the initial letters of the name using the "name_prefix" parameter.';
The function takes an input parameter name_prefix and queries the
ne.admin_0_countries table using the case-insensitive pattern match predicate
ILIKE. We get a result set back that matches the table defined as the function
output.
Here's how we'd query the API to return countries whose names begin with "Mo":
http://localhost:9000/functions/countries_name/items?name_prefix=Mo
GeoJSON response:
{
"type":"FeatureCollection",
"features":[
{
"type":"Feature",
"geometry":{
"type":"MultiPolygon",
"coordinates":[
[
[
[
-62.1484375,
16.74033203125
],
[
-62.154248046875,
16.681201171875
],
...
[
-62.1484375,
16.74033203125
]
]
]
]
},
"properties":{
"abbrev":"Monts.",
"continent":"North America",
"name":"Montserrat"
}
},
...
],
"numberReturned":7,
"timeStamp":"2020-03-18T03:15:15Z",
"links":[
{
"href":"http://localhost:9000/collections/countries_name/items.json",
"rel":"self",
"type":"application/json",
"title":"This document as JSON"
},
{
"href":"http://localhost:9000/collections/countries_name/items.html",
"rel":"alternate",
"type":"text/html",
"title":"This document as HTML"
}
]
}
And this is how it looks in the web UI:
Example: Generate a Geographic Grid
The next example is also a spatial function, for generating a rectangular grid over a desired area. This shows how geometry data can be generated from function parameters, instead of querying another data set.
Grids generated in this way could be used by a client application for visualization purposes. Or, a database function could use the generated grid and join it to another dataset for analysis or clustering.
CREATE OR REPLACE FUNCTION postgisftw.geo_grid(
num_x integer DEFAULT 10,
num_y integer DEFAULT 10,
lon_min numeric DEFAULT -180.0,
lat_min numeric DEFAULT -90.0,
lon_max numeric DEFAULT 180.0,
lat_max numeric DEFAULT 90.0)
RETURNS TABLE(id text, geom geometry)
AS $$
DECLARE
dlon numeric;
dlat numeric;
BEGIN
dlon := (lon_max - lon_min) / num_x;
dlat := (lat_max - lat_min) / num_y;
RETURN QUERY
SELECT
x.x::text || '_' || y.y::text AS id,
ST_MakeEnvelope(
lon_min + (x.x - 1) * dlon, lat_min + (y.y - 1) * dlat,
lon_min + x.x * dlon, lat_min + y.y * dlat, 4326
) AS geom
FROM generate_series(1, num_x) AS x(x)
CROSS JOIN generate_series(1, num_y) AS y(y);
END;
$$
LANGUAGE 'plpgsql'
STABLE
STRICT;
COMMENT ON FUNCTION postgisftw.geo_grid IS 'Generates a grid of rectangles over a geographic extent';
In our geo_grid function, we:
- Accept minimum and maximum longitude and latitude values for the area we want to cover, along with the number of grid cells along the longitudinal (X) and latitudinal (Y) axes,
- Compute the lengths of the grid cell sides (
dlonanddlat), - Use a
CROSS JOINbetween twogenerate_seriesfunctions to compute the X and Y indices for each grid cell, - Use the PostGIS
ST_MakeEnvelope()function to construct a rectangular polygon for each grid cell, along with a textual ID value encoding the grid index.
The default parameters of the function define a rectangular grid that spans the entire globe. Some clients may read arbitrary function definitions and need default values to use with interface fields, so setting default parameters is recommended.
To query the function, we can use a request like:
This generates a grid that covers the United States:
.png)
You’ll notice that the URL has an additional query parameter of limit=50. The
server by default has a fairly low limit to the number of features in a
response. This can be changed by a
configuration parameter,
but I haven’t changed mine yet so I need to specify a limit to make sure I get
the entire 5x5 grid.
Non-Spatial Example: Locate Country Name
We'll now take a look at a different kind of function: this one, unlike the previous two, does not return spatial data. This non-spatial function still returns a table, but the API will instead deliver the response in JSON.
CREATE OR REPLACE FUNCTION postgisftw.country_by_loc(
lon numeric DEFAULT 0.0,
lat numeric DEFAULT 0.0)
RETURNS TABLE(name text, abbrev text, postal text)
AS $$
BEGIN
RETURN QUERY
SELECT c.name::text, c.abbrev::text, c.postal::text
FROM ne.admin_0_countries c
WHERE ST_Intersects(c.geom,
ST_SetSRID(ST_MakePoint(lon, lat), 4326))
LIMIT 1;
END;
$$
LANGUAGE 'plpgsql' STABLE STRICT;
COMMENT ON FUNCTION postgisftw.country_by_loc
IS 'Finds the country at a geographic location';
This function accepts a longitude and latitude, and returns one record (if found) with the country's name, abbreviation, and country code.
First, the function generates a
Point based on the provided
longitude and latitude. The ne.admin_0_countries table is then filtered based
on whether the point intersects a
country polygon. If the point happens to lie exactly on the boundary between two
countries, both records will be in the result, so LIMIT 1 is used to restrict
the result to a single record.
We can try using the coordinate pair (47,8) like so:
http://localhost:9000/functions/country_by_loc/items.json?lat=47&lon=8
And we get back:
[
{
"abbrev": "Switz.",
"name": "Switzerland",
"postal": "CH"
}
]
More Functions in PostgreSQL
You’ve now seen the difference between spatial and non-spatial functions, as well as some quick examples of how to use the API to retrieve function data. On a final note, if you’re wondering whether pg_featureserv could publish functions that don’t use PostGIS at all: the answer is yes. Any kind of function can be published, as long as it resides in the postgisftw schema. This allows you to provide API queries that do things like populate dropdown lists, summarize data statistics, or other uses that aren’t strictly spatial in nature.
If you’d like to play a little bit more with functions in PostgreSQL, check out the Basics of Writing PostgreSQL Functions course in our Crunchy Data Learning Portal. Of course, don’t forget to go to the user guide whenever you want to learn more about pg_featureserv!
