API with NestJS #185. Operations with PostGIS Polygons in PostgreSQL and Drizzle

January 27, 2025

This entry is part 185 of 186 in the API with NestJS

When using PostgreSQL with PostGIS, we can do various operations using polygons. In this article, we learn how to do that both through raw SQL queries and the Drizzle ORM.

Storing polygons using the Drizzle ORM

To store polygons using the Drizzle ORM, we use a custom data type and the areas table.

database-schema.ts

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

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;

});

export const areas = pgTable('areas', {

id: serial().primaryKey(),

name: text().notNull(),

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

});

export const databaseSchema = {

areas,

};

If you want to read a detailed step-by-step explanation, check out API with NestJS #184. Storing PostGIS Polygons in PostgreSQL with Drizzle ORM

Checking if two polygons overlap

One of the operations on polygons we can do is to check if two different polygons overlap. Let’s first insert a few polygons into our database.

[

{

"id": 1,

"name": "Central Park",

"polygon": [

[

-73.981898,

40.768094

...

]

{

"id": 2,

"name": "Manhattan",

"polygon": [

[

-74.02,

40.7

...

]

The Central Park is in Manhattan in New York. Above, we’re providing the data in the GeoJSON format. If you want to know more, check out API with NestJS #184. Storing PostGIS Polygons in PostgreSQL with Drizzle ORM

To check if two polygons overlap, we can use the ST_Intersects function built into PostGIS.

SELECT ST_Intersects(

(SELECT polygon FROM areas WHERE id = 1),

(SELECT polygon FROM areas WHERE id = 2)

);

When we run it, the database returns a boolean indicating whether they overlap.

To check if polygons overlap with Drizzle ORM, we need to use the above SQL query in our service.

areas.service.ts

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

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

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

import { sql } from 'drizzle-orm';

@Injectable()

export class AreasService {

constructor(private readonly drizzleService: DrizzleService) {}

async areAreasOverlapping(firstAreaId: number, secondAreaId: number) {

const queryResult = await this.drizzleService.db.execute(

sql`

SELECT ST_Intersects(

(SELECT ${databaseSchema.areas.polygon} FROM ${databaseSchema.areas} WHERE id = ${firstAreaId}),

(SELECT ${databaseSchema.areas.polygon} FROM ${databaseSchema.areas} WHERE id = ${secondAreaId})

) AS "areAreasOverlapping";

);

const areAreasOverlapping = queryResult.rows.pop()?.areAreasOverlapping;

if (areAreasOverlapping === null) {

// One of the provided area ids don't exist in the database

throw new NotFoundException();

}

return areAreasOverlapping;

}

// ...

}

Checking if one polygon contains another polygon

When using ST_Intersects, we check if the polygons overlap or touch each other. The order of the provided polygons does not matter. If the first polygon intersects with the second one, the second one also intersects with the first one.

Alternatively, we can use the ST_Within function to check whether one polygon is entirely within another. This function checks whether the first polygon is entirely within the boundaries of the second polygon.

SELECT ST_Within(

(SELECT polygon FROM areas WHERE id = 1),

(SELECT polygon FROM areas WHERE id = 2)

);

It’s a bit different than using ST_Intersects. For example, Central Park is within Manhattan, but Manhattan is not within Central Park.

Checking if a coordinate is within a polygon

Similarly, we can check if a single coordinate is within a polygon. To do that, we can also use the ST_Within function.

SELECT ST_Within(

ST_SetSRID(ST_MakePoint(-73.985428, 40.748817), 4326),

(SELECT polygon FROM areas WHERE id = 2)

);

Above, we’re checking if the Empire State Building is within Manhattan.

We should specify the Spatial Reference System Identifier (SRID) when working with geographical data. It tells PostGIS how to interpret the provided coordinates. When we defined the polygon column, we used the SRID 4326, which means the coordinates are defined in latitude and longitude. Because of that, we use the ST_SetSRID function to indicate that the coordinate we specify uses latitude and longitude as well.

If you want to know more about SRID, check out API with NestJS #183. Distance and radius in PostgreSQL with Drizzle ORM

To perform the above with the Drizzle ORM, we also need to provide a piece of a raw SQL query.

areas.service.ts

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

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

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

import { sql } from 'drizzle-orm';

@Injectable()

export class AreasService {

constructor(private readonly drizzleService: DrizzleService) {}

async doesAreaContainCoordinates(

areaId: number,

longitude: number,

latitude: number,

) {

const queryResult = await this.drizzleService.db.execute(

sql`

SELECT ST_Within(

ST_SetSRID(ST_MakePoint(${longitude}, ${latitude}), 4326),

(SELECT ${databaseSchema.areas.polygon} FROM ${databaseSchema.areas} WHERE id = ${areaId})

) AS "doesAreaContainCoordinates";

);

const doesAreaContainCoordinates =

queryResult.rows.pop()?.doesAreaContainCoordinates;

if (doesAreaContainCoordinates === null) {

// The provided area id does not exist in the database

throw new NotFoundException();

}

return doesAreaContainCoordinates;

}

// ...

}

Getting the intersection of two polygons

We can also get an intersection of two polygons, which is the area that both polygons share. As an example, we will use the Yellowstone National Park, which is located in the northwest corner of Wyoming and extends into Montana and Idaho.

[

{

"id": 3,

"name": "Wyoming",

"polygon": [

[

-111.05,

41.00

...

]

{

"id": 4,

"name": "Yellowstone National Park",

"polygon": [

[

-111.20,

44.60

...

]

}

]

When we calculate the intersection of Wyoming and the Yellowstone National Park, we get the part of the Yellowstone National Park in Wyoming. It excludes the parts of the park that are in Montana and Idaho. To do that, we need to use the ST_Intersection function built into PostGIS.

SELECT ST_Intersection(

(SELECT polygon FROM areas WHERE id = 3),

(SELECT polygon FROM areas WHERE id = 4)

);

To make the output of the ST_Intersection function more straightforward to read, we can convert its output to GeoJSON using the ST_AsGeoJSON function.

SELECT ST_AsGeoJSON(

ST_Intersection(

(SELECT polygon FROM areas WHERE id = 3),

(SELECT polygon FROM areas WHERE id = 4)

)

);

To perform the above operation with the Drizzle ORM, we need to provide a raw SQL query.

areas.service.ts

import {

Injectable,

InternalServerErrorException,

NotFoundException,

} from '@nestjs/common';

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

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

import { sql } from 'drizzle-orm';

@Injectable()

export class AreasService {

constructor(private readonly drizzleService: DrizzleService) {}

async getAreasIntersection(firstAreaId: number, secondAreaId: number) {

const queryResult = await this.drizzleService.db.execute(

sql`

SELECT ST_AsGeoJSON(

ST_Intersection(

(SELECT ${databaseSchema.areas.polygon} FROM ${databaseSchema.areas} WHERE id = ${firstAreaId}),

(SELECT ${databaseSchema.areas.polygon} FROM ${databaseSchema.areas} WHERE id = ${secondAreaId})

)

) AS "areasIntersection";

);

const areasIntersection = queryResult.rows.pop()?.areasIntersection;

if (areasIntersection === null) {

// One of the provided area ids doesn't exist in the database

throw new NotFoundException();

}

if (typeof areasIntersection !== 'string') {

throw new InternalServerErrorException();

}

// GeoJSON should be safe to parse as JSON

return JSON.parse(areasIntersection);

}

// ...

}

With the above solution, our getAreasIntersection method returns the GeoJSON representation of the intersection of two different areas.

Summary

In this article, we went through various operations we can do using polygons with PostGIS, PostgreSQL, and the Drizzle ORM. It included checking if two polygons overlap, getting an intersection of two polygons, and checking if a certain coordinate is within the polygon. While the Drizzle ORM does not provide functions to perform the above operations, we can create raw SQL queries and use them with Drizzle. All of the above gives us a solid understanding of the basics of various calculations we can do with polygons using Drizzle. Feel free to play with it more and experiment.

Series Navigation<< API with NestJS #184. Storing PostGIS Polygons in PostgreSQL with Drizzle ORMAPI with NestJS #186. What’s new in Express 5? >>