PostgreSQLの利用例:郵便番号検索
せっかくPostgreSQLをインストールしたので、その利用例として郵便番号検索システムを構築しました。
郵便番号検索システムの構築方針
システムを以下の方針に沿って構築します
- 住所から郵便番号を検索する
- その場合、あいまい検索を可能とする(住所の一部からでも検索可能)
- 郵便番号から住所を検索できるようにする(1の逆)
具体的なイメージが分かりにくいかもしれません。
実際の郵便番号検索システムを体験してください
<データベース・テーブルの分割>
当初、全国のデータを一つのテーブルで管理しようと考えました。
しかし、郵便番号情報のデータ件数は12万件を超えており、筆者のサーバPCでは、能力不足でした。
この問題を回避するために、データベースのテーブルを都道府県別に分けて管理することにしました。 |
郵便番号検索システムの構築
- 元データのダウンロード
住所と郵便番号の一覧が日本郵便公社からダウンロードできます。
ここから全国一括(ken_all.lzh)と事業所の個別郵便番号(jigyosyo.lzh)をダウンロードし適当なフォルダーに解凍します。
- 元データの編集
上記の解凍結果として、KEN_ALL.CSVとJIGYOSYO.CSVの2つファイルが出来ます。
このファイルをPostgeSQLで利用しやすいように変換します。
具体的には次の通りです
ファイル名 |
変換内容 |
KEN_ALL.CSV |
ファイルを都道府県別に分割し、1行のフォーマットを次のようにする
各項目の区切りは TAB を使用
旧郵便番号 |
郵便番号 |
都道府県名 |
住所−1 |
住所-2 |
|
JIGYOSYO.CSV |
1行のフォーマットを次のようにする
各項目の区切りは TAB を使用
|
この変換を手作業で行うとすれば、気の遠くなりそうな話です。
変換作業を自動で行うプログラムを作成しました。
必要な方は、ここからダウンロードして使ってください。
- 変換作業
各ファイルを次のように配置します。
ここのyubinは、適当な名前で結構です。
yubin |
− |
+ |
− |
result |
resultフォルダを作成する |
|
|
| |
|
|
|
|
|
+ |
− |
KEN_ALL.CSV |
全国一括の解凍ファイル |
|
|
| |
|
|
|
|
|
+ |
− |
JIGYOSYO.CSV |
事業所と個別郵便番号の解凍ファイル |
|
|
| |
|
|
|
|
|
+ |
− |
cvt_yu_no.exe |
変換プログラム |
DOS窓を開いて、カレントフォルダを上記の場合ではyubinに移動します。
そこで、cvt_yu_noを実行します。
c:\>cd yubin←カレントフォルダをyubinに変更
c:\yubin>cvt_yu_no←cvt_yu_noを実行
yubin\result に変換されたファイルが作成されます。
- 3で作成したファイルをサーバへUP
変換後のファイルをサーバへUPします。
筆者の場合、DBの作業は アカウント=alk で行うので、下記フォルダをベースにします。
/home/alk/
このフォルダに ydb の名前でフォルダを作成します。
この作成したフォルダに、変換したファイル全てをコピーします。
PCのフォルダ |
|
サーバのフォルダ |
yubin\result\ |
→ |
/home/alk/ydb/ |
注意:これらのファイルをサーバへコピーするときは、EUCコードでコピーしてください
- SQLファイルの準備
準備したデータをDBへ登録するためには、PostgreSQLのコマンドを使って登録作業を行わなければなりません。
この作業も手作業で行うには量が多すぎます。
DBへの登録作業を行うためのコマンド群を定義したファイル(bldydb.sql
)を準備しました。
bldydb.sql を /home/alk/ の下へコピーします。
このファイルは、テキストモードでコピーしてください
これで、郵便番号検索システム構築の準備作業が終わりました。
次に実際のDBの作成をサーバ上で行います
DBの作成
PostgreSQLへユーザを登録し、DBの操作が出来るようにします
- PostgreSQLユーザの登録
PostgreSQLの管理者用のアカウントは postges です。
postgresでログインします。
# su postgres←rootでログインし、posugresに切り替える
$ createuser alk←PostgreSQLのユーザを作成する
Shall the new user be allowed to create databases?
(y/n) y←作成するか確認メッセージ y を入力
Shall the new user be allowed to create more
new users? (y/n) n←さらに登録するか聞いてくる n を入力して終了
CREATE USER
- 1で作成したPostgreSQLユーザでログインしDBの作成
$ createdb yubin←alkでログインし、データベース yubin を作成する
CREATE DATABASE
- データの登録
作成したDBへ郵便番号データを登録します。
準備段階で用意した bldydb.sql に全作業のコマンドが記述されています。
次のコマンドで自動的に登録作業が行われます。
ユーザ:alk で実行すること。
$ psql -f bldydb.sql yubin←これで、DBへの郵便番号データの登録を行う
初めてこのコマンドを実行したときは、次のようなエラーメッセージを多数出力します。
psql:bldydb.sql:1: ERROR: table "yu_hokkaido"
does not exist
これは無視してもらって結構です。
2回目の登録からは、出なくなります。
psql -f bldydb.sql yubin は何をしている?
psqlは、DB操作のためのユーティリティ(プログラム)です。
本来 psql はDBと対話するためのユーティリティで、DBへのデータの登録等の処理を対話形式で行います。
しかし、登録すべきデータが多い場合、対話形式の作業は不向きです。
今回は、対話で入力すべきコマンドをファイルで与えています。
bldydb.sqlファイルの観察
bldydb.sqlの中を覗くと、次の4行が都道府県の数だけ並んでいるのが分かると思います。
yu_hokkaidoの部分が都道府県別の名前になっています。
この各行が psql へのコマンドになっています。
drop table yu_hokkaido;
create table yu_hokkaido(old_no text,new_no text,ken_name text,shi_name
text,cho_name text);
\copy yu_hokkaido from ydb/hokkaido.ydb
grant all on yu_hokkaido to "www-data";
コマンド |
意 味 |
drop table |
テーブルを削除します。
最初のデータ登録時にエラーが多量に出ていました。
これは、テーブルが登録されていないのにテーブルの削除(drop)を試みたからです |
create table |
テーブルを作成します。
レコード内の項目と属性は次の通りです。
項目名 |
属性 |
内容 |
old_no |
text |
旧郵便番号 |
new_no |
text |
新郵便番号 |
ken_name |
text |
都道府県名 |
shi_name |
text |
市名 |
cho_name |
text |
町名 |
|
\copy |
テーブルへデータを登録(コピー)します。 |
grant |
yubin の DB は alk が作成しました。
このままだと、ユーザ alk 以外は DB へアクセスできません。
web上では ユーザ名=www-data となります。
grantコマンドを使って、www-dataにアクセス権限を与えています。 |
各種ファイルのダウンロード
ここで使用した各種ファイルは下記からダウンロードできます。
ダウンロードページ
|