[BUG]: Spatial Support - Geometry Column

[dylel]

What version of drizzle-orm are you using?

0.22.0-cbee77

Describe the Bug

The geometry column isn't defined yet so i had a go at making it via the MySqlColumnBuilder, worked for db push as my planetscale detected the geoemtry correctly. However when trying to retrieve values from mapFromDriverValue it appears that it's not returning what I would expect from the driver which would be a buffer. Can someone shed some light on what format/value the driver is actually returning?

driver: @planetscale/database
drizzle wrapper: drizzle-orm/planetscale-serverless

This type may not be supported by @planetscale/database but couldn't find anything that said it wasn't.

Here's my attempt at the geometry column:

import type { ColumnBaseConfig, ColumnConfig } from "drizzle-orm";
import type { ColumnBuilderConfig } from "drizzle-orm/column-builder";
import { MySqlColumn } from "drizzle-orm/mysql-core/columns";
import { MySqlColumnBuilder } from "drizzle-orm/mysql-core/columns/common";
import type { AnyMySqlTable } from "drizzle-orm/mysql-core/table";
import wkx from "wkx";

// Define the builder for the geometry column
export class MySqlGeometryBuilder<
  TData extends wkx.Geometry
> extends MySqlColumnBuilder<
  ColumnBuilderConfig<{ data: TData; driverParam: wkx.Geometry }>
> {
  build<TTableName extends string>(
    table: AnyMySqlTable<{ name: TTableName }>
  ): MySqlGeometry<TTableName, TData> {
    return new MySqlGeometry(table, this.config);
  }
}

export class MySqlGeometry<
  TTableName extends string,
  TData extends wkx.Geometry
> extends MySqlColumn<
  ColumnConfig<{
    tableName: TTableName;
    data: wkx.Geometry;
    driverParam: string;
    notNull: false;
    hasDefault: false;
  }>
> {
  readonly $mySqlColumnBrand!: "MySqlColumn";

  constructor(
    table: AnyMySqlTable<{ name: TTableName }>,
    builder: MySqlGeometryBuilder<TData>["config"]
  ) {
    super(table, builder);
  }

  getSQLType(): string {
    return "GEOMETRY";
  }

  override mapFromDriverValue(value: string): wkx.Geometry {
    //@ts-ignore
    const buffer = value as Buffer;
    console.log("buffer", buffer.toJSON());
    const geometry = wkx.Geometry.parse(buffer);
    console.log("parsed: ", geometry);
    return geometry;
  }

  override mapToDriverValue(value: wkx.Geometry): string {
    if (!value) return "NULL";
    return `ST_GeomFromGeoJSON('${JSON.stringify(value)}')`;
  }
}

export function geometry<T extends wkx.Geometry>(
  name: string
): MySqlGeometryBuilder<T> {
  return new MySqlGeometryBuilder(name);
}

[dylel]

Seems that I had an oversight thinking that the format would be hex, most definitely is "binary".

override mapFromDriverValue(value: string): GeometryValue {
const buffer = Buffer.from(value, "binary");
const geom = wkx.Geometry.parse(buffer);
const geoJson = geom.toGeoJSON();
return geoJson as GeometryValue;
}

[travismfrank]

@dylel Found this while searching for a solution to sending binary to PS serverless. I notice you're using a SQL function in your mapToDriverValue:

return `ST_GeomFromGeoJSON('${JSON.stringify(value)}')`;

I'm attempting the same but running into trouble:

`UUID_TO_BIN('${value}',1)`

I think this is because the return value of mapToDriverValue is passed to the bound vars, and SQL functions don't work there(?). Is yours still working as expected?

[travismfrank]

@dankochetov Is there a way to give drizzle a SQL function to wrap the prepared value in? I'm storing UUIDs as binary(16) in PlanetScale, but as per this issue, the only way to insert binary via the serverless client is by sending a string and wrapping it in a SQL function to convert it to binary. Is there a way to implement this method? As I mentioned above, including the SQL function in the return value of mapToDriverValue doesn't solve the problem.

[dylel]

@TravisFrankMTG
Yeah i eventually amended my mapToDriverValue to this:

 override mapToDriverValue(value: GeometryValue): string {
    if (!value) return "NULL";

    // parse geoJson
    const geom = wkx.Geometry.parseGeoJSON(value);
    const bufWithSrid = Buffer.alloc(4);
    bufWithSrid.writeUInt32LE(4326, 0);
    const finalBuffer = Buffer.concat([bufWithSrid, geom.toWkb()]);

    // @ts-ignore
    return sql`UNHEX(${finalBuffer.toString("hex")})` as string;
  }

the sql function comes from drizzle, not sure if it was really intended for this but seems to work
import { sql } from "drizzle-orm";

As you can see i'm using the UNHEX sql function, i'm sure you would be able to use the sql function you wanted this way.

You basically just wrap the template string you want to run using this function instead and it will end up parsing it correctly to the db

[travismfrank]

@dylel You're a hero, thank you!

[nmajor]

Big thanks to @dylel for solving this issue:

My version of the custom drizzle type:

import { LineString, Point, Polygon } from "@turf/turf";
import { sql } from "drizzle-orm";
import { customType } from "drizzle-orm/mysql-core";
import wkx from "wkx";

type GeoJsonType = Point | LineString | Polygon;

const geometry = customType<{
  data: GeoJsonType;
  driverData: string;
  notNull: true;
}>({
  dataType() {
    return "geometry";
  },

  toDriver(value: GeoJsonType): string {
    if (!value) return "NULL";

    // parse geoJson
    const geom = wkx.Geometry.parseGeoJSON(value);

    const bufWithSrid = Buffer.alloc(4);
    bufWithSrid.writeUInt32LE(4326, 0);
    const finalBuffer = Buffer.concat([bufWithSrid, geom.toWkb()]);

    // @ts-ignore
    return sql`UNHEX(${finalBuffer.toString("hex")})` as string;
  },
  fromDriver(value: string): GeoJsonType {

    const buffer = Buffer.from(value.slice(4), "binary");
    const geom = wkx.Geometry.parse(buffer);
    const geoJson = geom.toGeoJSON();
    return geoJson as GeoJsonType;
  },
});

export default geometry;

For this to work with planetscale, you also have to add this to your connection to prevent GEOMETRY columns from being converted automatically to a string (planetscale/database-js#98):

const connection = connect({
  url: env.DATABASE_URL,
  cast: (field, value) => {
    if (field.type === "GEOMETRY") {
      return value;
    }
    return cast(field, value);
  },
});

[ayrton]

Now that Node has wide(r) support for Uint8Array we've added binary data casting straight to the library planetscale/database-js#164

[andrao]

Thank you @dylel & @nmajor!

Adding a point type here with TS data format { lat: number; lon: number }, for those looking for a simpler type just for lat/lon points sans Turf dependency:

import { sql } from "drizzle-orm";
import { customType } from "drizzle-orm/mysql-core";
import { Geometry } from "wkx";

/** @desc Spatial Reference System ID for Earth's surface */
const SRID = 4326;

/**
 * @desc Defines a `point` column type for the ORM, built on MySQL POINT
 * @see https://dev.mysql.com/doc/refman/8.4/en/gis-point-property-functions.html
 * @see https://github.com/drizzle-team/drizzle-orm/issues/337
 */
export const point = customType<{
  config: never;
  configRequired: false;
  data: { lat: number; lon: number };
  driverData: { x: number; y: number };
}>({
  dataType() {
    return `point`;
  },

  /** @desc Between driver/db and Typescript */
  fromDriver(value) {
    return { lat: value.y, lon: value.x };
  },

  /** @desc Between Typescript and driver/db */
  toDriver(value) {
    // Parse input to wkx/Geometry
    const geo = Geometry.parseGeoJSON({
      coordinates: [value.lon, value.lat],
      type: "Point",
    });

    // Build buffer including SRID value
    const srid_buffer = Buffer.alloc(4);
    srid_buffer.writeUInt32LE(SRID, 0);

    // Data buffer is SRID buffer + point data buffer
    const data_buffer = Buffer.concat([srid_buffer, geo.toWkb()]);

    return sql`UNHEX(${data_buffer.toString("hex")})`;
  },
});

And here a pointDistance function for querying on distance between points:

import type { SQL } from "drizzle-orm";
import type { MySqlColumn } from "drizzle-orm/mysql-core";
import { sql } from "drizzle-orm";

type PointColumn = MySqlColumn<{
  columnType: "MySqlCustomColumn";
  data: { lat: number; lon: number };
  dataType: "custom";
  driverParam: { x: number; y: number };
  enumValues: undefined;
  hasDefault: false;
  hasRuntimeDefault: false;
  isAutoincrement: false;
  isPrimaryKey: false;
  name: string;
  notNull: boolean;
  tableName: string;
}>;

/**
 * @desc Query for the distance between a set of lat/lon coordinates and a POINT column
 */
export function pointDistance(
  column: PointColumn,
  point: { lat: number; lon: number },
  unit: "kilometers" | "metres",
): SQL<number> {
  const divisor = unit === "kilometers" ? 1000 : 1;

  return sql<number>`ST_Distance_Sphere(${column}, ST_GeomFromText('POINT(${point.lat} ${point.lon})', 4326)) / ${divisor}`;
}

which can be used like so:

const distance = pointDistance(MyTable.my_point_column, { lat: 45.5019, lon: -73.5674 }, "kilometers");

const data = await dbx(db =>
  db
    .select({ distance, location_name: MyTable.location_name })
    .from(MyTable)
    .orderBy(distance),
);