Analyze the Gaud map api to obtain the provincial and urban areas, and generate the latest three-level linkage sql table

Preface:

In the recent project, the three-level information of provincial and urban areas in China has been used, but the information found on the Internet is relatively old. It doesn't match the latest regional information. Later, I saw that there was such information on the map of Gaode, so I analyzed the api interface and generated the sql information of relevant provinces and cities. (Note: there is no Hong Kong and Taiwan area) for details, please refer to the map document of Gaud.

alibaba.fastjson parsing + mysql table

 

1. Description document of the call interface of Gaud map api: https://lbs.amap.com/api/webservice/guide/api/district/#limit

Specific data interface:

  https://restapi.amap.com/v3/config/district?subdistrict=4&key=bb4198a1f146184af53322d424732f6b

Note: if you want to call this interface, you need to apply for a key. For free, you can get it by registering

 

2. My code cloud: https://gitee.com/liran123/yft-evidence

 

3. java code is as follows:

    @RequestMapping("/getAll")
    public Object getMasterList() throws Exception {
        HttpClientResult result = HttpClientUtils.doGet("https://restapi.amap.com/v3/config/district?subdistrict=4&key=bb4198a1f146184af53322d424732f6b");
        String jsonString = result.getContent();
        //The data structure is as follows
        /* String jsonString = "{\n" +
                "    \"status\": \"1\",\n" +
                "    \"info\": \"OK\",\n" +
                "    \"infocode\": \"10000\",\n" +
                "    \"count\": \"1\",\n" +
                "    \"suggestion\": {\n" +
                "        \"keywords\": [],\n" +
                "        \"cities\": []\n" +
                "    },\n" +
                "    \"districts\": [{\n" +
                "        \"citycode\": [],\n" +
                "        \"adcode\": \"100000\",\n" +
                "        \"name\": \"People's Republic of China \ ", \ n"+
                "        \"center\": \"116.3683244,39.915085\",\n" +
                "        \"level\": \"country\",\n" +
                "        \"districts\": [{\n" +
                "            \"citycode\": [],\n" +
                "            \"adcode\": \"410000\",\n" +
                "            \"name\": \"Henan Province \ ", \ n"+
                "            \"center\": \"113.665412,34.757975\",\n" +
                "            \"level\": \"province\",\n" +
                "            \"districts\": [{\n" +
                "                \"citycode\": \"0393\",\n" +
                "                \"adcode\": \"410900\",\n" +
                "                \"name\": \"Puyang City \ ", \ n"+
                "                \"center\": \"115.041299,35.768234\",\n" +
                "                \"level\": \"city\",\n" +
                "                \"districts\": [{\n" +
                "                    \"citycode\": \"0393\",\n" +
                "                    \"adcode\": \"410923\",\n" +
                "                    \"name\": \"Nanle County \ ", \ n"+
                "                    \"center\": \"115.204336,36.075204\",\n" +
                "                    \"level\": \"district\",\n" +
                "                    \"districts\": [{\n" +
                "                        \"citycode\": \"0393\",\n" +
                "                        \"adcode\": \"410923\",\n" +
                "                        \"name\": \"FUKAN town \ ", \ n"+
                "                        \"center\": \"115.398,36.0869\",\n" +
                "                        \"level\": \"street\",\n" +
                "                        \"districts\": []\n" +
                "                    }]\n" +
                "                }]\n" +
                "            }]\n" +
                "        }]\n" +
                "    }]\n" +
                "}";//result.getContent();*/
        JSONObject jsonObject = JSONObject.parseObject(jsonString);
        //Get the country and all the information below and start to insert circularly. It can be written as recursive call here, but it is not so convenient to view and understand
        JSONArray countryAll = jsonObject.getJSONArray("districts");
        for (int i = 0; i < countryAll.size(); i++) {
            JSONObject countryLeve0 = countryAll.getJSONObject(i);
            String citycode0 = countryLeve0.getString("citycode");
            String adcode0 = countryLeve0.getString("adcode");
            String name0 = countryLeve0.getString("name");
            String center0 = countryLeve0.getString("center");
            String country = countryLeve0.getString("level");
            int level = 0;
            if (country.equals("country")) {
                level = 0;
            }
            //Insert country
            Integer id1 = insert(0, adcode0, citycode0, name0, center0, level, name0);
            JSONArray province0 = countryLeve0.getJSONArray("districts");

            for (int j = 0; j < province0.size(); j++) {
                JSONObject province1 = province0.getJSONObject(j);
                String citycode1 = province1.getString("citycode");
                String adcode1 = province1.getString("adcode");
                String name1 = province1.getString("name");
                String center1 = province1.getString("center");
                String province = province1.getString("level");
                int level1 = 0;
                if (province.equals("province")) {
                    level1 = 1;
                }
                //Insert Province
                Integer id2 = insert(id1, adcode1, citycode1, name1, center1, level1, name0, name1);
                JSONArray city0 = province1.getJSONArray("districts");

                for (int z = 0; z < city0.size(); z++) {
                    JSONObject city2 = city0.getJSONObject(z);
                    String citycode2 = city2.getString("citycode");
                    String adcode2 = city2.getString("adcode");
                    String name2 = city2.getString("name");
                    String center2 = city2.getString("center");
                    String city = city2.getString("level");
                    int level2 = 0;
                    if (city.equals("city")) {
                        level2 = 2;
                    }
                    //Insert Market
                    Integer id3 = insert(id2, adcode2, citycode2, name2, center2, level2, name0, name1, name2);

                    JSONArray street0 = city2.getJSONArray("districts");
                    for (int w = 0; w < street0.size(); w++) {
                        JSONObject street3 = street0.getJSONObject(w);
                        String citycode3 = street3.getString("citycode");
                        String adcode3 = street3.getString("adcode");
                        String name3 = street3.getString("name");
                        String center3 = street3.getString("center");
                        String street = street3.getString("level");
                        int level3 = 0;
                        if (street.equals("street")) {
                            level3 = 2;
                        }
                        //Insert District County
                        insert(id3, adcode3, citycode3, name3, center3, level3, name0, name1, name2, name3);
                        //  JSONArray street = street3.getJSONArray("districts");
                        //Continue to traverse down if necessary

                    }

                }
            }
        }

        return getSuccessResult();
    }


    /**
     * Public insert method
     *
     * @param parentId  Father id
     * @param citycode  City coding
     * @param adcode    The area code street does not have a unique adcode. It inherits the adcode of the parent class (district / county)
     * @param name      City name (name of Administrative Region)
     * @param center    Geographic coordinates
     * @param level     Rank 0 country, 1 Province,... Rank in order
     * @param mergeName Splice names into full names
     * @return
     */
    public Integer insert(Integer parentId, String citycode, String adcode, String name, String center, int level, String... mergeName) {
//         \"citycode\": [],\n" +
//                "        \"adcode\": \"100000\",\n" +
//                "        \"name\": \"The People's Republic of China\",\n" +
//                "        \"center\": \"116.3683244,39.915085\",\n" +
//                "        \"level\": \"country\",\n" +
        RegionAmap record = new RegionAmap();
        if (!adcode.equals("[]")) {
            record.setAdcode(adcode);
        }
        record.setCitycode(Integer.parseInt(citycode));
        record.setCenter(center);
        record.setLevel(level);
        record.setName(name);
        record.setParentId(parentId);
        String megName = "";
        for (int i = 0; i < mergeName.length; i++) {
            megName = megName + mergeName[i];
            if (i < mergeName.length - 1) {
                megName = megName + ",";
            }
        }
        record.setMergerName(megName);
        regionAmapMapperCustom.insertSelective(record);
        return record.getCitycode();
    }

    /**
     * Reorder generated sql
     *
     * @return
     */
    @RequestMapping("/order")
    public Object order() {
        RegionAmapExample example = new RegionAmapExample();
        example.setOrderByClause("citycode, adcode");
        List<RegionAmap> selectByExample = regionAmapMapperCustom.selectByExample(example);
        for (RegionAmap regionAmap : selectByExample) {
            RegionOrder record = new RegionOrder();
            record.setAdcode(regionAmap.getAdcode());
            record.setCitycode(regionAmap.getCitycode());
            record.setCenter(regionAmap.getCenter());
            record.setLevel(regionAmap.getLevel());
            record.setMergerName(regionAmap.getMergerName());
            record.setName(regionAmap.getName());
            record.setParentId(regionAmap.getParentId());
            regionOrderMapper.insertSelective(record);
        }
        return getSuccessResult();
    }

 

Note: the province or city that first calls the interface resolution is not resolved in order, so it needs to call the sorting interface to regenerate. Finally, look at the sorted region order table

Tags: Java Amap SQL MySQL

Posted on Tue, 03 Dec 2019 21:46:01 -0500 by keithwjones