Drizzle ORM - PostGIS 几何点 - Drizzle ORM 文档

Drizzle | PostGIS 几何点

本指南假定您熟悉

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 返回两个几何体之间的最小平面距离。以下是如何在 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 的最小值和最大值创建一个矩形多边形。如果几何体 A 位于几何体 B 内，ST_Within() 返回 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));