[PostgreSQL] 郵便番号辞書をデータベースに投入する手順 Kawa.netブログ（川崎有亮）/ウェブリブログ

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

<< 作成日時： 2006/10/20 21:49 >>

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 ~ '[０-９地]+階';
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,'第?[０-９]+地割.*','')
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 を使わなくても済む場合も増えてきました。

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

<< 前記事（2006/10/20）

トップへ

後記事（2006/10/20）>>

月別リンク

トラックバック（1件）

タイトル（本文）	ブログ名／日時
日本全国の郵便番号＆住所データDBを作る方法「郵便番号データダウンロード - 日本郵便」でオープンソースとして配布されている「郵便番号＆住所データ」（CSVファイル：全国版は12万行で12MB）を使えば、誰でも簡単に日本全国の郵... ...続きを見る	RAILS PRESS 2008/02/26 02:58

コメント（0件）

内容	ニックネーム／日時

<< 前記事（2006/10/20）

トップへ

後記事（2006/10/20）>>

Kawa.netブログ（川崎有亮）