MySQLのGIS機能とか超入門 ～MyNA会2018年7月

日本MySQLユーザ会会 2018年7月 MySQLの GIS機能とか 超入門 2018/07/23 日本MySQLユーザ会 坂井 恵（@sakaik）

自己紹介 • 坂井 恵（さかいけい） • Twitter： @sakaik • 日本MySQLユーザ会 副代表 • 各地OSC(オープンソースカンファレンス)とかで 時々しゃべったりしています。 • MySQL：#エンジョイ勢 #機能派 • https://employment.en-japan.com/engineerhub/entry/2018/06/22/110000

• https://employment.en-japan.com/engineerhub/entry/2018/06/22/110000

自己紹介 最近インタビュー記事が掲載されました。ご笑覧ください。 https://employment.en-japan.com/engineerhub/entry/2018/06/22/110000 私の知らない人だ。。。

• https://employment.en-japan.com/engineerhub/entry/2018/06/22/110000

今は昔 今のMySQLの状況はとっても初期のMySQLの雰 囲気に似ている MySQL 8.0 ：新機能満載 「ちょっと試してみた」 「こんな動きするんだ。知ってる？」 参考：MySQL 8.0 よくある質問と回答例： 『MySQL 8.0.11 (GA)がリリースされました』をご覧ください http://sakaik.hateblo.jp/entry/20180420/mysql_8.0.11_ga_released

• http://sakaik.hateblo.jp/entry/20180420/mysql_8.0.11_ga_released

MySQL 8.0 • Full InnoDB 化 • ドキュメントストア(X Protocol、MySQL Shell) • JSON • 文字コード関連 • Window関数 • CTE • GIS

私がここでGISの話をする目的 日本MySQLユーザ会代表 とみたまさひろ氏の発表を引用： • MySQLユーザ会のこれまでとこれから(2011) https://www.slideshare.net/tmtm/mysql-6956074

• https://www.slideshare.net/tmtm/mysql-6956074

私がここでGISの話をする目的 MySQLのGIS機能について よろしくおねがいする次第です！

免責事項 本日お話する内容は、発表者の個人的な調査と学習に基 づき公表するものです。発表者自身も本発表分野につい て学習中であり、本発表中には不正確な内容が含まれて いる可能性があることを、発表者は排除しません。本内 容はノーコミットメントとして公表するものですから、 本内容を重大な経営上の意思判断を行う唯一の情報源と はなさらないでください。 お願い事項 本内容に誤りを見つけたとき、または、本内容をきっか けとして調査なされた結果等の情報がある場合は、ぜひ ブログなどでその情報を他の方に知らせてください。そ の際よろしければ、Twitter で @sakaik にメンショ ンを飛ばしていただけると私も気づけるので嬉しいです。

What is 'GIS' ？ • GIS: Geographic Information System （地理情報システム） • MySQL内では spatial (空間の；空間情報)というキーワード • MySQL: spatial 用の型たちと関数群が用意されている • MySQL: 要するに (x, y) 座標のデータを扱えるもの • MySQL: (緯度 経度)の情報も扱える（たぶんこっちが今後広がる使い方）

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 GAに！

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

私の興味の方向 • MySQLにGIS機能が本格的に入った！ • 緯度、経度を取り扱えるぞ！ • 緯度、経度ってそもそも何だろう・・・ • 興味がナナメ上のほうに

私の興味 緯度経度といえば、これ！

三角点 • 測量の基準となる点 • 国土地理院が設置／管理 • 主に一等から四等まである • 一等三角点は全国に約330か所 （一辺が30～50km程度の三角形を描くよう） • 名前は三角点だけど四角い

MySQLの Spatial 機能を理 解する ３つの基礎知識 1. 型に関する知識 2. 関数に関する知識 3. 地球に関する知識

1. spatial の型に関する知識 空間情報(Spatial) をあらわす３つの形態 •点 (POINT) •線 (LINESTRING) •面 (POLYGON) そのほか、 MULTIPOINT MULTILINESTRING MULTIPOLYGON GEOMETRYCOLLECTION もありますが、今日は触れません。 ひっくるめて 幾何(形状） (GEOMETRY)

2. Spatial 関数に関する知識 • 約90個の関数（8.0.11時点) 形式の変換をするもの ST_[gtype]From[fmt] ： 16個 ※ gtype: Geom, Point, Line, Polygon, およびそれぞれのMulti. ※ fmt: WKT, WKB MySQL固有のもの（値の初期化）： 8個 値を抽出するもの： 22個 値を比較するもの： 20個 ほか

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

WKT と 内部バイナリ 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 (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)) WKT → MySQLの内部バイナリ： • ST_GeomFromText() 関数を使用する。 MySQLの内部バイナリ → WKT： • 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;

関数の使い方の例 • 1.414 + 1.414 + 1 = 3.828 mysql> SELECT ST_Length(ST_GeomFromText('LINESTRING(1 1, 2 2, 3 1, 4 1)')) len; +--------------------+ | len | +--------------------+ | 3.8284271247461903 | +--------------------+ • WHERE 文などに使うと、エリアに含まれるポイントを抽出できる関数 mysql> SELECT MBRContains(ST_GeomFromText('POLYGON((1 1, 3 1, 3 3, 1 3, 1 1))'), ST_GeomFromText('POINT( 2 2)')) cont; +------+ | cont | +------+ | 1 | ※対象を POINT(2 4) に変えると： +------+ | cont | +------+ | 0 |

ここまでのまとめ • 点と線と面を理解する • WKT と MySQL内部バイナリがあることを知 る • WKT と MySQL内部バイナリの行き来を自由 にできるようになる • いろいろな関数があるので、マニュアルを見て みよう https://dev.mysql.com/doc/refman/8.0/en/spatial-analysisfunctions.html

• https://dev.mysql.com/doc/refman/8.0/en/spatial-analysis-

３．地球に関する知識 ここまでの話 直交座標系 地球 ※地球上の位置は、緯度と経度であらわす

「地球」を知っていますか？ • 大きさは？ • どんな形？ • 半径だいたい 6,400キロ • 回転楕円体。極径が赤道径が よりも、約300分の1、短い 実は、「地球の形」はひとつではありません！ それが「空間参照系」。 さらに、地図（＝平面）へ投影する手法もたくさんある

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

SRSの定義 • MySQLでは SRSの定義を INFORMATION_SCHEMA の 以下のテーブルで参照できる ST_SPATIAL_REFERENCE_SYSTEMS mysql> SELECT * FROM ST_SPATIAL_REFERENCE_SYSTEMS WHERE SRS_ID=6668; +----------+--------+--------------+--------------------------+-----------------+ | SRS_NAME | SRS_ID | ORGANIZATION | ORGANIZATION_COORDSYS_ID | DEFINITION | DESCRIPTION | +----------+--------+--------------+--------------------------+-----------------+ | JGD2011 | 6668 | EPSG | 6668 | GEOGCS["JGD2011",DATUM["Japanese Geodetic Datum 2011",SPHEROID["GRS 1980",6378137,298.257222101,AUTHORITY["EPSG","7019"]],AUTHORITY["EPSG","1128"]],PRIMEM["Gre enwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.017453292519943278,AUTHORITY["EPSG","91 22"]],AXIS["Lat",NORTH],AXIS["Lon",EAST],AUTHORITY["EPSG","6668"]] | NULL |

SRSの定義 • まぁふつう、 ¥G で表示しますかね、1個なら。 mysql> SELECT * FROM ST_SPATIAL_REFERENCE_SYSTEMS WHERE SRS_ID=6668¥G *************************** 1. row *************************** SRS_NAME: JGD2011 SRS_ID: 6668 ORGANIZATION: EPSG ORGANIZATION_COORDSYS_ID: 6668 DEFINITION: GEOGCS["JGD2011",DATUM["Japanese Geodetic Datum 2011",SPHEROID["GRS 1980",6378137,298.257222101,AUTHORITY["EPSG","7019"]],AUTHORITY["EPSG","1128"]],PRIMEM["Gre enwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.017453292519943278,AUTHORITY["EPSG","91 22"]],AXIS["Lat",NORTH],AXIS["Lon",EAST],AUTHORITY["EPSG","6668"]] DESCRIPTION: NULL

SRSの定義 • DEFINITION 列を整形してみる 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"]] GEOGCS: 地理座標系。 投影座標系の場合は PROJCS AXIS: Lat(緯度)、Lon(経度)の順で定義されているの で、WKTで表現する時もこの順で。

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. SRIDがちゃうで。カラムで 4326 言うてるんやから、0なんて呉れたら、 あかんて。

緯度経度で関数の利用 2点間の距離を測る mysql> SELECT ST_Distance_Sphere( -> ST_GeomFromText('POINT(43.057265 141.389053)', 4326), -> ST_GeomFromText('POINT(43.064167 141.346945)', 4326) ) dist; +--------------------+ | dist | +--------------------+ | 3505.9915447964927 | +--------------------+ 1 row in set (0.02 sec) 札幌のコンベンションセンターから、北海道庁まで、 約 3.5km だと計算された

MySQLのGIS機能を理解するために 読むべきMySQL8.0マニュアル 11章：Data Types の 11.5 Spatila Data Types 12章：Functions and Operators の 12.15 Spatial Analysis Functions 13章：SQL Satement Syntax の 13.1 Data Definition Statemen の 中にある SPATIAL REFERENCE SYSTEM Syntax 関連の CREATE/DROP の節 MySQL 8.0.13ではこうなります、という記述なども多いの で注意（現在の最新は 8.0.11) 見るべきデータ INFORMATION_SCHEMA の ST_SPATIAL_REFERENCE_SYSTEMS テーブル

今日取り上げなかった主なト ピック • Multi 系のデータ型およびその取扱い方法 • GeoHash • GeoJSON • MySQL Workbench での GIS Viewer • 度分秒と度（小数）との変換（MySQL内でさくっと変 換できる仕組み、ないんですかね） • 様々な関数を駆使した実用事例

MySQLのGIS機能の課題 • 現在絶賛発展中 • まだまだ不足している関数がある • ドキュメントに記載されている関数が動作しない （実装されていない）ことがある（8.0.12以降で実 装されているものと思われるが、8.0.11では動作し ない） • 関数名を一気に変更（＝旧名を廃止）したので、 MySQL 5.7 までで動作していた外部のプログラ ムが動作しなくなるケースがある • →きっと時間(とき)が解決してくれるよ

最後のおねがい GISに詳しい人 が増えて私に教 えてください。

ご清聴あり がとうござ いました。