Building Spatial APIs in PostgreSQL with PostgREST

This blog encompasses queries, installations and steps taken during workshop happening at FOSS4G-EU in Tartu this year by Krishna Lodha from Rotten Grapes Private Ltd.

Installation

This workshop uses various softwares such as - PostgreSQL/PostGIS - Swagger API - pgAdmin - PostgREST - Docker

We'll start by creating docker-compose file with all configuration

version: '3'
services:
 server:
 image: postgrest/postgrest
 ports:
 - "3000:3000"
 environment:
 PGRST_DB_URI: postgres://app_user:password@db:5432/app_db
 PGRST_OPENAPI_SERVER_PROXY_URI: http://127.0.0.1:3000
 depends_on:
 - db
 db:
 image: kartoza/postgis
 ports:
 - "5511:5432"
 environment:
 POSTGRES_DBNAME: app_db
 POSTGRES_USER: app_user
 POSTGRES_PASSWORD: password
 volumes:
 - ./postgis:/var/lib/postgresql/data
 pgadmin:
 image: dpage/pgadmin4
 container_name: pgadmin4_container
 restart: always
 ports:
 - "8888:80"
 environment:
 PGADMIN_DEFAULT_EMAIL: krishna@rottengrapes.tech
 PGADMIN_DEFAULT_PASSWORD: foss4g
 volumes:
 - ./pgadmin:/var/lib/pgadmin
 swagger:
 image: swaggerapi/swagger-ui
 ports:
 - "8811:8080"
 expose:
 - "8080"
 environment:
 API_URL: http://localhost:3000/

Once this is up and running, you should be able to see things working

Postgres

pgAdmin

swagger API

PostgreSQL Configuration

Let us start by configuring Postgres.

Add some dummy data

We'll start by adding few non-spatial as well as spatial.

Non-spatial table

-- Create sample table
create table pokemon (
 id int primary key generated by default as identity,
 own boolean not null default false,
 name text not null,
 last_seen timestamptz
);
-- Add some sample data
insert into pokemon (own,name,last_seen) values
 ('True', 'pikachu', '2023-01-01'), ('False', 'Charmander', '2024-12-30'), ('True', 'Squirtle', '2023-01-01')
-- See if data is added correctly
select * from pokemon

Spatial table

PostgREST Configuration

Now that we have data , it is time to setup Authorisation.

GET Data

We'll start by creating anonymous user first which will have read only access. This role will be used by PostgREST as well.

create role web_anon nologin;

grant usage on schema public to web_anon;
grant select on pokemon to web_anon;

It is time for us to configure PostgREST, to do that we'll first stop docker-compose, and start making edits in the environment variables

server:
 image: postgrest/postgrest
 ports:
 - "3000:3000"
 environment:
 PGRST_DB_URI: postgres://app_user:password@db:5432/app_db
 PGRST_DB_ANON_ROLE: web_anon
 PGRST_OPENAPI_SERVER_PROXY_URI: http://127.0.0.1:3000
 depends_on:
 - db

now restart the docker-compose and visit swagger endpoint again, you'll start seeing endpoints which included tables, stored procedures,etc.

Now you can start by hitting GET call for pokemon

curl -X 'GET' \
 'http://127.0.0.1:3000/pokemon' \
 -H 'accept: application/json' \
 -H 'Range-Unit: items'

you can also use the form above to query the data. Checkout operators here.

POST Data

If you try to use POST call, you'll get authentication error, since we have only given read access to the role.

curl -X 'POST' \
 'http://127.0.0.1:3000/pokemon' \
 -H 'accept: application/json' \
 -H 'Content-Type: application/json' \
 -d '{
 "own": false,
 "name": "meow",
 "last_seen": "2021-01-01"
}'

Using JWT token for role

One of the simplest way to allow users to post data is by creating new user which will have posting rights. We want to create this user keeping in mind that we'll be passing some kind of authentication in API to get the role identity.

Let us start by creating a role

create role post_user nologin;

grant usage on schema public to post_user;
grant all on pokemon to post_user;

Now we'll be adding one more variable in docker-compose file for server

 server:
 image: postgrest/postgrest
 ports:
 - "3000:3000"
 environment:
 PGRST_DB_URI: postgres://app_user:password@db:5432/app_db
 PGRST_DB_ANON_ROLE: web_anon
 PGRST_JWT_SECRET : Iamlongcharactersecrtocodewhichishardtocrack
 PGRST_OPENAPI_SERVER_PROXY_URI: http://127.0.0.1:3000
 depends_on:
 - db

now we can create JWT token from jwt.io using role and secret key

Dealing with PostGIS

Now we'll start dealing with Spatial data.

Get data

Let's start with viewing the pokemon centers. We'll give read access to web_anon

grant select on pokecenters to web_anon;

Now you can start getting data from the table via API

curl -X 'GET' \
 'http://127.0.0.1:3000/pokecenters' \
 -H 'accept: application/json' \
 -H 'Range-Unit: items'

Interestingly, PostGIS also allows to get data back as GeoJSON by setting correct Accept value

curl --location 'http://127.0.0.1:3000/pokecenters' \
--header 'Accept: application/geo+json'

Use PostGIS function

PostGIS functions are available to use as rpc. It is important to read the definition of function correctly before using it.

ST_Intersects

ST_Intersects returns true/false depending upon whether the geometries passed as params intersects or not. Swagger API might not always have the params what you desired, reason being that there can be multiple functions with same name, thus swagger will pickup always the last one.

You can refer to the function params in pgAdmin and then make the function call.

curl --location 'http://127.0.0.1:3000/rpc/_st_intersects?geom1=POINT(1 1)&geom2=POLYGON((0 0,2 0,2 2,0 2,0 0))'

ST_Transform

ST_Transform is used to reproject geometry based on current projection and desired projection.

curl --location 'http://127.0.0.1:3000/rpc/st_transform?geom=POINT(1%201)&from_proj=EPSG%3A4326&to_srid=3857'

Create Independent functions

We can also create our own functions which can take input, process the data based on PostGIS functions and spits out the results. Let us create a simple function which will take longitude and latitude as parameter and find out distance between given location and Tartu.

CREATE OR REPLACE FUNCTION how_far_is_tartu(
 lon DOUBLE PRECISION,
 lat DOUBLE PRECISION
)
RETURNS DOUBLE PRECISION AS $$
DECLARE
 target_geography GEOGRAPHY := ST_SetSRID(ST_MakePoint(26.71638461354608, 58.3732218646717), 4326)::GEOGRAPHY;
 input_geography GEOGRAPHY := ST_SetSRID(ST_MakePoint(lon, lat), 4326)::GEOGRAPHY;
 distance DOUBLE PRECISION;
BEGIN
 -- Calculate the distance in meters using ST_DistanceSphere
 distance := st_distance(input_geography, target_geography)/1000;

 RETURN distance;
END;
$$ LANGUAGE plpgsql;

Run above function and restart docker service for postgREST. Once done, you will be able to find out function in swagger and then you can start using the function.

curl -X 'GET' \
 'http://127.0.0.1:3000/rpc/how_far_is_tartu?lon=73.626385&lat=19.9910' \
 -H 'accept: application/json'

Create table dependent functions

We can also create a function which can be combination of parameters and tables in postgres. Let us create a simple function to find out nearest pokecenter to us. Let's add new function in PostGIS

CREATE OR REPLACE FUNCTION find_nearest_pokecenter(geojson_feature text)
RETURNS json AS $$
DECLARE
 input_geom geometry;
 nearest_id int;
 nearest_name varchar;
 nearest_location geometry;
 geojson_output json;
BEGIN
 -- Parse the GeoJSON feature to a geometry
 SELECT ST_SetSRID(ST_GeomFromGeoJSON(geojson_feature), 4326) INTO input_geom;

 -- Find the nearest pokecenter
 SELECT pc.id, pc.name, pc.location
 INTO nearest_id, nearest_name, nearest_location
 FROM pokecenters pc
 ORDER BY input_geom <-> pc.location
 LIMIT 1;

 -- Construct the GeoJSON with distance property
 geojson_output := json_build_object(
 'type', 'Feature',
 'geometry', ST_AsGeoJSON(nearest_location)::json,
 'properties', json_build_object(
 'id', nearest_id,
 'name', nearest_name
 )
 );

 RETURN geojson_output;
END;
$$ LANGUAGE plpgsql;

Now we can restart docker container and explore function as API.

curl --location --globoff 'http://127.0.0.1:3000/rpc/find_nearest_pokecenter?geojson_feature={"type": "Point", "coordinates": [19.912695608276465,41.87481245473549]}'