如何查找比客户当前附着基站距离更近的基站位置点

需求描述 #

现有客户-附着基站关联表，字段如下：

• customer_number：客户编号
• cx_lat、cx_long：客户位置经纬度
• tower_lat、tower_long：当前附着基站经纬度
• Latched_tower_ID：当前附着基站ID
• Distance：客户与当前附着基站的距离（单位：KM）

需要为每个客户匹配距离比当前附着基站更近的基站，若不存在更近基站，则对应更近基站ID、距离字段返回NULL。

举个例子：某客户当前附着基站KA001，距离1.6KM，另有基站KA002与客户距离仅1.3KM，KA002即为需要匹配的更近基站。

原始表数据 #

customer_number   cx_lat       cx_long      tower_lat   tower_long  Latched_tower_ID   Distance
34532             6.897257333  79.86474533  6.890487    79.869199   CM0321             0.51477
43445             6.935598403  81.14939421  6.947618    81.160246   BD0010             1.2292
54365             6.866224     79.88215     6.896111    79.868611   CM0037             1.6216
52568             7.113198     80.037247    7.121666    80.028888   GM0121             0.9476

期望输出结果 #

customer_number   cx_lat       cx_long      tower_lat   tower_long  Latched_tower_ID   Distance  Cloesed_tower_ID  Closed_Distance
34532             6.897257333  79.86474533  6.890487    79.869199   CM0321             0.51477   CM0037            0.43222
52568             7.113198     80.037247    7.121666    80.028888   GM0121             0.9476  NULL                NULL

SQL实现参考 #

你需要先准备一张全量基站信息表tower_all，包含所有基站的tower_id、tower_lat、tower_long字段，再用以下SQL查询（基于MySQL语法，距离计算采用哈弗辛公式，单位为KM）：

SELECT 
    a.customer_number,
    a.cx_lat,
    a.cx_long,
    a.tower_lat,
    a.tower_long,
    a.Latched_tower_ID,
    a.Distance,
    SUBSTRING_INDEX(GROUP_CONCAT(b.tower_id ORDER BY 
        6371 * ACOS(
            COS(RADIANS(a.cx_lat)) * COS(RADIANS(b.tower_lat)) *
            COS(RADIANS(b.tower_long) - RADIANS(a.cx_long)) +
            SIN(RADIANS(a.cx_lat)) * SIN(RADIANS(b.tower_lat))
        ) ASC), ',', 1) AS Closest_tower_ID,
    MIN(ROUND(6371 * ACOS(
        COS(RADIANS(a.cx_lat)) * COS(RADIANS(b.tower_lat)) *
        COS(RADIANS(b.tower_long) - RADIANS(a.cx_long)) +
        SIN(RADIANS(a.cx_lat)) * SIN(RADIANS(b.tower_lat))
    ), 5)) AS Closed_Distance
FROM customer_latched_tower a
LEFT JOIN tower_all b 
    ON b.tower_id != a.Latched_tower_ID
    AND ROUND(6371 * ACOS(
        COS(RADIANS(a.cx_lat)) * COS(RADIANS(b.tower_lat)) *
        COS(RADIANS(b.tower_long) - RADIANS(a.cx_long)) +
        SIN(RADIANS(a.cx_lat)) * SIN(RADIANS(b.tower_lat))
    ), 5) < a.Distance
GROUP BY a.customer_number, a.cx_lat, a.cx_long, a.tower_lat, a.tower_long, a.Latched_tower_ID, a.Distance

如果你所有基站ID都已经存在于当前表的Latched_tower_ID字段中，也可以用自关联替换tower_all表，关联当前表的去重基站数据即可。

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