Asterisk - アスタリスク -

説明するほどでもないブログ

MySQL に郵便番号データを取り込んでみた

どうも。

CakePHP の勉強を少しずつやってる今日この頃。ドキュメントや他ブログを参考にしながら頑張ってる。
フレームワークのひとつくらいはマスターしたいので・・・ (つД`)

MySQL に郵便番号データを取り込んでみました。

1.郵便番号データのダウンロード

郵便番号データのダウンロード - zipcloud

今回は「全国一括データ (加工済バージョン)」を使用しました。

2.テーブルの作成

MySQL に以下のクエリでテーブルを作った。

DROP TABLE IF EXISTS postals;
CREATE TABLE postals (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '主キー'
, jiscode         CHAR(6)      NOT NULL COMMENT '全国地方公共団体コード'
, zip_code        CHAR(7)      NOT NULL COMMENT '郵便番号'
, prefecture_ruby VARCHAR(255) NOT NULL COMMENT '都道府県名(半角カナ)'
, city_ruby       VARCHAR(255) NOT NULL COMMENT '市区町村名(半角カナ)'
, town_ruby       VARCHAR(255) DEFAULT NULL COMMENT '町域名(半角カナ)'
, prefecture      VARCHAR(4)   NOT NULL COMMENT '都道府県名(漢字)'
, city            VARCHAR(255) NOT NULL COMMENT '市区町村名(漢字)'
, town            VARCHAR(255) DEFAULT NULL COMMENT '町域名(漢字)'
, flag1 INT NOT NULL COMMENT '一町域が二以上の郵便番号で表される場合の表示'
, flag2 INT NOT NULL COMMENT '小字毎に番地が起番されている町域の表示'
, flag3 INT NOT NULL COMMENT '丁目を有する町域の場合の表示'
, flag4 INT NOT NULL COMMENT '一つの郵便番号で二以上の町域を表す場合の表示'
, flag5 INT NOT NULL COMMENT '更新の表示'
, flag6 INT NOT NULL COMMENT '変更理由'
, created  DATETIME DEFAULT NULL COMMENT '作成日時'
, modified DATETIME DEFAULT NULL COMMENT '更新日時'
);

3.テーブルに合わせてプチ加工する

文字コードの変換や不要なフィールドの除外を行う。
wc, nkf, head などのコマンドを初めて知りました・・・ orz

文字コード変換前の件数を確認
# wc x-ken-all.csv
   124738   184177 12202762 x-ken-all.csv

UTF-8 変換
# nkf -w x-ken-all.csv > x-ken-all-utf.csv

変換確認
# nkf -guess x-ken-all-utf.csv
   UTF-8

文字コード変換後の件数を確認
# wc x-ken-all-utf.csv

先頭行を確認
# head -n 1 x-ken-all-utf.csv

ダブルクォーテーションを消す
# sed -e "s/\"//g" x-ken-all-utf.csv > x-ken-all-utf-s.csv

先頭行を確認
# head -n 1 x-ken-all-utf-s.csv

awkコマンドで必要なものだけを取り出す
# awk -F, '{print ",\""$1"\",\""$3"\",\""$4"\",\""$5"\",\""$6"\",\""$7"\",\""$8"\",\""$9"\","$10","$11","$12","$13","$14","$15}' x-ken-all-utf-s.csv > x-ken-all-utf-si.csv

先頭行を確認
# head -n 1 x-ken-all-utf-si.csv

インポートするためファイルをコピー
# cp x-ken-all-utf-si.csv /var/lib/mysql/cake_db/x-ken-all-utf-si.csv

コマンドラインから MySQL へログイン
# mysql -u root -p

使うデーターベースを選択
# mysql> use cake_db;

load data infile で取り込み
# mysql> load data infile "x-ken-all-utf-si.csv" into table postals fields terminated by ',' optionally enclosed by '"';

取り込み結果を確認
# mysql> select * from postals limit 10\G

# mysql> select count(*) as cnt from postals;

これで良い感じに取り込みができました。load data infile まわりでエラーが発生したときは権限の確認を。

参考サイト
http://cakephp20sysnsa.blogspot.jp/2012/05/blog-post.html


因みに、プロフィール画像を変更しました。あと応援バナーの追加もしておいたw


以上です。