プログラマでありたい

おっさんになっても、プログラマでありつづけたい

位置参照情報データと郵便番号データのマッチング

技術メモ

　以前、郵便番号データに緯度経度を付加する手順を書きました。Google Mapsと郵便番号データを利用することで生成してたのですが、別のアプローチで作る方法がありました。ずばり位置参照情報データというのがあったので、それと郵便番号データをマッチングするという方法です。日曜大工でやってみました。
　ちなみに何故私が郵便番号データに拘るかというと、住所とのマッチングだと表記揺れの吸収が大変だからです。その点、郵便番号だと間違いなくキーとしてマッチングできます。ということで解析系のデータとして使う時に便利です。（マッチングデータに郵便番号があったとしたらですけど。）


ということで生成手順です。
位置参照情報ダウンロードサービス

　一括で解凍。unzipで一括で解凍したければ、アスタリスクをエスケープする必要があります。
nkfでUTF-8に変換したあと、ヘッダー行を削除して一つのファイルにまとめます。

$ unzip -o \*.zip
$ nkf -w --overwrite *.csv
$ sed -e "1d" *_*.csv > gaiku_infos.csv

　テーブルを作って、インポートします。
環境にもよりますが、結構な時間がかかります。
インデックスを検討した方が良いかもしれません。

DROP TABLE gaiku_infos;
CREATE TABLE gaiku_infos (
id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
pref_kanji varchar(36),
city_kanji varchar(128),
town_kanji varchar(128),
number varchar(128),
coordinate_no char(2),
coordinate_x double,
coordinate_y double,
longitude double,
latitude double,
disp_flg char(1),
represent_flg char(1),
history_flg1 char(1),
history_flg2 char(1)
)
DEFAULT CHARSET=utf8
;
CREATE INDEX idx_pref ON gaiku_infos (
pref_kanji
);
CREATE INDEX idx_city ON gaiku_infos (
city_kanji
);
CREATE INDEX idx_town ON gaiku_infos (
town_kanji
);
CREATE INDEX idx_address ON gaiku_infos (
pref_kanji,city_kanji,town_kanji
);

load data local infile '/tmp/geo/gaiku_infos.csv'
into table gaiku_infos
fields terminated by ',' enclosed by '"'
(
pref_kanji,
city_kanji,
town_kanji,
number,
coordinate_no,
coordinate_x,
coordinate_y,
longitude,
latitude,
disp_flg,
represent_flg,
history_flg1,
history_flg2
);


　次に郵便番号データを入れます。
ダウンロード元は、こちらです。

$ lha x ken_all.lzh
$ nkf -w --overwrite ken_all.csv 
$ sed -e "s/以下に掲載がない場合//g"ken_all.csv > /tmp/geo/post_codes.csv


テーブルの作成と、データのインポートを行います。
こちらは、街区情報に比べるとすぐに終わります。

drop table post_codes;
create table post_codes(
id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
jiscode char(5),
postcode_short varchar(5),
postcode char(7),
pref_kana varchar(200),
city_kana varchar(200),
town_kana varchar(200),
pref_kanji varchar(36),
city_kanji varchar(128),
town_kanji varchar(128),
flag1 smallint,
flag2 smallint,
flag3 smallint,
flag4 smallint,
flag5 smallint,
flag6 smallint
)
DEFAULT CHARSET=utf8
;

CREATE INDEX idx_postcode ON post_codes (
postcode
);
CREATE INDEX idx_pref ON post_codes (
pref_kanji
);
CREATE INDEX idx_city ON post_codes (
city_kanji
);
CREATE INDEX idx_town ON post_codes (
town_kanji
);
CREATE INDEX idx_address ON post_codes (
pref_kanji,city_kanji,town_kanji
);


load data local infile '/tmp/geo/post_codes.csv'
into table post_codes
fields terminated by ',' enclosed by '"'
(
jiscode,
postcode_short,
postcode,
pref_kana,
city_kana,
town_kana,
pref_kanji,
city_kanji,
town_kanji,
flag1,
flag2,
flag3,
flag4,
flag5,
flag6
);

　最後に郵便番号データと街区データの経度、緯度をマッチングさせれば完了です。
悩ましい点として、街区データなので番地ごとの経度、緯度まであります。
一方郵便番号データは、街までです。つまり一つの郵便番号に対して複数の経度、緯度が
マッチングします。ということで、ちょっと強引ですが街ごとの平均を使うことにします。
そのデータをgaiku_geocodesというテーブルに一回いれてからzip_geocodesというテーブルにインサートします。
(left outer joinで一度でやろうとすると凄まじくコストがかかるので)

CREATE TABLE gaiku_geocodes (
id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
pref_kanji varchar(36),
city_kanji varchar(128),
town_kanji varchar(128),
longitude double,
latitude double
)
DEFAULT CHARSET=utf8
;
CREATE INDEX idx_pref ON gaiku_geocodes (
pref_kanji
);
CREATE INDEX idx_city ON gaiku_geocodes (
city_kanji
);
CREATE INDEX idx_town ON gaiku_geocodes (
town_kanji
);
CREATE INDEX idx_address ON gaiku_geocodes (
pref_kanji,city_kanji,town_kanji
);

insert into gaiku_geocodes (pref_kanji,city_kanji,town_kanji,longitude,latitude)
select
pref_kanji,city_kanji,town_kanji,avg(longitude),avg(latitude)
from gaiku_infos
group by pref_kanji,city_kanji,town_kanji
;

CREATE TABLE zip_geocodes (
id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
zip_code CHAR(7),
longitude double,
latitude double
)
DEFAULT CHARSET=utf8
;
CREATE INDEX idx_zip_cd ON zip_geocodes (
zip_code
);



select
pref_kanji,city_kanji,town_kanji,avg(longitude),avg(latitude)
from gaiku_infos
group by pref_kanji,city_kanji,town_kanji
limit 5;

insert into zip_geocodes (zip_code,longitude,latitude)
select
postcode,longitude,latitude
from post_codes
left outer join gaiku_geocodes
on post_codes.pref_kanji = gaiku_geocodes.pref_kanji
and post_codes.city_kanji = gaiku_geocodes.city_kanji
and post_codes.town_kanji = gaiku_geocodes.town_kanji
where gaiku_geocodes.town_kanji is not null
;

上記でマッチングするのは、12万件中５万４千件程度。
郵便の元データの方を修正したら使い物になりそうですね。
こんなデータとか

北二条西（１〜１９丁目）