--
-- 郵便住所テーブルの作成用SQL  2010-02-01
--

-- 対象 MySQL 5.1 UTF-8

-- 準備
-- 1.住所の郵便番号、事業所の個別郵便番号のデータを用意します。
--   /home/user/postal/ken_all.csv
--   /home/user/postal/jigyosyo.csv
--   コマンドの例
--   $ cd /home/user/postal
--   $ wget http://www.post.japanpost.jp/zipcode/dl/kogaki/lzh/ken_all.lzh
--   $ lha e ken_all.lzh
--   $ wget http://www.post.japanpost.jp/zipcode/dl/jigyosyo/lzh/jigyosyo.lzh
--   $ lha e jigyosyo.lzh
-- 2.このファイル(postaladdr.sql)をFTP等で /home/user/postal に置きます。
-- 3.このファイル(postaladdr.sql)を編集します。
--   SQLのLOAD DATA ... INFILE '/home/user/postal/...' のパスを２箇所修正します。

-- 実行
--   $ mysql -v -v -u user -p -h host --local-infile=1 dbname < postaladdr.sql
--   Enter password:
--   ※ user, host, dbname などは適宜変更してください。
--   ※オプションに --local-infile=1 を指定することで
--      mysqlの LOAD DATA INFILE の実行時のエラーを回避することができます。
--      ERROR 1148 (42000): The used command is not allowed with this MySQL version

-- 作成
--

-- DB操作の文字コードをUTF-8に設定する
SET NAMES utf8;


-- 郵便住所用テーブル作成
CREATE TABLE IF NOT EXISTS postaladdr (
 code varchar(7) DEFAULT NULL,
 pref text,
 addr1 text,
 addr2 text,
 addr3 text,
 addr1_kana text,
 addr2_kana text
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


-- 作業用テーブル（住所の郵便番号）の作成
CREATE TEMPORARY TABLE postaladdr_temp (
 f1 varchar(10) DEFAULT NULL,
 code5 varchar(5) DEFAULT NULL,
 code varchar(7) DEFAULT NULL,
 pref_kana text DEFAULT NULL,
 addr1_kana text DEFAULT NULL,
 addr2_kana text DEFAULT NULL,
 pref text DEFAULT NULL,
 addr1 text DEFAULT NULL,
 addr2 text DEFAULT NULL,
 f10 integer DEFAULT NULL,
 f11 integer DEFAULT NULL,
 f12 integer DEFAULT NULL,
 f13 integer DEFAULT NULL,
 f14 integer DEFAULT NULL,
 f15 integer DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- 作業用テーブル（事業所の個別郵便番号）の作成
CREATE TEMPORARY TABLE postaladdr_jigyosyo_temp (
 f1 varchar(10) DEFAULT NULL,
 name_kana text DEFAULT NULL,
 name  text DEFAULT NULL,
 pref text DEFAULT NULL,
 addr1 text DEFAULT NULL,
 addr2 text DEFAULT NULL,
 addr3 text DEFAULT NULL,
 code varchar(7) DEFAULT NULL,
 code5 varchar(5) DEFAULT NULL,
 f10 text DEFAULT NULL,
 f11 integer DEFAULT NULL,
 f12 integer DEFAULT NULL,
 f13 integer DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


-- 住所の郵便番号データの読み込み
LOAD DATA LOCAL INFILE '/home/user/postal/ken_all.csv' INTO TABLE postaladdr_temp
 CHARACTER SET 'sjis' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

-- 事業所の個別郵便番号データの読み込み
LOAD DATA LOCAL INFILE '/home/user/postal/jigyosyo.csv' INTO TABLE postaladdr_jigyosyo_temp
 CHARACTER SET 'sjis' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';


-- データ補正

-- 町域名の補正
-- （、）（～）（その他）（を除く）（不明）（丁目）（番地）（大字）（以上）（以降）を除く
UPDATE postaladdr_temp SET addr2=substring(addr2, 1, locate('（', addr2)-1),
 addr2_kana=substring(addr2_kana, 1, locate('(', addr2_kana)-1)
 WHERE addr2 like '%（%、%' OR addr2 like '%（%～%' OR addr2 like '%（%〜%'
 OR addr2 like '%（%その他%' OR addr2 like '%（%を除く%' OR addr2 like '%（%不明）%'
 OR addr2 like '%（丁目%' OR addr2 like '%（番地%' OR addr2 like '%（大字%'
 OR addr2 like '%（%以上）' OR addr2 like '%（%以降）';

-- 、 ～ ）を含む町域名を空白にする
UPDATE postaladdr_temp SET addr2='', addr2_kana=''
 WHERE addr2 like '%、%' OR addr2 like '%～%' OR addr2 like '%〜%'
 OR (addr2 not like '%（%' AND addr2 like '%）%');

-- 以下の場合は町域名を空白にする
UPDATE postaladdr_temp SET addr2='', addr2_kana=''
 WHERE addr2='以下に掲載がない場合' OR addr2 like '%の次に番地がくる場合%';

-- 選択肢が30以上ある地域には共通の町域名を用意する
INSERT INTO postaladdr_temp (code, pref, addr1, addr2, addr1_kana, addr2_kana)
 VALUES ('4520961', '愛知県', '清須市', '春日' ,'ｷﾖｽｼ', 'ﾊﾙﾋ');
INSERT INTO postaladdr_temp (code, pref, addr1, addr2, addr1_kana, addr2_kana)
 VALUES ('7793405', '徳島県', '吉野川市', '山川町' ,'ﾖｼﾉｶﾞﾜｼ', 'ﾔﾏｶﾜﾁｮｳ');
INSERT INTO postaladdr_temp (code, pref, addr1, addr2, addr1_kana, addr2_kana)
 VALUES ('0294205', '岩手県', '奥州市', '前沢区' ,'ｵｳｼｭｳｼ', 'ﾏｴｻﾜｸ');
INSERT INTO postaladdr_temp (code, pref, addr1, addr2, addr1_kana, addr2_kana)
 VALUES ('9892351', '宮城県', '亘理郡亘理町', '' ,'ﾜﾀﾘｸﾞﾝﾜﾀﾘﾁｮｳ', '');
INSERT INTO postaladdr_temp (code, pref, addr1, addr2, addr1_kana, addr2_kana)
 VALUES ('9896712', '宮城県', '大崎市', '鳴子温泉' ,'ｵｵｻｷｼ', 'ﾅﾙｺｵﾝｾﾝ');


-- 郵便住所データの挿入
-- 住所の郵便番号の重複するレコードを１件にまとめる
INSERT INTO postaladdr (code, pref, addr1, addr2, addr3, addr1_kana, addr2_kana)
 SELECT code, pref, addr1, addr2, '' AS addr3, min(addr1_kana) AS addr1_kana, min(addr2_kana) AS addr2_kana
 FROM postaladdr_temp 
 GROUP BY code, pref, addr1, addr2
 ORDER BY code, pref, addr1, addr2;

-- 事業所の個別郵便番号の重複するレコードを１件にまとめる
INSERT INTO postaladdr (code, pref, addr1, addr2, addr3, addr1_kana, addr2_kana)
 SELECT code, pref, addr1, addr2, addr3, '' AS addr1_kana, '' AS addr2_kana
 FROM postaladdr_jigyosyo_temp 
 GROUP BY code, pref, addr1, addr2, addr3
 ORDER BY code, pref, addr1, addr2, addr3;


-- 索引の作成
CREATE INDEX addr_code_index ON postaladdr(code);


-- 作業用テーブル（住所の郵便番号）の削除
DROP TEMPORARY TABLE postaladdr_temp;

-- 作業用テーブル（事業所の個別郵便番号）の削除
DROP TEMPORARY TABLE postaladdr_jigyosyo_temp;