Distance Based Queries with PostgreSQL, PostGIS, and Sequelize

This post assumes you are familar enough with node, express, sequelize, and postgreSQl to spin up a basic express server and connect it to a Postgres database using sequelize.

When I attempted to first create an api that would find users within a certain distance of an alert, I was unable to find any working examples that itegrated Sequelize queries and PostGIS. Below you’ll find an example of how to enable PostGIS and one method of formatting your queries.

Luckily, most common installations of Postgres include PostGIS, but if you are unlucky head on over to postgis.net/install for instructions. FYI, their first instruction will be to install a current version of Postgres.

On the command line use psql to create your database and enable PostGIS

1
2
3
4
psql
CREATE DATABASE geoBase
\connect geoBase
CREATE EXTENSION POSTGIS;

You will now be blessed with spatial functions and object types in your database. Specifically, you can now use the Sequelize.GEOMETRY and GEOGRAPHY data types and the PostGIS functions are available to you.

In our database we have list of users at specific “points”. A Sequelize.GEOMETRY(‘POINT’) is stored in your database by first creating a point object and storing it in your db. Sequelize will convert your point into geometry for you. YAY! (LineStrings and Polygons are also valid geometry types)

Example Model definition:

1
2
3
4
5
6
7
8
9
10
11
const Sequelize = require('sequelize')
const db = require('./_db')
const Alert = db.define('alert', {
  deviceId: Sequelize.STRING,
  position: Sequelize.GEOMETRY('POINT', 4326),
  codename: Sequelize.STRING,
  message: Sequelize.STRING
})
module.exports = Alert

If you get an error that type Geometry does not exist, make sure you go back into psql and enable PostGIS.

We are using the 4326 SRID which will give us distances in degrees. We can then convert to our distance unit of choice.

Creating points in the db:

1
2
3
4
5
6
7
8
9
10
11
12
router.post('/alerts', (req, res, next) => {
  let point = {
    type: 'Point',
    coordinates: [req.body.long, req.body.lat],
    crs: { type: 'name', properties: { name: 'EPSG:4326'} }
  }
  Alert.create({
    message: req.body.message,
    codename: req.body.message,
    position: point
  })

Sample query to find all within 0.032 deg which is approximately 2 miles.

1
2
3
4
5
6
7
8
9
10
11
12
13
Alert.findAll({
  where: Sequelize.where(
    Sequelize.fn('ST_DWithin',
      Sequelize.col('position'),
      Sequelize.fn('ST_SetSRID',
        Sequelize.fn('ST_MakePoint',
          req.query.long, req.query.lat),
        4326),
      0.032),
    true)
})
.then(alerts => res.send(alerts))
.catch(next)

It is very important to note that Sequelize uses the ST_GeomFromGeoJSON PostGIS method to create points. GeoJSON coordinates require that longitude is first and latitude is second. This is counter intuitive to those of us accustomed to using latitude first, but very important when creating points to measure distance. The sequelize docs and issues history also appear to have confused lat/long. The PostGIS docs and GeoJSON spec are very explicit as to the order.