Crawling China Administrative Division Code

1

iconv -f fromcode -t tocode [file ...]

1

iconv -l | grep -Ei '^(gb|utf)'

1

http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2016/index.html

1

http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2016/13.html

1

http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2016/13/1306.html

1

http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2016/13/06/130637.html

1

http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2016/13/06/37/130637100.html

1

curl -fsL http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2016/index.html | iconv -f GBK -t UTF-8 2> /dev/null | sed -n "/class='provincetr'/{[email protected]'@@g;[email protected]<td>@\[email protected];p}" | sed -r -n '/href/{[email protected]*href=([[:digit:]]+)[^>]*>([^<]*)<.*@\1|\2|&@g;p}'

1

curl -fsL http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2016/13.html | iconv -f GBK -t UTF-8 | sed -r -n "/class='citytr'/{[email protected]</tr>@\[email protected];[email protected]<\/td>@ @g;[email protected]['\''|\"]@@g;[email protected][[:space:]]*<a href=[^>]*>([[:digit:]]+)<\/a>[[:space:]]@\[email protected];[email protected](href=)([^>]+)@\1>\2<@g;[email protected]<[^>]*>@ @g;[email protected][[:blank:]][email protected] @g;p}" | sed -r -n '/^[[:space:]]*$/d;[email protected]^[[:space:]]*@@g;[email protected][[:space:]]*[email protected]@g;p'

1

curl -fsL http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2016/13/1306.html  | iconv -f GBK -t UTF-8 | sed -r -n "/class='countytr'/{[email protected]</tr>@\[email protected];[email protected]<\/td>@ @g;[email protected]['\''|\"]@@g;[email protected][[:space:]]*<a href=[^>]*>([[:digit:]]+)<\/a>[[:space:]]@\[email protected];[email protected](href=)([^>]+)@\1>\2<@g;[email protected]<[^>]*>@ @g;[email protected][[:blank:]][email protected] @g;p}" | sed -r -n '/^[[:space:]]*$/d;[email protected]^[[:space:]]*@@g;[email protected][[:space:]]*[email protected]@g;p'

1

curl -fsL http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2016/13/06/130637.html  | iconv -f GBK -t UTF-8 | sed -r -n "/class='towntr'/{[email protected]</tr>@\[email protected];[email protected]<\/td>@ @g;[email protected]['\''|\"]@@g;[email protected][[:space:]]*<a href=[^>]*>([[:digit:]]+)<\/a>[[:space:]]@\[email protected];[email protected](href=)([^>]+)@\1>\2<@g;[email protected]<[^>]*>@ @g;[email protected][[:blank:]][email protected] @g;p}" | sed -r -n '/^[[:space:]]*$/d;[email protected]^[[:space:]]*@@g;[email protected][[:space:]]*[email protected]@g;p'

1

curl -fsL http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2016/13/06/37/130637100.html  | iconv -f GBK -t UTF-8 | sed -r -n "/class='villagetr'/{[email protected]</tr>@\[email protected];[email protected]<\/td>@ @g;[email protected]<[^>]*>@@g;p}" | sed -r '/^[[:digit:]]+/!d;[email protected][[:blank:]]*[email protected]@g'

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

┌─[[email protected]]─[/tmp/CADC]
└──╼ $tree
.
├── cadc_database_table.sql
├── cadc_crawler.sh
└── cadc_import_database.sh

0 directories, 3 files
┌─[[email protected]]─[/tmp/CADC]
└──╼ $

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84

-- 數據庫名 cadc
drop database if exists cadc;
create database if not exists cadc character set=utf8 collate=utf8_general_ci;
use cadc

-- province省、直轄市、自治區
drop table if exists province;
create table if not exists province (
    id tinyint unsigned not null auto_increment primary key comment '省級列表自增id',
    name char(30) not null comment '省份名稱',
    code tinyint unsigned not null comment '行政區劃代碼(共12位)，前2位指定，第1-2位為省級代碼',
    region enum('華北','東北','華東','中南','西南','西北') not null comment '省所屬地理區域:1華北、2東北、3華東、4中南(華中,華南)、5西南、6西北',
    createTime timestamp default current_timestamp comment '數據入庫時間 YYYY-MM-DD HH:MM:SS',
    updateTime timestamp null on update current_timestamp comment '數據更新時間 YYYY-MM-DD HH:MM:SS',
    key in_name (name),
    unique key in_code (code)
)engine=innodb default charset=utf8 collate=utf8_general_ci comment='中國大陸地區省份列表';
-- alter table province add index in_name (name);

--  city地市級
drop table if exists city;
create table if not exists city (
    id smallint unsigned not null auto_increment primary key comment '地級市列表自增id',
    province_id tinyint unsigned not null comment '省級id，對應表province，外鍵約束',
    name char(60) not null comment '地級市名稱',
    code char(12) not null comment '行政區劃代碼(共12位)，由前4位指定，第3-4位為地級代碼',
    createTime timestamp default current_timestamp comment '數據入庫時間 YYYY-MM-DD HH:MM:SS',
    updateTime timestamp null on update current_timestamp comment '數據更新時間 YYYY-MM-DD HH:MM:SS',
    key in_name (name(15)),
    key in_fk_province (province_id),
    unique key in_code (code),
    constraint fk_province_city foreign key (province_id) references province(id) on update cascade
)engine=innodb default charset=utf8 collate=utf8_general_ci comment='中國大陸地區地級市列表';
-- alter table city add constraint fk_province_city foreign key(province_id) references province(id) on update cascade;

--  country縣級
drop table if exists country;
create table if not exists country (
    id smallint unsigned not null auto_increment primary key comment '縣級列表自增id',
    city_id smallint unsigned not null comment '地級市id，對應表city，外鍵約束',
    name char(60) not null comment '縣級市名稱',
    code char(12) not null comment '行政區劃代碼(共12位)，由前6位指定，第5-6位為縣級代碼',
    createTime timestamp default current_timestamp comment '數據入庫時間 YYYY-MM-DD HH:MM:SS',
    updateTime timestamp null on update current_timestamp comment '數據更新時間 YYYY-MM-DD HH:MM:SS',
    key in_name (name(15)),
    key in_fk_city (city_id),
    unique key in_code (code),
    constraint fk_city_country foreign key (city_id) references city(id) on update cascade
)engine=innodb default charset=utf8 collate=utf8_general_ci comment='中國大陸地區縣級列表';

--  town鄉級
drop table if exists town;
create table if not exists town (
    id mediumint unsigned not null auto_increment primary key comment '鄉鎮列表自增id',
    coutnry_id smallint unsigned not null comment '縣級市id，對應表country，外鍵約束',
    name char(60) not null comment '鄉鎮名稱',
    code char(12) not null comment '行政區劃代碼(共12位)，由前9位指定，第7-9位為鄉級代碼',
    code_type enum('街道','鎮','鄉','類似鄉級單位') null comment '鄉級代碼分類(第7-9位指定)，1為街道 001~099，2為鎮 100~199，3為鄉 200~399, 4為類似鄉級單位 400~599',
    createTime timestamp default current_timestamp comment '數據入庫時間 YYYY-MM-DD HH:MM:SS',
    updateTime timestamp null on update current_timestamp comment '數據更新時間 YYYY-MM-DD HH:MM:SS',
    key in_name (name(15)),
    key in_fk_country (coutnry_id),
    unique key in_code (code),
    constraint fk_country_town foreign key (coutnry_id) references country(id) on update cascade
)engine=innodb default charset=utf8 collate=utf8_general_ci comment='中國大陸地區鄉鎮列表';

--  village村級
drop table if exists village;
create table if not exists village (
    id mediumint unsigned not null auto_increment primary key comment '村級列表自增id',
    town_id mediumint unsigned not null comment '鄉鎮id，對應表town，外鍵約束',
    name char(60) not null comment '村莊名稱',
    code char(12) not null comment '行政區劃代碼(共12位)，第10-12位為村級代碼',
    code_type enum('居民委員會', '村民委員會', '類似居民委員會','類似村民委員會','498虛擬社區','598虛擬社區') not null comment '村級代碼分類(第10-12位指定)，: 1 居民委員會 001~199, 2 村民委員會 200~399, 3 類似居民委員會（不含498代碼） 400~499, 4 類似村民委員會（不含598代碼） 500~599, 5 虛擬社區 498, 6 虛擬社區 598',
    -- town_attr enum('縣級政府駐地','連接的鄉級區域','其他鄉級區域') not null comment '城鄉屬性代碼鄉級屬性(由第13位決定): 1 縣級政府駐地, 2 連接的鄉級區域, 3 其他鄉級區域',
    -- village_attr enum('鄉級政府駐地','完全連接的村級地域','部分連接的村級地域','與其他區、市完全連接的村級地域','與其他區、市部分連接的村級地域','與其他鎮完全連接的村級地域','與其他鎮部分連接的村級地域','特殊地域','其他村級地域') not null comment '城鄉屬性代碼村級屬性(由第14位決定): 1 鄉級政府駐地, 2 完全連接的村級地域, 3 部分連接的村級地域, 4 與其他區、市完全連接的村級地域, 5 與其他區、市部分連接的村級地域, 6 與其他鎮完全連接的村級地域, 7 與其他鎮部分連接的村級地域, 8 特殊地域, 9 其他村級地域',
    classification enum('主城區','城鄉結合區','鎮中心區','鎮鄉結合區','特殊地區','鄉中心區','村莊') not null comment '城鄉分類,由第15-17位代碼指定,頁面中為3個獨立的數字(第15位代碼指定城鄉分類，1為城鎮，2為鄉村)：主城區 111，城鄉結合區 112，鎮中心區 121，鎮鄉結合區 122，特殊地區 123，鄉中心區 210，村莊 220',
    createTime timestamp default current_timestamp comment '數據入庫時間 YYYY-MM-DD HH:MM:SS',
    updateTime timestamp null on update current_timestamp comment '數據更新時間 YYYY-MM-DD HH:MM:SS',
    key in_name (name(15)),
    key in_fk_town (town_id),
    unique key in_code (code),
    constraint fk_town_village foreign key (town_id) references town(id) on update cascade
)engine=innodb default charset=utf8 collate=utf8_general_ci comment='中國大陸地區村級列表';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

MySQL [(none)]> show databases like 'cadc%';
+------------------+
| Database (cadc%) |
+------------------+
| cadc_2013        |
| cadc_2014        |
| cadc_2015        |
| cadc_2016        |
+------------------+
4 rows in set (0.00 sec)

MySQL [(none)]> use cadc_2016
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MySQL [cadc_2016]> show tables;
+---------------------+
| Tables_in_cadc_2016 |
+---------------------+
| city                |
| country             |
| province            |
| town                |
| village             |
+---------------------+
5 rows in set (0.01 sec)

MySQL [cadc_2016]> select count(distinct(a.id)) as province,count(distinct(b.id)) as city,count(distinct(c.id)) as country,count(distinct(d.id)) as town,count(distinct(e.id)) as village from province a
    -> left join city b on a.id=b.province_id
    -> left join country c on b.id=c.city_id
    -> left join town d on c.id=d.coutnry_id
    -> left join village e on d.id=e.town_id;
+----------+------+---------+-------+---------+
| province | city | country | town  | village |
+----------+------+---------+-------+---------+
|       31 |  344 |    3133 | 42866 |  666612 |
+----------+------+---------+-------+---------+
1 row in set (0.63 sec)

MySQL [cadc_2016]>

1
2
3

database_name='cadc_2016'

mysql -D "${database_name}" -se "select a.name province, b.name city, c.name country, d.name as town, e.name as village from province a left join city b on a.id=b.province_id left join country c on b.id=c.city_id left join town d on c.id=d.coutnry_id left join village e on d.id=e.town_id where e.name like '佛祖%';"