郵便局データを正規化してみた

IT
2025.04.18

以前ご紹介した記事では、全国の都道府県に対応した郵便局データを活用し、郵便番号から市区町村を取得するプログラムを作成しました。

今回は、郵便局の住所データを使ってデータベースの正規化にチャレンジしていきます。

作業の流れは、以下のステップで進めます：

  1. ブラウザから郵便データをダウンロード
  2. MySQLで対応するテーブルを作成
  3. ダウンロードしたデータをテーブルに取り込む

使用している環境は以下の通りです：

  • MySQL（Ubuntuサーバー上で稼働）
  • Python3（ローカルで使用）
  • macOS（ローカルマシン）

前提として、

  • データベース（post）はすでに作成済み
  • ターミナルからUbuntuサーバーへはSSHで接続可能な状態で

よろしくお願いいたします。

郵便局データの取得

まず、ブラウザでこちらのページを開き、csvファイルを取得します。

公式のCSVは、以下のようなカラム順になっています。

Noカラム名（便宜的な命名）内容
1jis_code全国地方公共団体コード（JIS X0401、X0402）
2old_zip_code（旧）郵便番号（5桁）
3zip_code郵便番号（7桁）
4pref_kana都道府県名カナ
5city_kana市区町村名カナ
6town_kana町域名カナ
7pref都道府県名
8city市区町村名
9town町域名
10flag1一町域が二以上の郵便番号を持つ場合の表示（0:該当せず, 1:該当）
11flag2小字毎に番地が起番されている町域の表示（同上）
12flag3丁目を有する町域か（同上）
13flag4一つの郵便番号で二以上の町域を表すか（同上）
14update_status更新の表示（0:変更なし, 1:変更あり, 2:廃止）
15update_reason変更理由（0:変更なし, 1:市政・区政・町政・分区・政令指定都市施行, 2:住居表示, 3:郵便区調整等）

テーブルを作ろう

先ほどダウンロードしたcsvファイルのすべてのデータを持つテーブル(テーブル名：m_zip)を用意します。

m_zipテーブル：

CREATE TABLE m_zip (
    id INT AUTO_INCREMENT PRIMARY KEY,
    jis_code VARCHAR(5),           -- 全国地方公共団体コード
    old_zip_code VARCHAR(5),       -- 旧郵便番号（5桁）
    zip_code VARCHAR(7),           -- 郵便番号（7桁）
    pref_kana VARCHAR(100),        -- 都道府県名カナ
    city_kana VARCHAR(100),        -- 市区町村名カナ
    town_kana VARCHAR(100),        -- 町域名カナ
    pref VARCHAR(100),             -- 都道府県名
    city VARCHAR(100),             -- 市区町村名
    town VARCHAR(100),             -- 町域名
    flag1 TINYINT,                 -- １つの町域が2つ以上の郵便番号を持つか
    flag2 TINYINT,                 -- 小字毎に番地が起番されているか
    flag3 TINYINT,                 -- 丁目を有する町域か
    flag4 TINYINT,                 -- 一つの郵便番号で複数の町域を表すか
    update_status TINYINT,         -- 更新の表示（0:変更なしなど）
    update_reason TINYINT          -- 変更理由（0:変更なしなど）
) DEFAULT CHARSET=utf8mb4;

続いて、都道府県データを格納するテーブル(テーブル名:prefectures)を作ります。

都道府県テーブル：

CREATE TABLE prefectures (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  kana VARCHAR(100) NOT NULL,
  name VARCHAR(100) NOT NULL
);

同様に市区テーブル（テーブル名:cities)と町村テーブル(テーブル名:area)を作ります。

市区テーブル：

CREATE TABLE cities (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  prefecture_id INT NOT NULL,
  kana VARCHAR(100) NOT NULL,
  name VARCHAR(100) NOT NULL,
  FOREIGN KEY (prefecture_id) REFERENCES prefectures(id)
);

町村テーブル：

CREATE TABLE areas (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  city_id INT NOT NULL,
  kana VARCHAR(100) NOT NULL,
  name VARCHAR(255),
  FOREIGN KEY (city_id) REFERENCES cities(id)
);

そして、最後にflag1から6のデータを格納するテーブル(テーブル名：zip_code)を作ります。

詳細テーブル：

CREATE TABLE zip_code (
  code CHAR(7) NOT NULL PRIMARY KEY,
  area_id INT NOT NULL,
  is_multiple_zip TINYINT(1),
  has_banchi TINYINT(1),
  has_chome TINYINT(1),
  is_multiple_area TINYINT(1),
  update_status TINYINT,
  change_reason TINYINT,
  FOREIGN KEY (area_id) REFERENCES areas(id)
);

郵便局データを各テーブルにインポートしよう

ダウンロードしたcsvファイルをm_zipに取り込みます。

MySQLにはデータをダウンロードできる場所が制限されてることが多いみたいで、

SHOW VARIABLES LIKE 'secure_file_priv';

で出てきたディレクトリ（例：/var/lib/mysql-files/）に CSVファイルを置いてから実行します。

私も最初は、CSVファイルがローカルの「ダウンロード」フォルダにあったので、
Ubuntu上のMySQLからアクセスできる場所に移動させました。

（もしファイルを移動しようとしたときに権限エラーが出た場合は、
sudo su - で管理者権限に切り替えてから、改めて mv コマンドを使えばOKです）

LOAD DATA INFILE '/var/lib/mysql-files/KEN_ALL.CSV'
INTO TABLE m_zip
CHARACTER SET sjis
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 0 LINES;

こんな感じのテーブルが作成されました。

続いて、m_zip テーブルから都道府県のカナ (pref_kana)と都道府県名 (pref)を使って、

prefectures テーブルに追加していきます。

INSERT INTO prefectures (kana, name)
SELECT DISTINCT pref_kana, pref
FROM m_zip
ORDER BY pref_kana;

完成したテーブルがこちらです。

続いて、m_zip テーブルから市区町村の情報を抽出します。

SELECT DISTINCT
  pref,
  city,
  pref_kana,
  city_kana
FROM m_zip;

このクエリで、都道府県名（pref）、市区町村名（city）、およびそれぞれのフリガナ（pref_kana、city_kana）を取得します。

続いて、抽出したデータを、prefectures テーブルとマッピングして、

各市区町村がどの都道府県に属するかを特定します。

SELECT
  p.id AS prefecture_id,
  mz.city_kana,
  mz.city
FROM
  m_zip mz
JOIN
  prefectures p
ON
  mz.pref = p.name
GROUP BY
  p.id, mz.city_kana, mz.city;

マッピングが完了したら、cities テーブルにデータを挿入します。

INSERT INTO cities (prefecture_id, kana, name)
SELECT
  p.id AS prefecture_id,
  mz.city_kana,
  mz.city
FROM
  m_zip mz
JOIN
  prefectures p
ON
  mz.pref = p.name
GROUP BY
  p.id, mz.city_kana, mz.city;

このクエリで、cities テーブルに市区町村の情報が追加されます。

続いて、areasテーブルですが、city_idが外部キーなので

cities テーブルと紐づけてデータ取得します。

SELECT
  c.id AS city_id,
  mz.town_kana AS kana,
  mz.town AS name
FROM
  m_zip mz
JOIN
  prefectures p ON mz.pref = p.name
JOIN
  cities c ON mz.city = c.name AND c.prefecture_id = p.id
WHERE
  mz.town != '以下に掲載がない場合'
GROUP BY
  c.id, mz.town_kana, mz.town;

こんな感じになりました。

次は、データをareasテーブルにインポートします。

INSERT INTO areas (city_id, kana, name)
SELECT
  c.id AS city_id,
  mz.town_kana,
  mz.town
FROM
  m_zip mz
JOIN
  prefectures p ON mz.pref = p.name
JOIN
  cities c ON mz.city = c.name AND c.prefecture_id = p.id
WHERE
  mz.town != '以下に掲載がない場合'
GROUP BY
  c.id, mz.town_kana, mz.town;

こうなりました。

画像ではcity_idが全て１となっていますが、もっと下の行を確認したら、ちゃんと増えてました。

最後にzip_codeにインポートしていきます。

【ポイント2倍】BenQ AQCOLOR PD2700Q 27インチ 16:9 対応 [9H高硬度] ブルーライトカットフィルム　超透明　光沢　ノートパソコン液晶保護フィルム 指紋防止 気泡防止 抗菌 画面保護 PC保護シート

価格:3880円
(2025/4/22 11:32時点)
感想(0件)

INSERT INTO zip_code (
  code,
  area_id,
  is_multiple_zip,
  has_banchi,
  has_chome,
  is_multiple_area,
  update_status,
  change_reason
)
SELECT
  mz.zip AS code,
  a.id AS area_id,
  mz.flg1 AS is_multiple_zip,  -- flag1 -> is_multiple_zip
  mz.flg2 AS has_banchi,       -- flag2 -> has_banchi
  mz.flg3 AS has_chome,        -- flag3 -> has_chome
  mz.flg4 AS is_multiple_area, -- flag4 -> is_multiple_area
  mz.flg5 AS update_status,    -- flag5 -> update_status
  mz.flg6 AS change_reason     -- flag6 -> change_reason
FROM
  m_zip mz
JOIN prefectures p ON mz.pref = p.name
JOIN cities c ON mz.city = c.name AND c.prefecture_id = p.id
JOIN areas a ON mz.town = a.name AND a.city_id = c.id;

すると、以下のエラーが出ました。

ERROR 1062 (23000): Duplicate entry '0680546' for key 'zip_code.PRIMARY'

zip_code テーブルの code カラム（郵便番号）が主キー（PRIMARY KEY）として設定されているため、同じ郵便番号が複数回挿入されることができないことを意味しています。

このエラーを解決するために、重複する郵便番号を上書きしました。

INSERT INTO zip_code (
  code,
  area_id,
  is_multiple_zip,
  has_banchi,
  has_chome,
  is_multiple_area,
  update_status,
  change_reason
)
SELECT
  mz.zip AS code,
  a.id AS area_id,
  mz.flg1 AS is_multiple_zip,
  mz.flg2 AS has_banchi,
  mz.flg3 AS has_chome,
  mz.flg4 AS is_multiple_area,
  mz.flg5 AS update_status,
  mz.flg6 AS change_reason
FROM
  m_zip mz
JOIN prefectures p ON mz.pref = p.name
JOIN cities c ON mz.city = c.name AND c.prefecture_id = p.id
JOIN areas a ON mz.town = a.name AND a.city_id = c.id
ON DUPLICATE KEY UPDATE
  area_id = VALUES(area_id),
  is_multiple_zip = VALUES(is_multiple_zip),
  has_banchi = VALUES(has_banchi),
  has_chome = VALUES(has_chome),
  is_multiple_area = VALUES(is_multiple_area),
  update_status = VALUES(update_status),
  change_reason = VALUES(change_reason);

無事インポートできました。

まとめ

今回は、郵便局の住所データを使ってデータベースの正規化を行いました。

次回は、今日作成したテーブルを使って、郵便番号を入力すると都道府県や市区町村が表示されるプログラムを作っていきます。

最後まで読んでいただき、ありがとうございました！

コメント

タイトルとURLをコピーしました