基于PostGIS查询指定英里半径内最近商家并按距离排序的SQL语句

假设数据表结构 #

以下是基于常见业务场景假设的表结构，你可根据实际字段调整：

• users
  • user_id (INT)：用户唯一标识
  • address_id (INT)：关联地址表的外键
• addresses
  • address_id (INT)：地址唯一标识
  • geom (GEOGRAPHY(POINT, 4326))：存储WGS84坐标系的经纬度点（推荐用GEOGRAPHY类型做球面距离计算）
• businesses
  • business_id (INT)：商家唯一标识
  • business_name (VARCHAR)：商家名称
  • address_id (INT)：关联地址表的外键

查询语句（针对GEOGRAPHY类型） #

示例：查找用户ID为123、5英里半径内的商家，按距离由近到远排序：

SELECT
    b.business_id,
    b.business_name,
    -- 计算球面距离并转换为英里，保留2位小数
    ROUND(ST_Distance(u_addr.geom, b_addr.geom) / 1609.34, 2) AS distance_miles
FROM
    users u
-- 关联用户的地址地理数据
INNER JOIN addresses u_addr 
    ON u.address_id = u_addr.address_id
-- 关联商家及其地址地理数据
INNER JOIN businesses b
INNER JOIN addresses b_addr 
    ON b.address_id = b_addr.address_id
WHERE
    u.user_id = 123
    -- 筛选5英里范围内的商家（1英里≈1609.34米）
    AND ST_DWithin(u_addr.geom, b_addr.geom, 5 * 1609.34)
-- 按距离升序排序
ORDER BY
    distance_miles ASC;

关键说明 #

• ST_DWithin：优先用这个函数做范围过滤，它能利用空间索引大幅提升查询效率，比先计算所有距离再过滤快得多。
• ST_Distance：针对GEOGRAPHY类型会自动计算球面距离（单位：米），除以1609.34转换为英里。
• 空间索引：确保addresses.geom字段已创建空间索引，执行CREATE INDEX idx_addresses_geom ON addresses USING GIST(geom);可创建。

适配GEOMETRY类型的版本 #

如果你的addresses.geom是GEOMETRY类型（SRID=4326），可转换为Web墨卡托投影（SRID=3857）做平面距离计算（小范围查询精度足够）：

SELECT
    b.business_id,
    b.business_name,
    ROUND(
        ST_Distance(
            ST_Transform(u_addr.geom, 3857),
            ST_Transform(b_addr.geom, 3857)
        ) / 1609.34,
        2
    ) AS distance_miles
FROM
    users u
INNER JOIN addresses u_addr ON u.address_id = u_addr.address_id
INNER JOIN businesses b
INNER JOIN addresses b_addr ON b.address_id = b_addr.address_id
WHERE
    u.user_id = 123
    AND ST_DWithin(
        ST_Transform(u_addr.geom, 3857),
        ST_Transform(b_addr.geom, 3857),
        5 * 1609.34
    )
ORDER BY
    distance_miles ASC;

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