【國家地址庫】省市區三級資料生成方案-有解無憂

最近做了一套國家三級地址庫前后端生成方案，因為是從國家民政部獲取的官方資料，與網路上各種各樣的地址庫產品相比，自己比較滿意，

給大家介紹一下思路：

獲取國家民政部官方資料：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

轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/24421.html

標籤：Java

上一篇：學習一下 JVM （三） -- 了解一下垃圾回收

下一篇：JDBC | 第八章: JDBC常用資料庫連接池c3p0,dbcp,durid,hikariCP,tomcat-jdbc性能及區別