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.
1import { serial, text, pgTable } from 'drizzle-orm/pg-core';
2import { customType } from 'drizzle-orm/pg-core';
3import { Geometry } from 'wkx';
4
5type Coordinate = [number, number];
6
7interface GeoJson {
8 type: string;
9 coordinates: Coordinate[][];
10}
11
12const polygon = customType<{ data: Coordinate[][]; driverData: string }>({
13 dataType() {
14 return 'geometry(Polygon, 4326)';
15 },
16 toDriver(coordinates: Coordinate[][]): string {
17 return JSON.stringify({
18 type: 'Polygon',
19 coordinates,
20 });
21 },
22 fromDriver(data: string) {
23 const geoJson = Geometry.parse(
24 Buffer.from(data, 'hex'),
25 ).toGeoJSON() as GeoJson;
26
27 return geoJson.coordinates;
28 },
29});
30
31export const areas = pgTable('areas', {
32 id: serial().primaryKey(),
33 name: text().notNull(),
34 polygon: polygon('polygon').notNull(),
35});
36
37export const databaseSchema = {
38 areas,
39};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.
1[
2 {
3 "id": 1,
4 "name": "Central Park",
5 "polygon": [
6 [
7 [
8 -73.981898,
9 40.768094
10 ],
11 ...
12 ]
13 ]
14 },
15 {
16 "id": 2,
17 "name": "Manhattan",
18 "polygon": [
19 [
20 [
21 -74.02,
22 40.7
23 ],
24 ...
25 ]
26 ]
27 },
28]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.
1SELECT ST_Intersects(
2 (SELECT polygon FROM areas WHERE id = 1),
3 (SELECT polygon FROM areas WHERE id = 2)
4);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.
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 AreasService {
8 constructor(private readonly drizzleService: DrizzleService) {}
9
10 async areAreasOverlapping(firstAreaId: number, secondAreaId: number) {
11 const queryResult = await this.drizzleService.db.execute(
12 sql`
13 SELECT ST_Intersects(
14 (SELECT ${databaseSchema.areas.polygon} FROM ${databaseSchema.areas} WHERE id = ${firstAreaId}),
15 (SELECT ${databaseSchema.areas.polygon} FROM ${databaseSchema.areas} WHERE id = ${secondAreaId})
16 ) AS "areAreasOverlapping";
17 `,
18 );
19
20 const areAreasOverlapping = queryResult.rows.pop()?.areAreasOverlapping;
21
22 if (areAreasOverlapping === null) {
23 // One of the provided area ids don't exist in the database
24 throw new NotFoundException();
25 }
26
27 return areAreasOverlapping;
28 }
29
30 // ...
31}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.
1SELECT ST_Within(
2 (SELECT polygon FROM areas WHERE id = 1),
3 (SELECT polygon FROM areas WHERE id = 2)
4);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.
1SELECT ST_Within(
2 ST_SetSRID(ST_MakePoint(-73.985428, 40.748817), 4326),
3 (SELECT polygon FROM areas WHERE id = 2)
4);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.
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 AreasService {
8 constructor(private readonly drizzleService: DrizzleService) {}
9
10 async doesAreaContainCoordinates(
11 areaId: number,
12 longitude: number,
13 latitude: number,
14 ) {
15 const queryResult = await this.drizzleService.db.execute(
16 sql`
17 SELECT ST_Within(
18 ST_SetSRID(ST_MakePoint(${longitude}, ${latitude}), 4326),
19 (SELECT ${databaseSchema.areas.polygon} FROM ${databaseSchema.areas} WHERE id = ${areaId})
20 ) AS "doesAreaContainCoordinates";
21 `,
22 );
23
24 const doesAreaContainCoordinates =
25 queryResult.rows.pop()?.doesAreaContainCoordinates;
26
27 if (doesAreaContainCoordinates === null) {
28 // The provided area id does not exist in the database
29 throw new NotFoundException();
30 }
31
32 return doesAreaContainCoordinates;
33 }
34
35 // ...
36}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.
1[
2 {
3 "id": 3,
4 "name": "Wyoming",
5 "polygon": [
6 [
7 [
8 -111.05,
9 41.00
10 ],
11 ...
12 ]
13 ]
14 },
15 {
16 "id": 4,
17 "name": "Yellowstone National Park",
18 "polygon": [
19 [
20 [
21 -111.20,
22 44.60
23 ],
24 ...
25 ]
26 ]
27 }
28]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.
1SELECT ST_Intersection(
2 (SELECT polygon FROM areas WHERE id = 3),
3 (SELECT polygon FROM areas WHERE id = 4)
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.
1SELECT ST_AsGeoJSON(
2 ST_Intersection(
3 (SELECT polygon FROM areas WHERE id = 3),
4 (SELECT polygon FROM areas WHERE id = 4)
5 )
6);To perform the above operation with the Drizzle ORM, we need to provide a raw SQL query.
1import {
2 Injectable,
3 InternalServerErrorException,
4 NotFoundException,
5} from '@nestjs/common';
6import { DrizzleService } from '../database/drizzle.service';
7import { databaseSchema } from '../database/database-schema';
8import { sql } from 'drizzle-orm';
9
10@Injectable()
11export class AreasService {
12 constructor(private readonly drizzleService: DrizzleService) {}
13
14 async getAreasIntersection(firstAreaId: number, secondAreaId: number) {
15 const queryResult = await this.drizzleService.db.execute(
16 sql`
17 SELECT ST_AsGeoJSON(
18 ST_Intersection(
19 (SELECT ${databaseSchema.areas.polygon} FROM ${databaseSchema.areas} WHERE id = ${firstAreaId}),
20 (SELECT ${databaseSchema.areas.polygon} FROM ${databaseSchema.areas} WHERE id = ${secondAreaId})
21 )
22 ) AS "areasIntersection";
23 `,
24 );
25
26 const areasIntersection = queryResult.rows.pop()?.areasIntersection;
27
28 if (areasIntersection === null) {
29 // One of the provided area ids doesn't exist in the database
30 throw new NotFoundException();
31 }
32
33 if (typeof areasIntersection !== 'string') {
34 throw new InternalServerErrorException();
35 }
36
37 // GeoJSON should be safe to parse as JSON
38 return JSON.parse(areasIntersection);
39 }
40
41 // ...
42}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.