1
import sys
2

3
print('python 版本：',sys.version.split('|')[0])
4
#python 版本： 3.11.9
5

6
import pandas as pd
7

8
print("pandas 版本：",pd.__version__)
9
#pandas 版本： 2.2.2

1
data=pd.read_excel('data_test.xlsx',
2
                   sheet_name='data',
3
                   engine='calamine')
4

5
print(len(data)) #100000
6

7
print(data.head())
8
#id   longitude   latitude
9
0  id1  114.657838  30.077734
10
1  id2  105.150585  27.599929
11
2  id3   97.396759  22.805973
12
3  id4  124.282532  41.283089
13
4  id5  115.477867  23.781731

1
from math import sin, asin, cos, radians, fabs, sqrt
2

3
EARTH_RADIUS = 6371      # 地球平均半径大约6371km
4

5

6
def hav(theta):
7
    s = sin(theta / 2)
8
    return s * s
9

10

11
def get_distance_hav(lat0, lng0, lat1, lng1):
12
    # 用haversine公式计算球面两点间的距离
13
    # 经纬度转换成弧度
14
    lat0 = radians(lat0)
15
    lat1 = radians(lat1)
16
    lng0 = radians(lng0)
17
    lng1 = radians(lng1)
18
    dlng = fabs(lng0 - lng1)
19
    dlat = fabs(lat0 - lat1)
20
    h = hav(dlat) + cos(lat0) * cos(lat1) * hav(dlng)
21
    distance = 2 * EARTH_RADIUS * asin(sqrt(h))      # km
22
    return distance

1
%%timeit -n10 -r10
2

3
# import timeit
4

5
result={}
6
for row1 in data.itertuples():
7
#     start=time.time()
8

9
    near_id=[]
10

11
    for row2 in data.itertuples():
12

13
        if row1.id != row2.id \
14
        and get_distance_hav(row1.latitude,
15
                             row1.longitude,
16
                             row2.latitude,
17
                             row2.longitude)<=10.0:
18

19
            near_id.append(row2.id)
20

21
    result[row1.id]=near_id
22

23
#     print(f"耗时：{time.time()-start}秒")
24
    break

1
import numpy as np
2

3
EARTH_RADIUS = 6371  # 地球平均半径大约6371km
4

5
def hav(theta):
6
    s = np.sin(theta / 2)
7
    return s * s
8

9
def get_distance_hav_vectorized(lat0, lng0, lat1, lng1):
10
    # 用haversine公式计算球面两点间的距离
11
    # 经纬度转换成弧度
12
    lat0 = np.radians(lat0)
13
    lat1 = np.radians(lat1)
14
    lng0 = np.radians(lng0)
15
    lng1 = np.radians(lng1)
16

17
    dlng = np.abs(lng0 - lng1)
18
    dlat = np.abs(lat0 - lat1)
19

20
    h = hav(dlat) + np.cos(lat0) * np.cos(lat1) * hav(dlng)
21
    distance = 2 * EARTH_RADIUS * np.arcsin(np.sqrt(h))  # km
22

23
    return distance

1
%%timeit -n10 -r100
2

3
result={}
4

5
# 对 data 逐行处理
6
for row in data.itertuples():
7
#     start = time.time()
8

9
    t = get_distance_hav_vectorized(row.latitude,
10
                                    row.longitude,
11
                                    data['latitude'],
12
                                    data['longitude'])
13

14
    result[row.id] = ','.join(data.loc[(0<t) & (t<=10.0),'id'].to_list())
15

16
    break
17

18
# print(f"耗时：{time.time() - start}秒")

1
import polars as pl
2

3
print(pl.__version__) #1.2.1
4

5
data_pl = pl.read_excel('data_test.xlsx',sheet_name='data')
6

7
import math
8

9
EARTH_RADIUS = 6371  # 地球平均半径大约6371km
10

11
def hav_pl(theta):
12
    s = (theta / 2).sin()
13
    return s * s
14

15

16
def get_distance_hav_vectorized_pl(lat0, lng0, lat1, lng1):
17
    # 用haversine公式计算球面两点间的距离
18
    # 经纬度转换成弧度
19

20
    lat0 = math.radians(lat0)
21
    lng0 = math.radians(lng0)
22

23
    lat1=lat1.to_frame().select(pl.col("latitude").radians())
24
    lng1=lng1.to_frame().select(pl.col("longitude").radians())
25

26

27

28
    dlng = (lng0 - lng1.to_series()).abs()
29
    dlat = (lat0 - lat1.to_series()).abs()
30

31
    h = hav_pl(dlat) + math.cos(lat0) * lat1.to_series().cos() * hav_pl(dlng)
32
    distance = 2 * EARTH_RADIUS * h.sqrt().arcsin()  # km
33

34
    return distance

1
%%timeit -n10 -r100
2

3
result={}
4

5
for row in data_pl.iter_rows(named=True):
6

7
    t = get_distance_hav_vectorized_pl(row['latitude'],
8
                                       row['longitude'],
9
                                       data_pl['latitude'],
10
                                       data_pl['longitude'])
11

12

13
    result[row['id']] = ','.join(data_pl.filter((t>0) & (t<=5))['id'].to_list())
14

15
    break