How can i use longitude and latitude with typeorm and postgres

Question

My current entity looks like this:

import { BaseEntity, Column, Entity, PrimaryGeneratedColumn } from 'typeorm';

@Entity()
export class Landmark extends BaseEntity {
    @PrimaryGeneratedColumn('uuid')
    id: string;

    @Column()
    longitude: number 

    @Column()
    latitude: number 
}

But i wonder if there is a better way to do this, with a special postgres type, that works with typeorm.

Answer 1

You're going to want to look into PostGIS and Spatial Column support in Typeorm:

https://github.com/typeorm/typeorm/blob/master/docs/entities.md#spatial-columns

PostGIS is an extension you can enable in your postgres database for dealing with spatial datatypes. Once you have PostGIS installed, you can use its special spatial data functions inside of a Typeorm query builder as you would any other PG function, backed up by GeoJSON.

Typeorm's postgres driver uses GeoJSON internally to work with PostGIS, so when you're defining Typeorm models, you need to add @types/geojson, which will let you correctly type your Typeorm columns as you asked.

For instance, you can import the Geometry type definition and type your column as so:

import { Geometry } from 'geojson';
...
@Column
location: Geometry

In your case, you might want to combine your latitude and longitude columns into a single column -- location -- which can use the point() function to combine latitude and longitude into a single Geometry type.

As a contrived example, you could do something like:

UPDATE customers SET location = 'point(37.7, 122.4)' where id = 123;

This would set the location column on your customers table (as an example) to a geometry(point) column type corresponding to the lat/lon position of San Francisco.

If you wanted to migrate existing double precision column values for lat/lon (which is how you should store lat/lon on their own) to a single location column of type geometry(point), you could use the ST_MakePoint function that comes out of the box with PostGIS.

i.e.

-- Assuming you have a lat and lon columns on the `customers` table that you are migrating to the PostGIS geometry(point) type
UPDATE customers SET location = ST_MakePoint(lat, lon) where id = 123;

Answer 2

Extending JosephHall Answer

Used postgres,postgis,typeORM,@types/geojson, Nest JS

import { Column, Entity, Index, PrimaryGeneratedColumn} from 'typeorm';
import { Point } from 'geojson';

@Entity({ name: 't_test_location' })
export class TestLocation {
  @PrimaryGeneratedColumn('increment')
  pk_id: number;
  
  @Column({ type: 'varchar', name: 's_city' })
  city: string;

  @Index({ spatial: true })
  @Column({
    type: 'geography',
    spatialFeatureType: 'Point', 
    srid: 4326,
    nullable: true,
  })
  location:Point
}

Service class

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

Controller

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