MySQL8.0の「地理情報」を理解しよう入門～いまからはじめるGIS

オープンソースカンファレンス2018 Tokyo/Fall MySQL 8.0 の新機能 「地理情報」 を理解しよう入門 ～いまからはじめるGIS～ ハッシュタグ： #mysql_jp #osc18tk 2018/10/28 日本MySQLユーザ会 坂井 恵 （@sakaik）

速報 MySQL 8.0.13 が出ました！(2018/10/24) そして、MySQL Cluster 8.0.13 dmr も！ 「なぜ MySQL 5.7 の次が 8.0？」の疑問が、いま明らかに！ 「６」は、昔、一旦出たバージョンだからスキップするよ → うんうん 「７」はMySQL Cluster で使っているからスキップするよ → ファ？ MySQLとCluster のバージョンを棲み分けしたいのではなく、すべての MySQLファミリーのソフトウェア群のバージョンをそろえたかったんだ！ ※感想には個人差があります。諸説あります。

フルバージョンは slideshare の資料を 見てください（ClubMySQL #4 の資料 が一番濃厚です） https://www.slideshare.net/sakaik

• https://www.slideshare.net/sakaik

データベースは楽しい！ • データベースは、現実世界の射影 • 手に取るように世界が見える • 数字、文字列、日付を中心とした情報 • 例：売上、売れ筋商品、顧客、学生の成績、etc • →そして、そこにいま「場所」が加わる GIS機能を楽しもう！

MySQLのGISの歴史 • MySQL 4.1.0 (2003): MyISAM に "Spatial Extensions" 実装 http://ftp.nchu.edu.tw/MySQL/doc/refman/4.1/en/spatial-extensions.html • MySQL 5.0.16 (2005): InnoDB にも実装 • MySQL 5.7.1 (2013): spatialデータ専用の型を実装（これまでは BLOBに保管） Boostライブラリを利用して InnoDBに再実装 • MySQL 8.0.0 (2016): 関数名をST_* 系に統一したり関数増やした り 地球が丸いことを知ったり、良い感じに 2018/04/19 8.0.11でGAに！ 2018/10/24 8.0.13 リリース ※MySQLでは、「GIS」ではなく「Spatial（空間情報）」というキーワード

• http://ftp.nchu.edu.tw/MySQL/doc/refman/4.1/en/spatial-extensions.html

MySQLのGIS機能習得への道 習得へのポイント 1. 平面での「座標」の扱い方の理解 2. 地球（球体）では違うぞ、と認識 3. どう違うのかの理解・表現方法の習得

平面での座標の扱い方 座標の基本： （ｘ，ｙ） 表現できる「形」： • 点： • 線： • 面： ひとつの点 線分の接続集合 線分の接続集合で囲まれたエリア （始点と終点が同じ位置となる）

型に関する知識 空間情報(Spatial) をあらわす３つの形態 •点 •線 •面 (POINT) (LINESTRING) そのほか、これらの集合である MULTIPOINT MULTILINESTRING MULTIPOLYGON GEOMETRYCOLLECTION もあります。 (POLYGON) ひっくるめて 幾何(形状） (GEOMETRY)

Spatialの ３つの表現 テキスト／バイナリ／MySQL内部バイナリ表現 • テキスト：Well known text (WKT) • バイナリ：Well known binary (WKB) • 内部バイナリ： WKB にSRID情報を追加した もの ※ここからは基本的に、WKT を使って話を進めます。

点・線・面のあらわし方 WKT (Well known text)： (まず覚えよう) コンマではないことに注意 • 点： POINT (3 5) • 線： LINESTRING (1 2, 5 3, 7 9, 3 12) • 面： POLYGON((1 1, 5 1, 5 8, 1 8, 1 1)) 必ず元の点にもどる POLYGON((1 1, 9 1, 9 9, 1 9, 1 1), (3 3, 5 3, 5 5, 3 5, 3 3)) 中をくり抜く 参考： • 複数点： MULTIPOINT (3 5, 1 2, 8 3) MULTIPOINT ((3 5), (1 2), (8 3)) • 複数線： MULTILINESTRING ((1 2, 5 3, 7 9, 3 12), (4 1, 3 3, -1 4)) • 複数面： MULTIPOLYGON (略)

WKT をMySQL内部表現に変換するには、 ST_GeomFromText() 関数を使用する mysql> SELECT ST_GeomFromText('POINT(3 5)') mygeo; +---------------------------+ | mygeo | +---------------------------+ | @ @ | +---------------------------+ mysql> SELECT HEX(ST_GeomFromText('POINT(3 5)')) mygeo; +----------------------------------------------------+ | mygeo | +----------------------------------------------------+ | 00000000010100000000000000000008400000000000001440 | +----------------------------------------------------+ mysql> SELECT ST_AsText(ST_GeomFromText('POINT(3 5)')) mygeo; +------------+ | mygeo | +------------+ | POINT(3 5) | +------------+

点・線・面のあらわし方 WKT → MySQLの内部バイナリ： • ST_GeomFromText() 関数を使用する。 MySQLの内部バイナリ → WKT： Geom の代わりに Point, LineString, Polygon などの関数が 個別にそれぞれあります • ST_AsText() 関数を使用する。 mysql> SELECT ST_AsText(ST_GeomFromText('LINESTRING (1 2, 5 3, +------------------------------+ | geom | +------------------------------+ | LINESTRING(1 2,5 3,7 9,3 12) | +------------------------------+ 7 9, 3 12)')) geom;

様々な関数 個別の関数名の紹介は今日はしませんが・・・ • • • • • この２つの線(面)は交わってますか？ このエリア内に線(点、面)は含まれていますか？ この点、線、面を含む最小の矩形を返して この線の長さは？ この２つの点のあいだの距離は？ のような事ができる関数群が用意されています。

平面での座標の扱い(おさらい） • 点、線、面、それらの上位である Geometryがある • 人間が見やすく書きやすいのは「ＷＫＴ」 • MySQL内部で扱うのは「MySQL内部バイナリ」 • WKTとMySQL内部バイナリの変換は、 ST_GeomFromText() と ST_AsText() を使用する

地球上の位置の表現 地球上の位置。どうやってあらわす？ 地球の形：だいたい球形 よく見る地図の形：平面 主に２つの表現方法がある 1. 緯度、経度 2. 東、北 方向への距離（メートル）

緯度・経度 『MySQLは8.0になって、地球が 丸いことを知った』とは？ SRS：Spatial Reference System 地球の形、平面地図のルールなどを定義したもの が導入されたということ。

いろいろな地球のモデル 楕円体のままで位置を特定する「地理座標系」 • 長半径と扁平率の定義が異なる様々な地球の形がある • それぞれにID(SRS_ID;SRID)と SRS名が付けられている • 日本で使われる主な 地理座標系の SRS SRS_NAME SRS_ID Tokyo 4301 JGD2000 4612 JGD2011 6668 WGS 84 4326 備考 長半径 扁平率 の逆数 今はほとんど使われない。6,377,397.155 299.1528128 m WGS84との差も大きい 東日本大震災の地殻変化 に対応 6,378,137m 298.257222101 6,378,137m 298.257222101 世界でよく使われる 6,378,137m 298.257223563 WGS84との差は小さい ※とりあえず JGD2011 (6668)、WGS84 (4326) の２つの SRS_ID（数字）は覚えておくとよさげ

いろいろな地球

平面の地図に落とし込む「投影座標系」

• 回転楕円体を平面の地図に落とし込む(＝投影する)ルール
• 地図の中心となる緯度、経度や投影方法などの情報を持つ
• それぞれにID(SRS_ID)と SRS名が付けられている

JGD2011だけでも、こんなに！
mysql> SELECT SRS_NAME, SRS_ID, ORGANIZATION
-> FROM ST_SPATIAL_REFERENCE_SYSTEMS WHERE SRS_NAME LIKE 'JGD2011%';
各中心点(原点)は 測量法付随の国交省告示で
+--------------------------------------------+--------+--------------+
http://www.gsi.go.jp/LAW/heimencho.html
| SRS_NAME
| SRS_ID | ORGANIZATION |
+--------------------------------------------+--------+--------------+
| JGD2011
| 6668 | EPSG
|
| JGD2011 / Japan Plane Rectangular CS I
| 6669 | EPSG
|
| JGD2011 / |Japan Plane Rectangular CS XIII | 6681 | EPSG
|
| JGD2011 / Japan Plane Rectangular CS II
| 6670 | EPSG
| JGD2011 / |Japan Plane Rectangular CS XIV | 6682 | EPSG
|
| JGD2011 / Japan Plane Rectangular CS III | 6671 | EPSG
| JGD2011 / |Japan Plane Rectangular CS XV
| 6683 | EPSG
|
| JGD2011 / Japan Plane Rectangular CS IV
| 6672 | EPSG
| JGD2011 / |Japan Plane Rectangular CS XVI | 6684 | EPSG
|
| JGD2011 / Japan Plane Rectangular CS V
| 6673 | EPSG
| JGD2011 / |Japan Plane Rectangular CS XVII | 6685 | EPSG
|
| JGD2011 / Japan Plane Rectangular CS VI
| 6674 | EPSG
|
JGD2011
/
Japan
Plane
Rectangular
CS
XVIII
|
6686
|
EPSG
|
| JGD2011 / Japan Plane Rectangular CS VII | 6675 | EPSG
|
|
JGD2011
/
Japan
Plane
Rectangular
CS
XIX
|
6687
|
EPSG
|
| JGD2011 / Japan Plane Rectangular CS VIII | 6676 | EPSG
|
| JGD2011 / |UTM zone 51N
| 6688 | EPSG
|
| JGD2011 / Japan Plane Rectangular CS IX
| 6677 | EPSG
| JGD2011 / |UTM zone 52N
| 6689 | EPSG
|
| JGD2011 / Japan Plane Rectangular CS X
| 6678 | EPSG
| JGD2011 / |UTM zone 53N
| 6690 | EPSG
|
| JGD2011 / Japan Plane Rectangular CS XI
| 6679 | EPSG
| JGD2011 / |UTM zone 54N
| 6691 | EPSG
|
| JGD2011 / Japan Plane Rectangular CS XII | 6680 | EPSG
| JGD2011 / UTM zone 55N
| 6692 | EPSG
|
+--------------------------------------------+--------+--------------+
25 rows in set (0.00 sec)

• http://www.gsi.go.jp/LAW/heimencho.html

JGD2011 「日本測地系2011」(学術系) 「世界測地系」(法律等) 「測地成果2011」(国土地理院)を元に制定 • １つの回転楕円体（地理座標系；緯度経度）と • 19の平面直角座標系と • 5つのUTM

Japan Plane Rectangular CS • 「日本の平面直角座標系」 • 国土交通省告示第9号で定義 • 全国を19のエリアに分割 • 位置は、各エリアの原点か らの北、東方向への距離で 表す • 各エリアは自治体界を境界 線とするよう定義されてい る（機械的に長方形で区切 られているわけではない） 国土地理院サイトより http://www.gsi.go.jp/sokuchikijun/jpc.html

• http://www.gsi.go.jp/sokuchikijun/jpc.html

UTM zone • 日本近郊は zone 51N～55Nでカバーされてい る • Universal Transverse Mercator • 経度6度ごとに区切った細長い帯に 1～60の番 号を付与 • 西経180度から東回りに 1から順に附番 • 本来は緯度8度ごとに更に分割するが、MySQL では(?)、北側と南側の２つにのみ分割して 扱っている模様（だから zone 51は、51N と 51S の2 種類のみが存在する）

SRSの定義 • MySQLでは SRSの定義を INFORMATION_SCHEMA の以下のテーブル で参照できる ST_SPATIAL_REFERENCE_SYSTEMS 地理座標系の定義(JGD2011)： GEOGCS["JGD2011" ,DATUM["Japanese Geodetic Datum 2011", SPHEROID["GRS 1980", 6378137, 298.257222101, AUTHORITY["EPSG","7019"]] ,AUTHORITY["EPSG","1128"]] ,PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]] ,UNIT["degree",0.017453292519943278, AUTHORITY["EPSG","9122"]] ,AXIS["Lat",NORTH] ,AXIS["Lon",EAST] ,AUTHORITY["EPSG","6668"]]

投影座標系の定義(JGD2011 平面直角座標系 IX(東京付近))： PROJCS["JGD2011 / Japan Plane Rectangular CS IX", GEOGCS["JGD2011", ....] ,PROJECTION["Transverse Mercator", AUTHORITY["EPSG","9807"]] ,PARAMETER["Latitude of natural origin" , 36 ,AUTHORITY["EPSG","8801"]] ,PARAMETER["Longitude of natural origin",139.833333333333 ,AUTHORITY["EPSG","8802"]] ,PARAMETER["Scale factor at natural origin",0.9999 ,AUTHORITY["EPSG","8805"]] ,PARAMETER["False easting" ,0 ,AUTHORITY["EPSG","8806"]] ,PARAMETER["False northing",0 ,AUTHORITY["EPSG","8807"]] ,UNIT["metre",1 ,AUTHORITY["EPSG","9001"]] ,AXIS["X",NORTH] ,AXIS["Y",EAST] ,AUTHORITY["EPSG","6677"] ]

MySQLでの地理座標系の座標の表し方 ■テーブルの作成。 扱う座標系が決まっている場合はSRIDを指定する。 CREATE TABLE mygeo3 (id INTEGER, pnt POINT SRID 4326); ■データの挿入。SRIDを指定する。 INSERT INTO mygeo3 VALUES (1, ST_GeomFromText('POINT(43.057265 141.389053)', 4326)); ■SRIDがマッチしていないとエラーになる mysql> INSERT INTO mygeo3 VALUES (1, ST_GeomFromText('POINT(43.057265 141.389053)')); ERROR 3643 (HY000): The SRID of the geometry does not match the SRID of the column 'pnt'. The SRID of the geometry is 0, but the SRID of the column is 4326. Consider changing the SRID of the geometry or the SRID property of the column.

その他のトピック

緯度、経度の 表し方 • 度分秒の表し方と、度(小 数)の表し方がある • MySQLでは、度(小数)を使 用する • この部屋(28号館)の位置は、 およそ、東経139度24分25.6 秒、北緯35度38分42.7秒 変換方法の例： 度 分 秒 35 38 42.7 ↓ x1 ↓÷60 ↓÷60÷60 35 合計 0.633333 0.011861111 35.64519 ※図は、iOS用アプリ「FieldAccess2」より

軸の向き

明石の標準子午線 ※青線が天文台 のある、市が全 面的に推してい る「135度」。 実は天文測量の 135度であり、 現在および過去 の地図上の135 度ではない。 現在の135度 （JGD2011）は 緑線の位置。 その前のTokyo 測地系はもっと 西（市役所のあ たり）を通って いた。

※JGD2011の135度ちょ うどのところにあるマン ホール。 カラマンではないが、微 妙に色づけがなされてい る。 ふと顔を上げると、フォ ントで有名な会社の看板 が！（写真自重） 市内の至るところで、こ のデザインのマンホール を見ることができる。

マンホールカードも、も らってきたよ！ あとね、天文台にて、 「シゴセンジャー」とい う正義の味方のファンク ラブがあったので、入っ ちゃったｗ

人丸前駅ホーム

こういった、現実世界の情報が、RDBMSの中に 入り、取り扱っていくのだというイメージを持 てば、データベースの設計や操作が一層楽しく なるに違いありません。 と考えて、私は今日も何か見に行ける面白いも のを探しています:-)

地理情報に興味を持ったら • FOSS4G 2018 Okayama • 2018/11/04-05 • https://www.osgeo.jp/events/foss4g-2018/foss4g2018-okayama • FOSS4G 2018 Tokyo • 2018/11/09-10 • https://www.osgeo.jp/events/foss4g-2018/foss4g2018-tokyo

• https://www.osgeo.jp/events/foss4g-2018/foss4g-

• 引き続き12時からは、Oracle社(MySQL Community Team)の 「MySQL InnoDB Cluster」のお話があります。 • ブースにもお越しください（日本MySQLユーザ会：3階）

～ おしまい ～