untitled .engineer

技術系のブログ(仮)

MySQL8.0でGIS機能を試すためのデータ準備


目次


本エントリの概要

MySQL8.0で新しくなったGIS機能をいじってみたいと思い、まずはサンプルデータを作ろうと考えたときにやったこと。
地方公共団体の役所の位置情報をMySQLのテーブルに取り込んでみた記録です。

MySQL8.0の設定

LOAD DATA INFILEをするので、設定変更が必要。
my.ini/my.cnfからsecure-file-priv="*****"の部分を探します。
secure-file-priv=""に書き換えておけばどこからでもインポートできるようになります。
(本番サーバーでは適切に設定してください。)

元データダウンロード

地方公共団体の位置データを以下のサイトからダウンロードする。
http://www.amano-tec.com/data/localgovernments.html

地方公共団体のデータを使うメリット

  • 無償提供されている
  • 地図上の場所をイメージしやすく、jiscodeというユニークIDが降られていて、検証しやすい
  • そこそこのデータ量(2000件弱)
    • ※多くはないのでパフォーマンスの検証には向かない

テーブル作成/インポート

gisスキーマを作ってその中で使うことにします。

CREATE DATABASE gis;
USE gis;

ダウンロードしたデータをそのまま取り込めるようなテーブルを作ります。
(NULL, NOT NULLの区別はあまり深い意味はありません)

CREATE TABLE local_governments (
  `jiscode` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `namekana` varchar(100) NOT NULL,
  `building` varchar(100) DEFAULT NULL,
  `zipcode` varchar(10) DEFAULT NULL,
  `address` varchar(100) DEFAULT NULL,
  `tel` varchar(20) DEFAULT NULL,
  `source` varchar(20) DEFAULT NULL,
  `lat` varchar(20) DEFAULT NULL,
  `lon` varchar(20) DEFAULT NULL,
  `note` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`jiscode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ここに先ほどのCSVファイルをそのままインポートします。

LOAD DATA INFILE '/path/to/h2905puboffice_utf8.csv' INTO TABLE local_governments
FIELDS TERMINATED BY ',' ENCLOSED BY '"';

GEOMETRY型の列を持つテーブルを作る

私の場合は実際に地点をGEOMETRY型にして保持したテーブルが必要だったので、以下のように作ります。

CREATE TABLE portals (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `address` varchar(100) DEFAULT NULL,
  `lat` varchar(20) DEFAULT NULL,
  `lon` varchar(20) DEFAULT NULL,
  `geom` GEOMETRY NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  • geom列には座標情報のPOINT型のデータを入れます。POINT型で宣言してもいいと思います。
  • NULL, NOT NULLの区別はあまり深い意味はありません。

ここに先ほどの地方公共団体のデータを入れます。 以下ではWGS84 SRID4326 JGD2000 SRID 4612の地理座標系の型でインポートしています。

INSERT INTO portals
(SELECT
  `jiscode`,
  `building`,
  `address`,
  `lat`,
  `lon`,
  ST_GeomFromText(CONCAT('POINT(',`lat`,' ',`lon`,')'), 4612)
FROM local_governments);
  • ST_GeomFromText(CONCAT('POINT(',`lat`,' ',`lon`,')'), 4612) は内部的に ST_GeomFromText('POINT(35.6936421 139.7033475)', 4612) のようになります。

これでお手軽に2000件弱の位置情報が作れました。
次回以降このデータをこねくり回していく予定です。

余談

  • もっと細かいデータ(大量のデータ)が欲しい場合は以下のデータを使えばよいと思います。
  • 例えば市区町村の街区までのデータなら東京都内だけでも27万レコードあります。
  • 国土交通省 位置参照情報ダウンロードサービス http://nlftp.mlit.go.jp/isj/index.html

SRIDの間違いを修正しました 2018/09/06追記

当初アマノ技研さんからダウンロードしたデータをしれっとSRID=4326でインポートしていましたが、ダウンロードしたファイルに含まれるreadme.txtに

地方公共団体の所在地情報に国土交通省が提供する国土数値情報の公共施設データとを突き合わせ,日本測地系2000(世界測地系)の緯度・経度情報を付与しています。

とあることに後で気づきました。
これはつまり WGS84 ではなく JGD2000 であるべきでした。その地理座標系のSRIDである 4612 が正しいと考えます。

ちょっとかじったところによると 日本測地系2000(JGD2000)と世界測地系(WGS84)の差は数cm程度である とのことなので あまり問題がないかもしれませんが、念のため後続のエントリも修正いたします。ご容赦願います。
修正方法:本文中は訂正とわかるように追記、コード中は書き換えます。