I have been using mikroorm for sometime now. In my new project, I have to work with postgis. Basically, I have an entity that stores coordinates. Then I can use this coordinates to do my operations later like finding distance between two places. Below is a sample entity

@Entity()
export class Entity {
  @Property()
  latitude:number;

  @Property()
  longitude:number;
}

Basically I want an equivalent of Point in typeorm. The custom types mikroorm docs have an implementation of Point but using mysql. mikroorm custom types

With this i get the following error when I run a select query on the entity with the point column

DriverException: select "e0".*, ST_AsText("e0"."point") as "point", "f1"."id" as "f1__id", "f1"."created_at" as "f1__created_at", "f1"."updated_at" as "f1__updated_at", "f1"."featured" as "f1__featured", "f1"."event_id" as "f1__event_id", "f1"."public_id" as "f1__public_id", "f1"."secure_url" as "f1__secure_url", "f1"."owner_id" as "f1__owner_id", "f1"."file_type" as "f1__file_type", "f1"."purpose" as "f1__purpose" from "event" as "e0" left join "file" as "f1" on "e0"."id" = "f1"."event_id" where "e0"."id" = '3ce4348f-1248-4e25-9d56-13e2da5ed1c8' - function st_astext(point) does not exist

Here is my entity definition with the custom point type from the docs

export class Point {
  constructor(
    public latitude: number,
    public longitude: number,
  ) {}
}

export class PointType extends Type<Point | undefined, string | undefined> {
  convertToDatabaseValue(value: Point | undefined): string | undefined {
    if (!value) {
      return value;
    }

    return `point(${value.latitude} ${value.longitude})`;
  }

  convertToJSValue(value: string | undefined): Point | undefined {
    const m = value?.match(/point\((-?\d+(\.\d+)?) (-?\d+(\.\d+)?)\)/i);

    if (!m) {
      return undefined;
    }

    return new Point(+m[1], +m[3]);
  }

  convertToJSValueSQL(key: string) {
    return `ST_AsText(${key})`;
  }

  convertToDatabaseValueSQL(key: string) {
    return `ST_PointFromText(${key})`;
  }

  getColumnType(): string {
    return 'point';
  }
}

@Entity()
export class Event{

  @Property({ nullable: true })
  location: string;

  @Property({ nullable: true })
  latitude: number;

  @Property({ nullable: true })
  longitude: number;

  @Property({ type: PointType, nullable: true })
  point: Point;
}

Postgis installation in my db

I have been using mikroorm for sometime now. In my new project, I have to work with postgis. Basically, I have an entity that stores coordinates. Then I can use this coordinates to do my operations later like finding distance between two places. Below is a sample entity

@Entity()
export class Entity {
  @Property()
  latitude:number;

  @Property()
  longitude:number;
}

Basically I want an equivalent of Point in typeorm. The custom types mikroorm docs have an implementation of Point but using mysql. mikroorm custom types

With this i get the following error when I run a select query on the entity with the point column

DriverException: select "e0".*, ST_AsText("e0"."point") as "point", "f1"."id" as "f1__id", "f1"."created_at" as "f1__created_at", "f1"."updated_at" as "f1__updated_at", "f1"."featured" as "f1__featured", "f1"."event_id" as "f1__event_id", "f1"."public_id" as "f1__public_id", "f1"."secure_url" as "f1__secure_url", "f1"."owner_id" as "f1__owner_id", "f1"."file_type" as "f1__file_type", "f1"."purpose" as "f1__purpose" from "event" as "e0" left join "file" as "f1" on "e0"."id" = "f1"."event_id" where "e0"."id" = '3ce4348f-1248-4e25-9d56-13e2da5ed1c8' - function st_astext(point) does not exist

Here is my entity definition with the custom point type from the docs

export class Point {
  constructor(
    public latitude: number,
    public longitude: number,
  ) {}
}

export class PointType extends Type<Point | undefined, string | undefined> {
  convertToDatabaseValue(value: Point | undefined): string | undefined {
    if (!value) {
      return value;
    }

    return `point(${value.latitude} ${value.longitude})`;
  }

  convertToJSValue(value: string | undefined): Point | undefined {
    const m = value?.match(/point\((-?\d+(\.\d+)?) (-?\d+(\.\d+)?)\)/i);

    if (!m) {
      return undefined;
    }

    return new Point(+m[1], +m[3]);
  }

  convertToJSValueSQL(key: string) {
    return `ST_AsText(${key})`;
  }

  convertToDatabaseValueSQL(key: string) {
    return `ST_PointFromText(${key})`;
  }

  getColumnType(): string {
    return 'point';
  }
}

@Entity()
export class Event{

  @Property({ nullable: true })
  location: string;

  @Property({ nullable: true })
  latitude: number;

  @Property({ nullable: true })
  longitude: number;

  @Property({ type: PointType, nullable: true })
  point: Point;
}

Postgis installation in my db

• node.js
• postgresql
• nestjs
• postgis
• mikro-orm

• 1 point is the built-in type. PostGIS uses type geometry (among others) and that's the type st_ functions expect, not the one built-in: dbfiddle.uk/6S8N3uyw – Zegarek Commented Mar 15 at 15:42
• @Zegarek thanks for the fiddle. However, i still don't know what to change in the code above. If you can give me code sample i would appreciate. Thanks. – drogon98 Commented Mar 15 at 17:38
• I'm not familiar with this ORM but it sounds like you want getColumnType() to be geometry, if that's what it uses to define the column and cast/assign types on input/output from it – Zegarek Commented Mar 15 at 17:59
• You might have to activate the extension first. Try running await orm.schema.execute('create extension if not exists postgis') after the ORM init. – Martin Adámek Commented Mar 17 at 9:47
• @MartinAdámek should the activation be another step? I have already added the postgis extension. I have edited the question to show postgis installation in my database. Also regarding calling the activation code on ORM init i am using the defineConfig() method. I am not using the init method. – drogon98 Commented Mar 17 at 10:10

1 Answer 1

I solved this by using knex-postgis rather than custom types.