Python:通过地址获得经纬度并判断离得最近的门店

前言：代码部分其实不难，短短80行左右的代码就能完成，甚至还可以进一步简化。真正的难点在于标准化地址的获得（也就是数据清洗），只要有一些误差就会千差万别。

import requests
import openpyxl
from math import radians, cos, sin, asin, sqrt


def getjwd(address):
    '''根据地址获得经纬度'''
    url = 'http://api.map.baidu.com/geocoder?output=json&key=f247cdb592eb43ebac6ccd27f796e2d2&address='+str(address)
    response = requests.get(url)
    answer = response.json()
    lng = float(answer['result']['location']['lng']) #经度 Longitude  简写Lng
    lat = float(answer['result']['location']['lat']) #纬度 Latitude   简写Lat
    return lng, lat

def get_store_add():
    '''获得门店地址的经纬度'''
    wb = openpyxl.load_workbook('网格对应关系（通过经纬度判断最近门店）.xlsx')
    ws = wb['营业厅']
    maxrow = ws.max_row
    for i in range(2, maxrow + 1):
        address = ws["B"+str(i)].value
        ws["C" + str(i)].value = getjwd(address)[0]
        ws["D" + str(i)].value = getjwd(address)[1]
    wb.save('网格对应关系（通过经纬度判断最近门店）.xlsx')
    wb.close()

def get_wg_add():
    '''获得网格地址的经纬度'''
    wb = openpyxl.load_workbook('网格对应关系（通过经纬度判断最近门店）.xlsx')
    ws = wb['网格对应关系']
    maxrow = ws.max_row
    for i in range(2, maxrow + 1):
        address = ws["H" + str(i)].value
        temp_list = getjwd(address)
        ws["J" + str(i)].value = temp_list[0]
        ws["K" + str(i)].value = temp_list[1]
        print("{}的经度是{}，纬度是{}".format(address, ws["J" + str(i)].value, ws["K" + str(i)].value))
    wb.save('网格对应关系（通过经纬度判断最近门店）.xlsx')
    wb.close()

def get_distance(lng1,lat1,lng2,lat2):
    '''根据2个地址的经纬度获得直线距离'''
    lng1, lat1, lng2, lat2 = map(radians, [float(lng1), float(lat1), float(lng2), float(lat2)])  # 经纬度转换成弧度
    dlon = lng2 - lng1
    dlat = lat2 - lat1
    a = sin(dlat / 2) ** 2 + cos(lat1) * cos(lat2) * sin(dlon / 2) ** 2
    distance = 2 * asin(sqrt(a)) * 6371 * 1000  # 地球平均半径，6371km
    distance = round(distance, 0)
    return distance

def get_nearest_store():
    '''获得最近的门店名称'''
    wb = openpyxl.load_workbook('网格对应关系（通过经纬度判断最近门店）.xlsx')
    ws_wg = wb['网格对应关系']
    ws_wg_maxrow = ws_wg.max_row
    ws_store = wb['营业厅']
    ws_store_maxrow = ws_store.max_row
    for i in range(2, ws_wg_maxrow + 1):
        try:
            wg_add = ws_wg["H" + str(i)].value
            wg_lng = ws_wg["J" + str(i)].value
            wg_lat = ws_wg["K" + str(i)].value
            distance = 120000 #先给一个最大的距离
            for j in range(2, ws_store_maxrow + 1):
                store_name = ws_store["A" + str(j)].value
                store_lng = ws_store["C" + str(j)].value
                store_lat = ws_store["D" + str(j)].value
                temp_distance = get_distance(wg_lng, wg_lat, store_lng, store_lat)
                if temp_distance < distance:
                    distance = temp_distance
                    nearest_store = store_name
                else: continue
            ws_wg["L" + str(i)].value = nearest_store
            print("{}最近的门店是：{}".format(wg_add, nearest_store))
        except:
            continue
    wb.save('网格对应关系（通过经纬度判断最近门店）.xlsx')
    wb.close()

if __name__ =='__main__':
    get_store_add()
    get_wg_add()
    get_nearest_store()