MySQLに本格GIS機能がやってきた～MySQL8.0最新情報～@OSC2018北海道

オープンソースカンファレンス2018 Hokkaido MySQLに本格GIS機能 がやってきた ～MySQL 8.0 最新情報～ 2018年七夕 日本MySQLユーザ会 坂井 恵 (@sakaik)

Who am I ? • 坂井 恵（さかいけい） • Twitter： @sakaik • 日本MySQLユーザ会 副代表 • 千葉県在住 • OSC-Hokkaido は、、何回目だろう。結構参加している • 最近は将棋に興味（#見る将 #食べ将 #少し指す将 #エン ジョイ勢）

Who am I? 最近インタビュー記事が掲載されました。ご笑覧ください。 https://employment.en-japan.com/engineerhub/entry/2018/06/22/110000

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

MySQL 8.0 登場！ • 2018年４月GAリリース • 安 速 多 • よくある質問と回答例： 『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

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

私がここでGISの話をする目的 （１）みんなのために いつGISの情報をキャッチアップするの？ いまでしょ！ ※情報には「旬」があります。MySQLのGIS情報の勉強を始め るなら、基礎的な情報から触れることができる「いま」が最適 です！ ぜひ、いまから、GISのお勉強／調査をはじめて、積極的に情報 発信をしてください！

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

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

私がここでGISの話をする目的 （２）わたしのために MySQLのGPS機能について よろしくです！

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

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

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

1. spatial の型に関する知識 INTEGER や VARCHAR と同じように、ひとつの「型」なので、以下のよ うにして、使用する。 mysql> CREATE TABLE mygeo (id INTEGER, geo GEOMETRY); mysql> CREATE TABLE mygeo2 (id INTEGER, pnt POINT); ※いまのところ私は、そんなに型にシビアな扱いが要求される作業をして いないので、常に GEOMETRY 型を使っています。他の POINT / LINESTRING / POLYGON 等は使っていません。この「おおざっぱな」使 い方が、どのようなシーンで、どのような弊害をもたらすのかは、今後誰 かが教えてくれると思います:-)

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 と WKB 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-analysis-functions.html

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

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

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

いろいろな地球 楕円体のままで位置を特定する「地理座標系」 • 長半径と扁平率の定義が異なる様々な地球の形がある • それぞれにID(SRS_ID;SRID)と SRS名が付けられている • 日本で使われる主な 地理座標系の SRS SRS_NAME SRS_ID 扁平率 の逆数 備考 長半径 6,377,397.155m 299.1528128 Tokyo 4301 今はほとんど使われな い。WGS84との差も大 きい 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を試してください • そしてブログとかイベントでの発表とかで私にも教えてくださ い！ • まず平面座標の取り扱い方法の把握、次に回転楕円体（地球）で の取り扱い • 「緯度経度」について

おまけ：どうぞお遊びに利用ください CREATE TABLE mygeo4 (id INTEGER, pnt POINT SRID 4326, description VARCHAR(20)); INSERT INTO mygeo4 VALUES (1, ST_GeomFromText('POINT(43.057265 141.389053)', 4326), 'コンベンションセンター'); INSERT INTO mygeo4 VALUES (2, ST_GeomFromText('POINT(43.064167 141.346945)', 4326), '北海道庁'); INSERT INTO mygeo4 VALUES (3, ST_GeomFromText('POINT(43.385375 145.817501)', 4326), '最東端'); INSERT INTO mygeo4 VALUES (4, ST_GeomFromText('POINT(45.523012 141.936591)', 4326), '最北端');