郵便番号検索を作る─１.テーブルを作成

今まででCakePHP2.1の基礎の部分を勉強してきたんで、そろそろ実践しながら学んでいこうと思う。やっぱり実際に作りながら覚えるのが一番だよね。

こういう場合一番いいのが簡単なブログを作ってみるっていうのだと思うけど、それはCookBook2.xに日本語版でも詳しく載ってるので、何つくろうかなって５秒くらい考えた結果、ブログの次に簡単そうで色んな人もやってる郵便番号検索をぼくも作成してみることにした。

多分、郵便番号検索を作ってみるメリットはこんな感じであると思う。

・大量のデータを扱える
・DBに登録するデータは簡単にDLできる
・ただ郵便番号を検索するだけなら比較的作りが簡単(多分ね)
・後で複雑な検索も付け加えられる(と思う)

今回はあくまでもcakePHPの練習用として作成するわけだし、「データをcakePHPのモデル内で加工する」ってこともやってみたいから郵便番号をdecimal型で保存しようと思う。decimal型にするのは、単に文字列型より検索時に軽くなるっぽいしね。

事前準備──郵便番号データを用意

一応日本郵便のホームページからもダウンロードできるんだけど、いろいろと問題があるっぽいので(郵便番号データの落とし穴)、下記の再配信サービスから「x-ken-all.csv」ダウンロードした。

zipcroud─郵便番号再配信サービス
(郵便番号データ（加工済バージョン）をダウンロード)

なお、日本郵便もだけどzipcloudの郵便番号データも文字コードがShift-JISになってる。
ぼくの開発環境はUTF8だし、そもそもCakePHPのデフォルト文字コードがUTF8だから、ここは変換すべきかも。

$ nkf -w x-ken-all.csv > x-ken-all-utf.csv //UTF形式に変換
$ nkf -guess x-ken-all-utf.csv //ちゃんと変換されたかチェック
UTF-8 (CR)
$ wc ken_all_utf.csv //csvの行数を調べておく
  124650   186766 18042207 x-ken-all-utf.csv

(Windosでの文字コード変換ソフトはVectorで簡単に見つかります）

更にデータを加工する

ダウンロードしたデータは、以下の項目がcsv形式で格納されている。

全国地方公共団体コード
(旧)郵便番号(5桁)
郵便番号(7桁)
都道府県名(カタカナ)
市区町村名(カタカナ)
町域名(カタカナ)
都道府県名(漢字)
市区町村名(漢字)
町域名(漢字)
他よくわからない数字が6つ
(詳細は日本郵便:郵便番号データファイルの形式等に記載してます)

この中で、旧郵便番号はいらない気がするし、よくわからない数字も「更新の有無」と「その理由」以外はあっても使わない気がするから、中身をちょっとsedコマンドとawkコマンドを使って編集する。

//まずsedコマンドでダブルクォーテーションを消す
$ sed -e "s/\"//g" x-ken-all-utf.csv > x-ken-all-utf-s.csv 
//結果を１行だけ表示
$ head -n 1 x-ken-all-utf-s.csv
01101,060  ,0600000,ホッカイドウ,サッポロシチュウオウク,,北海道,札幌市中央区,,0,0,0,0,0,0

//次に、awkコマンドで必要なものだけを取り出す
$ awk -F, '{print ",\""$1"\",0."$3",\""$4"\",\""$5"\",\""$6"\",\""$7"\",\""$8"\",\""$9"\","$14","$15}' x-ken-all-utf-s.csv > x-ken-all-utf-si.csv
//できた結果を1行だけ表示
$ head -n 1 x-ken-all-utf-si.csv
,"01101",0.0600000,"ホッカイドウ","サッポロシチュウオウク","","北海道","札幌市中央区","",0,0

先頭にカンマ(,)を入れたのは、このあとmysqlでLoad Data Infileをするつもりで、auto_incement型の数値を入れる為。「全国地方公共団体コード」はなんとなく文字列型で保存することにします(先頭に0があってヤだし)。

テーブルを作成する

以下の形でテーブルを作成する。

■テーブル名
  postal_codes(命名規則に沿って複数型に)
■フィールド
  id──主キー(int型)
  jiscode──全国地方公共団体コード(varchar型)
  zipcode──郵便番号(decimal型)
  state_kana──都道府県カタカナ(varchar型)
  city_kana──市区町村カタカナ(varchar型)
  street_kana──町域カタカナ(varchar型)
  state──都道府県(varchar型)
  city──市区町村(varchar型)
  street──町域(varchar型)
  changed──最近変更されたか(tinyint(3)型)
  cause──変更理由(tinyint(3)型)
  (以下追加：2012/06/11)住所検索を実装する際に追加しました。
  address──全部の住所(varchar(100)型)

とりあえず、都道府県とかはどれくらいの文字数があるかわからないので、全部varchar(100)で作成しておく。
あとインデックスは最初はzipcodeのみに貼っておく。

mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> create table if not exists postal_codes(
    -> id int(11) unsigned not null auto_increment ,
    -> jiscode varchar(6) collate utf8_bin not null default '',
    -> zipcode decimal(8,7) unsigned not null default 0,
    -> state_kana varchar(100) collate utf8_bin not null default '',
    -> city_kana varchar(100) collate utf8_bin not null default '',
    -> street_kana varchar(100) collate utf8_bin not null default '',
    -> state varchar(100) collate utf8_bin not null default '',
    -> city varchar(100) collate utf8_bin not null default '',
    -> street varchar(100) collate utf8_bin not null default '',
    -> changed tinyint(3) unsigned not null default 0,
    -> cause tinyint(3) unsigned not null default 0,
    -> primary key (id),
    -> key zipcode (zipcode)
    -> )Engine=Innodb default character set utf8 collate utf8_bin comment '郵便番号';
Query OK, 0 rows affected (0.29 sec)

データを挿入

あとは、LOAD DATA IN FILEで一気に挿入するだけ。
注意すべき点は、LOAD DATA IN FILEをする際にはset character_set_database='utf8';みたいに指定しておかないと文字化けしちゃうってこと。

mysql> set character_set_database='utf8';
Query OK, 0 rows affected (0.03 sec)
mysql> load data infile "/x-ken-all-utf-si.csv" into table postal_codes 
    -> fields terminated by ',' optionally enclosed by '"';
Query OK, 124650 rows affected, 65535 warnings (6.86 sec)
Records: 124650  Deleted: 0  Skipped: 0  Warnings: 0

最初に確認した124650行全部はいりました。ということで最後に確認。

mysql> select * from postal_codes limit 10\G
*************************** 1. row ***************************
         id: 1
    jiscode: 01101
    zipcode: 0.0600000
 state_kana: ホッカイドウ
  city_kana: サッポロシチュウオウク
street_kana: 
      state: 北海道
       city: 札幌市中央区
     street: 
    changed: 0
      cause: 0
*************************** 2. row ***************************
(中略)
10 rows in set (0.00 sec)

mysql> select count(*) as cnt from postal_codes;
+--------+
| cnt    |
+--------+
| 124650 |
+--------+
1 row in set (0.10 sec)

バッチリだね。ここまでは、cakePHPと関係あるのは命名規約でテーブル名を複数型にしたことくらいしかないけど、次回はこれを使ってモデルとコントローラーを作成していこうと思います。

CakePHP2.1初心者メモ

2012年5月8日火曜日