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
-- Create sample table
create table pokecenters (
id int primary key generated by default as identity,
name text not null,
location geometry(Point, 4326)
);
-- Add some sample data
insert into pokecenters (name,location) values
('Mumbai', 'POINT(72.88826407512137 19.10985257722805)'),
('Nashik', 'POINT(73.76313261726506 19.976012115196763)'),
('Berlin', 'POINT(13.395655965898628 52.51437508411031)')
-- See if data is added correctly
select * from pokecenters
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
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
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]}'
