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.
1SELECT 'SRID=4326;POLYGON((
2 -73.981898 40.768094,
3 -73.958094 40.800621,
4 -73.949282 40.796853,
5 -73.973057 40.764356,
6 -73.981898 40.768094
7))'::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.
1SELECT 'SRID=4326;POLYGON((
2 -73.981898 40.768094,
3 -73.958094 40.800621,
4 -73.949282 40.796853,
5 -73.973057 40.764356,
6 -73.981898 40.768094
7), (
8 -73.970000 40.780000,
9 -73.965000 40.780000,
10 -73.965000 40.775000,
11 -73.970000 40.775000,
12 -73.970000 40.780000
13), (
14 -73.960000 40.790000,
15 -73.955000 40.790000,
16 -73.955000 40.785000,
17 -73.960000 40.785000,
18 -73.960000 40.790000
19))'::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.
1SELECT '{
2 "type": "Polygon",
3 "coordinates": [
4 [
5 [-73.981898, 40.768094],
6 [-73.958094, 40.800621],
7 [-73.949282, 40.796853],
8 [-73.973057, 40.764356],
9 [-73.981898, 40.768094]
10 ],
11 [
12 [-73.970000, 40.780000],
13 [-73.965000, 40.780000],
14 [-73.965000, 40.775000],
15 [-73.970000, 40.775000],
16 [-73.970000, 40.780000]
17 ],
18 [
19 [-73.960000, 40.790000],
20 [-73.955000, 40.790000],
21 [-73.955000, 40.785000],
22 [-73.960000, 40.785000],
23 [-73.960000, 40.790000]
24 ]
25 ]
26}'::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.
1import { serial, text, pgTable } from 'drizzle-orm/pg-core';
2import { customType } from 'drizzle-orm/pg-core';
3
4const polygon = customType({
5 dataType() {
6 return 'geometry(Polygon, 4326)';
7 },
8});
9
10export const areas = pgTable('areas', {
11 id: serial().primaryKey(),
12 name: text().notNull(),
13 polygon: polygon('polygon').notNull(),
14});
15
16export const databaseSchema = {
17 areas,
18};Thanks to writing the dataType function, creating a migration results in the following SQL query:
1CREATE TABLE IF NOT EXISTS "areas" (
2 "id" serial PRIMARY KEY NOT NULL,
3 "name" text NOT NULL,
4 "polygon" geometry(polygon, 4326) NOT NULL
5);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.
1import { customType } from 'drizzle-orm/pg-core';
2
3type Coordinate = [number, number];
4
5const polygon = customType<{ data: Coordinate[][] }>({
6 dataType() {
7 return 'geometry(Polygon, 4326)';
8 },
9 toDriver(coordinates: Coordinate[][]): string {
10 return JSON.stringify({
11 type: 'Polygon',
12 coordinates,
13 });
14 },
15});
16
17// ...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.
1this.drizzleService.db
2 .insert(databaseSchema.areas)
3 .values({
4 name: 'Central Park',
5 polygon: [
6 [
7 [-73.981898, 40.768094],
8 [-73.958094, 40.800621],
9 [-73.949282, 40.796853],
10 [-73.973057, 40.764356],
11 [-73.981898, 40.768094],
12 ],
13 ],
14 })
15 .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.
1import { IsString, IsNotEmpty, Validate } from 'class-validator';
2import { ArePolygonCoordinates } from './are-polygon-coordinates';
3
4export class AreaDto {
5 @IsString()
6 @IsNotEmpty()
7 name: string;
8
9 @Validate(ArePolygonCoordinates)
10 polygon: [number, number][][];
11}To do it, we can create a class using the validate method.
1import {
2 ValidationArguments,
3 ValidatorConstraint,
4 ValidatorConstraintInterface,
5} from 'class-validator';
6
7@ValidatorConstraint()
8export class ArePolygonCoordinates implements ValidatorConstraintInterface {
9 validate(value: unknown) {
10 // Coordinates must be an array of polygons.
11 if (!Array.isArray(value) || value.length === 0) {
12 return false;
13 }
14
15 // Test each polygon
16 for (const polygon of value) {
17 if (!this.validatePolygon(polygon)) {
18 return false;
19 }
20 }
21
22 return true;
23 }
24
25 defaultMessage({ property }: ValidationArguments) {
26 return `${property} must be valid GeoJSON polygon coordinates`;
27 }
28
29 // ...
30}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.
1import {
2 ValidationArguments,
3 ValidatorConstraint,
4 ValidatorConstraintInterface,
5 isLatitude,
6 isLongitude,
7} from 'class-validator';
8
9@ValidatorConstraint()
10export class ArePolygonCoordinates implements ValidatorConstraintInterface {
11 validate(value: unknown) {
12 // Coordinates must be an array of polygons.
13 if (!Array.isArray(value) || value.length === 0) {
14 return false;
15 }
16
17 // Test each polygon
18 for (const polygon of value) {
19 if (!this.validatePolygon(polygon)) {
20 return false;
21 }
22 }
23
24 return true;
25 }
26 validatePolygon(polygon: unknown) {
27 // Polygons must be an array of coordinates.
28 if (!Array.isArray(polygon)) {
29 return false;
30 }
31
32 // Test every coordinate
33 for (const coordinates of polygon) {
34 if (!this.validateCoordinates(coordinates)) {
35 return false;
36 }
37 }
38
39 return true;
40 }
41
42 // ...
43}The validateCoordinates function uses the isLatitude and isLongitude functions built into the class-validator library.
1import {
2 ValidationArguments,
3 ValidatorConstraint,
4 ValidatorConstraintInterface,
5 isLatitude,
6 isLongitude,
7} from 'class-validator';
8
9@ValidatorConstraint()
10export class ArePolygonCoordinates implements ValidatorConstraintInterface {
11 validate(value: unknown) {
12 // Coordinates must be an array of polygons.
13 if (!Array.isArray(value) || value.length === 0) {
14 return false;
15 }
16
17 // Test each polygon
18 for (const polygon of value) {
19 if (!this.validatePolygon(polygon)) {
20 return false;
21 }
22 }
23
24 return true;
25 }
26 validatePolygon(polygon: unknown) {
27 // Polygons must be an array of coordinates.
28 if (!Array.isArray(polygon)) {
29 return false;
30 }
31
32 // Test every coordinate
33 for (const coordinates of polygon) {
34 if (!this.validateCoordinates(coordinates)) {
35 return false;
36 }
37 }
38
39 return true;
40 }
41 validateCoordinates(coordinates: unknown) {
42 // Coordinates need to be arrays with two elements
43 if (!Array.isArray(coordinates) || coordinates.length !== 2) {
44 return false;
45 }
46
47 const [longitude, latitude] = coordinates;
48
49 return isLongitude(longitude) && isLatitude(latitude);
50 }
51 defaultMessage({ property }: ValidationArguments) {
52 return `${property} must be valid GeoJSON polygon coordinates`;
53 }
54}We can now use our AreaDto class in the controller to validate the data sent by the user.
1import { Body, Controller, Post } from '@nestjs/common';
2import { AreasService } from './areas.service';
3import { AreaDto } from './dto/area.dto';
4
5@Controller('areas')
6export class AreasController {
7 constructor(private readonly areasService: AreasService) {}
8
9 @Post()
10 create(@Body() area: AreaDto) {
11 return this.areasService.create(area);
12 }
13
14 // ...
15}We can also use it in our service.
1import { Injectable } from '@nestjs/common';
2import { DrizzleService } from '../database/drizzle.service';
3import { databaseSchema } from '../database/database-schema';
4import { AreaDto } from './dto/area.dto';
5
6@Injectable()
7export class AreasService {
8 constructor(private readonly drizzleService: DrizzleService) {}
9
10 async create(area: AreaDto) {
11 const createdAreas = await this.drizzleService.db
12 .insert(databaseSchema.areas)
13 .values({
14 name: area.name,
15 polygon: area.polygon,
16 })
17 .returning();
18
19 return createdAreas.pop();
20 }
21
22 // ...
23}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.
1SELECT id, name, ST_AsGeoJSON(polygon)
2FROM areas
3WHERE 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.
1import { customType } from 'drizzle-orm/pg-core';
2import { Geometry } from 'wkx';
3
4type Coordinate = [number, number];
5
6interface GeoJson {
7 type: string;
8 coordinates: Coordinate[][];
9}
10
11const polygon = customType<{ data: Coordinate[][]; driverData: string }>({
12 dataType() {
13 return 'geometry(Polygon, 4326)';
14 },
15 toDriver(coordinates: Coordinate[][]): string {
16 return JSON.stringify({
17 type: 'Polygon',
18 coordinates,
19 });
20 },
21 fromDriver(data: string) {
22 const geoJson = Geometry.parse(
23 Buffer.from(data, 'hex'),
24 ).toGeoJSON() as GeoJson;
25
26 return geoJson.coordinates;
27 },
28});
29
30// ...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.