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() 函数，对于 geometry types 返回两个几何形状之间的最小平面距离。这是您如何在 Drizzle 中通过 PostGIS 查询最近的位置：

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));