Thank you in advance. I have scavenged the internet for a working example/documentation for a way to store location point (longitude, latitude), find distance between two points, find points within a given distance. I am using typeorm, nestjs, postgresql.
(I already tried Mariadb but St_distance_sphere is not working there so am going with postgresql)
this is my entity
@ApiProperty({
type: String,
title: 'current_location',
example: '{"type":"Point","coordinates":[28.612849, 77.229883]}',
})
@Index({ spatial: true })
@Column({
type: 'geometry',
srid: 4326,
nullable: true,
spatialFeatureType: 'Point',
transformer: {
to: (v: Point) => {
console.log(JSON.stringify(v));
return eval(`ST_GeomFromGeoJSON(${JSON.stringify(v)})`);
},
from: (v: any) => {
return { type: 'Point', coordinates: [v.x, v.y] } as Point;
},
},
})
current_location: string;
there seem to be too much postgres/postgis documentation but nothing useful for my case. any help is much appreciated. I have been stuck on this for more than a week.
*note: I don't want to use JSONB datatype for its slower speed.
The following code will store in the DB and finds the locations within the range
Tech Stack nestJS,typeorm,postgres,postgis extension,@types/geojson
testlocation.entity.ts
import { Column, Entity, Index, PrimaryGeneratedColumn} from 'typeorm';
import { Geometry, Point } from 'geojson';
@Entity({ name: 't_test_location' })
export class TestLocation {
@PrimaryGeneratedColumn('increment')
pk_id: number;
@Column({ type: 'varchar', name: 's_city' })
city: string;
@Column({ type: 'double precision', name: 'd_lat' })
lat: number;
@Column({ type: 'double precision', name: 'd_long' })
long: number;
@Index({ spatial: true })
@Column({
type: 'geography',
spatialFeatureType: 'Point',
srid: 4326,
nullable: true,
})
location:Point
}
location.service.ts
import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { TestLocation } from 'src/model/testlocation.entity';
import { getManager, QueryBuilder, Repository } from 'typeorm';
import { Geometry, Point } from 'geojson';
@Injectable()
export class LocationService {
constructor(
@InjectRepository(TestLocation) private readonly repo: Repository<TestLocation>,
) {}
public async getAll() {
return await this.repo.find();
}
public async create(location:TestLocation){
const pointObject :Point= {
type: "Point",
coordinates: [location.long,location.lat]
};
location.location = pointObject;
return await this.repo.save(location)
}
public async getRange(lat:number,long:number,range:number = 1000) {
let origin = {
type: "Point",
coordinates: [long, lat]
};
let locations = await this.repo
.createQueryBuilder('t_test_location')
.select(['t_test_location.city AS city','ST_Distance(location, ST_SetSRID(ST_GeomFromGeoJSON(:origin), ST_SRID(location)))/1000 AS distance' ])
.where("ST_DWithin(location, ST_SetSRID(ST_GeomFromGeoJSON(:origin), ST_SRID(location)) ,:range)")
.orderBy("distance","ASC")
.setParameters({
// stringify GeoJSON
origin: JSON.stringify(origin),
range:range*1000 //KM conversion
})
.getRawMany();
return locations;
}
}
location.controller.ts
import { Body, Controller, Get, Post } from '@nestjs/common';
import { TestLocation } from 'src/model/testlocation.entity';
import { LocationService } from './location.service';
@Controller('location')
export class LocationController {
constructor(private serv: LocationService) {}
@Get()
public async getAll() {
return await this.serv.getAll();
}
@Post()
createLocation(@Body() location : TestLocation): void{
this.serv.create(location);
}
@Post('range')
public async getRange(@Body() location : {
lat:number,
long:number,
range:number
}){
return await this.serv.getRange(location.lat,location.long,location.range);
}
}