PostgreSQL と PHP で郵便番号検索入門

目的

RDB をバックエンドに持った WebApplication を作ろう！と志は大きいのですが、技が伴いませんので、とりあえず検索ってのが簡単かなと。 まずは PHP から PostgreSQL をつつけるようになりましょう。 これが目的。 せっかくだから郵便番号検索でも。

前準備(データの準備)

http://www.post.yusei.go.jp/zipcode/dl/kogaki.html から郵便番号のデータを拾ってきましょう。 ゴージャスに全県分です。 拾ってきたら適当な場所に溶かします。 lha を準備しておく必要がありますが、最近の linux なら最初から入ってるでしょう。

文字コードの変換

さて、取り出した ken_all.csv の中身は JIS コードで書かれているので、 Postgres に都合の良い EUC にしてあげないといけません。今回は、emacs で ken_all.csv を読み込んで coding-system を変更します。

emacs ken_all.csv

emacs が起動しましたらば

C-x <RET> f euc-japan-unix

つまり、コントロールキー押したまま x 押します。 離してから Enter キー押します。 そして f 押します。 coding system を何にするか、と問い合わせてきますので、 euc-japan-unix と答えておきます。 そのあと C-x-s で上書き保存。 コントロールキー押したままで x 押して、コントロールキーはまだ離さずに s を押します。 もう離して良し。 最後に C-x-c で終了です。

nkf 1.9 辺りを使うと、半角仮名を全角仮名になおしてくれるんですが、僕が試した限りでは、精度がイマイチなのでした。なのでわざわざemacsを使っているのです。

PostgreSQLで使いやすいように変換

次に、「”」ダブルクオートを取り去り、よけいなスペースを取り去り、 検索に必要無い部分は飛ばしてしまいましょう。 使う部分だけを切り取る作業です。 データの中からは、2〜9番目のカラムが欲しい部分です。 それと、カンマ区切りをタブ区切りにします。 タブ区切りにする理由は、 pgsql で読み込むとき都合がよいからです。 それだけ。

僕がおこなった方法は、

---- post_conv.pl ----
while(<>){
$_ =~ s/\s//g;
$_ =~ s/\"//g;
@a = split( /\,/ );
printf ("%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\n" ,$a[1],$a[2],$a[3],$a[4],$a[5],$a[6],$a[7],$a[8]);
}

という内容のファイルを準備して

perl post_conv.pl ken_all.csv > ken_all2.csv

で、できあがりました。 プログラムのきれい or 汚い は、この際無視です。 目的達成のため走りましょう。 生成された ken_all2.csv が目的のブツです。 ここまででデータの準備は終わりです。

テーブル等を作る、データを入れる

データを入れるデータベースを作ります。 postgres ユーザでログインして作業を行います。 今回は PostDB という名のDBを作ることにしましょう。

createdb PostDB

このコマンドで PostDB ができました。DB ができたら、次はデータを入れるためのテーブルを作ります。create table コマンドをぽつぽつ打っても構わないのですが、面倒なのでファイルを準備して一括処理させます。

ファイル例：

---- post_table.sql ----
create table PostalTBL (
OldCode text,
NewCode text,
kPref text,
kCity text,
kTown text,
Pref text,
City text,
Town text );

create index OldCodeIndex on PostalTBL using btree(OldCode);
create index NewCodeIndex on PostalTBL using btree(NewCode);
create index kPrefIndex on PostalTBL using btree(kPref);
create index kCityIndex on PostalTBL using btree(kCity);
create index kTownIndex on PostalTBL using btree(kTown);
create index PrefIndex on PostalTBL using btree(Pref);
create index CityIndex on PostalTBL using btree(City);
create index TownIndex on PostalTBL using btree(Town);

copy PostalTBL from '/home/postgres/post/ken_all2.csv';

やってることは3つ。 まずテーブルを作り、次にインデックスを作り、最後にデータをインポートです。 インデックスは、ディスクに余裕がなければ作らなくても良いですが、有ると無いとでは検索速度に大きな違いがでます。

さて実際に処理させます。

psql -f post_table.sql PostDB postgres

-f ファイル名 で読み込むファイルを指定します。次の引数で読み込む DB 名を指定します。最後の引数でどのユーザ権限で作業するかを指定します。デフォルトだと、ファイルのインポートは postgres ユーザでないとできないので、postgres ユーザを使ってます。 インポートには時間が掛かるので、死んだか？と焦らずに、じっくり待ってあげてください。 エラー等が出なければ終了です。

データが入ってるか確認

正常にデータが入ったか確認しましょう。 psql コマンドでDBを操作できる状態にします。

psql PostDB

プロンプトが出たらSQLを打ってみます。

select * from postaltbl where OldCode='100';

東京都〜〜というのが数十件帰ってきたらDBの準備はうまく行ってます。確認は終わりましたので、 \q と打って psql から抜けましょう。

ユーザの作成

WebApplication、ということで今回の場合 Apache から DB にアクセスします。 Apache 経由でアクセスできるよう、ユーザを作る必要があります。 デフォルトでは nobody ですので、 nobody という名のユーザを作ります。

createuser nobody

これでユーザができました。 DB側の作業がようやく終わりました。

Moriya / moriya@s1.xrea.com