【国家地址库】省市区三级数据生成方案
最近做了一套国家三级地址库前后端生成方案。因为是从国家民政部获取的官方数据，与网络上各种各样的地址库产品相比，自己比较满意。
给大家介绍一下思路：
- 获取国家民政部官方数据：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
