PostgreSQLの利用例:郵便番号検索

郵便番号検索システム


せっかくPostgreSQLをインストールしたので、その利用例として郵便番号検索システムを構築しました。

郵便番号検索システムの構築方針


システムを以下の方針に沿って構築します
  1. 住所から郵便番号を検索する
  2. その場合、あいまい検索を可能とする(住所の一部からでも検索可能)
  3. 郵便番号から住所を検索できるようにする(1の逆)

具体的なイメージが分かりにくいかもしれません。
実際の郵便番号検索システムを体験してください

<データベース・テーブルの分割>

当初、全国のデータを一つのテーブルで管理しようと考えました。
しかし、郵便番号情報のデータ件数は12万件を超えており、筆者のサーバPCでは、能力不足でした。
この問題を回避するために、データベースのテーブルを都道府県別に分けて管理することにしました。

郵便番号検索システムの構築


  1. 元データのダウンロード
    住所と郵便番号の一覧が日本郵便公社からダウンロードできます。
    ここから全国一括(ken_all.lzh)と事業所の個別郵便番号(jigyosyo.lzh)をダウンロードし適当なフォルダーに解凍します。

  2. 元データの編集
    上記の解凍結果として、KEN_ALL.CSVとJIGYOSYO.CSVの2つファイルが出来ます。
    このファイルをPostgeSQLで利用しやすいように変換します。
    具体的には次の通りです

    ファイル名 変換内容
    KEN_ALL.CSV ファイルを都道府県別に分割し、1行のフォーマットを次のようにする
    各項目の区切りは TAB を使用

    旧郵便番号 郵便番号 都道府県名 住所−1 住所-2

    JIGYOSYO.CSV 1行のフォーマットを次のようにする
    各項目の区切りは TAB を使用

    旧郵便番号 郵便番号 事業署名 住所


    この変換を手作業で行うとすれば、気の遠くなりそうな話です。
    変換作業を自動で行うプログラムを作成しました。
    必要な方は、ここからダウンロードして使ってください。

  3. 変換作業
    各ファイルを次のように配置します。
    ここの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 に変換されたファイルが作成されます。

  4. 3で作成したファイルをサーバへUP
    変換後のファイルをサーバへUPします。
    筆者の場合、DBの作業は アカウント=alk で行うので、下記フォルダをベースにします。

    /home/alk/

    このフォルダに ydb の名前でフォルダを作成します。
    この作成したフォルダに、変換したファイル全てをコピーします。

    PCのフォルダ   サーバのフォルダ
    yubin\result\   →   /home/alk/ydb/

    注意:これらのファイルをサーバへコピーするときは、EUCコードでコピーしてください

  5. SQLファイルの準備
    準備したデータをDBへ登録するためには、PostgreSQLのコマンドを使って登録作業を行わなければなりません。
    この作業も手作業で行うには量が多すぎます。
    DBへの登録作業を行うためのコマンド群を定義したファイル(bldydb.sql )を準備しました。
    bldydb.sql を /home/alk/ の下へコピーします。
    このファイルは、テキストモードでコピーしてください

これで、郵便番号検索システム構築の準備作業が終わりました。
次に実際のDBの作成をサーバ上で行います

DBの作成


PostgreSQLへユーザを登録し、DBの操作が出来るようにします
  1. 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

  2. 1で作成したPostgreSQLユーザでログインしDBの作成


    $ createdb yubin←alkでログインし、データベース yubin を作成する
    CREATE DATABASE

  3. データの登録
    作成した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にアクセス権限を与えています。


各種ファイルのダウンロード


ここで使用した各種ファイルは下記からダウンロードできます。

ダウンロードページ