调用高德的【行政区划查询】接口，组装省市区SQL

调用高德的【行政区划查询】接口，组装省市区SQL 高德API

没有高德开发平台的账号，自己去注册一个。高德开放平台地址

创建一个应用，web服务，主要用到【行政区域查询API】，找到你的key


复制上面的key，等会调用的时候要用

添加依赖

这两个都是工具包，一个转换为拼音，一个通用的，还有一个简化get/set

  		
            com.belerweb
            pinyin4j
            2.5.0
        
         
            cn.hutool
            hutool-all
            5.4.7
        
         
            org.projectlombok
            lombok
            true
        

工具类

package com.lee.utils;

import net.sourceforge.pinyin4j.PinyinHelper;
import net.sourceforge.pinyin4j.format.HanyuPinyinCaseType;
import net.sourceforge.pinyin4j.format.HanyuPinyinOutputFormat;
import net.sourceforge.pinyin4j.format.HanyuPinyinToneType;
import net.sourceforge.pinyin4j.format.exception.BadHanyuPinyinOutputFormatCombination;
import org.apache.commons.lang3.StringUtils;


public class PinYinUtil {

    public static void main(String[] args) {
        String str = "吉林省";
        // 北京市,天津市,重庆市,上海市
        if (str.contains("省") || str.contains("市")) {
            // 省和市忽略，保留前面的
            String province = str.substring(0, str.length() - 1);
            String bj = getUpperStr(province);
            System.out.println(bj);
        }
        // 宁夏回族自治区，新疆维吾尔自治区，西藏自治区,广西壮族自治区，内蒙古自治区
        if (str.contains("区")) {
            // 区保留前2位
            String province = str.substring(0, 2);
            String bj = getUpperStr(province);
            System.out.println(bj);
        }

    }

    public static String getUpperStr(String name) {
        if (StringUtils.isNotBlank(name)) {
            StringBuilder str = new StringBuilder();
            for (int i = 0; i < name.length(); i++) {
                String characters = String.valueOf(name.charAt(i));
                str.append(converterToFirstSpell(characters).toUpperCase().charAt(0));
            }
            return str.toString();
        }
        return "";
    }

    private static String converterToFirstSpell(String str) {
        StringBuilder pinyinName = new StringBuilder();
        char[] nameChar = str.toCharArray();
        HanyuPinyinOutputFormat defaultFormat = new HanyuPinyinOutputFormat();
        defaultFormat.setCaseType(HanyuPinyinCaseType.LOWERCASE);
        defaultFormat.setToneType(HanyuPinyinToneType.WITHOUT_TONE);
        for (char characters : nameChar) {
            String string = String.valueOf(characters);
            if (string.matches("[\u4e00-\u9fa5]")) {
                try {
                    String[] mPinyinArray = PinyinHelper.toHanyuPinyinStringArray(characters, defaultFormat);
                    pinyinName.append(mPinyinArray[0]);
                } catch (BadHanyuPinyinOutputFormatCombination e) {
                    e.printStackTrace();
                }
            } else {
                pinyinName.append(characters);
            }
        }
        return pinyinName.toString();
    }

}

数据库表映射类

package com.lee.gaode;

import lombok.Data;

import java.math.BigDecimal;
import java.util.Date;


@Data
public class GaodeArea {

    private Long id;

    
    private String name;
    
    private Long parentId;
    
    private String fullName;
    
    private String treePath;
    
    private Integer treeLevel;
    
    private String level;
    
    private BigDecimal centerLng;
    
    private BigDecimal centerLat;
    
    private Date createTime;
    
    private String initials;

}

高德转换类

package com.lee.gaode;

import lombok.Data;

import java.util.List;


@Data
public class Geocoding {

    private String adcode;
    private String name;
    private String center;
    private String level;
    private List citycode;
    private List districts;

    @Data
    public static class DistrictsBeanX {
        private String citycode;
        private String adcode;
        private String name;
        private String center;
        private String level;
        private List districts;

    }

}

生成类

restapi.amap.com/v3/config/district?key=您的key&keywords=100000&subdistrict=3&extensions=base
代码中的路径的高德key别忘了换成你自己的

package com.lee.gaode;

import cn.hutool.core.date.DateTime;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.CharsetUtil;
import cn.hutool.http.HttpUtil;
import cn.hutool.json.JSON;
import cn.hutool.json.JSONUtil;
import com.google.common.collect.Lists;
import com.lee.utils.FileUtils;
import com.lee.utils.PinYinUtil;
import com.rnkrsoft.bopomofo4j.Bopomofo4j;
import com.rnkrsoft.bopomofo4j.ToneType;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.CharSet;
import org.apache.commons.lang3.StringUtils;

import java.io.File;
import java.math.BigDecimal;
import java.nio.charset.Charset;
import java.util.*;
import java.util.stream.Collectors;
import java.util.stream.Stream;


@Slf4j
public class GaodeTest {
    public static void main(String[] args) {

        // 1、读取静态JSON文件，转换为数据
        // JSON json = JSONUtil.readJSON(new File("datas/GaoDeGeCoding.json"), CharsetUtil.CHARSET_UTF_8);
        // Geocoding geocoding = json.toBean(Geocoding.class);
        // 获取第一级
        // List districts = geocoding.getDistricts();
        // analysis(districts);

        // 2、实时去查高德【行政区域查询接口】，转换为数据
        String jsonStr = HttpUtil.get("restapi.amap.com/v3/config/district?key=您的key&keywords=100000&subdistrict=3&extensions=base");
        Geocoding geocoding = JSONUtil.toBean(jsonStr, Geocoding.class);
        analysis(geocoding.getDistricts());
    }

    
    private static void analysis(List districts) {
        List list = Lists.newArrayList();
        for (Geocoding.DistrictsBeanX country : districts) {

            // 国家
            GaodeArea countryGaodeArea = getGaodeArea(country, null, country.getName() + ",", country.getAdcode() + ",", null);
            list.add(countryGaodeArea);
            // 省份
            for (Geocoding.DistrictsBeanX province : country.getDistricts()) {
                String provinceFullName = country.getName().concat(",").concat(province.getName()).concat(",");
                String provinceTreePath = country.getAdcode().concat(",").concat(province.getAdcode()).concat(",");
                GaodeArea provinceGaodeArea = getGaodeArea(province, countryGaodeArea.getId(), provinceFullName, provinceTreePath, province.getName());
                list.add(provinceGaodeArea);

                // 城市
                for (Geocoding.DistrictsBeanX city : province.getDistricts()) {
                    String cityFullName =  country.getName().concat(",").concat(province.getName()).concat(",").concat(city.getName()).concat(",");
                    String cityTreePath =  country.getAdcode().concat(",").concat(province.getAdcode()).concat(",").concat(city.getAdcode()).concat(",");
                    GaodeArea cityGaodeArea = getGaodeArea(city, provinceGaodeArea.getId(), cityFullName, cityTreePath, null);
                    list.add(cityGaodeArea);

                    // 区县
                    for (Geocoding.DistrictsBeanX district : city.getDistricts()) {
                        String districtFullName =  country.getName().concat(",").concat(province.getName()).concat(",").concat(city.getName()).concat(",").concat(district.getName()).concat(",");
                        String districtTreePath =  country.getAdcode().concat(",").concat(province.getAdcode()).concat(",").concat(city.getAdcode()).concat(",").concat(district.getAdcode()).concat(",");
                        GaodeArea districtGaodeArea = getGaodeArea(district, cityGaodeArea.getId(), districtFullName, districtTreePath,null);
                        list.add(districtGaodeArea);

                        // 街道
                        for (Geocoding.DistrictsBeanX districtDistrict : district.getDistricts()) {
                            String districtDistrictFullName =  country.getName().concat(",").concat(province.getName()).concat(",").concat(city.getName()).concat(",").concat(district.getName()).concat(",").concat(districtDistrict.getName()).concat(",");
                            String districtDistrictTreePath =  country.getAdcode().concat(",").concat(province.getAdcode()).concat(",").concat(city.getAdcode()).concat(",").concat(district.getAdcode()).concat(",").concat(districtDistrict.getAdcode()).concat(",");
                            GaodeArea districtDistrictGaodeArea = getGaodeArea(districtDistrict, districtGaodeArea.getId(), districtDistrictFullName, districtDistrictTreePath, null);
                            list.add(districtDistrictGaodeArea);
                        }
                    }
                }
            }
        }
        // 按照code进行排序
        List sortList = list.stream().sorted(Comparator.comparing(GaodeArea::getId)).collect(Collectors.toList());
        // code有重复的，济源市下面的镇，code都和济源市一样，按照以前表的处理，只保留济源市，也就是第一个,其他的还有仙桃市等
        Map> collect = sortList.stream().collect(Collectors.groupingBy(GaodeArea::getId));

        // 组装SQL
        List sqlList = collect.entrySet().stream().map(areaId -> {
            GaodeArea area = areaId.getValue().get(0);
            String sql = "INSERT INTO `db_puxian_account`.`t_gaode_area`(`id`, `name`, `parent_id`, `full_name`, `tree_path`, `tree_level`, `level`, `center_lng`, `center_lat`, `create_time`, `initials`) VALUES (";
            StringBuilder sb = new StringBuilder(sql);
            sb.append(area.getId()).append(",")
                    .append("'").append(area.getName()).append("'").append(",")
                    .append(area.getParentId()).append(",")
                    .append("'").append(area.getFullName()).append("'").append(",")
                    .append("'").append(area.getTreePath()).append("'").append(",")
                    .append(area.getTreeLevel()).append(",")
                    .append("'").append(area.getLevel()).append("'").append(",")
                    .append(area.getCenterLng()).append(",")
                    .append(area.getCenterLat()).append(",")
                    .append("'").append(DateTime.of(area.getCreateTime()).toString()).append("'").append(",");

            if (StringUtils.isNotBlank(area.getInitials())) {
                sb.append("'").append(area.getInitials()).append("'").append(" );");
            } else {
                sb.append(area.getInitials()).append(" );");
            }
            return sb.toString();
        }).collect(Collectors.toList());

        sqlList.forEach(log::info);
        File file = new File("D:\gao_de_area.txt");
        FileUtil.writeLines(sqlList, file, Charset.defaultCharset());
    }

    
    private static GaodeArea getGaodeArea(Geocoding.DistrictsBeanX k,Long parentId,String fullName,String treePath,String provinceName) {
        GaodeArea gaodeArea = new GaodeArea();
        gaodeArea.setName(k.getName());
        gaodeArea.setId(Long.valueOf(k.getAdcode()));
        gaodeArea.setCreateTime(new Date());
        gaodeArea.setFullName(fullName);
        gaodeArea.setTreePath(treePath);
        gaodeArea.setParentId(parentId);
        int treeLevel = 0;
        if (k.getLevel().equals("country")) {
            treeLevel = 1;
        } else if (k.getLevel().equals("province")) {
            treeLevel = 2;
        } else if (k.getLevel().equals("city")) {
            treeLevel = 3;
        } else if (k.getLevel().equals("district")) {
            treeLevel = 4;
        } else if (k.getLevel().equals("street")) {
            treeLevel = 4;
        }
        // 级别
        gaodeArea.setTreeLevel(treeLevel);
        gaodeArea.setLevel(k.getLevel());
        String[] centerArray = k.getCenter().split(",");
        // 中心点经度
        gaodeArea.setCenterLng(new BigDecimal(centerArray[1]));
        // 中心点纬度
        gaodeArea.setCenterLat(new BigDecimal(centerArray[0]));
        // 省份前缀
        if (StringUtils.isNotBlank(provinceName)) {
            // 北京市,天津市,重庆市,上海市
            if (provinceName.contains("省") || provinceName.contains("市")) {
                // 省和市忽略，保留前面的
                String province = provinceName.substring(0, provinceName.length() - 1);
                String bj = PinYinUtil.getUpperStr(province);
                gaodeArea.setInitials(bj);
            }
            // 宁夏回族自治区，新疆维吾尔自治区，西藏自治区,广西壮族自治区，内蒙古自治区
            if (provinceName.contains("区")) {
                // 区保留前2位
                String province = provinceName.substring(0, 2);
                String bj = PinYinUtil.getUpperStr(province);
                gaodeArea.setInitials(bj);
            }
        }
        // log.info(">>>>>>>>>>>>>>>：{}", JSONUtil.toJsonStr(gaodeArea));
        return gaodeArea;
    }

}

最后去这个路径下，找到这个文件【D:gao_de_area.txt】，SQL已经生成了，直接插入到表里面就行，每家业务的省市区表可能不一样，自己改改就行。