Silex + PDO + Volcanus_CsvでSQLiteに郵便番号データを取り込んでみた

先日、CSVファイルの入出力用ライブラリ Volcanus_Csv を公開しました。

CSV処理クラスを作成した者の宿命として、まずは全国の郵便番号データ…悪名高き「ken_all.csv」と戦ってみるつもりでいたのですが…。

どうやら全国多数のプログラマ達の怨嗟の声を受け zipcloud さんが ken_all.csv と日夜戦ってくださっているようですので、難しいところは zipcloud さんにお任せします。

というわけで、zipcloud さんの x-ken-all.csv (2012年11月30日更新) を使わせていただき、郵便番号と住所を相互変換するためのデータベースをSQLiteで作成してみます。

郵便番号データファイルについて

x-ken-all.csv は簡単に書くと、ken_all.csv で複数行に渡ってしまっているデータが綺麗に1行にまとめられているもののようです。

元データである「郵便番号データファイル」のフォーマットについては、日本郵便の説明ページがあります。

1 全国地方公共団体コード(JIS X0401、X0402)………半角数字
2 (旧)郵便番号(5桁)………………………………………半角数字
3 郵便番号(7桁)………………………………………半角数字
4 都道府県名…………半角カタカナ(コード順に掲載)(注1)
5 市区町村名…………半角カタカナ(コード順に掲載)(注1)
6 町域名………………半角カタカナ(五十音順に掲載)(注1)
7 都道府県名…………漢字(コード順に掲載)(注1,2)
8 市区町村名…………漢字(コード順に掲載)(注1,2)
9 町域名………………漢字(五十音順に掲載)(注1,2)
10 一町域が二以上の郵便番号で表される場合の表示(注3)(「1」は該当、「0」は該当せず)
11 小字毎に番地が起番されている町域の表示(注4)(「1」は該当、「0」は該当せず)
12 丁目を有する町域の場合の表示(「1」は該当、「0」は該当せず)
13 一つの郵便番号で二以上の町域を表す場合の表示(注5)(「1」は該当、「0」は該当せず)
14 更新の表示（注6）（「0」は変更なし、「1」は変更あり、「2」廃止（廃止データのみ使用））
15 変更理由(「0」は変更なし、「1」市政・区政・町政・分区・政令指定都市施行、「2」住居表示の実施、「3」区画整理、「4」郵便区調整等、「5」訂正、「6」廃止(廃止データのみ使用))
※1 文字コードには、MS漢字コード(SHIFT JIS)を使用しています。
※2 文字セットとして、JIS X0208-1983を使用し、規定されていない文字はひらがなで表記しています。
※3 「一町域が二以上の郵便番号で表される場合の表示」とは、町域のみでは郵便番号が特定できず、丁目、番地、小字などにより番号が異なる町域のことです。
※4 「小字毎に番地が起番されている町域の表示」とは、郵便番号を設定した町域(大字)が複数の小字を有しており、各小字毎に番地が起番されているため、町域(郵便番号)と番地だけでは住所が特定できない町域のことです。
※5 「一つの郵便番号で二以上の町域を表す場合の表示」とは、一つの郵便番号で複数の町域をまとめて表しており、郵便番号と番地だけでは住所が特定できないことを示すものです。
※6 「変更あり」とは追加および修正により更新されたデータを示すものです。
※7 全角となっている町域名の文字数が38文字を超える場合、また、半角カタカナとなっている町域名のフリガナが76文字を越える場合には、複数レコードに分割しています。
http://www.post.japanpost.jp/zipcode/dl/readme.html

（実際には説明文とは異なる点などもあるようで、詳しくは郵便番号データの落とし穴が参考になります）

地方公共団体コードや都道府県名など、郵便番号と直接関連付ける必要のないデータも含まれていますが、今回は正規化を考えず、郵便番号と住所を相互変換する上で必要な項目をインポートします。
10から15までのフラグ類については、実際のデータを見ないと分からない感じがしますし、明らかに不要な14,15以外は一応取り込んでおきます。

いくつかCSVを直接覗いたところ、「半角カタカナ」といいつつ「ｱｲﾉｻﾄ1ｼﾞｮｳ」のように半角数字が混じっていたり、何のためにカナ項目を設けているのか理解不能だったりしますが、気にしないでおきます。

テーブル定義は以下としました。
※コメントの括弧内はCSVのフィールドインデックス

Silex と PDO で SQLite に取り込む

これをSilex のリクエストハンドラから Volcanus\Csv\Reader と PDO を使って SQLite にインポートしていきます。

PDOは複数のリクエストハンドラで共有しますので、Pimple::share() で PDO_Sqlite オブジェクトを Application に登録します。

まずは GET /postals/prepare へのリクエストハンドラでテーブルを作成します。

次に、GET /postals/import へのリクエストハンドラで、x-ken-all.csv を読み込んでインポートします。

Volcanus\Csv\Reader では、1レコード分のCSVを読み込む毎に実行されるコールバック関数を定義することで、SplFileObjectからのCSVパース時に様々な処理を行えます。

$reader->appendFilter(function($fields) {....}); の部分で、パースしたCSVのフィールドをカナ文字半角から全角に変換、フラグは数値に変換しつつ、PDOStatement::execute()に渡すパラメータの配列にセットしています。

$reader->fetchAll() メソッドで一気に実行しますので、このメソッドの前後でトランザクションの開始・コミットを行い、例外捕捉時はロールバックします。
整理された x-ken-all.csv でもやはりレコード数は12万件以上ありますので、実行前に set_time_limit(0) で実行時間の制限を解除しています。

せっかくなので、CSVの開始から完了までの時間も計測してみました。
実行した環境は先日正式にショートプランで契約しました、レンタルサーバ Gehirn RS2 です。メモリ64GBの威力はどんなもんでしょうか…。