Drizzle ORM 한글 - PostGIS geometry point

Drizzle | PostGIS geometry point

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) => [
    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() 함수를 사용할 수 있습니다. geometry types의 경우 두 기하학 객체 간의 최소 평면 거리를 반환합니다. PostGIS를 사용하여 Drizzle에서 좌표로 가장 가까운 위치를 쿼리하는 방법입니다:

IMPORTANT

getColumns는 drizzle-orm@1.0.0-beta.2부터 사용 가능합니다(자세히 보기)

0.45.1과 같은 pre-1 버전을 사용하는 경우 getTableColumns를 사용하세요

import { getColumns, 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({
    ...getColumns(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의 최소값과 최대값으로 직사각형 Polygon을 생성합니다. 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));