Saving and Retrieving Well-known Text (WKT) in MySQL or Postgres with TypeORM
Tags: typescript, typeorm, wkt, geojson
Published: 2021-06-09 19:50:49

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:
```bash
npm install wkx geojson 
npm install @types/geojson --save-dev
```

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

Example GeoJSON:
```ts
const location = {
   type: 'point',
   coordinates: [-74.534, 39.123]
}
```

Example WKT (like you would use in MySQL query):
```SQL
POINT(-74.534, 39.123)
```
This would be saved as `BLOB` type in MySQL

## Files
First you need to add the transformer class
```ts
// ./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()
    }
}
```

Then inside your target entity class:
```ts
// ./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
}
```
## Insert GeoJSON
And then finally if you want to insert location data, insert geoJSON:
```ts
// ./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()
}
```
## Retrieve GeoJSON
And then when you query the object you will get `location` as type `Geometry`.

That's it!

-------
Hi I'm [Emmanuel](https://emmanuelnk.com)! I write about Software and DevOps.

If you liked this article and want to see more, add me on [LinkedIn](https://www.linkedin.com/in/emmanuel-nsubuga-kyeyune/) or follow me on [Twitter](https://twitter.com/emmanuel_n_k)