Point datatype in PostgreSQL - Drizzle Orm

import Section from "@mdx/Section.astro"; import Prerequisites from "@mdx/Prerequisites.astro"; import CodeTabs from '@mdx/CodeTabs.astro'; import CodeTab from '@mdx/CodeTab.astro'; import Callout from '@mdx/Callout.astro';

<Prerequisites> - Get started with [PostgreSQL](/docs/get-started-postgresql) - [Point datatype](/docs/column-types/pg#point) - [Filtering in select statement](/docs/select#filtering) - [sql operator](/docs/sql) </Prerequisites>

PostgreSQL has a special datatype to store geometric data called point. It is used to represent a point in a two-dimensional space. The point datatype is represented as a pair of (x, y) coordinates. The point expects to receive longitude first, followed by latitude.

<Section> ```ts copy {6} import { sql } from 'drizzle-orm';

const db = drizzle(...);

await db.execute( sqlselect point(-90.9, 18.7), );


```json
[ 
  { 
    point: '(-90.9,18.7)' 
  }
]

</Section>

This is how you can create table with point datatype in Drizzle:

import { pgTable, point, serial, text } from 'drizzle-orm/pg-core';

export const stores = pgTable('stores', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  location: point('location', { mode: 'xy' }).notNull(),
});

This is how you can insert point data into the table in Drizzle:

// mode: 'xy'
await db.insert(stores).values({
  name: 'Test',
  location: { x: -90.9, y: 18.7 },
});

// mode: 'tuple'
await db.insert(stores).values({
  name: 'Test',
  location: [-90.9, 18.7],
});

// sql raw
await db.insert(stores).values({
  name: 'Test',
  location: sql`point(-90.9, 18.7)`,
});

To compute the distance between the objects you can use <-> operator. This is how you can query for the nearest location by coordinates in Drizzle:

<Callout type='warning'> `getColumns` available starting from `[email protected]`(read more [here](/docs/upgrade-v1))

If you are on pre-1 version(like 0.45.1) then use getTableColumns </Callout>

<Section> ```ts {9, 14, 17} import { getColumns, sql } from 'drizzle-orm'; import { stores } from './schema';

const point = { x: -73.935_242, y: 40.730_61, };

const sqlDistance = sqllocation <-> point(${point.x}, ${point.y});

await db .select({ ...getColumns(stores), distance: sqlround((${sqlDistance})::numeric, 2), }) .from(stores) .orderBy(sqlDistance) .limit(1);


```sql
select *, round((location <-> point(-73.935242, 40.73061))::numeric, 2)
from stores order by location <-> point(-73.935242, 40.73061)
limit 1;

</Section>

To filter rows to include only those where a point type location falls within a specified rectangular boundary defined by two diagonal points you can user <@ operator. It checks if the first object is contained in or on the second object:

<Section> ```ts {12} const point = { x1: -88, x2: -73, y1: 40, y2: 43, };

await db .select() .from(stores) .where( sql${stores.location} <@ box(point(${point.x1}, ${point.y1}), point(${point.x2}, ${point.y2})) );


```sql
select * from stores where location <@ box(point(-88, 40), point(-73, 43));

</Section>