API with NestJS #184. Storing PostGIS Polygons in PostgreSQL with Drizzle ORM

January 20, 2025

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

PostgreSQL, together with PostGIS, allows us to store various types of geographical data. Besides working with simple coordinates, we can also store entire areas in the form of polygons. In this article, we learn how to handle polygons with PostgreSQL and the Drizzle ORM. While Drizzle ORM does not support it out of the box, we can create a custom type to handle it.

If you want to know the basics of working with geographical data using PostGIS and the Drizzle ORM, check out the following articles:

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

API with NestJS #183. Distance and radius in PostgreSQL with Drizzle ORM

Polygons

A polygon is a two-dimensional object that represents a flat area with a defined boundary. A straightforward way of defining a polygon with PostGIS is to use the POLYGON() function. We need to provide the coordinates of each point of our polygon.

SELECT 'SRID=4326;POLYGON((

-73.981898 40.768094,

-73.958094 40.800621,

-73.949282 40.796853,

-73.973057 40.764356,

-73.981898 40.768094

))'::geometry;

Above, we use SRID 4326 to let PostGIS know that we defined coordinates with latitude and longitude.

It’s important to note that the first and last pair of coordinates must be identical. This ensures that our polygon is a properly enclosed geometric shape.

The outer and inner ring

Each polygon needs an outer ring. It defines the outer boundary of the polygon. Optionally, we can provide inner rings that represent holes in the polygon. Each inner ring should be fully contained within the outer ring and shouldn’t overlap or touch the outer boundary.

SELECT 'SRID=4326;POLYGON((

-73.981898 40.768094,

-73.958094 40.800621,

-73.949282 40.796853,

-73.973057 40.764356,

-73.981898 40.768094

), (

-73.970000 40.780000,

-73.965000 40.780000,

-73.965000 40.775000,

-73.970000 40.775000,

-73.970000 40.780000

), (

-73.960000 40.790000,

-73.955000 40.790000,

-73.955000 40.785000,

-73.960000 40.785000,

-73.960000 40.790000

))'::geometry;

The GeoJSON format

Alternatively, we can use the GeoJSON type to represent our polygon. It includes the type of our geometry data and an array of rings in our polygon.

SELECT '{

"type": "Polygon",

"coordinates": [

[

[-73.981898, 40.768094],

[-73.958094, 40.800621],

[-73.949282, 40.796853],

[-73.973057, 40.764356],

[-73.981898, 40.768094]

[

[-73.970000, 40.780000],

[-73.965000, 40.780000],

[-73.965000, 40.775000],

[-73.970000, 40.775000],

[-73.970000, 40.780000]

[

[-73.960000, 40.790000],

[-73.955000, 40.790000],

[-73.955000, 40.785000],

[-73.960000, 40.785000],

[-73.960000, 40.790000]

]

}'::geometry;

The first array defines the outer ring. The following arrays define the inner rings. Finally, each coordinates pair is an array with two element. The first one is the longitude, the second one is the latitude.

Using polygons with the Drizzle ORM and NestJS

Unfortunately, the Drizzle ORM does not support polygons out of the box. Let’s create a custom type to handle it.

database-schema.ts

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

import { customType } from 'drizzle-orm/pg-core';

const polygon = customType({

dataType() {

return 'geometry(Polygon, 4326)';

});

export const areas = pgTable('areas', {

id: serial().primaryKey(),

polygon: polygon('polygon').notNull(),

});

export const databaseSchema = {

areas,

};

Thanks to writing the dataType function, creating a migration results in the following SQL query:

CREATE TABLE IF NOT EXISTS "areas" (

"id" serial PRIMARY KEY NOT NULL,

"name" text NOT NULL,

"polygon" geometry(polygon, 4326) NOT NULL

);

Storing data

The most straightforward way to store our polygons in the database while using Drizzle ORM is to provide them in the GeoJSON format. To let the users provide them as an array, we need to write the toDriver method that converts the polygon data into a valid GeoJSON format.

database-schema.ts

import { customType } from 'drizzle-orm/pg-core';

type Coordinate = [number, number];

const polygon = customType<{ data: Coordinate[][] }>({

dataType() {

return 'geometry(Polygon, 4326)';

toDriver(coordinates: Coordinate[][]): string {

return JSON.stringify({

type: 'Polygon',

coordinates,

});

// ...

As we’ve learned before, the polygon in the GeoJSON data is an three-dimensional array.

Now, we can use Drizzle to store polygons in the database.

this.drizzleService.db

.insert(databaseSchema.areas)

.values({

polygon: [

[

[-73.981898, 40.768094],

[-73.958094, 40.800621],

[-73.949282, 40.796853],

[-73.973057, 40.764356],

[-73.981898, 40.768094],

})

.returning();

Validating the data

When using NestJS, we often use the class-validator library to validate the data sent by the user. While there is no built-in decorator to handle polygons, we can create a custom validator.

area.dto.ts

import { IsString, IsNotEmpty, Validate } from 'class-validator';

import { ArePolygonCoordinates } from './are-polygon-coordinates';

export class AreaDto {

@IsString()

@IsNotEmpty()

@Validate(ArePolygonCoordinates)

polygon: [number, number][][];

}

To do it, we can create a class using the validate method.

are-polygon-coordinates.ts

import {

ValidationArguments,

ValidatorConstraint,

ValidatorConstraintInterface,

} from 'class-validator';

@ValidatorConstraint()

export class ArePolygonCoordinates implements ValidatorConstraintInterface {

validate(value: unknown) {

// Coordinates must be an array of polygons.

if (!Array.isArray(value) || value.length === 0) {

return false;

}

// Test each polygon

for (const polygon of value) {

if (!this.validatePolygon(polygon)) {

return false;

}

return true;

}

defaultMessage({ property }: ValidationArguments) {

return `${property} must be valid GeoJSON polygon coordinates`;

}

// ...

}

Our validatePolygon method verifies if every polygon is valid.

If you want to implement more strict validation, you can check if the first and last pair of coordinates in a polygon is identical.

are-polygon-coordinates.ts

import {

ValidationArguments,

ValidatorConstraint,

ValidatorConstraintInterface,

isLatitude,

isLongitude,

} from 'class-validator';

@ValidatorConstraint()

export class ArePolygonCoordinates implements ValidatorConstraintInterface {

validate(value: unknown) {

// Coordinates must be an array of polygons.

if (!Array.isArray(value) || value.length === 0) {

return false;

}

// Test each polygon

for (const polygon of value) {

if (!this.validatePolygon(polygon)) {

return false;

}

return true;

}

validatePolygon(polygon: unknown) {

// Polygons must be an array of coordinates.

if (!Array.isArray(polygon)) {

return false;

}

// Test every coordinate

for (const coordinates of polygon) {

if (!this.validateCoordinates(coordinates)) {

return false;

}

return true;

}

// ...

}

The validateCoordinates function uses the isLatitude and isLongitude functions built into the class-validator library.

are-polygon-coordinates.ts

import {

ValidationArguments,

ValidatorConstraint,

ValidatorConstraintInterface,

isLatitude,

isLongitude,

} from 'class-validator';

@ValidatorConstraint()

export class ArePolygonCoordinates implements ValidatorConstraintInterface {

validate(value: unknown) {

// Coordinates must be an array of polygons.

if (!Array.isArray(value) || value.length === 0) {

return false;

}

// Test each polygon

for (const polygon of value) {

if (!this.validatePolygon(polygon)) {

return false;

}

return true;

}

validatePolygon(polygon: unknown) {

// Polygons must be an array of coordinates.

if (!Array.isArray(polygon)) {

return false;

}

// Test every coordinate

for (const coordinates of polygon) {

if (!this.validateCoordinates(coordinates)) {

return false;

}

return true;

}

validateCoordinates(coordinates: unknown) {

// Coordinates need to be arrays with two elements

if (!Array.isArray(coordinates) || coordinates.length !== 2) {

return false;

}

const [longitude, latitude] = coordinates;

return isLongitude(longitude) && isLatitude(latitude);

}

defaultMessage({ property }: ValidationArguments) {

return `${property} must be valid GeoJSON polygon coordinates`;

}

We can now use our AreaDto class in the controller to validate the data sent by the user.

areas.controller.ts

import { Body, Controller, Post } from '@nestjs/common';

import { AreasService } from './areas.service';

import { AreaDto } from './dto/area.dto';

@Controller('areas')

export class AreasController {

constructor(private readonly areasService: AreasService) {}

@Post()

create(@Body() area: AreaDto) {

return this.areasService.create(area);

}

// ...

}

We can also use it in our service.

areas.service.ts

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

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

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

import { AreaDto } from './dto/area.dto';

@Injectable()

export class AreasService {

constructor(private readonly drizzleService: DrizzleService) {}

async create(area: AreaDto) {

const createdAreas = await this.drizzleService.db

.insert(databaseSchema.areas)

.values({

polygon: area.polygon,

})

.returning();

return createdAreas.pop();

}

// ...

}

Retrieving data

By default, PostgreSQL returns the polygon data in the WKB (Well-Known Binary) format.

However, this format is not very readable. To retrieve the data in the GeoJSON format, we can use the ST_AsGeoJSON function built into PostGIS.

SELECT id, name, ST_AsGeoJSON(polygon)

FROM areas

WHERE id = 1;

Unfortunately, right now, we can’t tell Drizzle ORM to use the ST_AsGeoJSON when retrieving the data from a particular column. Until this PR is merged, we can use the wkx library in our fromDriver method to convert the WKB format to GeoJSON.

database-schema.ts

import { customType } from 'drizzle-orm/pg-core';

import { Geometry } from 'wkx';

type Coordinate = [number, number];

interface GeoJson {

type: string;

coordinates: Coordinate[][];

}

const polygon = customType<{ data: Coordinate[][]; driverData: string }>({

dataType() {

return 'geometry(Polygon, 4326)';

toDriver(coordinates: Coordinate[][]): string {

return JSON.stringify({

type: 'Polygon',

coordinates,

});

fromDriver(data: string) {

const geoJson = Geometry.parse(

Buffer.from(data, 'hex'),

).toGeoJSON() as GeoJson;

return geoJson.coordinates;

});

// ...

Thanks to this change, our data is converted to GeoJSON, which is much easier to read.

Summary

In this lesson, we’ve learned how to store PostGIS polygons in a PostgreSQL database using the Drizzle ORM. Since it’s not supported out of the box, we had to create a custom Drizzle type to handle it. We also wrote a custom validator to ensure that users provide the polygons in the correct format. All that gives us solid foundations to work with geographical data effectively in our applications.

Series Navigation<< API with NestJS #183. Distance and radius in PostgreSQL with Drizzle ORM