Python如何查找两个站点列表中与List1最近的经纬度匹配站点

以下提供两种常见匹配逻辑的实现方案，可根据实际业务需求选择：

方案1：按SS No字段直接匹配合并 #

如果两个列表的SS No为唯一关联标识，直接用pandas合并功能即可实现，支持批量处理任意体量数据：

import pandas as pd

# 构造List1数据集，如数据存储在csv/excel中，可替换为pd.read_csv()/pd.read_excel()读取
list1 = pd.DataFrame([
    [977, 23.141747, 53.796469],
    [946, 23.398398, 55.422916],
    [742, 23.615732, 53.717952],
    [980, 23.633077, 55.567046],
    [660, 23.6504, 54.4007]
], columns=['SS No', 'Latitude', 'Longitude'])

# 构造List2数据集
list2 = pd.DataFrame([
    [962, 23.657571, 53.703683],
    [745, 23.671971, 52.955976],
    [743, 23.766849, 53.770344],
    [978, 23.847163, 52.809653],
    [748, 23.942166, 52.16236],
    [744, 23.955817, 52.790424],
    [760, 23.984592, 55.55764],
    [945, 24.030256, 55.844842],
    [894, 24.03511, 53.891547],
    [856, 24.741601, 55.80063],
    [893, 24.04123, 53.899958],
    [387, 24.059988, 51.748138],
    [675, 24.061578, 53.417912],
    [664, 24.063978, 51.76195]
], columns=['SS No', 'Latitude', 'Longitude'])

# 按SS No合并，how参数可根据需求改为left/right/inner/outer
merged_data = pd.merge(list1, list2, on='SS No', how='outer', suffixes=('_list1', '_list2'))
print(merged_data)

方案2：按经纬度空间距离匹配（对应PowerBI手动映射逻辑） #

如果需要根据经纬度的实际距离匹配（即找List1每个站点在List2中最近的站点），推荐用KDTree实现，效率远高于逐行计算距离，支持万级以上数据量，可扩展性强：
首先安装依赖：
pip install pandas scipy geopy

实现代码：

import pandas as pd
from scipy.spatial import KDTree
from geopy.distance import geodesic

# 构造数据集，或从文件读取
list1 = pd.DataFrame([
    [977, 23.141747, 53.796469],
    [946, 23.398398, 55.422916],
    [742, 23.615732, 53.717952],
    [980, 23.633077, 55.567046],
    [660, 23.6504, 54.4007]
], columns=['SS No', 'Latitude', 'Longitude'])

list2 = pd.DataFrame([
    [962, 23.657571, 53.703683],
    [745, 23.671971, 52.955976],
    [743, 23.766849, 53.770344],
    [978, 23.847163, 52.809653],
    [748, 23.942166, 52.16236],
    [744, 23.955817, 52.790424],
    [760, 23.984592, 55.55764],
    [945, 24.030256, 55.844842],
    [894, 24.03511, 53.891547],
    [856, 24.741601, 55.80063],
    [893, 24.04123, 53.899958],
    [387, 24.059988, 51.748138],
    [675, 24.061578, 53.417912],
    [664, 24.063978, 51.76195]
], columns=['SS No', 'Latitude', 'Longitude'])

# 提取List2经纬度构建KDTree
list2_coords = list2[['Latitude', 'Longitude']].values
kdtree = KDTree(list2_coords)

# 对List1每个点查询最近的1个List2点
distances, indices = kdtree.query(list1[['Latitude', 'Longitude']].values, k=1)

# 合并匹配结果到List1
list1['matched_SSNo_list2'] = list2.iloc[indices]['SS No'].values
list1['matched_Latitude_list2'] = list2.iloc[indices]['Latitude'].values
list1['matched_Longitude_list2'] = list2.iloc[indices]['Longitude'].values
# 计算实际球面距离（单位：米）
list1['distance_m'] = list1.apply(
    lambda row: geodesic((row['Latitude'], row['Longitude']),
                        (row['matched_Latitude_list2'], row['matched_Longitude_list2'])).m,
    axis=1
)

# 可自定义距离阈值过滤误匹配记录，比如超过5000米就判定为无匹配
# list1 = list1[list1['distance_m'] <= 5000]

print(list1)

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