API with NestJS #182. Storing coordinates in PostgreSQL with Drizzle ORM

January 6, 2025

This entry is part 182 of 182 in the API with NestJS

Many applications rely on geographical data to calculate distances and track locations. PostgreSQL offers several ways to store geospatial data, each designed with different goals. In this article, we learn how to store coordinates when working with PostgreSQL and the Drizzle ORM.

Latitude and longitude

A coordinate consists of two numbers that pinpoint a location on Earth: latitude and longitude. Since the Earth is roughly spherical, degrees are used to measure coordinates.

Latitude tells us how far north or south a place is from the equator. The equator is a line that circles the Earth, dividing it into the northern and southern hemispheres.

The smallest value for for latitude is -90° and represents the South Pole. The largest value is +90° and represents the North Pole.

Longitude measures how far east or west a place is from the Prime Meridian. The Prime Meridian line divides the Earth into the eastern and western hemispheres.

The smallest value for longitude is -180° which is the farthest west. The largest value is +180° and is the farthest east.

Both latitude and longitude can also be divided into smaller units, such as minutes and seconds. For example, the Empire State Building in New York is located at:

40° 44′ 54.24″ N
- in other words, 40 degrees, 44 minutes, and 54,24 seconds north
73° 59′ 8.52″ W
- which is 73 degrees, 59 minutes, and 8.52 seconds west

Another common way to describe coordinates is in decimal format:

40.7484° N
73.9857° W

If no letters indicate the direction, we can assume that positive numbers mean north and east. Negative numbers, on the other hand, mean south and west. Usually, the first number is the latitude, and the second one is the longitude:

40.7484, -73.9857

Storing coordinates as numbers

The most straightforward way of storing coordinates in PostgreSQL is to use two separate columns.

database-schema.ts

import { serial, text, pgTable, doublePrecision } from 'drizzle-orm/pg-core';

export const addresses = pgTable('addresses', {

id: serial().primaryKey(),

lineOne: text('line_one').notNull(),

lineTwo: text('line_two'),

city: text().notNull(),

state: text(),

postalCode: text('postal_code').notNull(),

country: text().notNull(),

latitude: doublePrecision().notNull(),

longitude: doublePrecision().notNull(),

});

export const databaseSchema = {

addresses,

};

Above, we use the double column type to store the coordinates. Double provides better accuracy for small fractional differences than the float column type.

This approach might be enough if our application only needs to store and retrieve coordinates without performing complex geospatial operations. However, if we want to do operations such as calculating distances or finding nearby locations, we can use other data types built into PostgreSQL.

The Point data type

Another data type that comes to mind when storing coordinates is the Point. It stores coordinates representing a point in a two-dimensional space.

database-schema.ts

import { serial, text, pgTable, point } from 'drizzle-orm/pg-core';

export const addresses = pgTable('addresses', {

id: serial().primaryKey(),

lineOne: text('line_one').notNull(),

lineTwo: text('line_two'),

city: text().notNull(),

state: text(),

postalCode: text('postal_code').notNull(),

country: text().notNull(),

location: point().notNull(),

});

export const databaseSchema = {

addresses,

};

By default, the Drizzle ORM maps a Point to an array with two elements. If we want to use a dictionary with the x and y properties, we can use the xy mode.

database-schema.ts

import { serial, text, pgTable, point } from 'drizzle-orm/pg-core';

export const addresses = pgTable('addresses', {

id: serial().primaryKey(),

lineOne: text('line_one').notNull(),

lineTwo: text('line_two'),

city: text().notNull(),

state: text(),

postalCode: text('postal_code').notNull(),

country: text().notNull(),

location: point({

mode: 'xy',

}).notNull(),

});

export const databaseSchema = {

addresses,

};

The disadvantage of the Point type

Using the Point type allows us to perform various operations using PostgreSQL. For example, let’s use the <-> operator to calculate the distance between San Francisco and New York.

1	SELECT (point(37.7749, -122.4194) <-> point(40.7128, -74.0060));

Unfortunately, the above code has a significant issue. The Point data type assumes a flat 2D surface. Since it doesn’t account for the curvature of the Earth, using it for calculations can result in inaccuracies. While it might be suitable for small distances or rough estimations, it’s not enough on a global scale.

Using PostGIS

Thankfully, PostgreSQL supports more sophisticated ways of storing and operating on coordinates. To achieve this, we need the PostGIS extension.

database-schema.ts

import { serial, text, pgTable, geometry } from 'drizzle-orm/pg-core';

export const addresses = pgTable('addresses', {

id: serial().primaryKey(),

lineOne: text('line_one').notNull(),

lineTwo: text('line_two'),

city: text().notNull(),

state: text(),

postalCode: text('postal_code').notNull(),

country: text().notNull(),

location: geometry('location', { type: 'point', mode: 'xy' }).notNull(),

});

export const databaseSchema = {

addresses,

};

Let’s create a migration that adds our addresses table.

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

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

What’s important is that we need to alter it to manually enable PostGIS in our database.

0000_add-addresses-table.sql

CREATE EXTENSION IF NOT EXISTS postgis;

CREATE TABLE IF NOT EXISTS "addresses" (

"id" serial PRIMARY KEY NOT NULL,

"line_one" text NOT NULL,

"line_two" text,

"city" text NOT NULL,

"state" text,

"postal_code" text NOT NULL,

"country" text NOT NULL,

"location" geometry(point) NOT NULL

);

Inserting coordinates

Let’s create a DTO that validates if the address that the user provided is valid.

address.dto.ts

import {

IsString,

IsNotEmpty,

IsOptional,

IsLatitude,

IsLongitude,

ValidateNested,

} from 'class-validator';

import { Type } from 'class-transformer';

export class LocationDto {

@IsLatitude()

x: number;

@IsLongitude()

y: number;

}

export class AddressDto {

@IsString()

@IsNotEmpty()

lineOne: string;

@IsString()

@IsNotEmpty()

@IsOptional()

lineTwo: string;

@IsString()

@IsNotEmpty()

city: string;

@IsString()

@IsNotEmpty()

@IsOptional()

state: string;

@IsString()

@IsNotEmpty()

postalCode: string;

@IsString()

@IsNotEmpty()

country: string;

@ValidateNested()

@Type(() => LocationDto)

location: LocationDto;

}

Now, we can insert the address into our database.

address.service.ts

import { Injectable } from '@nestjs/common';

import { DrizzleService } from '../database/drizzle.service';

import { databaseSchema } from '../database/database-schema';

import { AddressDto } from './dto/address.dto';

@Injectable()

export class AddresssService {

constructor(private readonly drizzleService: DrizzleService) {}

async create(address: AddressDto) {

const createdAddresses = await this.drizzleService.db

.insert(databaseSchema.addresses)

.values({

lineOne: address.lineOne,

lineTwo: address.lineTwo,

city: address.city,

state: address.state,

postalCode: address.postalCode,

country: address.country,

location: address.location,

})

.returning();

return createdAddresses.pop();

}

// ...

}

Advantages of using PostGIS

PostGIS allows us to perform a wide range of operations on our geospatial data. What’s crucial is that PostGIS takes the curvature of the Earth into account when performing calculations. It makes PostGIS much more accurate than using a simple Point type when calculating distances over large areas. Besides simple operations, PostGIS also supports more advanced functions, such as calculating the area. On top of that, PostGIS includes spatial indexes, which are specifically optimized for working with geospatial data. This, and many more, makes PostGIS the best choice when working with geographical data with PostgreSQL.

Summary

In this article, we learned how to store coordinates with PostgreSQL, Drizzle ORM, and NestJS. To do that, we compared various different approaches, such as storing coordinates as numbers, using the Poinst data type, and taking advantage of the PostGIS extension.

For very basic applications, storing coordinates as plain numbers might be appropriate. However, if we would like to do various operations on them, we should use PostGIS so that PostgreSQL can take the curvature of the Earth into account. PostGIS provides accurate distance calculations and advanced operations, making it the preferred choice for more complex applications.

Series Navigation<< API with NestJS #181. Prepared statements in PostgreSQL with Drizzle ORM