Geometric Types in PostgreSQL

[AndriiSherman]

We are adding a few more types for PostgreSQL and need to get some insights about their production usage and how these types should be mapped in TypeScript. (Want to add it to 0.31.0 release, so we will have a few more indexes use cases supported natively)

For now, we are adding point and line, and we will add other geo types later if users need them.

We are considering two modes of mapping for these types:

mode: tuple

pointColumn: point('point', { mode: 'tuple' })
lineColumn: line('line', { mode: 'tuple' })
// or it will be used by default
pointColumn: point('point')
lineColumn: line('line')

In this case, the type for this column will be a tuple: (1, 2)
You would need to insert a tuple and will get back a tuple from the database.

await db.insert(table).values({ pointColumn: [1,2], lineColumn: [1, 2, 3] })

type SelectType = { pointColumn: [number, number], lineColumn: [number, number, number] } 

const response: SelectType = await db.select().from(table);

mode: object

pointColumn: point('point', { mode: 'object' })
lineColumn: line('line', { mode: 'object' })

In this case, the type for this column will be an object: { x: 1, y: 2 } for a point and { a: 1, b: 2, c: 3 } for a line.
You would need to insert an object and will get back an object from the database

await db.insert(table).values({ column: { x: 1, y: 2 }, line: { a: 1, b: 2, c:3 }})

type SelectType = { pointColumn: { x: number, y: number }, lineColumn: { a: number, b: number, c: number } } 

const response: SelectType = await db.select().from(table);

If a line is represented as two points, then the type will be the same as explained for a point:
tuple - [[number, number], [number, number]]
object - { point1: { x: number, y: number }, point2: { x: number, y: number } }

So for line there should be one more mode: linear_tuple, linear_object, points_tuple, points_object. Naming here is to be discussed

Is there something missing? Is one mode enough, like a tuple or an object? Or should we have both?

[DanRibbens]

Object types can be nice, but I prefer the concise way of defining them as tuples.

In Payload, we opted to make the type an array with two numbers. We generate types with json-schema-to-typescript and that adds the @minItems and @maxItems, though this probably isn't needed:

/**
 * @minItems 2
 * @maxItems 2
 */
point: [number, number]

Excited to hear you will be supporting these!

[Cherry]

Excited to hear this is something you're exploring!

We're currently using our own implementation of something like this using customType and postgis, something like so:

import { customType } from 'drizzle-orm/pg-core';

export type Point = {
	lat: number;
	lng: number;
};
type Options = {
	srid: number;
};

export function decodePoint(value: string): Point {
	const matches = value.match(/POINT\((?<lng>[\d.-]+) (?<lat>[\d.-]+)\)/);
	const { lat, lng } = matches?.groups ?? {};

	if (!matches) {
		console.warn('Could not parse point value in function pointType.fromDriver', value);
	}

	return { lat: Number.parseFloat(String(lat)), lng: Number.parseFloat(String(lng)) };
}

export const point = (
	name: string,
	options?: Options,
) => customType<{ data: Point; driverData: string; }>({
	dataType() {
		return `geometry(point,${options?.srid ?? 4326})`;
	},
	toDriver(value: Point) {
		return `SRID=4326;POINT(${value.lng} ${value.lat})`;
	},
	fromDriver(value: string) {
		return decodePoint(value);
	},
})(name);

[AndriiSherman]

Nice catch! We can try to add geometry as well. We can use your use case as a starting point and then add more functionality if needed. So, for this case, we will have:

// both `type` and `srid` are optional
column: geometry("column_name", { type: 'point', srid: 4236 })

Also, as I understand, you can pass not only point inside geometry, so we will make the type 'point' | (string & {}) to enable point in autosuggest and allow adding any other string if needed.

We will use your mappings and mention you as the one who helped us with that (PostGIS docs are a bit hard, to be honest)

[emmbm]

I was working on some generic custom types for geometry and geography: https://github.com/iolyd/drizzle-orm-helpers/blob/main/src/pg/postgis/custom-types.ts. Not necessarily anything about object/tuple/other mapping modes, but I think my biggest concern would be support for additional dimensions (z and m types).

[mattiasbonte]

Hi people,
I am not familiar with geospatial queries, but need to implement them as we speak :).

I need to use polygons for regional based searches. Is this possible with the current implementation of drizzle?

When I store polygon data as a polygon json and try to retreive it via drizzle I'm getting the Error Unsupported geometry type. Which makes sense cause I can't find anything about polygons in the drizzle documentation.

So now I'm confronted with these questions:

• am I not understanding how to store this type of data via drizzle. (should I store it as a line)
• or is it just not possible to store it with drizzle's current implementation and should I make my own custom type?

Can polygons stored as tuples and then queried via drizzle? I feel like there are a couple of ways this could be handled. Ideally I want to leverage drizzle for building queries rather then building my own sql queries.

insert: ST_GeomFromGeoJSON(${JSON.stringify(my_polygon)})
select: select().from(countries).where(eq(countries.id, id)) - produces the error

---

Context
Im fetching location data from the google geolocation api

//   - Northeast (NE) corner:
//     - Latitude: 51.5051016
//     - Longitude: 6.4081241
//   - Southwest (SW) corner:
//     - Latitude: 49.4969518
//     - Longitude: 2.5240999
     
interface Viewport {
	/** The bounding box of the viewport. */
	northeast: {
		lat: number;
		lng: number;
	};
	southwest: {
		lat: number;
		lng: number;
	};
}

I then map it to a polygon json structure and use it for insertion

interface PolygonJSON {
	type: 'Polygon';
	coordinates: [[number, number][]];
}

/**
 * MAP VIEWPORT TO POLYGON
 *
 * @note examples
 * INSERT: ST_GeomFromGeoJSON(${JSON.stringify(polygon)})
 * SELECT: ST_AsGeoJSON(polygon)
 */
function mapViewportToPolygon(viewport: Viewport): PolygonJSON {
	return {
		type: 'Polygon',
		coordinates: [
			[
				[viewport.northeast.lng, viewport.northeast.lat],
				[viewport.northeast.lng, viewport.southwest.lat],
				[viewport.southwest.lng, viewport.southwest.lat],
				[viewport.southwest.lng, viewport.northeast.lat],
				[viewport.northeast.lng, viewport.northeast.lat],
			],
		],
	};
}

Sorry for the long post but maybe this can shine some clarity onto the way we can manage this type of data via drizzle for others as well.

Cheers

[vwnd]

Hi there, just want to add up to @mattiasbonte as I have a project who makes extensive use of Polygons to represent buildings, rooms inside buildings and building plots. We currently use TypeORM to manage the entities with those spatial columns, but it would be nice to migrate to Drizzle. https://typeorm.io/indices#spatial-indices

[eknowles]

hi @AndriiSherman . I use PostGIS a lot, most tables will use some sort of WKB form of geometry, ideally I would like a column type that could cover any and all of the WKT types.

https://en.wikipedia.org/wiki/Well-known_text_representation_of_geometry

[oscar-corredor]

Same here, we use polygons as well and are having @mattiasbonte's same issue. Just like @vwnd we're still using typeORM when we need to manipulate polygons but it'd be nice to be able to remove TypeORM and completely migrate to Drizzle. If I can be of any assistance for these changes please let me know, I'm more than willing to help.

[GarrisonBates]

I'd also love to see polygons supported in Drizzle. I'm currently trying to get Drizzle working with polygons and I think I'm going to have to settle for raw SQL, which is never ideal. Support for passing WKT directly would also be nice, since I already have a function that spits out WKT (and I'd imagine a lot of other people do too, since this is what libraries like Python's GeoAlchemy use.

[chanmathew]

+1 for polygon support! Until support is added, does anyone have a working temp solution for defining a Drizzle schema that can generate the polygon type column in migrations?

[GarrisonBates]

Yeah, just create the col as a string with drizzle and then edit the migration file to use polygon.

[cwhite92]

Would also love to see support for polygons and multipolygons.

In the meantime I was able to write a custom type:

const polygons = customType<{ data: string }>({
    dataType() {
        return 'geometry(MultiPolygon, 4326)';
    },
});

and use it in my schema like this:

boundary: polygons('boundary').notNull(),

I haven't run it on a real Postgres database yet, but it seems to create the correct migration:

"boundary" geometry(MultiPolygon, 4326) NOT NULL,

cc @chanmathew since you asked for a workaround

[chanmathew]

That's perfect, thank you!

[monopolo11]

Quick question, how would this look in the TS side of drizzle? How would insertions work?

[cwhite92]

@monopolo11 inserts look like this:

const parkToInsert = {
    boundary: JSON.stringify(park.geometry),
    // ... more fields ...
};

await db.insert(parks).values(parkToInsert);

park.geometry in this case is a GeoJSON object that has this shape:

{
    "type": "Polygon",
    "coordinates": [...]
}

Not sure what happens under the hood here, maybe postgis knows what to do when inserting a GeoJSON string into a geometry column... but it works for me lol.

[kravetsone]

@monopolo11 inserts look like this:

const parkToInsert = {
    boundary: JSON.stringify(park.geometry),
    // ... more fields ...
};

await db.insert(parks).values(parkToInsert);

park.geometry in this case is a GeoJSON object that has this shape:

{
    "type": "Polygon",
    "coordinates": [...]
}

Not sure what happens under the hood here, maybe postgis knows what to do when inserting a GeoJSON string into a geometry column... but it works for me lol.

u can also should add to/from driver utilities to custom type

[SitrucL]

• 1 for MultiPolygon support

[ld-gary]

+1 For Polygons!

[torse]

+1 for (Multi) Polygons with the ability to specify other SRIDs then 4326!