NestJS+TypeORM+PostgreSQL环境下GeoJSON点存储与范围查询求助

前置准备 #

• 确保PostgreSQL已安装PostGIS扩展，进入目标数据库执行激活语句：CREATE EXTENSION IF NOT EXISTS postgis;
• 安装GeoJSON类型定义：npm i @types/geojson -D 用于TypeScript类型校验

实体定义修正 #

你原有代码存在两个问题：一是eval调用存在严重安全风险，二是current_location类型标注错误。修正后完整代码如下：

import { Point } from 'geojson';
import { Raw, Column, Index } from 'typeorm';
import { ApiProperty } from '@nestjs/swagger';

// 实体类内部属性定义
@ApiProperty({
  type: Object,
  title: 'current_location',
  example: {
    type: 'Point',
    coordinates: [116.403874, 39.914885]
  },
})
@Index({ spatial: true })
@Column({
  type: 'geometry',
  srid: 4326,
  nullable: true,
  spatialFeatureType: 'Point',
  transformer: {
    to: (v: Point) => {
      return Raw(() => `ST_GeomFromGeoJSON('${JSON.stringify(v)}')`);
    },
    from: (v: any) => {
      return { type: 'Point', coordinates: [v.x, v.y] } as Point;
    },
  },
})
current_location: Point;

注意：GeoJSON规范要求坐标顺序为[经度, 纬度]，不要写反

核心功能实现 #

点位存储 #

直接传入符合GeoJSON规范的Point对象即可，转换器会自动处理为PostGIS识别的空间类型：

const newRecord = this.locationRepository.create({
  current_location: {
    type: 'Point',
    coordinates: [116.403874, 39.914885]
  }
});
await this.locationRepository.save(newRecord);

两点距离计算 #

使用ST_DistanceSphere函数计算，返回结果单位为米：

// 目标点经纬度
const targetLon = 116.403874;
const targetLat = 39.914885;

const result = await this.locationRepository
  .createQueryBuilder('location')
  .select(`ST_DistanceSphere(
    location.current_location,
    ST_SetSRID(ST_MakePoint(:lon, :lat), 4326)
  )`, 'distance')
  .setParameters({ lon: targetLon, lat: targetLat })
  .where('location.id = :targetId', { targetId: 1 })
  .getRawOne();

// 取出距离数值：result.distance

指定范围点位查询 #

使用ST_DWithin函数，会自动调用你创建的空间索引，查询性能远高于JSONB方案，支持海量数据快速检索：

// 目标点经纬度，查询半径单位为米
const targetLon = 116.403874;
const targetLat = 39.914885;
const searchRadius = 5000; // 5公里范围

const nearRecords = await this.locationRepository
  .createQueryBuilder('location')
  .where(`ST_DWithin(
    location.current_location::geography,
    ST_SetSRID(ST_MakePoint(:lon, :lat), 4326)::geography,
    :radius
  )`)
  .setParameters({ lon: targetLon, lat: targetLat, radius: searchRadius })
  .addSelect(`ST_DistanceSphere(
    location.current_location,
    ST_SetSRID(ST_MakePoint(:lon, :lat), 4326)
  )`, 'distance')
  .orderBy('distance', 'ASC')
  .getRawMany();

内容的提问来源于stack exchange，提问作者Hussain Wali