Distance and radius in PostgreSQL with Drizzle ORM

PostgreSQL provides many options for working with geographical data, especially with the PostGIS extension. In this article, we’ll explore how to calculate the distance between two coordinates and how to identify locations within a specific radius.

If you want to check out the basics of storing coordinates in PostgreSQL with the Drizzle ORM, check out API with NestJS #182. Storing coordinates in PostgreSQL with Drizzle ORM

Spatial Reference System Identifier

We will use a straightforward database schema that contains a table with locations.

1import { serial, text, pgTable, geometry } from 'drizzle-orm/pg-core';
2 
3export const locations = pgTable('locations', {
4  id: serial().primaryKey(),
5  name: text().notNull(),
6  coordinates: geometry('coordinates', {
7    type: 'point',
8    mode: 'xy',
9    srid: 4326,
10  }).notNull(),
11});
12 
13export const databaseSchema = {
14  locations,
15};

Above, we specify the Spatial Reference System Identifier (SRID). This number tells PostGIS how to interpret the coordinates. It specifies one of several coordinate systems used to represent the data.

• In SRID 4326, the coordinates are defined in latitude and longitude.
• In SRID 3857, the coordinates are specified in meters on a flat map.

If two sets of coordinates use different SRIDs, they are based on different systems and won’t line up correctly. For example, comparing latitude/longitude directly to flat map coordinates will give incorrect results.

The default SRID in PostGIS is 0, which means the data has no defined coordinate system. It’s good practice to define the SRID for our data explicitly.

Let’s create the SQL migration to add our table to the database.

If you want to know more about migrations with the Drizzle ORM, check out API with NestJS #176. Database migrations with the Drizzle ORM

1npx drizzle-kit generate --name add-locations-table

1CREATE TABLE IF NOT EXISTS "locations" (
2  "id" serial PRIMARY KEY NOT NULL,
3  "name" text NOT NULL,
4  "coordinates" geometry(point) NOT NULL
5);

Unfortunately, right now, Drizzle ORM has a bug that causes it to ignore the provided SRID. To deal with that, we have to adjust our migration manually. Let’s also add a line ensuring the PostGIS extension is set up.

1CREATE EXTENSION IF NOT EXISTS postgis;
2 
3CREATE TABLE IF NOT EXISTS "locations" (
4  "id" serial PRIMARY KEY NOT NULL,
5  "name" text NOT NULL,
6  "coordinates" geometry(point, 4326) NOT NULL
7);

Calculating the distance between two points

Let’s insert a few locations into our database:

• Empire State Building in New York
• Santa Monica Pier in the Los Angeles County
• Times Square in New York

Let’s calculate the distance between the Empire State Building and the Santa Monica Pier. To do that, we can use the ST_DistanceSphere function built into PostgreSQL.

1SELECT ST_DistanceSphere(
2  (SELECT coordinates FROM locations WHERE id = 1),
3  (SELECT coordinates FROM locations WHERE id = 2)
4) as distance;

The result is roughly 3960 kilometers (about 2460 miles). What’s crucial is that the ST_DistanceSphere function takes into account that Earth is a sphere. To use it with the Drizzle ORM, we need to provide the raw SQL.

1import { Injectable, NotFoundException } from '@nestjs/common';
2import { DrizzleService } from '../database/drizzle.service';
3import { databaseSchema } from '../database/database-schema';
4import { sql } from 'drizzle-orm';
5 
6@Injectable()
7export class LocationsService {
8  constructor(private readonly drizzleService: DrizzleService) {}
9 
10  async getDistanceBetweenLocations(
11    firstLocationId: number,
12    secondLocationId: number,
13  ) {
14    const queryResult = await this.drizzleService.db.execute(
15      sql`
16        SELECT ST_DistanceSphere(
17          (SELECT coordinates FROM ${databaseSchema.locations} WHERE id = ${firstLocationId}),
18          (SELECT coordinates FROM ${databaseSchema.locations} WHERE id = ${secondLocationId})
19        ) AS distance;
20      `,
21    );
22 
23    const distance = queryResult.rows.pop()?.distance;
24 
25    if (distance === null) {
26      throw new NotFoundException();
27    }
28 
29    return distance;
30  }
31 
32  // ...
33}

The Earth is not a perfect sphere

What’s interesting is that ST_DistanceSphere assumes that Earth is a perfect sphere. However, this is not the case. To be more precise, we can use the ST_DistanceSpheroid function, which accounts for Earth’s ellipsoidal shape.

1SELECT ST_DistanceSpheroid(
2  (SELECT coordinates FROM locations WHERE id = 1),
3  (SELECT coordinates FROM locations WHERE id = 2),
4  'SPHEROID["WGS 84",6378137,298.257223563]'
5) AS distance;

By providing SPHEROID["WGS 84",6378137,298.257223563] we specify the Earth’s shape, using the WGS84 model.

The above query results in roughly 3969 kilometers (about 2466 miles). It is 9 kilometers more than the ST_DistanceSphere function returned. If we want to calculate big distances, taking the Earth’s shape into account can give us more precise results.

Getting locations in a certain radius

Another very useful feature is finding locations in a certain radius. To do that, we need to use the ST_DWithin function built into PostGIS.

So far, we’ve been using the geometry data type built into PostGIS that treats Earth as a flat 2D plane. However, when combined with functions such as ST_DistanceSpheroid, which we used above, it can calculate distances on a spherical Earth.

Alternatively, PostGIS implements the geography data type, which automatically accounts for the Earth’s ellipsoidal shape. Unfortunately, the Drizzle ORM does not currently support this type of data. To use the ST_DWithin function, we need to convert our data to geography on the fly.

1SELECT * FROM locations
2WHERE ST_DWithin(
3  coordinates::geography,
4  (SELECT coordinates::geography FROM locations WHERE id = 1),
5  5000 -- Radius in meters
6);

Above, we find all the locations in a 5-kilometer radius of the Empire State Building in New York.

We can exclude the Empire State Building from our results to receive more meaningful results.

1SELECT * FROM locations
2WHERE id != 1
3AND ST_DWithin(
4  coordinates::geography,
5  (SELECT coordinates::geography FROM locations WHERE id = 1),
6  5000 -- Radius in meters
7);

To use ST_DWithin with the Drizzle ORM, we need to incorporate raw SQL into our code. Drizzle ORM handles most of the query, while the raw SQL is only used partly in the WHERE clause.

1import { Injectable } from '@nestjs/common';
2import { DrizzleService } from '../database/drizzle.service';
3import { databaseSchema } from '../database/database-schema';
4import { ne, sql, and } from 'drizzle-orm';
5 
6@Injectable()
7export class LocationsService {
8  constructor(private readonly drizzleService: DrizzleService) {}
9 
10  getLocationsInRadius(locationId: number, radiusInMeters: number) {
11    return this.drizzleService.db
12      .select()
13      .from(databaseSchema.locations)
14      .where(
15        and(
16          ne(databaseSchema.locations.id, locationId),
17          sql`ST_DWithin(
18            ${databaseSchema.locations.coordinates}::geography,
19            (SELECT ${databaseSchema.locations.coordinates}::geography FROM ${databaseSchema.locations} WHERE id = ${locationId}),
20            ${radiusInMeters}
21          )`,
22        ),
23      );
24  }
25  
26  // ...
27}

Summary

In this article, we learned how to use PostGIS with the Drizzle ORM to calculate the distance between two coordinates and find all locations within a certain radius.

PostgreSQL offers many functionalities for various coordinate calculations thanks to the PostGIS extension. While the integration with Drizzle ORM isn’t perfect, we can work around most problems to get the job done. Assuming that the Drizzle team continues to improve the ORM, it has the potential to become a solid choice for handling spatial data.