Dirt-Simple PostGIS HTTP API

The Dirt-Simple PostGIS HTTP API, or dirt, exposes PostGIS functionality to your applications over HTTP.

Important Note!

Dirt is now optimized for Postgis 3. If you're using Postgis 2.x, use the postgis2x branch.

Getting started

Requirements

• Node
• PostgreSQL with PostGIS 3
• A PostgreSQL login for the service that has select rights to any tables or views you want to expose to dirt.

Step 1: get the goodies

Note: if you don't have git, you can download a zip file of the project instead.

git clone https://github.com/tobinbradley/dirt-simple-postgis-http-api.git dirt
cd dirt
npm install

Step 2: add your configuration

Dirt is configured via environmental variables. These variables can be placed in a .env file in the project's root folder, via the command line at run time, or however you set environmental variables on your operating system. The only environmental variable that must be set is POSTGRES_CONNECTION, which contains your postgres login information.

.env file

POSTGRES_CONNECTION="postgres://user:password@server/database"

command line, linux and mac

POSTGRES_CONNECTION="postgres://user:password@server/database" npm start

This is the complete complete list of environmental variables that can be set.

Step 3: fire it up!

npm start

To view interactive documentation, head to http://127.0.0.1:3000/.

Running via Docker

To build a Docker image:

docker build -t dirt .

To run the Docker image:

docker run -dp 3000:3000 -e POSTGRES_CONNECTION=<connection string> dirt

Architecture

Due credit

The real credit for this project goes to the great folks behind the following open source software:

• PostgreSQL
• PostGIS
• Fastify

How it works

The core of the project is Fastify.

Fastify is a web framework highly focused on providing the best developer experience with the least overhead and a powerful plugin architecture. It is inspired by Hapi and Express and as far as we know, it is one of the fastest web frameworks in town.

Fastify is written by some of the core Node developers, and it's awesome. A number of Fastify plugins (fastify-autoload, fastify-caching, fastify-compress, fastify-cors, fastify-postgres, and fastify-swagger) are used to abstract away a lot of boilerplate. If you're looking for additional functionality, check out the Fastify ecosystem.

All routes are stored in the routes folder and are automatically loaded on start. Check out the routes readme for more information.

Tips and Tricks

Database

Your Postgres login will need select rights to any tables or views it should be able to access. That includes the geometry_columns view for the list_layers end point to work.

For security, it should only have select rights unless you plan to specifically add a route that writes to a table.

Dirt uses connection pooling, minimizing database connections.

SQL Functions

If a query parameter looks like it should be able to handle SQL functions, it probably can. For example, the columns parameter for most queries can use the count(*) function. You can use any function in the database, including user defined functions.

Mapbox vector tiles

The mvt route serves Mapbox Vector Tiles. The layer name in the returned protobuf will be the same as the table name passed as input. Here's an example of using both geojson and mvt routes with MapLibre GL JS.

map.on('load', function() {
  map.addLayer({
    id: 'dirt-mvt',
    source: {
      type: 'vector',
      tiles: ['http://localhost:3000/v1/mvt/voter_precinct/{z}/{x}/{y}'],
      maxzoom: 14,
      minzoom: 5
    },
    'source-layer': 'voter_precinct',
    type: 'fill',
    minzoom: 5,
    paint: {
      'fill-color': '#088',
      'fill-outline-color': '#333'
    }
  })

  map.addLayer({
    id: 'dirt-geojson',
    type: 'circle',
    source: {
      type: 'geojson',
      data: 'http://localhost:3000/v1/geojson/voter_polling_location'
    },
    paint: {
      'circle-radius': 2,
      'circle-color': '#bada55'
    }
  })
})

Changes require a Restart

If you modify code or add a route, dirt will not see it until dirt is restarted.