2021-01-26

java、python两种方法操作百度，高德地图POI

查询出相关资源信息

java

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.jupiter.api.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.web.client.RestTemplate;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;

@RunWith(SpringRunner.class)
@SpringBootTest(classes={DemoApplication.class})
class DemoApplicationTests {

    @Autowired
    private RestTemplate restTemplate;

    @Test
    public void testRead03() throws Exception{
        InputStream is = new FileInputStream("D:\\log\\防护目标.xlsx");
        FileOutputStream outputStream = new FileOutputStream( "D:\\log\\防护目标04.xlsx");
        Workbook workbook = new XSSFWorkbook(is);
        Sheet sheet = workbook.getSheetAt(3);
        for (int i = 0;i<=94;i++){
            // 读取第一行第一列
            Row row = sheet.getRow(i);
            Cell cell = row.getCell(5);
            // 输出单元内容
            System.out.println(cell.getStringCellValue());
            String address = cell.getStringCellValue();
            if (address == null){

            }
            String url = "http://api.map.baidu.com/geocoding/v3";
            String AK = "****************";
            String output = "json";
            String city = "长沙市";
            String body = restTemplate.getForObject(url + "?address=" + address + "&output=" + output
                    + "&city=" + city + "&ak=" + AK, String.class);
//            String body = restTemplate.getForObject(url + "?address=" + address + "&output=" + output
//                     + "&ak=" + AK, String.class);
            JSONObject jsonObject = JSONObject.parseObject(body);
            String status = jsonObject.getString("status");
            if (body != null && "0".equals(status)){
                String lng = jsonObject.getJSONObject("result").getJSONObject("location").getString("lng");
                String lat = jsonObject.getJSONObject("result").getJSONObject("location").getString("lat");
                System.out.println("经度："+lng + ",纬度：" +lat);
                Cell cellLng = row.createCell(6);
                cellLng.setCellValue(lng);
                Cell cellLat = row.createCell(7);
                cellLat.setCellValue(lat);
            }else {
                System.out.println("不存在此地址");
            }
        }
        workbook.write(outputStream);
        // 操作结束，关闭文件
        is.close();
    }

    @Test
    public void testRead() throws Exception{
        InputStream is = new FileInputStream("D:\\log\\test.xlsx");
        FileOutputStream outputStream = new FileOutputStream( "D:\\log\\test02.xlsx");
        Workbook workbook = new XSSFWorkbook(is);
        Sheet sheet = workbook.getSheetAt(0);
        for (int i = 0;i<=94;i++){
            // 读取第一行第一列
            Row row = sheet.getRow(i);
            Cell cell = row.getCell(0);
            // 输出单元内容
            System.out.println(cell.getStringCellValue());
            String query = cell.getStringCellValue();
            String url = "http://api.map.baidu.com/place/v2/search";
            String AK = "*****************************";
            String output = "json";
            String region = "长沙";
            String body = restTemplate.getForObject(url + "?query=" + query + "&output=" + output
                    + "&region=" + region + "&ak=" + AK, String.class);
//            String body = restTemplate.getForObject(url + "?address=" + address + "&output=" + output
//                     + "&ak=" + AK, String.class);
            JSONObject jsonObject = JSONObject.parseObject(body);
            String status = jsonObject.getString("status");
            JSONArray results = jsonObject.getJSONArray("results");
            if (body != null && "0".equals(status) && results.size() != 0){
                String address = jsonObject.getJSONArray("results").getJSONObject(0).getString("address");
                Cell cellAddress = row.createCell(1);
                cellAddress.setCellValue(address);
            }else {
                System.out.println("不存在此地址");
            }
        }
        workbook.write(outputStream);
        // 操作结束，关闭文件
        is.close();
    }

}

python

import collections
from urllib.parse import quote
from urllib import request
import json
import xlwt

#TODO 替换为上面申请的密钥
amap_web_key = '***************'
poi_search_url = "http://restapi.amap.com/v3/place/text"
poi_boundary_url = "https://ditu.amap.com/detail/get/detail"
#from transCoordinateSystem import gcj02_to_wgs84

#TODO cityname为需要爬取的POI所属的城市名，nanning_areas为城市下面的所有区，classes为多个分类名集合. (中文名或者代码都可以，代码详见高德地图的POI分类编码表)
cityname = '长沙'
nanning_areas = ['芙蓉区','天心区','岳麓区','开福区','雨花区','望城区','长沙县','浏阳市','宁乡市']

classes = ['141300']


# 根据城市名称和分类关键字获取poi数据
def getpois(cityname, keywords):
    i = 1
    poilist = []
    while True:  # 使用while循环不断分页获取数据
        result = getpoi_page(cityname, keywords, i)
        print(result)
        result = json.loads(result)  # 将字符串转换为json
        if result['count'] == '0':
            break
        hand(poilist, result)
        i = i + 1
    return poilist


# 数据写入excel
def write_to_excel(poilist, cityname, classfield):
    # 一个Workbook对象，这就相当于创建了一个Excel文件
    book = xlwt.Workbook(encoding='utf-8', style_compression=0)
    sheet = book.add_sheet(classfield, cell_overwrite_ok=True)

    # 第一行(列标题)
    sheet.write(0, 0, 'x')
    sheet.write(0, 1, 'y')
    sheet.write(0, 3, 'address')
    sheet.write(0, 4, 'name')

    d = collections.defaultdict(list)
    for k, v in range(len(poilist)):
        d[k][k].append(poilist[k]['address'])
    for i in range(len(d)):
        address = poilist[i]['address']
        print(address)
        name = poilist[i]['name']
        location = poilist[i]['location']


        lng = str(location).split(",")[0]
        lat = str(location).split(",")[1]

        '''
        result = gcj02_to_wgs84(float(lng), float(lat))

        lng = result[0]
        lat = result[1]
        '''

        # 每一行写入
        sheet.write(i + 1, 0, lng)
        sheet.write(i + 1, 1, lat)
        sheet.write(i + 1, 4, name)
        sheet.write(i + 1, 3, address)



    # 最后，将以上操作保存到指定的Excel文件中
    book.save(r'' + cityname + "_" + classfield + '.xls')


# 将返回的poi数据装入集合返回
def hand(poilist, result):
    # result = json.loads(result)  # 将字符串转换为json
    pois = result['pois']
    for i in range(len(pois)):
        poilist.append(pois[i])


# 单页获取pois
def getpoi_page(cityname, keywords, page):
    req_url = poi_search_url + "?key=" + amap_web_key + '&extensions=all&types=' + quote(
        keywords) + '&city=' + quote(cityname) + '&citylimit=true' + '&offset=25' + '&page=' + str(
        page) + '&output=json'
    data = ''
    with request.urlopen(req_url) as f:
        data = f.read()
        data = data.decode('utf-8')
    return data


for clas in classes:
    classes_all_pois = []
    for area in nanning_areas:
        pois_area = getpois(area, clas)
        print('当前城区：' + str(area) + ', 分类：' + str(clas) + ", 总的有" + str(len(pois_area)) + "条数据")
        classes_all_pois.extend(pois_area)
    print("所有城区的数据汇总，总数为：" + str(len(classes_all_pois)))

    write_to_excel(classes_all_pois, cityname, clas)

    print('================分类：'  + str(clas) + "写入成功")