如何根据指定经纬度点的距离获取并排序DE表记录

原代码存在的问题 #

• 参数名冲突：原函数的lat、long参数与DE表的字段名完全一致，PostgreSQL会优先引用表字段值，导致计算距离时用的是记录自身的经纬度，而非传入的参数。
• 返回结构不匹配：原查询返回的结果包含额外的distance字段，但函数声明返回setof "DE"，字段数量和类型不匹配会直接报错。
• 多余的LIMIT限制：需求是获取所有记录，LIMIT 20会截断结果，不符合要求。
• 经纬度顺序错误：PostgreSQL原生point类型遵循(x, y)格式，对应地理坐标的经度（x）、纬度（y），原代码把纬度放在前面会导致距离计算完全错误。

正确的函数实现 #

简洁版（推荐，性能更优）

create or replace function get_sorted_de(p_lat float, p_long float)
returns setof "DE" as $$
BEGIN
    return query 
        select de.*
        from "DE" de
        -- 按传入点与记录点的球面距离排序，注意经纬度顺序：(经度, 纬度)
        order by point(de.long, de.lat) <-> point(p_long, p_lat);
END;
$$ language plpgsql;

带CTE的版本（便于扩展其他逻辑）

create or replace function get_sorted_de(p_lat float, p_long float)
returns setof "DE" as $$
BEGIN
    with distances as (
        select 
            de.*,
            -- 计算每个点与传入参数的距离
            point(de.long, de.lat) <-> point(p_long, p_lat) as distance
        from "DE" de
    )
    return query 
        -- 只返回DE表的原始字段，保证与返回类型匹配
        select (distances).*
        from distances
        order by distance;
END;
$$ language plpgsql;

性能优化建议 #

如果DE表的数据量较大，建议给经纬度字段创建GiST索引，让<->运算符可以利用索引快速完成排序，大幅提升查询速度：

create index idx_de_geo_point on "DE" using gist (point(long, lat));

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