In this post we will learn how to store and query geospatial information with PostgreSQL (using PostGIS extension) and TypeORM, in a NestJS application.
PostGIS is an extension available for PostgreSQL databases that allows you to store geospatial data (such as the longitude and latitude of a location) in a regular PostgreSQL table.
To understand how it works, we will build an API using NestJS, which will deal with an entity Location with a corresponding table in a PostgreSQL database, using TypeORM.
To see how these work together, we will take the following setup steps:
- Create DB and install PostGIS extension
- Create NestJS project and install dependencies
- Create Location entity in our database
When we are done setting up our project, we’ll go on writing an API endpoint that allows us to query locations given a longitude, latitude, and radius.
Making DB Ready
Before we do anything, we have to make sure PostGIS is available in our system for our database to use. I currently run Postgres from a Docker container, so we are going to connect to its terminal and get our database ready from there.
If you are running Postgres directly in your system, or want to download a docker image with PostgreSQL and PostGIS already installed, refer to PostGIS getting started page.
As stated, we will install PostGIS and create our database directly from the terminal of our Docker image, as shown below
> docker start postgresDB # Start container
> docker exec -it postgresDB bash # Connect to container terminal
> apt-get update # Update package information
> apt-get install -y postgis postgresql-15-postgis-3 # Install postgis and version-specific postgisOnce we correctly installed PostGIS, we will use the postgres terminal tool to create a database and make the extension itself available to it. Still in our Docker terminal, we will run:
> createdb -U admin postgis_test # Create DB named postgis_test with user 'admin'
> psql -U admin postgis_test # Connect to DB as 'admin' user
> CREATE EXTENSION postgis; # Create extension for DB
> SELECT PostGIS_version() # Check PostGIS was correctly installed
# Output from last command:
postgis_version
---------------------------------------
3.4 USE_GEOS=1 USE_PROJ=1 USE_STATS=1(1 row)Creating NestJS Project
We will now create our NestJS API.
Creating Project Scaffold
Once our database and extension are in place, we can proceed to create our NestJS project. For this, we will run
> nest new postgis-test -p npm # Use NPM as package managerWith our project in place, we will install the necessary dependencies, which are TypeORM and Postgres
> cd postgis-test
> npm install @nestjs/typeorm typeorm pgWith our dependencies installed, we will create the Location module, controller and service, and its corresponding entity file.
> nest g module location
> nest g controller location --no-spec # No test file generated
> nest g service location --no-spec
> touch src/location/location.entity.ts # Create entity fileDefining Location Entity
With our project scaffold in place, we will go in and define the Location entity using TypeORM decorators
import { Column, Entity, Point, PrimaryGeneratedColumn } from 'typeorm';
@Entity()
export class Location {
@PrimaryGeneratedColumn()
id: number;
@Column()
name: string;
@Column({
type: 'geography',
srid: 4326,
spatialFeatureType: 'Point',
})
coordinates: Point;
}With our entity in place, we can break down the coordinates column to understand exactly what we have done.
The type we have defined for our column, geography, stores geographic data that takes into consideration the earth’s curvature, in the form of latitude and longitude coordinates in the WGS 84 coordinate system.
SRID refers to the Spatial Reference (hence SR) system used to represent the spatial object. The ID of the SR system we want to use is 4326, which representes spatial object using two-dimensional coordinates.
Running the Project
With our entity in place, we will update the base module of our app to add the database connection and the Location entity, and make sure everything is up and running before continuing. Our app module will now look like this:
import { Module } from '@nestjs/common';
import { AppController } from './app.controller';
import { AppService } from './app.service';
import { LocationController } from './location/location.controller';
import { LocationService } from './location/location.service';
import { LocationModule } from './location/location.module';
import { TypeOrmModule } from '@nestjs/typeorm';
import { Location } from './location/location.entity';
@Module({
imports: [
TypeOrmModule.forRoot({
type: 'postgres',
host: 'localhost',
port: 5432,
username: 'admin',
password: 'admin',
database: 'postgis_test',
entities: [Location],
synchronize: true,
}),
TypeOrmModule.forFeature([Location]),
LocationModule,
],
controllers: [AppController, LocationController],
providers: [AppService, LocationService],
})
export class AppModule {}We will the run nest start, and we can see the application is running and the controllers were properly mapped, this means our DB connection was established succesfully. Because we have set synchronize to true, TypeORM will automatically create our Location table when the application is run.
Defining Location Service and Controller
With our application running, we will write our Location service and controller so that we can a) Create a location with its corresponding geospatial information, and b) receive a longitude, latitude, and range (in KM) values, and return a list of locations within that range.
To do this, we need to create a DTO that includes validations so that we can make sure the location being created has valid coordinates. For this we will install the following packages
> npm i class-validator class-transformerHaving our packages installed, we will define our CreateLocationDTO according to the following:
- Valid range for longitude is -180 < n < 180
- Valid range for latitude is -90 < n < 90
- Incoming number is properly parsed as float
With this in mind, we can create a location.dto.ts file and place our CreateLocationDTO inside
import { IsNumber, Min, Max, IsString } from 'class-validator';
import { Transform } from 'class-transformer';
export class CreateLocationDto {
@IsString()
name: string;
@IsNumber()
@Transform((obj) => parseFloat(obj.value))
@Min(-180)
@Max(180)
longitude: number;
@IsNumber()
@Transform((obj) => parseFloat(obj.value))
@Min(-90)
@Max(90)
latitude: number;
}Now, we have to tell our application to use the validation and transformer pipes. We have to modify the main.ts at the root of our src folder to look like this:
import { NestFactory } from '@nestjs/core';
import { AppModule } from './app.module';
import { ValidationPipe } from '@nestjs/common';
async function bootstrap() {
const app = await NestFactory.create(AppModule);
// Apply validation and transformation globally
app.useGlobalPipes(new ValidationPipe({ transform: true }));
await app.listen(3000);
}
bootstrap();Having created our DTO and registered the validation pipe, we can define our LocationService starting by its constructor and a createLocation method.
@Injectable()
export class LocationService {
constructor(
@InjectRepository(Location)
private readonly locationRepository: Repository<Location>,
) {}
public async create(location: CreateLocationDto): Promise<Location> {
const coordinates: Point = {
type: 'Point',
coordinates: [location.longitude, location.latitude],
};
return this.locationRepository.save({
name: location.name,
coordinates,
});
}
}At this point we know that the coordinates and name fields have been validated in our DTO, so we can go ahead and save it to the DB.
Next we will define our getWithinRange method, which will take a longitude, latitude, and range in KM, and return an array of locations within that range. Adding this method would result in the following:
@Injectable()
export class LocationService {
constructor(
@InjectRepository(Location)
private readonly locationRepository: Repository<Location>,
) {}
public async create(location: CreateLocationDto): Promise<Location> {
const coordinates: Point = {
type: 'Point',
coordinates: [location.longitude, location.latitude],
};
return this.locationRepository.save({
name: location.name,
coordinates,
});
}
public async getWithinRange(
longitude: number,
latitude: number,
rangeKm: number,
): Promise<Location[]> {
const isValidLongitude = longitude >= -180 && longitude <= 180;
const isValidLatitude = latitude >= -90 && latitude <= 90;
if (!isValidLongitude || !isValidLatitude) {
throw new BadRequestException('Invalid coordinates');
}
return this.locationRepository
.createQueryBuilder('location')
.select()
.where(
'ST_DWithin(location.coordinates, ST_SetSRID(ST_MakePoint(:lon, :lat), 4326), :range)',
{ lon: longitude, lat: latitude, range: rangeKm * 1000 },
)
.getMany();
}
}First, we validate that longitude and latitude values received are within the range. This implictly also validates that they are numeric values. If values are not valid, we throw a bad request exception.
As you can see, when writing our query we use functions ST_DWithin, ST_MakePoint, and ST_SetSRID which are parte of the PostGIS extension we installed. ST_MakePoint is a function that creates a (in our case) 2D Point geometry object, and we use it when calling ST_DWIthin, which receives the coordinates field from the Location entity, the Point object we created, and a range, and returns true if the geometries are within this range.
It is very important to note that because we have defined our column as geography type, the range being passed to this function will be interpreted as meters; this is why we multiply the parameter we received by 1000.
With our service in place, we will go ahead an add our POST and GET methods for our controller. Note that for our GET method, we specify the range parameter in KM.
import { Body, Controller, Get, Post, Query } from '@nestjs/common';
import { LocationService } from './location.service';
import { CreateLocationDto } from './location.dto';
import { Location } from './location.entity';
@Controller('location')
export class LocationController {
constructor(private readonly locationService: LocationService) {}
@Post()
async create(@Body() location: CreateLocationDto): Promise<Location> {
return this.locationService.create(location);
}
@Get('radius')
async getWithinRange(
@Query('lat') lat: number,
@Query('lon') lon: number,
@Query('rangeKm') rangeKm: number,
): Promise<Location[]> {
return this.locationService.getWithinRange(lon, lat, rangeKm);
}
}
Testing our API
Having written our service and controller, as well as our Location entity an its corresponding DTO, we can now go ahead and test our API.
First, we will try to create a location with invalid coordinates and test our validations work properly
> curl --location 'localhost:3000/location' \
--header 'Content-Type: application/json' \
--data '{
"name": "Location1",
"longitude": 200,
"latitude": -200
}'
# Output:
{
"message": [
"longitude must not be greater than 180",
"latitude must not be less than -90"
],
"error": "Bad Request",
"statusCode": 400
}Next we will create a few locations so that we can test our retrieval later
> curl --location 'localhost:3000/location' \
--header 'Content-Type: application/json' \
--data '{
"name": "Location1",
"longitude": 15,
"latitude": 15
}'
> curl --location 'localhost:3000/location' \
--header 'Content-Type: application/json' \
--data '{
"name": "Location2",
"longitude": 30,
"latitude": 30
}'
> curl --location 'localhost:3000/location' \
--header 'Content-Type: application/json' \
--data '{
"name": "Location3",
"longitude": -15,
"latitude": -15
}'If these run succesfully, we should see as response of each of them the actual Location that was just created.
With our testing data in place, we will use our GET method to check our querying works correctly. Before testing the API itself, we want to calculate distances in the world map using this site.
If we check, we will see that the points (-15,-15) and (15,15) are both at approximately 2345.2 KM of distance from point (0,0). If we query with these parameters, we should get Location1 and Location3 listed, and see no result when querying with a smaller range:
> curl --location 'localhost:3000/location/radius?lon=0&lat=0&rangeKm=2340'
# Output: []
> curl --location 'localhost:3000/location/radius?lon=0&lat=0&rangeKm=2345'
# Output:
[
{
"id": 1,
"name": "Location1",
"coordinates": {
"type": "Point",
"coordinates": [
15,
15
]
}
},
{
"id": 3,
"name": "Location3",
"coordinates": {
"type": "Point",
"coordinates": [
-15,
-15
]
}
}
]Lastly, if we measure distance from (0,0) to (30,30), we get about 4.604 KM. Thus if we repeat the query with a greater range, we should see all properties listed:
> curl --location 'localhost:3000/location/radius?lon=0&lat=0&rangeKm=4605'
# Output:
[
{
"id": 1,
"name": "Location1",
"coordinates": {
"type": "Point",
"coordinates": [
15,
15
]
}
},
{
"id": 2,
"name": "Location2",
"coordinates": {
"type": "Point",
"coordinates": [
30,
30
]
}
},
{
"id": 3,
"name": "Location3",
"coordinates": {
"type": "Point",
"coordinates": [
-15,
-15
]
}
}
]Having tested our API, we can see that the geospatial information we entered is being interpreted and queried accordingly. With this logic in place, we can know calculate distances between points easily using TypeORM and PostgreSQL.
Daniel Clas 