How to setup PostGIS with adonisJS v6 (lucid ORM)

Enable postgis

With the context above, the assumption is you already have Postgis installed and you just want to see the code on how to configure and use it within adonisJS (lucid ORM). To enable postgis, we need to create a migration file with the below content in the migration file

CREATE EXTENSION IF NOT EXISTS postgis;

What the above SQL statement does is to configure and enable the postgis extension within the database you are connected to. You will notice about 2 tables created in the db, along with a dozen functions.

Creating a column that uses geospatial type (geography / geometry)

Now the next step is actually using the postgis we just installed. To do so, we will create a sample database table called user_profile which would have the below columns

id - primary key

full_name - User’s full name

location - The User’s location (in coordinates) ie: 5.6393728,-0.1709543

Talk is cheap, let me see the code :)

import { BaseSchema } from "@adonisjs/lucid/schema";

export default class extends BaseSchema {
  protected tableName = "user_profile";

  public async up() {
    this.schema.createTable(this.tableName, (table) => {
      table.increments("id");
      table.string("full_name");
      table.geometry("location");
    });
  }

  public async down() {
    this.schema.dropTable(this.tableName);
  }
}

As seen in the code above, you will notice we are using a special data type called geometry to define the column data type for the location column.

Create a model

Now that we are done creating our table, we will need a model to interact with our data. To do so we will use the code below

import { BaseModel, column, computed } from "@adonisjs/lucid/orm";

type Coordinate = [number, number];
type PointGeometry = { type: "Point"; coordinates: Coordinate };

export default class UserProfile extends BaseModel {
  public static table = "user_profile";

  @column({ isPrimary: true })
  public declare id: number;

  @column()
  public declare location: string;

  @computed()
  public get locationAsGeoJSON(): GeoJsonGeometry | null {
    const locationAsGeoJSON = this.$extras.location_as_geojson;
    return locationAsGeoJSON
      ? JSON.parse(this.$extras.location_as_geojson)
      : null;
  }
}

The above code looks like any other adonisJS model, except for one thing, which is the locationAsGeoJSON property in the UserProfile class. You will notice its marked as computed which means, its not a table column field, but rather, its value is dynamically computed/generated at runtime when a query is run against the UserProfile model.

Then when we take a deeper look, we can see that it is JSON stringifying a field called location_as_geojson . We will get to the details of location_as_geojson in the next section, stay tuned.

How to query for db records including the geospatial column types

With all we have done so far, you might be wondering, okay cool, that’s nice but how do i actually query the data from the db and get it in the format i want, ie: as a GeoJSON object. Worry no more, because the solution is right in the code below.

import db from "@adonisjs/lucid/services/db";

const userProfiles = await UserProfile.query()
  .select("*")
  .select(db.raw("ST_AsGeoJSON(location) as location_as_geojson"));