【国家地址库】省市区三级数据生成方案

最近做了一套国家三级地址库前后端生成方案。因为是从国家民政部获取的官方数据，与网络上各种各样的地址库产品相比，自己比较满意。

给大家介绍一下思路：

获取国家民政部官方数据：http://www.mca.gov.cn/article/sj/xzqh/2020/
后端ETL这些数据，放入数据库中。
前端按照Element UI的组件element-china-area-data引入，替换其中省市区数据源，分离一个app.js，即可使用。

结果展示：

具体方案如图：

[文字留底]

地址库组件
   生成
       数据库：省市区表
           1. 从国家民政部获取最新行政区划代码。
               http://www.mca.gov.cn/article/sj/xzqh/2020/
           2. 放入Excel，按照尾部00、0000拆分出省、市、区。
           3. 用kettle按照设计表的规则导入mysql，不存在的省或市表主键设置为0。
           4. 生成insert语句。
       前端element-china-area-data控件
           后端写一个小程序，依照“数据库表”和“element-china-area-data数据格式”生成前端行政区划json数据。
               https://www.npmjs.com/package/element-china-area-data
           json数据，压缩成一行，替换element-china-area-data控件数据。
               https://www.sojson.com/yasuo.html
   维护
       民政部数据更新
           大约几个月会更新一次。
       数据库省市区表最新版与上一版数据比对
           差异
               区划代码
               名称
               所属二级或一级
           影响是什么？
               新增
                   无影响
               修改
                   新旧的区划代码不一致
               删除
                   合并
                       新旧的区划代码不一致
           方案：存储时，区划代码和名称都存储，作为快照；变更仅影响新的。
               不用比对
                   element-china-area-data控件json数据与民政部数据比对？
                       不用

[后端]

相关表

 1 CREATE TABLE `bd_province`  (
 2   `province_id` bigint(20) UNSIGNED NOT NULL COMMENT '表主键id 全局唯一, snowflake id',
 3   `adr_code` int(11) NOT NULL COMMENT '行政区划代码 administrative division 唯一依据',
 4   `adr_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '行政区划单位名称 有一个名称最长45位',
 5   `full_pinyin` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '名称全拼 需要200的长度，用于搜索',
 6   `short_pinyin` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '名称短拼音 有一个名称最长45位，用于搜索',
 7   `longitude` decimal(32, 10) NULL DEFAULT NULL COMMENT '中心点经度 从高德地图接口获取',
 8   `latitude` decimal(32, 10) NULL DEFAULT NULL COMMENT '中心点纬度 从高德地图接口获取',
 9   `creator_id` bigint(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT '创建人ID 默认0',
10   `create_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
11   `modifier_id` bigint(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT '修改人ID 默认0',
12   `modify_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '修改时间 自动更新',
13   `deleted` tinyint(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT '逻辑删除标识 0是未删除，1是已删除。',
14   PRIMARY KEY (`province_id`) USING BTREE,
15   UNIQUE INDEX `uk_adr_code`(`adr_code`) USING BTREE
16 ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '行政划分-省和直辖市表 ' ROW_FORMAT = Dynamic;

 1 CREATE TABLE `bd_city`  (
 2   `city_id` bigint(20) UNSIGNED NOT NULL COMMENT '表主键id 全局唯一, snowflake id',
 3   `province_id` bigint(20) UNSIGNED NOT NULL COMMENT '所属省ID',
 4   `province_code` int(11) NULL DEFAULT NULL COMMENT '所属省代码 冗余',
 5   `adr_code` int(11) NOT NULL COMMENT '行政区划代码 administrative division 唯一依据',
 6   `adr_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '行政区划单位名称 有一个名称最长45位',
 7   `full_pinyin` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '名称全拼 需要200的长度，用于搜索',
 8   `short_pinyin` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '名称短拼音 有一个名称最长45位，用于搜索',
 9   `longitude` decimal(32, 10) NULL DEFAULT NULL COMMENT '中心点经度 从高德地图接口获取',
10   `latitude` decimal(32, 10) NULL DEFAULT NULL COMMENT '中心点纬度 从高德地图接口获取',
11   `creator_id` bigint(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT '创建人ID 默认0',
12   `create_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
13   `modifier_id` bigint(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT '修改人ID 默认0',
14   `modify_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '修改时间 自动更新',
15   `deleted` tinyint(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT '逻辑删除标识 0是未删除，1是已删除。',
16   PRIMARY KEY (`city_id`) USING BTREE,
17   UNIQUE INDEX `uk_adr_code`(`adr_code`) USING BTREE
18 ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '行政划分-城市表 ' ROW_FORMAT = Dynamic;

 1 CREATE TABLE `bd_district`  (
 2   `district_id` bigint(20) UNSIGNED NOT NULL COMMENT '表主键id 全局唯一, snowflake id',
 3   `city_id` bigint(20) UNSIGNED NOT NULL COMMENT '所属市ID',
 4   `city_code` int(11) NULL DEFAULT NULL COMMENT '所属市代码 冗余',
 5   `adr_code` int(11) NOT NULL COMMENT '行政区划代码 唯一依据',
 6   `adr_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '行政区划单位名称 有一个名称最长45位',
 7   `full_pinyin` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '名称全拼 需要200的长度，用于搜索',
 8   `short_pinyin` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '名称短拼音 有一个名称最长45位，用于搜索',
 9   `longitude` decimal(32, 10) NULL DEFAULT NULL COMMENT '中心点经度',
10   `latitude` decimal(32, 10) NULL DEFAULT NULL COMMENT '中心点纬度',
11   `creator_id` bigint(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT '创建人ID 默认0',
12   `create_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
13   `modifier_id` bigint(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT '修改人ID 默认0',
14   `modify_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '修改时间 自动更新',
15   `deleted` tinyint(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT '逻辑删除标识 0是未删除，1是已删除；默认0。',
16   PRIMARY KEY (`district_id`) USING BTREE,
17   UNIQUE INDEX `uk_adr_code`(`adr_code`) USING BTREE
18 ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '行政划分-区县表 ' ROW_FORMAT = Dynamic;

相关代码

 1 package cn.com.service.impl;
 2 
 3 import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
 4 import org.springframework.beans.BeanUtils;
 5 import org.springframework.beans.factory.annotation.Autowired;
 6 import org.springframework.stereotype.Service;
 7 
 8 import java.util.*;
 9 
10 /**
11  * 区县实现类
12  * 
13  * @author Mike
14  * 2020/9/1 15:05
15  */
16 @Service
17 public class DistrictServiceImpl implements DistrictService {
18 
19     @Autowired
20     private ProvinceMapper provinceMapper;
21 
22     @Autowired
23     private CityMapper cityMapper;
24 
25     @Autowired
26     private DistrictMapper districtMapper;
27 
28     /**
29      * 生成Element UI 地址库控件数据源，用于替换为最新国家民政部发布的三级地址库信息。
30      *
31      * @author Mike
32      * 2020/8/27 17:39
33      */
34     @Override
35     public Result generateAdrInfo() {
36         Map<String, Map> result = new HashMap();
37         QueryWrapper queryWrapper = new QueryWrapper();
38 
39         //省
40         queryWrapper.eq("deleted", DeletableEnum.NOT_DELETED.ordinal());
41         HashMap hmProvince = new HashMap();
42         queryWrapper.select("adr_code", "adr_name");
43         List<Province> provinceList = provinceMapper.selectList(queryWrapper);
44         for (Province item : provinceList) {
45             hmProvince.put(item.getAdrCode(), item.getAdrName());
46         }
47         result.put("86", hmProvince);
48         int countProvince = hmProvince.size();
49 
50         //市
51         queryWrapper.clear();
52         queryWrapper.eq("deleted", DeletableEnum.NOT_DELETED.ordinal());
53         queryWrapper.select("province_code", "adr_code", "adr_name");
54         queryWrapper.orderByAsc("province_code", "adr_code");
55         List<City> cityList = cityMapper.selectList(queryWrapper);
56         Integer tempProvinceCode = cityList.get(0).getProvinceCode();
57         HashMap hmCity = new HashMap();
58         int countCity = 0;
59         for (City item : cityList) {
60             if (!item.getProvinceCode().equals(tempProvinceCode)) {
61                 HashMap tempHashMap = (HashMap) hmCity.clone();
62                 result.put(tempProvinceCode.toString(), tempHashMap);
63                 tempProvinceCode = item.getProvinceCode();
64                 countCity = countCity + hmCity.size();
65                 hmCity = new HashMap();
66             }
67             hmCity.put(item.getAdrCode(), item.getAdrName());
68         }
69         result.put(cityList.get(cityList.size() - 1).getProvinceCode().toString(), hmCity);
70         countCity = countCity + hmCity.size();
71 
72         //区
73         queryWrapper.clear();
74         queryWrapper.eq("deleted", DeletableEnum.NOT_DELETED.ordinal());
75         queryWrapper.select("city_code", "adr_code", "adr_name");
76         queryWrapper.orderByAsc("city_code", "adr_code");
77         List<District> districtList = districtMapper.selectList(queryWrapper);
78         Integer tempCityCode = districtList.get(0).getCityCode();
79         HashMap hmDistrict = new HashMap();
80         int countDistrict = 0;
81         for (District item : districtList) {
82             if (!item.getCityCode().equals(tempCityCode)) {
83                 HashMap tempHashMap = (HashMap) hmDistrict.clone();
84                 result.put(tempCityCode.toString(), tempHashMap);
85                 tempCityCode = item.getCityCode();
86                 countDistrict = countDistrict + hmDistrict.size();
87                 hmDistrict = new HashMap();
88             }
89             hmDistrict.put(item.getAdrCode(), item.getAdrName());
90         }
91         result.put(districtList.get(districtList.size() - 1).getCityCode().toString(), hmDistrict);
92         countDistrict = countDistrict + hmDistrict.size();
93 
94         return new Result(SUCCESS,
95                 String.format("%tF 最新省市区数据生成成功！其中省(直辖市)有%d个，市有%d个，区有%d个。",
96                         new Date(), countProvince, countCity, countDistrict),
97                 result);
98     }
99 }

后来代码增加了生成手机端 json格式的部分：

  1 /**
  2      * 生成Element UI 地址库控件数据源，用于替换为最新国家民政部发布的三级地址库信息。
  3      *
  4      * @author Mike
  5      * 2020/8/27 17:39
  6      */
  7     @Override
  8     public Result generateAdrInfo(Integer mode) {
  9         if (mode.equals(1)) {
 10             return mode1();
 11         }
 12         if (mode.equals(2)) {
 13             return mode2();
 14         }
 15         return null;
 16     }
 17 
 18     /**
 19      * 模式1的json
 20      *
 21      * @author Mike
 22      * 2020/9/8 18:26
 23      */
 24     private Result mode1() {
 25         Map<String, Map> result = new HashMap();
 26         QueryWrapper queryWrapper = new QueryWrapper();
 27 
 28         //省
 29         queryWrapper.eq("deleted", DeletableEnum.NOT_DELETED.ordinal());
 30         HashMap hmProvince = new HashMap();
 31         queryWrapper.select("adr_code", "adr_name");
 32         List<Province> provinceList = provinceMapper.selectList(queryWrapper);
 33         for (Province item : provinceList) {
 34             hmProvince.put(item.getAdrCode(), item.getAdrName());
 35         }
 36         result.put("86", hmProvince);
 37         int countProvince = hmProvince.size();
 38 
 39         //市
 40         queryWrapper.clear();
 41         queryWrapper.eq("deleted", DeletableEnum.NOT_DELETED.ordinal());
 42         queryWrapper.select("province_code", "adr_code", "adr_name");
 43         queryWrapper.orderByAsc("province_code", "adr_code");
 44         List<City> cityList = cityMapper.selectList(queryWrapper);
 45         Integer tempProvinceCode = cityList.get(0).getProvinceCode();
 46         HashMap hmCity = new HashMap();
 47         int countCity = 0;
 48         for (City item : cityList) {
 49             if (!item.getProvinceCode().equals(tempProvinceCode)) {
 50                 HashMap tempHashMap = (HashMap) hmCity.clone();
 51                 result.put(tempProvinceCode.toString(), tempHashMap);
 52                 tempProvinceCode = item.getProvinceCode();
 53                 countCity = countCity + hmCity.size();
 54                 hmCity = new HashMap();
 55             }
 56             hmCity.put(item.getAdrCode(), item.getAdrName());
 57         }
 58         result.put(cityList.get(cityList.size() - 1).getProvinceCode().toString(), hmCity);
 59         countCity = countCity + hmCity.size();
 60 
 61         //区
 62         queryWrapper.clear();
 63         queryWrapper.eq("deleted", DeletableEnum.NOT_DELETED.ordinal());
 64         queryWrapper.select("city_code", "adr_code", "adr_name");
 65         queryWrapper.orderByAsc("city_code", "adr_code");
 66         List<District> districtList = districtMapper.selectList(queryWrapper);
 67         Integer tempCityCode = districtList.get(0).getCityCode();
 68         HashMap hmDistrict = new HashMap();
 69         int countDistrict = 0;
 70         for (District item : districtList) {
 71             if (!item.getCityCode().equals(tempCityCode)) {
 72                 HashMap tempHashMap = (HashMap) hmDistrict.clone();
 73                 result.put(tempCityCode.toString(), tempHashMap);
 74                 tempCityCode = item.getCityCode();
 75                 countDistrict = countDistrict + hmDistrict.size();
 76                 hmDistrict = new HashMap();
 77             }
 78             hmDistrict.put(item.getAdrCode(), item.getAdrName());
 79         }
 80         result.put(districtList.get(districtList.size() - 1).getCityCode().toString(), hmDistrict);
 81         countDistrict = countDistrict + hmDistrict.size();
 82 
 83         return new Result(SUCCESS,
 84                 String.format("%tF 最新省市区数据生成成功！其中省(直辖市)有%d个，市有%d个，区有%d个。",
 85                         new Date(), countProvince, countCity, countDistrict),
 86                 result);
 87     }
 88 
 89     /**
 90      * 模式2的json
 91      *
 92      * @author Mike
 93      * 2020/9/8 18:28
 94      */
 95     private Result mode2() {
 96         List<Province> result = new ArrayList<>();
 97         QueryWrapper queryWrapper = new QueryWrapper();
 98 
 99         //省
100         queryWrapper.eq("deleted", DeletableEnum.NOT_DELETED.ordinal());
101         queryWrapper.select("adr_code", "adr_name");
102         result = provinceMapper.selectList(queryWrapper);
103         int countProvince = result.size();
104 
105         //市
106         queryWrapper.clear();
107         queryWrapper.eq("deleted", DeletableEnum.NOT_DELETED.ordinal());
108         queryWrapper.select("province_code", "adr_code", "adr_name");
109         queryWrapper.orderByAsc("province_code", "adr_code");
110         List<City> dbCityList = cityMapper.selectList(queryWrapper);
111         int countCity = dbCityList.size();
112         for (Province p : result) {
113             p.setCityList(dbCityList
114                     .stream()
115                     .filter(city -> city.getProvinceCode().equals(p.getAdrCode()))
116                     .collect(Collectors.toList()));
117         }
118 
119         //区
120         queryWrapper.clear();
121         queryWrapper.eq("deleted", DeletableEnum.NOT_DELETED.ordinal());
122         queryWrapper.select("city_code", "adr_code", "adr_name");
123         queryWrapper.orderByAsc("city_code", "adr_code");
124         List<District> dbDistrictList = districtMapper.selectList(queryWrapper);
125         int countDistrict = dbDistrictList.size();
126         for (Province p : result) {
127             for (City c : p.getCityList()) {
128                 c.setDistrictList(dbDistrictList
129                         .stream()
130                         .filter(district -> district.getCityCode().equals(c.getAdrCode()))
131                         .collect(Collectors.toList()));
132             }
133         }
134 
135         return new Result(SUCCESS,
136                 String.format("%tF 手机端使用的json格式，最新省市区数据生成成功！其中省(直辖市)有%d个，市有%d个，区有%d个。",
137                         new Date(), countProvince, countCity, countDistrict),
138                 result);
139     }

以上文件在百度网盘可下载（永久有效）：

链接：https://pan.baidu.com/s/1_OIgSUULPZDcn0W8yG7Thg
提取码：x6ig