PostGIS基于距离的条件双重排序查询问题与解决方案

需求 #

查询指定位置周边的坐标数据，排序规则如下：

• 若多个坐标间距离小于50米，这组坐标内部按评分（rating）排序
• 无邻近50米内坐标的单独位置，按到主坐标的距离排序

初始尝试及问题 #

最初尝试用以下SQL实现，但无法满足需求：

SELECT *
FROM (
    SELECT 
        *
    FROM public."MY_TABLE" AS t1
) AS TB 
WHERE ST_Intersects(
    ST_MakeEnvelope(
        -0.102769, 51.506590,
        -0.044822, 51.542643,
        4326
    ),
    TB.coordinates::geometry
)
ORDER BY
    CASE
        WHEN ST_Distance(Tb.coordinates::geography, Tb.coordinates::geography) < 50 THEN TB.rating
        ELSE ST_Distance(TB.coordinates::geography, ST_SetSRID(ST_MakePoint(-0.073796, 51.524617), 4326))
    END;

核心问题：ST_Distance(Tb.coordinates::geography, Tb.coordinates::geography)仅对比同一行数据的坐标（如Row1与Row1），无法识别不同行之间（如Row1与Row2）的距离是否小于50米，因此达不到分组排序的效果。

最终解决方案 #

通过ST_ClusterDBSCAN将邻近（50米内）的位置聚类分组，计算每个聚类的质心，实现先按聚类质心到主坐标的距离排序，聚类内部再按评分排序的效果，最终SQL代码如下：

WITH clusters AS (
                  SELECT *, ST_ClusterDBSCAN(coordinates::geometry, eps := 0.00045, minpoints := 2) OVER () AS cluster_id
                  FROM public."MY_TABLE"
                  WHERE ST_DWithin(coordinates::geography, ST_SetSRID(ST_MakePoint(-0.073796, 51.524617), 4326), 2000.000000)
                  ), 
                  centroids AS (
                        SELECT cluster_id,
                                   ST_Centroid(ST_Collect(coordinates::geometry)) AS cluster_centroid
                        FROM clusters
                        WHERE cluster_id IS NOT NULL
                        GROUP BY cluster_id
                )
                SELECT clusters.id, clusters.name, clusters.website, clusters.coordinates, clusters.description, clusters.rating
                FROM clusters
                LEFT JOIN centroids 
                ON clusters.cluster_id = centroids.cluster_id
                ORDER BY 
                        CASE 
                                WHEN clusters.cluster_id IS NULL THEN ST_Distance(clusters.coordinates::geometry, ST_SetSRID(ST_MakePoint(-0.073796, 51.524617), 4326))
                                ELSE ST_Distance(centroids.cluster_centroid::geometry, ST_SetSRID(ST_MakePoint(-0.073796, 51.524617), 4326))
                        END,
                        rating

关键说明：

• ST_ClusterDBSCAN的eps := 0.00045对应EPSG:4326坐标系下约50米的地理距离
• ST_DWithin先筛选主坐标2000米范围内的数据，缩小计算范围
• 无聚类的单独位置直接按到主坐标的距离排序；聚类内的位置先按聚类质心到主坐标的距离排序，再按评分默认升序排列，需降序可添加DESC

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