xyk blog

最近は iOS 開発の記事が多めです。

MySQLに街区レベル位置参照情報のCSVデータをインポートする

mysql

環境
Mac
MySQL Server version: 5.7.13

位置参照情報ダウンロードサービス
http://nlftp.mlit.go.jp/isj/

今回はこちらから東京都の大字・町丁目レベルのデータをダウンロードする。
13000-09.0b.zipというファイルがダウンロードされる。
これを解凍すると13_2015.csvというCSVファイルがあるのでこのデータをMySQLにインポートする。

まず先に文字コードをShift_JISからUTF-8に変換しておく。
nkfコマンドを使おうと思ったがMacには入っていなかったのでhomebrewでインストールした。

$ brew install nkf

UTF-8に変換

$ nkf -w 13_2015.csv > 13_2015_utf8.csv

中身を確認してみる。

$ head 13_2015_utf8.csv
"都道府県コード","都道府県名","市区町村コード","市区町村名","大字町丁目コード","大字町丁目名","緯度","経度","原典資料コード","大字・字・丁目区分コード"
"13","東京都","13101","千代田区","131010001001","一ツ橋一丁目","35.691634","139.756685","1","3"
"13","東京都","13101","千代田区","131010001002","一ツ橋二丁目","35.692947","139.757320","1","3"
"13","東京都","13101","千代田区","131010002000","一番町","35.687723","139.739668","1","1"
"13","東京都","13101","千代田区","131010003001","永田町一丁目","35.676328","139.745749","1","3"
"13","東京都","13101","千代田区","131010003002","永田町二丁目","35.675705","139.740497","1","3"
"13","東京都","13101","千代田区","131010004001","猿楽町一丁目","35.698471","139.759949","1","3"
"13","東京都","13101","千代田区","131010004002","猿楽町二丁目","35.700021","139.758377","1","3"
"13","東京都","13101","千代田区","131010005001","霞が関一丁目","35.674720","139.753419","1","3"
"13","東京都","13101","千代田区","131010005002","霞が関二丁目","35.675706","139.750734","1","3"

10列の項目があるが今回は使いたいのは名称と緯度経度のみなのでそれだけ入れるテーブルを用意する。
名称はすべて連結しnameカラムへ、緯度経度はPointにしてgeometry型のlatlonカラムに突っ込む。

-- MySQLにログイン
$ mysql -uroot

-- 適当なDBを作成
mysql> create database geo;
mysql> use geo;

-- テーブル作成
mysql> CREATE TABLE `spots` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `latlon` geometry NOT NULL,
  PRIMARY KEY (`id`),
  SPATIAL KEY `index_spots_on_latlon` (`latlon`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

※注 MySQL5.7.5以降でInnoDBでもSPATIALインデックスが使えるようになった。

そして、LOAD DATA INFILEコマンドでCSVデータをインポートする。

LOAD DATA INFILE '/path/to/13_2015_utf8.csv'
REPLACE INTO TABLE
geo.spots
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n' STARTING BY ''
IGNORE 1 LINES
(@dummy, @prefecture, @dummy, @city, @dummy, @area, @lat, @lon, @dummy, @dummy)
SET
name = CONCAT(@prefecture, @city, @area), 
latlon = ST_GeomFromText(CONCAT('POINT(', @lon, ' ', @lat, ')'));

Query OK, 5666 rows affected (0.20 sec)
Records: 5666  Deleted: 0  Skipped: 0  Warnings: 0

データを確認してみる。

mysql> SELECT id, name, ST_AsText(latlon) FROM spots limit 10;

+----+-----------------------------------------+-----------------------------+
| id | name                                    | ST_AsText(latlon)           |
+----+-----------------------------------------+-----------------------------+
|  1 | 東京都千代田区一ツ橋一丁目              | POINT(139.756685 35.691634) |
|  2 | 東京都千代田区一ツ橋二丁目              | POINT(139.75732 35.692947)  |
|  3 | 東京都千代田区一番町                    | POINT(139.739668 35.687723) |
|  4 | 東京都千代田区永田町一丁目              | POINT(139.745749 35.676328) |
|  5 | 東京都千代田区永田町二丁目              | POINT(139.740497 35.675705) |
|  6 | 東京都千代田区猿楽町一丁目              | POINT(139.759949 35.698471) |
|  7 | 東京都千代田区猿楽町二丁目              | POINT(139.758377 35.700021) |
|  8 | 東京都千代田区霞が関一丁目              | POINT(139.753419 35.67472)  |
|  9 | 東京都千代田区霞が関二丁目              | POINT(139.750734 35.675706) |
| 10 | 東京都千代田区霞が関三丁目              | POINT(139.748265 35.671608) |
+----+-----------------------------------------+-----------------------------+
10 rows in set (0.00 sec)

※注 STプレフィックスが付く空間用関数はMySQL5.6以降、追加されていっているもの。

CSVデータの中身は何もいじらずにサクッとできた。LOAD DATA INFILE 便利！

参考:
MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.2.6 LOAD DATA INFILE 構文
https://dev.mysql.com/doc/refman/5.6/ja/load-data.html