OSS Fan ~OSSでLinuxサーバ構築~

作成日: 2018/11/04

OSSでLinuxサーバ構築

PostgreSQL 10.4で郵便局の郵便番号データをテーブルにインポートする

トップページOSSでLinuxサーバ構築 > PostgreSQL 10.4で郵便局の郵便番号データをテーブルにインポートする
このエントリーをはてなブックマークに追加

概要

 郵便局が公式サイトで公開している「郵便番号データ」をPostgreSQLのテーブルにインポートします。 ファイルは可変長のCSVファイル(zipで圧縮されている)としてダウンロードできるので、編集せずにそのまま利用します。 ファイルは文字コードがSHIFT JISで、改行コードがCRLFとなっていました。

 サーバ環境は「PostgreSQL 10.4をRHEL 7.5(AWSのEC2)へインストールし、PITR可能な初期設定を行う」でAWS上に構築したサーバを使って試しました。

構成

想定環境

 サーバのスペックは以下のとおりです。

■サーバスペック
項目内容
インスタンスタイプt2.micro
vCPU1
メモリ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

サーバ構築

設定

(1) テーブル作成用の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
);

(2) 郵便番号データのダウンロード

 郵便局のサイトから郵便番号データをダウンロードします。

$ 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

(3) ダウンロードした郵便番号データの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

(4) データベースへ接続

 データベース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=>

(5) 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=>

(6) 郵便番号データのインポート

 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行すべてがインポートされた

(7) インポートしたデータの確認

 確認のために、インポートしたデータを確認します。 郵便番号が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

参考にしたサイト