DEV Community

loading...
Cover image for Saving and Retrieving Well-known Text (WKT) in MySQL or Postgres with TypeORM

Saving and Retrieving Well-known Text (WKT) in MySQL or Postgres with TypeORM

Emmanuel N Kyeyune
SRE / DevOps / Solutions Architect
Updated on ・2 min read

Sometimes in TypeORM you want to save location/coordinates in a type that can take advantage of GIS functions like distance, closeness etc MySQL and Postgres have these functions that get this information when you have geometry data column types.

This was surprisingly difficult to find information on so here's my implementation.

I assume you already have a TypeORM project set up.

Install these packages:

npm install wkx geojson 
npm install @types/geojson --save-dev
Enter fullscreen mode Exit fullscreen mode

Then add this custom transformer that converts GeoJSON to WKT and WKT back to GeoJSON

Example GeoJSON:

const location = {
   type: 'point',
   coordinates: [-74.534, 39.123]
}
Enter fullscreen mode Exit fullscreen mode

Example WKT (like you would use in MySQL query):

POINT(-74.534, 39.123)
Enter fullscreen mode Exit fullscreen mode

This would be saved as BLOB type in MySQL

Files

First you need to add the transformer class

// ./src/lib/transformers.ts

import * as wkx from 'wkx'
import { Geometry } from 'geojson'
import { ValueTransformer } from 'typeorm/decorator/options/ValueTransformer'

/**
 * TypeORM transformer to convert GeoJSON to MySQL WKT (Well Known Text) e.g. POINT(LAT, LON) and back
 */
export class GeometryTransformer implements ValueTransformer {
    to(geojson: Geometry): string {
        return wkx.Geometry.parseGeoJSON(geojson).toWkt()
    }

    from(wkb: string): Record<string, any> | undefined {
        if(!wkb) return

        return wkx.Geometry.parse(wkb).toGeoJSON()
    }
}
Enter fullscreen mode Exit fullscreen mode

Then inside your target entity class:

// ./src/entities/user.ts

import {
    Entity,
    PrimaryGeneratedColumn,
    Column,
    BaseEntity
} from 'typeorm'
import { GeometryTransformer } from '../transformers'
import { Geometry } from 'geojson'


@Entity({ name: 'user' })
export class User extends BaseEntity {
    @PrimaryGeneratedColumn()
    idUser!: number

    @Column({
        type: 'geometry',
        spatialFeatureType: 'Point',
        srid: 4326, // WGS84 reference system
        transformer: new GeometryTransformer(),
    })
    location?: Geometry
}
Enter fullscreen mode Exit fullscreen mode

Insert GeoJSON

And then finally if you want to insert location data, insert geoJSON:

// ./src/services/user.service.ts

import { Geometry } from 'geojson'
import { getRepository } from 'typeorm'
import { User } from '../entities/user'

export const createUser = async (): Promise<User|undefined> => {

    const location: Geometry = {
        type: 'Point',
        coordinates: [-74.534, 39.123]
     }
    // you should validate the geojson here
    // https://www.npmjs.com/package/geojson-validation

    // if(!validGeoJson(location))throw new Error('invalid GeoJSON')

    return getRepository(User)
        .create({ location })
        .save()
}
Enter fullscreen mode Exit fullscreen mode

Retrieve GeoJSON

And then when you query the object you will get location as type Geometry.

That's it!


Hi I'm Emmanuel! I write about Software and DevOps.

If you liked this article and want to see more, add me on LinkedIn or follow me on Twitter

Discussion (0)