PostgreSQL 9.6.2：不改坐标列类型实现坐标范围查询

当然没问题！不用修改coordinates列的类型就能实现这个地理范围筛选，针对你的PostgreSQL 9.6.2版本，我给你两种可行的方案，按需选择：

PostGIS是PostgreSQL官方的地理空间扩展，处理这类距离查询非常顺手，哪怕你的坐标存在jsonb里也完全ok。

1. 先确认安装好PostGIS（如果没装的话执行这条）：

CREATE EXTENSION IF NOT EXISTS postgis;

2. 编写查询语句
   我们需要把jsonb里的经纬度提取出来，转换成PostGIS的地理对象，再用ST_DWithin函数判断是否在指定距离内（注意这里距离单位是米，因为地理类型默认用米计算）。

假设客户端传的参数是lat=65.852104、lng=6.937035、distance=300（米），查询语句如下：

SELECT *
FROM matches
WHERE ST_DWithin(
    -- 把jsonb里的坐标转成GEOGRAPHY类型
    ST_SetSRID(ST_MakePoint(
        (coordinates->>'lng')::float8,
        (coordinates->>'lat')::float8
    ), 4326)::GEOGRAPHY,
    -- 客户端传入的目标坐标转成GEOGRAPHY类型
    ST_SetSRID(ST_MakePoint(6.937035, 65.852104), 4326)::GEOGRAPHY,
    300  -- 距离阈值，单位米
);

小提示：PostGIS创建点的时候是先经度后纬度，别搞反了哦！

3. 优化查询速度
   如果这个查询用得很频繁，建议建一个函数索引，避免每次查询都重复转换jsonb到地理对象：

CREATE INDEX idx_matches_coordinates_geog ON matches
USING GIST (
    ST_SetSRID(ST_MakePoint(
        (coordinates->>'lng')::float8,
        (coordinates->>'lat')::float8
    ), 4326)::GEOGRAPHY
);

如果没法装PostGIS扩展，也可以用PostgreSQL自带的earthdistance和cube扩展来实现球面距离计算。

1. 先安装需要的扩展：

CREATE EXTENSION IF NOT EXISTS earthdistance;
CREATE EXTENSION IF NOT EXISTS cube;

2. 编写查询语句
   这个方法的距离单位默认是英里，所以需要把客户端传的米转换成英里（1米≈0.000621371英里，300米≈0.1864英里）。查询时先用earth_box做粗过滤，再用earth_distance做精确判断，这样效率更高：

SELECT *
FROM matches
WHERE 
    -- 粗过滤：快速排除明显不在范围内的点
    earth_box(
        ll_to_earth(65.852104, 6.937035),
        0.1864  -- 转换后的英里距离
    ) @> ll_to_earth(
        (coordinates->>'lat')::float8,
        (coordinates->>'lng')::float8
    )
    -- 精确过滤：计算实际球面距离是否符合要求
    AND earth_distance(
        ll_to_earth(65.852104, 6.937035),
        ll_to_earth(
            (coordinates->>'lat')::float8,
            (coordinates->>'lng')::float8
        )
    ) <= 0.1864;

3. 优化索引
   同样可以建索引提升查询效率：

CREATE INDEX idx_matches_coordinates_earth ON matches
USING GIST (ll_to_earth(
    (coordinates->>'lat')::float8,
    (coordinates->>'lng')::float8
));

• 要确保jsonb里的lat和lng字段存在且是有效的数字，避免转换出错。可以加个判断条件，比如coordinates ? 'lat' AND coordinates ? 'lng' AND (coordinates->>'lat') ~ '^-?\d+\.?\d*$'
• 两种方案在PostgreSQL 9.6.2上都能正常运行，放心用

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