Silex + Volcanus_Csv + PDO_SQLiteユーザー定義関数で KEN_ALL.CSV と戦う（準備編）

CSVファイルの入出力用ライブラリ Volcanus_Csv を使ったシリーズ記事

Volcanus/Csv/Readerによる zipcloud さん提供の郵便番号データの取り込みは意外と簡単にできたので、今度こそ KEN_ALL.CSV と戦ってみようと思います。
しかし、なにせ敵は総勢12万の大軍ですし、何の準備もなくいきなり戦うのも少々危険です。

そんなわけで、まずはそのままの内容を取り込んで、データの中身を調査してみます。
利用したデータは前回と同じく、2012年11月30日更新のものです。

郵便番号データファイルのフォーマットについては説明を省略します。

公式の情報は郵便番号データの説明で。
公式からは分からない情報は郵便番号データの落とし穴が参考になります。

今回も Silex のリクエストハンドラから Volcanus\Csv\Reader と PDO を使って SQLite にインポートしていきます。
テーブル定義はほぼCSVのフォーマットそのままですが、文字コードのみSJISからUTF-8に変換して取り込みます。

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

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

次に、GET /ken_all/import へのリクエストハンドラで、KEN_ALL.CSV を読み込んでインポートします。

前回に続き、レンタルサーバ Gehirn RS2 で実行し、インポートにかかった時間を計測しました。

全123355件で28秒です。前回の結果とほとんど変わりません。

さて、今回はここからが本番。
SQLiteのユーザー定義関数を利用して、正規表現検索でデータの中身を調査していきます。

こんな感じで、ユーザー定義関数を登録すると、SQL内で呼び出せるんです。とても簡単でしょう？

次に、GET /ken_all/examples へのリクエストハンドラで、正規表現検索を実行してみます。
ざっと以下のような内容です。

町域名カナの終端が「ｲｶﾆｹｲｻｲｶﾞﾅｲﾊﾞｱｲ」
町域名カナの終端が「ﾂｷﾞﾆﾊﾞﾝﾁｶﾞｸﾙﾊﾞｱｲ」
町域名カナの終端が「ｲﾁｴﾝ」、ただし 5220317 滋賀県犬上郡多賀町一円は除く
郵便番号が同じかつ町域名が異なり、マージが必要
町域名カナに半角カナ以外の文字がある

上2つの例は明らかに住所ではない、データの取り扱いについてのコメントのようなもので、zipcloudさんのデータでは空文字に置換されています。

また3つ目の「ｲﾁｴﾝ」についても、町域名の末尾が「○○一円」のデータがいくつかあって、何か包括的な呼称として使われているらしいということで、zipcloudさんのデータで同様に空文字に置換されています。

4つ目は郵便番号データの落とし穴で解説されていた条件をそのまま使わせてもらいました。
4番目のフラグ（一つの郵便番号で二以上の町域を表す場合の表示）が0かつ、郵便番号が同じで町域名が異なるデータこそが、本来1件にまとめるべきデータということですね。

5つ目はだいたい眺めていて、町域名カナに数字や括弧、ハイフンといった半角カナ以外の文字があるデータが怪しいと感じたので、この条件で抽出してみたものです。

上記クエリの実行結果を見たところ、本来1レコードであるべきデータが2レコードだけではなく3,4レコードにまたがるものもありました。
そもそも「以下○○」というように順序性を前提とした構造なので、一度取り込んでからSQLで処理するよりも、CSVを順次パースしながら、条件を満たす場合はその場で登録せず編集してから登録する方が良いかもしれません。

また、zipcloud版の加工済みデータでは "括弧内の文字が「地名」や「ビルの階」など、住所として使えそうなものは町域名の末尾に追加します。"とありますが、これが原因でいくつか住所として必要な情報が欠落しているんじゃないかと思われるケースもありました。
たとえば 0600042「大通西（１～１９丁目）」 0640820「大通西（２０～２８丁目）」といった、本来はコード・住所ともに別のデータが 0600042「大通西」 0640820「大通西」と、括弧内の「○丁目」が削除されているケースです。

ただ、○丁目や○番地の扱いは、0050840「藤野（４００、４００－２番地）」や 0683161「栗沢町宮村（２４８、３３９、７２６、７８０、８００、８０６番地）」 0680113「栗沢町宮村（その他）」、あるいは 3812241「青木島町青木島乙（９５６番地以外）」のように、文字列検索では対応不可能な形式のデータも多く、残したところで実効性は疑わしい感じもします。

京都の住所表記のように利用者側でも統一されていないケースもあります。（ジオどすなんて専用のジオコーダAPIがあるくらい）
これはzipcloud版では 6020071「扇町（堀川通寺之内上る、堀川通上御霊前上る、上御霊前通堀川東入）」のようなケースでは 6020071「扇町堀川通寺之内上る」 6020071「扇町堀川通上御霊前上る」 6020071「扇町上御霊前通堀川東入」に3分割されていて、扱いやすくなっています。

注意点などはすでに多くの先達がまとめてくださっていますので、ここでは調査によって発見した、厄介な郵便番号データの実態をいくつか紹介します。

同じ郵便番号で、括弧内の町域名と町域名カナが複数レコードに渡って区切られている

（0210102 岩手県一関市萩荘）

同じ郵便番号で、括弧内の町域名が複数レコードに渡っているが、町域名カナは同一

（6008128 京都市下京区大工町）

同じ郵便番号で、括弧内の町域名と町域名カナが複数レコードに渡って区切られている…って何この地名

（9218046 石川県金沢市大桑町）

同じ郵便番号で、括弧内の町域名と町域名カナが複数レコードに渡って区切られており、番地？が「～」により範囲指定されている

（9910801 山形県西村山郡大江町左沢）

都心のビルはだいたいこんな感じ…

（1000013 東京都千代田区霞が関）

もうわけわかんない、何このカナ表記

（9420083 新潟県上越市大豆）

町域名が「一円」

（5220317 滋賀県犬上郡多賀町一円）

「○○一円」の中で、唯一の例外として町域名が「一円」のデータがあるという話でした。

自力で作成するのであれば「以下に掲載がない場合」や「○○町の次に番地がくる場合」のような住所ではない情報を除いて、できるだけ元の情報を残しつつ、マージすべきものはマージして、分割すべきものは分割する…と言うのは簡単だけど、特に分割の判断が難しいですね。

特に注意すべきものは、札幌市に多い「○～○丁目」といった範囲ごとに番号が振られているケースと、京都市の「町域名（○○下る、○○上る、○○西入、○○東入）」のように、同じ番号が振られた町域名に対して複数の通称（？）があるケースでしょうか。
前者だと住所から郵便番号を検索する場合に、「○丁目」を判別しない限り複数の候補から選択させるしかないし、後者だと逆に郵便番号から住所を検索する場合に、どの通りを基準とした住所が欲しいのかが分からないため、やはり複数の候補から選択させるしかありません。

美しい設計を目指すのであれば、丁目や番地の範囲は幾何データ型(x,y)で表現すべき？などと考えてしまいますが、まあ実態は前述の通りなので…zipcloud版のように、どういう形で郵便番号と住所を関連付けるかを考えて、どこかで切り捨てる必要がありそうです。

たぶん続きます。