通过导入Excel文件(包含一列需要标注的地址),通过百度地图经纬度转换得到需要标注地点的经纬度(保存在经纬度.xlsx),然后再通过导入经纬度.xlsx通过百度地图api批量在地图上标注地点。
首先先到百度地图开放平台百度地图开放平台 | 百度地图API SDK | 地图开发 (baidu.com)得到自己的ak,放到下面代码的相应位置就好了。
下面代码可以得到经纬度或者是详细地址:
# coding:utf-8
from urllib.request import quote
import requests
import pandas as pd
pd.set_option('display.width', 1000)
pd.set_option('display.max_columns', None)
import json
# 通过百度地图地理查询和逆地址查询
def get_location(address):
try:
url = 'http://api.map.baidu.com/place/v2/search?query={}®ion=标注范围&output=json&ak=你的ak&scope=2'.format(quote(address))
response = requests.get(url)
result = response.json()
if result['status'] == 0 and result['results']:
lng = result['results'][0]['location']['lng']
lat = result['results'][0]['location']['lat']
return lng, lat
except Exception as e:
print(f"Failed to get location for {address}: {e}")
return '', ''
def get_address(lng, lat):
try:
url = 'http://api.map.baidu.com/reverse_geocoding/v3/?'
output = 'json'
ak = 'BXottO3XSK3SKXn99fHquBoPo620cExI'
uri = url + '&output=' + output + '&ak=' + ak + '&location=' + str(lat) + ',' + str(lng)
response = requests.get(uri)
answer = response.json()
if answer['status'] == 0:
address = answer['result']['formatted_address']
return address
except Exception as e:
print(f"Failed to get address for coordinates ({lng}, {lat}): {e}")
return 'Nan'
# 导入Excel文件
df = pd.read_excel('getAdress.xlsx')
# 新增两列用于存储查询结果
df['经度'] = ''
df['纬度'] = ''
df['详细地址'] = ''
print('选择通过百度地图查询经纬度(输入1)或查询地址(输入2):')
i = int(input())
if i == 1:
# 批量查询经纬度
for index, row in df.iterrows():
if pd.isna(row['地址']):
continue
lng, lat = get_location(row['地址'])
df.at[index, '经度'] = lng
df.at[index, '纬度'] = lat
# 保存结果到新的xlsx文件
df[['经度', '纬度']].to_excel('经纬度.xlsx', index=False)
print('经纬度查询结果已保存到经纬度.xlsx')
elif i == 2:
# 加载保存了经纬度的xlsx文件
df_lnglat = pd.read_excel('经纬度.xlsx')
# 批量查询详细地址
for index, row in df_lnglat.iterrows():
lng = row['经度']
lat = row['纬度']
address = get_address(lng, lat)
df.at[index, '详细地址'] = address
# 输出结果并保存到新的xlsx文件
df.to_excel('详细地址.xlsx', index=False)
print('详细地址查询结果已保存到详细地址.xlsx')
经纬度.xlsx示例:
然后运行下面的HTML:
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name="viewport" content="initial-scale=1.0, user-scalable=no" />
<title>批量标注地点并显示名称</title>
<style type="text/css">
body,
html {
width: 100%;
height: 100%;
margin: 0;
}
#l-map {
height: 95%;
width: 100%;
}
#upload-container {
height: 5%;
}
.cluster-label {
background-color: rgba(0, 0, 0, 0.5);
color: white;
padding: 5px;
border-radius: 50%;
display: flex;
justify-content: center;
align-items: center;
}
</style>
<script type="text/javascript"
src="http://api.map.baidu.com/api?v=2.0&ak=你的ak"></script>
<script type="text/javascript" src="https://cdn.jsdelivr.net/npm/xlsx/dist/xlsx.full.min.js"></script>
</head>
<body>
<div id="l-map"></div>
<div id="upload-container">
<input type="file" accept=".xlsx, .xls" onchange="handleFileUpload(event)" />
</div>
</body>
<script type="text/javascript">
var map = new BMap.Map("l-map");
map.centerAndZoom(new BMap.Point(116.404, 39.915), 8);
map.enableScrollWheelZoom();
var markers = new Map(); // 使用 Map 数据结构存储经纬度和名称
function handleFileUpload(event) {
const file = event.target.files[0];
const reader = new FileReader();
reader.onload = function (event) {
const data = new Uint8Array(event.target.result);
const workbook = XLSX.read(data, { type: 'array' });
const firstSheetName = workbook.SheetNames[0];
const worksheet = workbook.Sheets[firstSheetName];
const jsonData = XLSX.utils.sheet_to_json(worksheet, { raw: true, header: 1 });
addPointsToMap(jsonData.slice(1)); // Skip header row
};
reader.readAsArrayBuffer(file);
}
function addPointsToMap(data) {
var markerCache = {};
data.forEach(function (row) {
const lng = row[0];
const lat = row[1];
const label = row[2];
const randX = Math.random() * 0.02 - 0.01; // 添加随机性偏移
const randY = Math.random() * 0.02 - 0.01; // 添加随机性偏移
const point = new BMap.Point(lng + randX, lat + randY);
if (!markerCache[`${lng},${lat}`]) {
markerCache[`${lng},${lat}`] = {
point: point,
labels: [label]
};
} else {
markerCache[`${lng},${lat}`].labels.push(label);
}
});
for (var key in markerCache) {
const markerData = markerCache[key];
const marker = new BMap.Marker(markerData.point);
const labelText = markerData.labels.join(', ');
var labelOpts = {
position: markerData.point,
offset: new BMap.Size(-20, -25)
};
if (markerData.labels.length > 1) {
// 创建和显示聚合标记
const clusterLabel = new BMap.Label(markerData.labels.length, {
position: markerData.point
});
clusterLabel.setStyle({
color: 'white',
backgroundColor: 'rgba(0, 0, 0, 0.8)',
border: 0,
borderRadius: '50%',
padding: '5px',
textAlign: 'center'
});
marker.setLabel(clusterLabel);
// 为聚合标记添加点击事件
addClusterMarkerInfoWindow(marker, markerData.labels);
} else {
var textLabel = new BMap.Label(labelText, labelOpts);
marker.setLabel(textLabel);
}
map.addOverlay(marker);
}
}
function addClusterMarkerInfoWindow(marker, labels) {
marker.addEventListener('click', function () {
const infoWindow = new BMap.InfoWindow(labels.join('<br>'));
map.openInfoWindow(infoWindow, marker.getPosition());
});
}
</script>
</html>
运行结果如下:
然后左下角选择需要导入的经纬度.xlsx,批量标注: