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:

pg_featureserv

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 (dlon and dlat),
Use a CROSS JOIN between two generate_series functions 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:

http://localhost:9000/functions/geo_grid/items?num_x=5&num_y=5&lon_min=-128&lat_min=25&lon_max=-65&lat_max=49&limit=50

This generates a grid that covers the United States:

functions-example2 (1)

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"
	}
]

Latest Articles