爬取经纬度并根据经纬度计算距离

根据前篇http://yelan.xyz/index.php/2024/04/01/%e6%89%b9%e9%87%8f%e7%88%ac%e5%8f%96%e5%89%8d%e7%9e%bb%e4%ba%a7%e4%b8%9a%e5%9b%ad%e5%ba%93%e5%9b%ad%e5%8c%ba%e4%bf%a1%e6%81%af/

import pandas as pd
import requests

# 读取 Excel 文件
excel_file = r'C:\Users\86191\Desktop\龙圆芝\海新域\海淀区所有园区_过滤且去重后.xlsx'
df = pd.read_excel(excel_file)
def get_coordinates(address):
    url = f"http://api.map.baidu.com/geocoding/v3/?address={address}&city=北京市海淀区&output=json&ak=lPzU67EffiZMbuSRSTIHMIeekZjHMfOi"
    response = requests.get(url)
    data = response.json()
    if data['status'] == 0:
        lng = data['result']['location']['lng']
        lat = data['result']['location']['lat']
        return lng, lat
    else:
        return None, None

# 查询每个园区名对应的地址的经纬度
coordinates = []
for address in df['园区名']:
    lng, lat = get_coordinates(address)
    coordinates.append((lng, lat))

# 将经纬度添加到 DataFrame 中
df['经度'], df['纬度'] = zip(*coordinates)

# 保存带有经纬度的 DataFrame 到新的 Excel 文件中
output_excel_file = r'C:\Users\86191\Desktop\龙圆芝\海新域\海淀区所有园区_经纬度.xlsx'
df.to_excel(output_excel_file, index=False)
print('经纬度已成功添加到 Excel 文件:', output_excel_file)

然后，爬取我们目的地的经纬度

import requests
def get_coordinates(address):
    url = f"http://api.map.baidu.com/geocoding/v3/?address={address}&output=json&ak=lPzU67EffiZMbuSRSTIHMIeekZjHMfOi"
    response = requests.get(url)
    data = response.json()
    if data['status'] == 0:
        lng = data['result']['location']['lng']
        lat = data['result']['location']['lat']
        return lng, lat
    else:
        return None, None
address = '北京市海淀区知春路23号量子银座863'
lng, lat = get_coordinates(address)
if lng is not None and lat is not None:
    print(f"经度: {lng}, 纬度: {lat}")
else:
    print("未找到该地址的经纬度信息")

然后，根据我们目的地和爬的园区的经纬度，算其距离

import pandas as pd
from geopy.distance import geodesic
# 读取目标地点的经纬度
target_lng = 116.37202544195206
target_lat = 40.071862266110685
# 读取 Excel 文件
file_path = r'C:\Users\86191\Desktop\龙圆芝\海新域\海淀区所有园区_经纬度.xlsx'
df = pd.read_excel(file_path)
# 计算每个园区与目标地点之间的距离
distances = []
for index, row in df.iterrows():
    lng = row['经度']
    lat = row['纬度']
    distance = geodesic((target_lat, target_lng), (lat, lng)).kilometers
    distances.append(distance)
# 将距离添加到 DataFrame 中
df['距离（km）'] = distances
# 写入新的 Excel 文件
new_file_path = r'C:\Users\86191\Desktop\龙圆芝\海新域\海淀区所有园区_距离.xlsx'
df.to_excel(new_file_path, index=False)
print('距离信息已写入新的 Excel 文件:', new_file_path)