PostgreSQL GEO 索引基础使用入门笔记

日常业务开发中，经常会存储海量门店、用户点位等地理坐标数据，需要实现「附近点位查询」「指定范围筛选」等需求。直接使用经纬度数值对比查询性能极差，PostGIS 提供的 GEO 空间索引可以大幅优化地理位置检索效率。本文整理一份零基础实操笔记，包含环境部署、建表、索引创建、常用查询语句，方便后续复盘查阅。
一、环境前置准备
想要使用空间地理能力，PostgreSQL 需要先安装 PostGIS 扩展，执行 SQL 开启扩展：
sql
-- 开启postgis空间扩展
CREATE EXTENSION IF NOT EXISTS postgis;
校验扩展是否安装成功，执行查询语句，能正常返回版本号即代表可用：
sql
SELECT PostGIS_Version();
二、创建带地理点位的业务表
使用 geometry(Point,4326) 存储经纬度坐标，4326 代表 WGS84 通用坐标系（GPS 标准坐标）。
sql
CREATE TABLE map_point (
id BIGSERIAL PRIMARY KEY,
point_name VARCHAR(100) NOT NULL,
lng NUMERIC(10,6), -- 经度
lat NUMERIC(10,6), -- 纬度
geom geometry(Point,4326) -- 空间点位字段
);

-- 插入测试数据，ST_SetSRID + ST_MakePoint 转换经纬度为空间点
INSERT INTO map_point (point_name, lng, lat, geom)
VALUES
('测试点位A', 120.62, 31.30, ST_SetSRID(ST_MakePoint(120.62, 31.30),4326)),
('测试点位B', 120.63, 31.31, ST_SetSRID(ST_MakePoint(120.63, 31.31),4326)),
('测试点位C', 120.65, 31.29, ST_SetSRID(ST_MakePoint(120.65, 31.29),4326));
三、创建 GEO 空间索引（核心优化步骤）
普通 B 树索引无法优化空间距离查询，必须创建 GIST 类型空间索引，这是地理检索提速的关键：
sql
-- 给geom空间字段创建GIST索引
CREATE INDEX idx_map_point_geom ON map_point USING GIST (geom);
索引创建完成后，百万级点位的范围、距离查询速度会提升数十倍。
四、常用 GEO 查询实操代码

1. 查询指定坐标周边 N 公里内所有点位
   ST_DWithin 是最常用的周边检索函数，单位为米，1000 代表 1 公里：
   sql
   -- 查询坐标(120.62,31.30)周边2000米内所有点位
   SELECT
   id,
   point_name,
   lng,
   lat,
   ST_Distance(geom, ST_SetSRID(ST_MakePoint(120.62,31.30),4326)) * 111000 AS distance_meter
   FROM map_point
   WHERE ST_DWithin(
   geom,
   ST_SetSRID(ST_MakePoint(120.62,31.30),4326),
   2000
   )
   ORDER BY distance_meter ASC;
   说明：ST_Distance 默认单位是度，1 度≈111 公里，乘以 111000 转换为米。
2. 计算两点之间直线距离
   sql
   -- 计算两个点位的直线距离（米）
   SELECT
   ST_Distance(
   ST_SetSRID(ST_MakePoint(120.62,31.30),4326),
   ST_SetSRID(ST_MakePoint(120.63,31.31),4326)
   ) * 111000 AS distance_meter;
   五、开发踩坑小结
   坐标系统一：所有点位必须统一使用 4326，混用坐标系会导致索引失效、距离计算错误；
   索引失效场景：查询时对 geom 字段做函数转换，会跳过 GIST 索引，尽量保证查询条件直接使用原始 geom 字段；
   数据量大优化：超过千万条点位时，可结合分区表、GIN 索引进一步优化检索性能；
   精度问题：存储经纬度保留 6 位小数，足以满足日常地图点位业务需求，无需过度加长小数位。
   结尾
   本文仅记录 PostGIS GEO 索引基础入门用法，后续会持续更新海量地理数据调优、空间多边形范围检索、批量坐标导入清洗等实操笔记，和各位开发同行交流学习。