PostgREST + PostGIS

How to set up a geospatial-capable REST API for your PostGIS database.

python

postgis

Author

Henry Rodman

Published

March 5, 2023

Introduction

PostgreSQL databases enabled with the PostGIS extensions are a great way to store and manage geospatial data. Managing database credentials for many users, however, can be cumbersome! If you want to provide easy, public (or authenticated), read-only access to information in any PostgreSQL database, it is possible to make a simple REST API using PostgREST. The API comes with some powerful table-querying capabilities out of the box but you can turn it into a geospatial API with user-defined SQL functions.

In this article I will walk through an example for setting up a PostGIS-enabled database and getting PostgREST running with a bounding box query endpoint. This process is basically identical to the one described in the first PostgREST tutorial but with a side of PostGIS sizzle. The example database + endpoint runs in a docker network that you can fire up locally for yourself. I based this workflow off of the docker-only setup in the PostgREST docs. All of the files used to get this system running are available on GitHub:

setup_db.sql: SQL script for setting up the database that gets run every time we fire up the docker network
docker-compose.yml: docker compose file for firing up the docker network

Set up a database

To demonstrate the geospatial query possibilities of PostgREST, we need to set up a PostGIS-enabled database. This process is defined in setup_db.sql which gets run every time we launch the docker network. For a real application, you probably would not want to initiate the database from scratch every time but it is convenient for this example.

add records to the `plot` table

First, we create a single table called plot that has an ID column (id), longitude/latitude (lon/lat), and a geometry column (geom):

-- create schema for a toy database
create schema api;
-- a little housekeeping
drop table if exists plot;
create extension if not exists postgis;
-- define the table schema
create table api.plot (
    id serial primary key,
    lon real not null,
    lat real not null,
    geom geometry(Point, 4326)
);

Once the table is set up we can generate 1000 random coordinates that fall in a bounding box that roughly covers CONUS. After the lon/lat values are populated, construct the geom values using the PostGIS functions st_setsrid and st_makepoint:

--- add data (random points from a large bounding box)
insert into api.plot (lon, lat)
select random() * (-57.2183 - (-131.3702)) + -131.3702,
    random() * (51.5376 - 11.9402) + 11.9402
from generate_series(1, 1000);
--- update geom column values
update api.plot
set geom = st_setsrid(st_makepoint(lon, lat), 4326);
--- add spatial index
alter table api.plot create index if not exists idx_plot_geom on api.plot using gist(geom);

set up credentials for API

The PostgREST API will access the database using a pre-defined user credential. The details for what you need for your specific use-case may vary, but this method (pulled straight out of the PostgREST docs) will generate a read-only user account that has select privileges for the plot table.

--- create roles for postgrest access
create role web_anon nologin;
grant usage on schema api to web_anon;
grant select on api.plot to web_anon;
create role authenticator noinherit login password 'mysecretpassword';
grant web_anon to authenticator;

define `query_bbox` function

PostgREST contains a powerful querying API out-of-the-box, but if you want to use any of the spatial operations from the PostGIS functions you need to create user-defined functions that use them.

The function query_bbox takes a set of bounding box coordinates (xmin, ymin, xmax, ymax) and returns all of the plot records that fall within the bounding box:

--- function for querying plots by bounding box
create function api.query_bbox(xmin real, xmax real, ymin real, ymax real) returns setof api.plot as $$
select *
from api.plot
where st_contains(
        st_makeenvelope(
            query_bbox.xmin,
            query_bbox.xmax,
            query_bbox.ymin,
            query_bbox.ymax,
            4326
        ),
        plot.geom::geometry
    );
$$ stable language sql;

Define the docker network

This docker network consists of two services: server and db. Wrapping the whole thing in a docker network is convenient for local testing but I would not recommend it for any kind of production deployment!

`db`: PostGIS database

I used the kartoza/postgis docker image and followed the docs to get this configuration. One helpful trick is to mount setup_db.sql into the docker-entrypoint-initdb.d directory to make it easy to initiate the database from a script. You can set up the docker-network.yml to use environment variables that might be shared across multiple services (i.e. server). In this case, only POSTGRES_DB gets used by both services.

IGNORE_INIT_HOOK_LOCKFILE: to make sure the setup_db.sql script runs every time you launch the docker network (useful for debugging).

db:
  image: kartoza/postgis:15-3.3
  platform: linux/amd64
  environment:
    - POSTGRES_USER=${POSTGRES_USER}
    - POSTGRES_PASS=${POSTGRES_PASS}
    - POSTGRES_DB=${POSTGRES_DB}
    - ALLOW_IP_RANGE=0.0.0.0/0
    - IGNORE_INIT_HOOK_LOCKFILE=true
  ports:
    - "${POSTGRESTGIS_PORT}:5432"
  volumes:
    - ./setup_db.sql:/docker-entrypoint-initdb.d/setup_db.sql
  healthcheck:
    test: "PGPASSWORD=${POSTGRES_PASS} pg_isready -h 127.0.0.1 -U ${POSTGRES_USER} -d ${POSTGRES_DB}"

Once db is up and running, the username:password combo authenticator:mysecretpassword is available to the PostgREST endpoint

`server`: PostgREST server

Conveniently, there is a postgrest docker image that can be configured to work with an existing database using environment variables only!

PGRST_DB_URI: the database connection URI with the credentials that we set up in setup_db.sql for the API connection (authenticator:mysecretpassword)
- note: the host:port for this connection references the location within the docker network (db:5432) rather than some real network location, which is convenient in this case because the containerized database is already configured for access by other services within the docker network.
PGRST_DB_SCHEMAS: name(s) of schemas that PostgREST can use
PGRST_DB_ANON_ROLE: database role to be used by PostgREST, needs to match the one that we defined in setup_db.sql
PGRST_OPENAPI_SERVER_PROXY_URI: URI for the database endpoint, set to use the same local port that we mapped using ports

server:
  image: postgrest/postgrest
  ports:
    - "3000:3000"
  environment:
    PGRST_DB_URI: postgres://authenticator:mysecretpassword@db:5432/${POSTGRES_DB}
    PGRST_DB_SCHEMAS: api
    PGRST_DB_ANON_ROLE: web_anon
    PGRST_OPENAPI_SERVER_PROXY_URI: http://127.0.0.1:3000
  depends_on:
    - db

Get it running

Build the docker network:

!POSTGRES_USER=postgrestgis \
  POSTGRES_PASS=password \
  POSTGRES_DB=postgrestgis \
  POSTGRESTGIS_PORT=25432 \
  docker-compose up -d

# pause to make sure the docker network gets up and running before we run any
# queries
!sleep 15s

Now that the database and PostgREST server are running we can send queries to the database.

query using `curl`

The PostgREST API documentation has an extensive guide to interacting with the API, so definitely check that out.

This query will pull the first 10 records from the plot table:

!curl "http://localhost:3000/plot?limit=10" -H "Accept: application/json" --silent

[{"id":1,"lon":-126.23441,"lat":23.477777,"geom":{"type":"Point","crs":{"type":"name","properties":{"name":"EPSG:4326"}},"coordinates":[-126.23441314697266,23.4777774810791]}}, 
 {"id":2,"lon":-69.01085,"lat":35.35603,"geom":{"type":"Point","crs":{"type":"name","properties":{"name":"EPSG:4326"}},"coordinates":[-69.01084899902344,35.35602951049805]}}, 
 {"id":3,"lon":-61.391056,"lat":19.874908,"geom":{"type":"Point","crs":{"type":"name","properties":{"name":"EPSG:4326"}},"coordinates":[-61.391056060791016,19.874908447265625]}}, 
 {"id":4,"lon":-71.88675,"lat":19.069567,"geom":{"type":"Point","crs":{"type":"name","properties":{"name":"EPSG:4326"}},"coordinates":[-71.88674926757812,19.06956672668457]}}, 
 {"id":5,"lon":-65.96288,"lat":19.045403,"geom":{"type":"Point","crs":{"type":"name","properties":{"name":"EPSG:4326"}},"coordinates":[-65.96288299560547,19.04540252685547]}}, 
 {"id":6,"lon":-66.61304,"lat":39.382282,"geom":{"type":"Point","crs":{"type":"name","properties":{"name":"EPSG:4326"}},"coordinates":[-66.613037109375,39.38228225708008]}}, 
 {"id":7,"lon":-89.889305,"lat":37.46081,"geom":{"type":"Point","crs":{"type":"name","properties":{"name":"EPSG:4326"}},"coordinates":[-89.8893051147461,37.460811614990234]}}, 
 {"id":8,"lon":-82.464355,"lat":39.511333,"geom":{"type":"Point","crs":{"type":"name","properties":{"name":"EPSG:4326"}},"coordinates":[-82.46435546875,39.51133346557617]}}, 
 {"id":9,"lon":-60.76739,"lat":44.584244,"geom":{"type":"Point","crs":{"type":"name","properties":{"name":"EPSG:4326"}},"coordinates":[-60.767391204833984,44.58424377441406]}}, 
 {"id":10,"lon":-110.2678,"lat":38.606846,"geom":{"type":"Point","crs":{"type":"name","properties":{"name":"EPSG:4326"}},"coordinates":[-110.2677993774414,38.60684585571289]}}]

This query uses the API to select the row where id is 1:

# run a query to get id == 1
!curl "http://localhost:3000/plot?id=eq.1" -H "Accept: application/geo+json" --silent

{"type" : "FeatureCollection", "features" : [{"type": "Feature", "geometry": {"type":"Point","coordinates":[-126.234413147,23.477777481]}, "properties": {"id": 1, "lon": -126.23441, "lat": 23.477777}}]}

note: we asked for the result to be returned as a GeoJSON with the Accept header!

Try writing data to the plot table.

!curl http://localhost:3000/plot -X POST -H "Content-Type: application/json" \
  -d '{"lon": -90.0, "lat": 45.0}' \
   --silent

{"code":"42501","details":null,"hint":null,"message":"permission denied for table plot"}

It doesn’t work! That’s a good thing. We could have set up the API’s credentials with write privileges but we did not, so this is exactly what we want to see. It is possible to configure an endpoint with write privileges but I have not tried it yet.

query using Python

We can use the requests library to send queries to PostgREST. This query uses the query_bbox function that we defined in setup_db.sql, which is conveniently set up as an endpoint in our API.

import requests

bbox = (-95.9190, 45.7978, -90.6841, 48.1250)
r = requests.post(
    "http://localhost:3000/rpc/query_bbox",
    json={"xmin": bbox[0], "xmax": bbox[1], "ymin": bbox[2], "ymax": bbox[3]},
    headers={"Accept": "application/geo+json"},
)

note: all user-defined functions get dropped under the rpc/ prefix, see the docs for more info

Since we requested the data be returned in the GeoJSON format, we can load the returned data into a geopandas.GeoDataFrame very easily:

import geopandas as gpd

gdf = gpd.GeoDataFrame.from_features(r.json(), crs=4326)
gdf

	geometry	id	lon	lat
0	POINT (-91.33234 45.80272)	81	-91.332340	45.802715
1	POINT (-93.76649 46.58055)	112	-93.766490	46.580555
2	POINT (-91.07545 47.01840)	192	-91.075455	47.018402
3	POINT (-93.52634 47.05753)	590	-93.526344	47.057533
4	POINT (-91.31033 47.63699)	820	-91.310326	47.636990

Or, we can drop the GeoJSON directly into an interactive map using folium:

import folium
import geojson_pydantic

center = ((bbox[1] + bbox[3]) / 2, (bbox[0] + bbox[2]) / 2)

m = folium.Map(location=center, tiles="Stamen Toner", zoom_start=7)

# add bounding box to map
bbox_geojson = folium.GeoJson(
    data=geojson_pydantic.Feature(
        geometry=geojson_pydantic.Polygon.from_bounds(*bbox)
    ).dict(exclude_none=True),
    style_function=lambda x: {
        "opacity": 1,
        "dashArray": "1",
        "fillOpacity": 0,
        "weight": 2,
    },
)
bbox_geojson.add_to(m)

# add points to map
plots_geojson = folium.GeoJson(data=r.json())
plots_geojson.add_to(m)

m

Make this Notebook Trusted to load map: File -> Trust Notebook

Conclusion

Hopefully this is enough to get you up and running with a REST API for your own PostGIS database! I highly recommend paging through the PostgREST documentation, so if you are left wondering about the possibilities for a REST API for your database, definitely check out the full documentation.