Drizzle ORM - PostGIS 지오메트리 포인트

Drizzle | PostGIS 지오메트리 포인트

This guide assumes familiarity with:

PostGIS는 PostgreSQL 관계형 데이터베이스의 기능을 확장하여 지리 공간 데이터를 저장, 인덱싱, 쿼리할 수 있도록 지원합니다.

현재 Drizzle은 확장 기능을 자동으로 생성하지 않으므로, 수동으로 생성해야 합니다. 빈 마이그레이션 파일을 만들고 SQL 쿼리를 추가하세요.

npx drizzle-kit generate --custom

CREATE EXTENSION postgis;

Drizzle에서 geometry 데이터 타입과 공간 인덱스를 가진 테이블을 생성하는 방법은 다음과 같습니다.

schema.ts

migration.sql

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

export const stores = pgTable(
  'stores',
  {
    id: serial('id').primaryKey(),
    name: text('name').notNull(),
    location: geometry('location', { type: 'point', mode: 'xy', srid: 4326 }).notNull(),
  },
  (t) => ({
    spatialIndex: index('spatial_index').using('gist', t.location),
  }),
);

CREATE TABLE IF NOT EXISTS "stores" (
    "id" serial PRIMARY KEY NOT NULL,
    "name" text NOT NULL,
    "location" geometry(point) NOT NULL
);
--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "spatial_index" ON "stores" USING gist ("location");

Drizzle에서 geometry 데이터를 테이블에 삽입하는 방법은 다음과 같습니다. PostGIS의 ST_MakePoint()는 지정된 좌표를 사용하여 point 타입의 지오메트리 객체를 생성합니다. ST_SetSRID()는 지오메트리의 SRID(특정 좌표계, 허용 오차 및 해상도와 연결된 고유 식별자)를 특정 정수 값으로 설정합니다.

// 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`ST_SetSRID(ST_MakePoint(-90.9, 18.7), 4326)`,
});

객체 간의 거리를 계산하려면 <-> 연산자와 ST_Distance() 함수를 사용할 수 있습니다. ST_Distance()는 geometry 타입에 대해 두 지오메트리 간의 최소 평면 거리를 반환합니다. PostGIS와 함께 Drizzle에서 좌표를 기준으로 가장 가까운 위치를 쿼리하는 방법은 다음과 같습니다.

import { getTableColumns, sql } from 'drizzle-orm';
import { stores } from './schema';

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

const sqlPoint = sql`ST_SetSRID(ST_MakePoint(${point.x}, ${point.y}), 4326)`;

await db
  .select({
    ...getTableColumns(stores),
    distance: sql`ST_Distance(${stores.location}, ${sqlPoint})`,
  })
  .from(stores)
  .orderBy(sql`${stores.location}  ${sqlPoint}`)
  .limit(1);

select *, ST_Distance(location, ST_SetSRID(ST_MakePoint(-73.935_242, 40.730_61), 4326))
from stores order by location  ST_SetSRID(ST_MakePoint(-73.935_242, 40.730_61), 4326)
limit 1;

지정된 직사각형 영역 내에 위치한 매장을 필터링하려면 ST_MakeEnvelope()와 ST_Within() 함수를 사용할 수 있습니다. ST_MakeEnvelope()는 X와 Y의 최소 및 최대 값을 사용하여 직사각형 폴리곤을 생성합니다. ST_Within()은 지오메트리 A가 지오메트리 B 내부에 있으면 TRUE를 반환합니다.

const point = {
  x1: -88,
  x2: -73,
  y1: 40,
  y2: 43,
};

await db
  .select()
  .from(stores)
  .where(
    sql`ST_Within(
      ${stores.location}, ST_MakeEnvelope(${point.x1}, ${point.y1}, ${point.x2}, ${point.y2}, 4326)
    )`,
  );

select * from stores where ST_Within(location, ST_MakeEnvelope(-88, 40, -73, 43, 4326));