API with NestJS #184. Storing PostGIS Polygons in PostgreSQL with Drizzle ORM – Beragampengetahuan
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:
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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(), name: text().notNull(), 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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( name: ‘Central Park’, 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() name: string;
@Validate(ArePolygonCoordinates) polygon: [number, number][][];
|
To do it, we can create a class using the
validate method.
are-polygon-coordinates.ts
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
import ValidationArguments, ValidatorConstraint, ValidatorConstraintInterface, from ‘class-validator’;
@ValidatorConstraint() export class ArePolygonCoordinates implements ValidatorConstraintInterface validate(value: unknown)
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
import ValidationArguments, ValidatorConstraint, ValidatorConstraintInterface, isLatitude, isLongitude, from ‘class-validator’;
@ValidatorConstraint() export class ArePolygonCoordinates implements ValidatorConstraintInterface validate(value: unknown) 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
import ValidationArguments, ValidatorConstraint, ValidatorConstraintInterface, isLatitude, isLongitude, from ‘class-validator’;
@ValidatorConstraint() export class ArePolygonCoordinates implements ValidatorConstraintInterface validate(value: unknown) 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) 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
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( name: area.name, 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
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.
rencana pengembangan website
metode pengembangan website
jelaskan beberapa rencana untuk pengembangan website, proses pengembangan website, kekuatan dan kelemahan bisnis pengembangan website
, jasa pengembangan website, tahap pengembangan website, biaya pengembangan website
#API #NestJS #Storing #PostGIS #Polygons #PostgreSQL #Drizzle #ORM
