Storing GeoJson points and finding points within a given distance/radius | NODEJS, Postgres, NestJs, TypeOrm

Question

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.

user_6125802 · Answer

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,
  ) {}

  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);
  }
}

Storing GeoJson points and finding points within a given distance/radius | NODEJS, Postgres, NestJs, TypeOrm

1 Answers