PostgreSQL 10.4で郵便局の郵便番号データをテーブルにインポートする
カテゴリ：OSSセットアップ | ソフトウェア：PostgreSQL | タグ：
最終更新日：2021/01/01 | 公開日：2018/11/04
目次
概要
郵便局が公式サイトで公開している「郵便番号データ」をPostgreSQLのテーブルにインポートします。 ファイルは可変長のCSVファイル（zipで圧縮されている）としてダウンロードできるので、編集せずにそのまま利用します。 ファイルは文字コードがSHIFT JISで、改行コードがCRLFとなっていました。
サーバ環境は「PostgreSQL 10.4をRHEL 7.5(AWSのEC2)へインストールし、PITR可能な初期設定を行う」でAWS上に構築したサーバを使って試しました。
構成
想定環境
サーバのスペックは以下のとおりです。
■サーバスペック
| 項目 | 内容 |
|---|---|
| インスタンスタイプ | t2.micro |
| vCPU | 1 |
| メモリ | 1GiB |
| ディスク | SSD 10GiB |
| リージョン | アジアパシフィック (東京) |
■OS情報
| OSユーザ名 | pguser01 |
|---|---|
| DDLファイルパス | /data/sql/ |
| CSVファイルパス | /data/csv/ |
■DB情報
| DBユーザ名 | pguser01 |
|---|---|
| データベース名 | addressbook |
| スキーマ名 | pguser01 |
| テーブル名 | zipcode |
■郵便番号データ
| 1.全国地方公共団体コード | （JIS X0401、X0402）半角数字 |
|---|---|
| 2.（旧）郵便番号 | （5桁）半角数字 |
| 3.郵便番号 | （7桁）半角数字 |
| 4.都道府県名 | 半角カタカナ（コード順に掲載） |
| 5.市区町村名 | 半角カタカナ（コード順に掲載） |
| 6.町域名 | 半角カタカナ（五十音順に掲載） |
| 7.都道府県名 | 漢字（コード順に掲載） |
| 8.市区町村名 | 漢字（コード順に掲載） |
| 9.町域名 | 漢字（五十音順に掲載） |
| 10.一町域が二以上の郵便番号で表される場合の表示 | 「1」は該当 「0」は該当せず |
| 11.小字毎に番地が起番されている町域の表示 | 「1」は該当 「0」は該当せず |
| 12.丁目を有する町域の場合の表示 | 「1」は該当 「0」は該当せず |
| 13.一つの郵便番号で二以上の町域を表す場合の表示 | 「1」は該当 「0」は該当せず |
| 14.更新の表示 | 「0」は変更なし 「1」は変更あり 「2」廃止（廃止データのみ使用） |
| 15.変更理由 | 「0」は変更なし 「1」市政・区政・町政・分区・政令指定都市施行 「2」住居表示の実施 「3」区画整理 「4」郵便区調整等 「5」訂正 「6」廃止（廃止データのみ使用） |
サーバ構成
OSバージョン
Red Hat Enterprise Linux 7.5 x86_64
ソフトウェア・パッケージ一覧
- postgresql10-10.4-1PGDG.rhel7.x86_64.rpm
- postgresql10-libs-10.4-1PGDG.rhel7.x86_64.rpm
- postgresql10-server-10.4-1PGDG.rhel7.x86_64.rpm
環境構築
設定
テーブル作成用のDDL作成
郵便番号データをインポートする先のzipcodeテーブルを作成するためのDDLファイルを作成します。
# su - pguser01 $ cd /data/sql/ $ vi zipcode.ddl
ファイル名：/data/ddl/zipcode.ddl
※新規作成※
CREATE TABLE zipcode (
jiscode VARCHAR(5),
zipcode_old CHAR(5),
zipcode CHAR(7),
prefecture_kana VARCHAR(15),
city_kana VARCHAR(31),
street_kana VARCHAR(255),
prefecture VARCHAR(15),
city VARCHAR(31),
street VARCHAR(255),
flag1 SMALLINT,
flag2 SMALLINT,
flag3 SMALLINT,
flag4 SMALLINT,
flag5 SMALLINT,
flag6 SMALLINT
);
郵便番号データのダウンロード
郵便局のサイトから郵便番号データをダウンロードします。
$ cd /data/csv/ $ wget https://www.post.japanpost.jp/zipcode/dl/kogaki/zip/ken_all.zip --2018-11-04 06:50:22-- https://www.post.japanpost.jp/zipcode/dl/kogaki/zip/ken_all.zip Resolving www.post.japanpost.jp (www.post.japanpost.jp)... 43.253.235.42 Connecting to www.post.japanpost.jp (www.post.japanpost.jp)|43.253.235.42|:443... connected. HTTP request sent, awaiting response... 200 OK Length: 1687323 (1.6M) [application/zip] Saving to: ‘ken_all.zip’ 100%[==================================================================================================>] 1,687,323 --.-K/s in 0.1s 2018-11-04 06:50:22 (11.0 MB/s) - ‘ken_all.zip’ saved [1687323/1687323] $ ls -l ken_all.zip -rw-r--r--. 1 pguser01 pggroup 1687323 Oct 31 07:59 ken_all.zip
ダウンロードした郵便番号データのzip解凍
郵便番号データはzip圧縮された形で配布されているため、解凍します。 zipを解凍して生成されたファイル（ken_all）はCSV形式のテキストファイルです。
$ gunzip -S .zip ken_all.zip $ ls -l total 12012 -rw-r--r--. 1 pguser01 pggroup 12296912 Oct 31 07:59 ken_all
データベースへ接続
データベースaddressbookにDBユーザpguser01で接続します。
$ psql -U pguser01 addressbook Password for user pguser01: ←pguser01のパスワードを入力 psql (10.4) Type "help" for help. addressbook=> \dt Did not find any relations. ←データベースaddressbookにはまだテーブルが存在しない addressbook=> show search_path; search_path ----------------- "$user", public ←ログインしているDBユーザ名(pguser01)と同名のスキーマにテーブルが作成される (1 row) addressbook=>
DDLを実行してテーブルを作成
先ほど作成したDDLを実行して、テーブルzipcodeを作成します。
addressbook=> \i /data/sql/zipcode.ddl
CREATE TABLE
addressbook=> \dt
List of relations
Schema | Name | Type | Owner
----------+---------+-------+----------
pguser01 | zipcode | table | pguser01
(1 row)
addressbook=> \d zipcode
Table "pguser01.zipcode"
Column | Type | Collation | Nullable | Default
-----------------+------------------------+-----------+----------+---------
jiscode | character varying(5) | | |
zipcode_old | character(5) | | |
zipcode | character(7) | | |
prefecture_kana | character varying(15) | | |
city_kana | character varying(31) | | |
street_kana | character varying(255) | | |
prefecture | character varying(15) | | |
city | character varying(31) | | |
street | character varying(255) | | |
flag1 | smallint | | |
flag2 | smallint | | |
flag3 | smallint | | |
flag4 | smallint | | |
flag5 | smallint | | |
flag6 | smallint | | |
addressbook=> select * from zipcode;
jiscode | zipcode_old | zipcode | prefecture_kana | city_kana | street_kana | prefecture | city | street | flag1 | flag2 | flag3 | flag4 |
flag5 | flag6
---------+-------------+---------+-----------------+-----------+-------------+------------+------+--------+-------+-------+-------+-------+-
------+-------
(0 rows)
addressbook=>
郵便番号データのインポート
CSV形式の郵便番号データをテーブルzipdoceにインポートします。 インポートにはメタコマンドの \copy を利用します。 インポート先のテーブル名には念のためスキーマ名を指定しています。 ファイルはCSV形式で、文字コードがSHIFT JISであることを指定します。 データベースクラスタの文字コードはUTF-8となっているため、CSVファイルの文字コードを明示的に指定することで、 インポート時にUTF-8に自動変換されます。
addressbook=> \copy pguser01.zipcode from '/data/csv/ken_all' with csv encoding 'shift_jis';
COPY 124249 ←CSVファイルの郵便番号データ124,249行すべてがインポートされた
インポートしたデータの確認
確認のために、インポートしたデータを確認します。 郵便番号が1670051のデータを検索すると、東京都杉並区荻窪であることが表示されます。
addressbook=> select * from zipcode where zipcode = '1670051'; jiscode | zipcode_old | zipcode | prefecture_kana | city_kana | street_kana | prefecture | city | street | flag1 | flag2 | flag3 | flag4 | flag5 | flag6 ---------+-------------+---------+-----------------+-----------+-------------+------------+--------+--------+-------+-------+-------+------- +-------+------- 13115 | 167 | 1670051 | ﾄｳｷｮｳﾄ | ｽｷﾞﾅﾐｸ | ｵｷﾞｸﾎﾞ | 東京都 | 杉並区 | 荻窪 | 0 | 0 | 1 | 0 | 0 | 0 (1 row) addressbook=> \q $ exit logout