[PostgreSQL] 郵便番号辞書をデータベースに投入する手順

PostgreSQL 8.1 の COPY WITH CSV の機能を利用すると、CSV ファイルが読み書きできるので、
例えば、郵便番号辞書をデータベースに取り込むのもとても簡単になりますね。
従来は、CSV 変換の事前処理などが必要だったのが、全てSQLの操作で済むようになります。
郵便番号辞書を、PostgreSQL に取り込む手順をまとめておきます。

用意するもの:
    ・PostgreSQL 8.1
    ・ken_all.lzh から取り出した KEN_ALL.CSV

まず、CSV インポート用の仮テーブルを構築します。
CREATE TEMP TABLE tmp_zip (
    citycode    text,
    zip_old     text,
    zip7        text,
    prefkana    text,
    citykana    text,
    areakana    text,
    pref        text,
    city        text,
    area        text,
    flag1       int,
    flag2       int,
    flag3       int,
    flag4       int,
    flag5       int,
    flag6       int
);
CSVファイル(Shift_JISコード)を読み込ます。(数十秒)
\encoding SJIS
\copy tmp_zip from KEN_ALL.CSV with csv
\encoding UTF8
次に、郵便番号辞書特有の、不要なレコード・カラムを整理します。
UPDATE tmp_zip SET
    areakana = '',
    area = ''
WHERE area ~ '[0-9地]+階';
UPDATE tmp_zip SET
    areakana = regexp_replace(areakana,'\\(.*',''),
    area = regexp_replace(area,'(.*','')
WHERE areakana ~ '\\(' OR area ~ '(';
UPDATE tmp_zip SET
    areakana = regexp_replace(areakana,'[0-9].*',''),
    area = regexp_replace(area,'第?[0-9]+地割.*','')
WHERE area ~ '地割';
UPDATE tmp_zip SET
    areakana = '',
    area = ''
WHERE area ~ '(^以下に掲載がない場合|の次に番地がくる場合$|一円$|)$|、)';
準備が終わったら、本テーブルを作成します。
仮テーブルの状態ではレコード重複があって使いづらいし、余分なフラグもあるので、
実際に検索処理で利用するのは、こちらの本テーブルの方になります。
CREATE SEQUENCE tbl_zip_seq START 100000;
CREATE TABLE tbl_zip (
    zip_seq     int not null default nextval('tbl_zip_seq'),
    prefcode    int,
    citycode    int,
    zip7        text,
    prefkana    text,
    citykana    text,
    areakana    text,
    pref        text,
    city        text,
    area        text,
    PRIMARY KEY ( zip_seq )
);
仮テーブルから本テーブルにデータを移動します。(数十秒)
DISTINCT ON 句で、郵便番号+住所でユニークなレコードを抽出しています。
オマケで JIS の都道府県番号などもカラム抽出しておきます。
INSERT INTO tbl_zip
SELECT DISTINCT ON ( zip7, pref, city, area )
    nextval('tbl_zip_seq'),
    citycode::integer/1000,
    citycode::integer,
    zip7,
    prefkana, citykana, areakana,
    pref, city, area
FROM tmp_zip;
DROP TABLE tmp_zip;
最後に、郵便番号からのインデックスを張ったら、とりあえず完成です。
CREATE INDEX idx_zip_list_zip7 ON tbl_zip USING btree ( zip7 );
確認のため、東京都千代田区千代田の郵便番号から検索してみます。
EXPLAIN SELECT * FROM tbl_zip WHERE zip7 = '1000001';
SELECT zip7, pref, city, area FROM tbl_zip WHERE zip7 = '1000001';
以下のような結果が得られれば、成功です。
「Index Scan」なので、インデックスを利用した検索処理になっています。
                              QUERY PLAN
-----------------------------------------------------------------------
 Index Scan using idx_zip_list_zip7 on tbl_zip  (cost=0.00..3.01 rows=1 width=200)
   Index Cond: (zip7 = '1000001'::text)

  zip7   |  pref  |   city   |  area
---------+--------+----------+--------
 1000001 | 東京都 | 千代田区 | 千代田
もっとも、最近はメモリ容量も潤沢に使えるようになってきたので、
単純な郵便番号検索程度なら、AjaxZip 2.0 の JSON 形式データのように
わざわざ RDBMS を使わなくても済む場合も増えてきました。

とはいえ、今でも会員DBとかで、他のテーブルと JOIN して処理したい場合などは、
郵便番号辞書をDBにインポートすることになりますね。まだまだ必要そう。

ブログ気持玉

クリックして気持ちを伝えよう!

ログインしてクリックすれば、自分のブログへのリンクが付きます。

→ログインへ

なるほど(納得、参考になった、ヘー)
驚いた
面白い
ナイス
ガッツ(がんばれ!)
かわいい

気持玉数 : 0

この記事へのコメント

この記事へのトラックバック

  • 日本全国の郵便番号&住所データDBを作る方法

    Excerpt: 「郵便番号データダウンロード - 日本郵便」でオープンソースとして配布されている「郵便番号&住所データ」(CSVファイル:全国版は12万行で12MB)を使えば、誰でも簡単に日本全国の郵... Weblog: RAILS PRESS racked: 2008-02-26 02:58