MySQLで扱う空間情報のきほん～MySQL Cafe #6

MySQL Cafe 2019年12月 MySQLで扱う 空間情報のきほん 2019/12/05 日本MySQLユーザ会 坂井 恵（@sakaik）

自己紹介 坂井 恵 (SAKAI, Kei) （ @sakaik ） 日本MySQLユーザ会 副代表 自称「交流に力を入れる」係 有限会社アートライ 代表取締役 Software Design誌に MySQL の 空間情報(GIS)に関する話を連載中 (ときどき、MySQLの話が抜けます) なんだか色々良い状態にする仕事 ITに詳しい「業務の "整体師"」 ・・・みたいなもの、という「すごく良 い説明」を思いついたのだけど、却って わかりにくいらしい。。。 この説明で「あぁそういう人って 必要なんだよね」と、ピンと来た 方はぜひお引き合いのご相談をく ださい

略歴(1) 2017/11/2 日本水準原点見学 2017/12/26 日本経緯度原点訪問(夜) 2018/2/10 三方原基線端点訪問 2018/5/23 日本水準原点公開日 2018/7/6 札幌基線訪問(南端・北端) 2018/9/2 伊能忠敬記念館訪問（千葉） 2018/10/7 日本標準時子午線(135E)訪問 2018/12/20 国土地理院「地図と測量の科学館」初訪問 2019/5/22 日本水準原点公開日 2019/5/16 間宮林蔵記念館訪問（茨城） 2019/5/20 測量士補（国家試験）受験 2019/5/30 薫別基線訪問失敗 2019/7/9 測量士補（国家試験）合格 2019/8/23 松浦武四郎記念館訪問（三重） 2019/9/8 日本経緯度原点訪問(昼) 2019/9/13 新潟市内三角点巡り 2019/11/7 平面直角座標系2系原点訪問

略歴(2) 2018/2/11 OSC2018 Hamanako LT「MySQLにGIS機能がやってきた」 2018/7/7 OSC2018 Hokkaido 「MySQLに本格GIS機能がやってきた～MySQL8.0最新情報～」 2018/7/- MyNA会 「MySQLのGIS機能とか超入門」 2018/8/31 ClubMySQL #4 「周辺知識から理解するMySQLのGIS機能」 2018/10/28 OSC2018 Tokyo/Fall 「MysQL8.0の新機能 "地理情報"を理解しよう」 2018/11/4 FOSS4G Okayama 「MySQL 8.0 で強化されたGIS機能のご紹介」 2018/11/10 OSC2018 Niigata LT「MySQLにGIS機能がやってきた」 2018/11/21 MySQL Innovation Day LT「MySQLとGISと私 この1年」 2019/02/- OSC2019 Hamanako LT「浜松の [三角点の] 紹介」 2019/4/20 GTFS-JP勉強会 「バス停の位置情報も格納できる！MySQL8.0の紹介」 2019/7/13 FOSS4G Hokkaido LT「札幌のひとに知ってもらいたい三角点の話」 2019/9/14 FOSS4G Niigata LT「LOVE 三角点」 2019/12/5 MySQL Technology Cafe #6 今回

テーマ 位置情報は楽しい 位置情報は難しくない

位置情報は難しくない お詫び なんか難しそうな話を今までいっぱいしてきました。 でもそれ あんまりMySQLと関係ないんです 位置情報は、難しくありません。

位置情報は難しくない 要するに、「型」がひとつ増えただけの話。 文字列型 数値型 日付型 空間情報型 ※正確には以前のMySQLバージョンでも存在してい たのですが、地球の丸さを考慮した実用的なものに なったのが、MySQL 8.0 なのです。

例：数値型 いろんな「数値」 • 整数とか小数とか • プラスとかマイナスとか（signed） • 超大きい数を扱えるやつとか • 二進数計算のワナにハマらないやつとか

空間情報型 いろんな「空間情報」 • ある一点をあらわす POINT • 点をいくつもつないでできる線 LINESTRING • 点をいくつもつないで閉じた形をつくる POLYGON

「空間情報」を表す型が増えた • とりあえず GEOMETRY 型だけ覚える 個別に特定の空間情報を扱いたいときは POINT型 LINESTRING型 POLYGON型 も覚える • 型が増えると関数も用意される

空間情報型とは まずは「座標」が基本 • 座標＝2つの数値の組み合わせで表される「位置」の情報 →X-Y座標、緯度・経度 など • 座標＝「点」 の情報 • 点をシリアルにつないだものが「線」 →枝分かれした形は、表せない • 点をつなぐことで領域を表したものが「面」 これらをGEOMETRY型のカラムに入れられる

WKT(Well Known Text) 人間が可読な 形式 （WKT) ST_GeomFromText( WKT ) MySQL ST_AsText( 内部バイナリ ) POINT ( 2 7 ) LINESTRING ( 1 3, 2 4, 5 1, 4 5) POLYGON((1 1, 5 1, 6 8, 2 5, 1 1 ))

「点」の表し方を理解することが重要 • 平面の座標なら(x, y) • 地球上なら？ →いろいろあります

地球上の位置の表し方 • 緯度経度（地理座標） • 平面に投影した地図上で定めた基点からの北／東方向それぞれへの距 離（投影座標）

位置の表し方：1.緯度経度 • 地球は丸い • だいたい球だけど球じゃない （約298分の1だけ極半径が短い） • 回転楕円体上にある位置を表すモデルで表現することになった ホントはもっとデコボコ 歴史上いろいろなモデルがある：半径も扁平率もいろいろ →「地理座標系」 日本で使われる（た）主な地理座標系 SRS_NAME SRS_ID 備考 長半径 扁平率 の逆数 Tokyo 4301 今はほとんど使われない。 WGS84との差も大きい 6,377,397.155m 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(数字)を覚えておくのが良い

MySQLでの座標系の情報 • MySQLでは INFORMATION_SCHEMA の ST_SPATIAL_REFERENCE_SYSTEMS に測地系の情報がある • 「今回はWGS84(4326)の地理座標系を使うんだ」とか決めてあるなら、 これを見る必要は特にない • が、いろんな情報が載っているので、見てみるときっと楽しい 地理座標系の定義一覧を見るクエリ例： SELECT SRS_ID, SRS_NAME, DEFINITION FROM ST_SPATIAL_REFERENCE_SYSTEMS WHERE DEFINITION LIKE 'GEOGCS%';

おまけ：緯度経度 • 度分秒と度（小数）の表現がある > 135度43分25.3975秒 > 135.72372436523438度 ※MySQLで扱えるのは、度(小数) • 度分（小数）ってのもある(少し変則) • 分は 度/60、秒は更に/60。時刻と同じ。計算すりゃいいだけ 変換方法の例（MySQLストアドプロシージャ）： delimiter // CREATE FUNCTION dfb2deg(d float, f float, b float) RETURNS float DETERMINISTIC RETURN d + f/60 + b/60/60 // delimiter ;

おまけの余談：度分秒 • 度分秒の記号 135°43′ 25.3975″ 英語 度 degree 分 minute 秒 second 質問： second って「2番目の」のsecondと同じ意味？

おまけの余談：度分秒 • 度分秒の記号 135°43′ 25.3975″ 英語 度 degree 分 minute 秒 second 質問： second って「2番目の」のsecondと同じ意味？ 答え：同じです  度（時間なら「時」）という基本単位がまずあります。  それを細かく分けたのが「minute」（mini- : 小さい)  さらにもう一回細かくして「second minute」→ second だけが残った だから記号も ′ と ″ （1個めと2個め）

位置の表し方：2.投影座標 • 地球球面を平面上に投影（地図など） • ぜったいに歪む。距離、角度、面積を同時には正確にできない（どれ かを諦める） • 有名なのは「メルカトル」 グリーンランドが大きくなるやつ

横メルカトル メルカトルの軸は赤道だが、代わりに任意の子午線を軸にしたもの 「メルカトル」だが、狭い範囲ならば それなりに正確に表せる ■日本でよく使われる投影座標系 • 地球を6度ごとに区切ったUTM （日本は 51-55の範囲に該当する） • 日本で決めた平面直角座標系（日本国内のエリアを扱える） Software Design 誌 2020年2月号も参照

平面直角座標系 • • 日本国内のエリアを19個の系に分割 したもの 分割は、基本的に同一都道府県は同 一のエリアとなるよう（島嶼部を除 く） • 各エリアに「原点」を定める • 原点から「北に何メートル、東に何 メートル」という情報で位置を表す • 横メルカトルだが、誤差（歪み）が、 0.9999～1.001の間に収まるように設 定されている 図は 国土地理院サイトより http://www.gsi.go.jp/sokuchikijun/jpc.html

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

平面直角座標系の例 例：このビルを出て少し左に行った所にある3級基準点（水準点兼用） 北緯 35°40′ 19.4321″ 東経 139°43′ 17.0142″ 平面直角座標系で表すと、東京は9系に含まれるので、 9系の X= -36,376.992 m Y= -10,133.578 m とあらわす。

ST_SPATIAL_REFERENCE_SYSTEMS

を見てみよう

JGD2011を例に見てみる：
•

DEFINITION が
GEOGCSで始ま
るのが、地理座
標系

•

DEFINITIONが
PROJCS で始ま
るのが、投影座
標系

•

JGD2011という
ひとつの地理座
標に対して、た
くさんの投影座
標系があること
がわかる

mysql> SELECT SRS_NAME, SRS_ID, CONCAT(SUBSTR(DEFINITION,1,15),"...") DEFINITION
-> FROM st_spatial_reference_systems WHERE SRS_NAME LIKE 'JGD2011%';
+--------------------------------------------+--------+--------------------+
| SRS_NAME
| SRS_ID | DEFINITION
|
+--------------------------------------------+--------+--------------------+
| JGD2011
| 6668 | GEOGCS["JGD2011... |
| JGD2011 / Japan Plane Rectangular CS I
| 6669 | PROJCS["JGD2011... |
| JGD2011 / Japan Plane Rectangular CS II
| 6670 | PROJCS["JGD2011... |
| JGD2011 / Japan Plane Rectangular CS III | 6671 | PROJCS["JGD2011... |
| JGD2011 / Japan Plane Rectangular CS IV
| 6672 | PROJCS["JGD2011... |
| JGD2011 / Japan Plane Rectangular CS V
| 6673 | PROJCS["JGD2011... |
| JGD2011 / Japan Plane Rectangular CS VI
| 6674 | PROJCS["JGD2011... |
| JGD2011 / Japan Plane Rectangular CS VII | 6675 | PROJCS["JGD2011... |
| JGD2011 / Japan Plane Rectangular CS VIII | 6676 | PROJCS["JGD2011... |
| JGD2011 / Japan Plane Rectangular CS IX
| 6677 | PROJCS["JGD2011... |
| JGD2011 / Japan Plane Rectangular CS XIII | 6681 | PROJCS["JGD2011... |
| JGD2011 / Japan Plane Rectangular CS X
| 6678 | PROJCS["JGD2011... |
| JGD2011 / Japan Plane Rectangular CS XIV | 6682 | PROJCS["JGD2011... |
| JGD2011 / Japan Plane Rectangular CS XI
| 6679 | PROJCS["JGD2011... |
| JGD2011 / Japan Plane Rectangular CS XV
| 6683 | PROJCS["JGD2011... |
| JGD2011 / Japan Plane Rectangular CS XII | 6680 | PROJCS["JGD2011... |
| JGD2011 / Japan Plane Rectangular CS XVI | 6684 | PROJCS["JGD2011... |
| JGD2011 / Japan Plane Rectangular CS XVII | 6685 | PROJCS["JGD2011... |
| JGD2011 / Japan Plane Rectangular CS XVIII | 6686 | PROJCS["JGD2011... |
| JGD2011 / Japan Plane Rectangular CS XIX | 6687 | PROJCS["JGD2011... |
| JGD2011 / UTM zone 51N
| 6688 | PROJCS["JGD2011... |
| JGD2011 / UTM zone 52N
| 6689 | PROJCS["JGD2011... |
| JGD2011 / UTM zone 53N
| 6690 | PROJCS["JGD2011... |
| JGD2011 / UTM zone 54N
| 6691 | PROJCS["JGD2011... |
| JGD2011 / UTM zone 55N
| 6692 | PROJCS["JGD2011... |
+--------------------------------------------+--------+--------------------+
25 rows in set (0.02 sec)

ST_SPATIAL_REFERENCE_SYSTEMS

を見てみよう

JGD2011を例に見てみる：
•

Japan Plane
Rectangular CS とい
うのが、日本の平
面直角座標系

•

1系から19系まであ
ることがわかる

•

•

UTM というのがユ
ニバーサル横メル
カトル
ゾーン51から55ま
でが対応している
ことがわかる

（つづき）

mysql> SELECT SRS_NAME, SRS_ID, CONCAT(SUBSTR(DEFINITION,1,15),"...") DEFINITION
-> FROM st_spatial_reference_systems WHERE SRS_NAME LIKE 'JGD2011%';
+--------------------------------------------+--------+--------------------+
| SRS_NAME
| SRS_ID | DEFINITION
|
+--------------------------------------------+--------+--------------------+
| JGD2011
| 6668 | GEOGCS["JGD2011... |
| JGD2011 / Japan Plane Rectangular CS I
| 6669 | PROJCS["JGD2011... |
| JGD2011 / Japan Plane Rectangular CS II
| 6670 | PROJCS["JGD2011... |
| JGD2011 / Japan Plane Rectangular CS III | 6671 | PROJCS["JGD2011... |
| JGD2011 / Japan Plane Rectangular CS IV
| 6672 | PROJCS["JGD2011... |
| JGD2011 / Japan Plane Rectangular CS V
| 6673 | PROJCS["JGD2011... |
| JGD2011 / Japan Plane Rectangular CS VI
| 6674 | PROJCS["JGD2011... |
| JGD2011 / Japan Plane Rectangular CS VII | 6675 | PROJCS["JGD2011... |
| JGD2011 / Japan Plane Rectangular CS VIII | 6676 | PROJCS["JGD2011... |
| JGD2011 / Japan Plane Rectangular CS IX
| 6677 | PROJCS["JGD2011... |
| JGD2011 / Japan Plane Rectangular CS XIII | 6681 | PROJCS["JGD2011... |
| JGD2011 / Japan Plane Rectangular CS X
| 6678 | PROJCS["JGD2011... |
| JGD2011 / Japan Plane Rectangular CS XIV | 6682 | PROJCS["JGD2011... |
| JGD2011 / Japan Plane Rectangular CS XI
| 6679 | PROJCS["JGD2011... |
| JGD2011 / Japan Plane Rectangular CS XV
| 6683 | PROJCS["JGD2011... |
| JGD2011 / Japan Plane Rectangular CS XII | 6680 | PROJCS["JGD2011... |
| JGD2011 / Japan Plane Rectangular CS XVI | 6684 | PROJCS["JGD2011... |
| JGD2011 / Japan Plane Rectangular CS XVII | 6685 | PROJCS["JGD2011... |
| JGD2011 / Japan Plane Rectangular CS XVIII | 6686 | PROJCS["JGD2011... |
| JGD2011 / Japan Plane Rectangular CS XIX | 6687 | PROJCS["JGD2011... |
| JGD2011 / UTM zone 51N
| 6688 | PROJCS["JGD2011... |
| JGD2011 / UTM zone 52N
| 6689 | PROJCS["JGD2011... |
| JGD2011 / UTM zone 53N
| 6690 | PROJCS["JGD2011... |
| JGD2011 / UTM zone 54N
| 6691 | PROJCS["JGD2011... |
| JGD2011 / UTM zone 55N
| 6692 | PROJCS["JGD2011... |
+--------------------------------------------+--------+--------------------+
25 rows in set (0.02 sec)

→それぞれにSRS_ID(SRID)
が割り振られていることに
注目！

ST_SPATIAL_REFERENCE_SYSTEMS 地理座標の例（JGD2011) 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"]]

ST_SPATIAL_REFERENCE_SYSTEMS 投影座標の例（JGD2011／9系) DEFINITION(坂井が整形) PROJCS["JGD2011 / Japan Plane Rectangular CS IX", 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"] ], 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"]] |

ここまでのまとめ 位置(POINT)を表す方法 地理座標（緯度・経度） 投影座標（一本杉） 緯度経度の表現法 何の測地系で ＋ 緯度 経度 平面直角座標系（またはUTM）の表現法 何の測地系のどのエリアの投影で ＋ N何m、E何m

おまけ：高さ ※時間があれば語りたいが、おそらく時間は、ない。 • 回転楕円体は複雑な地球の形を「モデル」化したもの • 本当は、でこぼこ。山あり海溝ありという話じゃない。それは高さが 違うという事なので。言いたいのは「ゼロメートルライン」がツルツ ルじゃないということ • 回転楕円体表面からの高さ＝回転楕円体高 ≠ 標高 • 標高は、デコボコなゼロメートルラインからの高さ。 →ゼロメートルラインに関する情報を予め知っておかないと求め られない

MySQLでの扱い方 例えば DATE 型へは '2019/12/05' といった文字列を与えれば、自動変換 してくれるが、GEOMETRY型は変換を明示する必要がある（と考える とよい）。その際、測地系(SRID)を必ず与える。 （default_srid みたいなのがあればいいんですけどねー） • ST_GeomFromText('POINT(35 135)',6668) • ST_GeomFromText('POINT(-4567 13671)',6670) • ST_GeomFromText('LINESTRING(35 135,35.5 134.2, 35.4 134.9)', 6668); ※その他、過去の私の発表資料や、Software Design誌の私の連載をご覧 ください

その他の話題 時間内に収まらないと思うので、以下テーマやキーワードだけ紹介： • GeoHash • 「高さ」「標高」「楕円体高」 • オープンデータ、shpファイル、取り込み • 「位置」は、誰かが測量しれくれたことで特定できる（最近はGNSS でお手軽ですが）。その測量に関する技術とか、名所、遺物など。

Ref: 周辺知識から理解するMySQL の GIS機能 ～ClubMySQL #4 GeoHashや「高さ」に関する詳細な説明もあります https://www.slideshare.net/sakaik/mysql-gis-clubmysql-4 その他私の過去のプレゼン 位置情報の「楽しさ」に焦点を当てたものが多いです https://www.slideshare.net/sakaik/presentations

• https://www.slideshare.net/sakaik/mysql-gis-clubmysql-4
• https://www.slideshare.net/sakaik/presentations

まとめ • 地球上のある一点の表し方を知る • GEOG/PROJ • それらをつなげたもの（LINESTRING)も扱えるし、POLYGONも • 測地系に番号がついている。SRS ID(SRID) • MySQLではその番号を使って扱う →「摂氏80度」「華氏80度」みたいな、、というか 摂氏は コード4859で、華氏はコード 3463、みたいな感じ

一言でまとめ 位置情報 は楽しい

MEMO：公開情報 ・発表タイトル MySQLで扱う空間情報のきほん ・セッション概要 MySQL 8.0 では、地球上の位置に関するデータを扱うことができるようになりました。これまでも平面の 座標情報は扱えましたが、8.0では、丸い地球上の位置を表現できるようになったのが、大きな進化です。 本セッションでは、地球上の位置を表すための方法（緯度経度だけで正確に表せると思っていませんか） をはじめとして、MySQLで空間情報を扱うために知っておきたい基本的な事柄を解説します。 ・登壇者の表記： 日本MySQLユーザ会 坂井 恵 ・プロフィール： 日本MySQLユーザ会副代表(特に役割分担があるわけではないが、イベント担当のような活動が多い)。有 限会社アートライ代表取締役。2003年頃にMySQLに触れ始めて以来「たのしいデータベース」をモットーに、 活動を続けている。2018年に正式リリースとなった MySQL 8.0 で空間情報(GIS)に関する機能が充実したこと をきっかけにGIS情報に関心を持ち始めたが、興味の方向が「位置情報を構成する現実世界のモノ・しくみ」 のほうに向いてしまう。最近の趣味は、位置情報や地図学的な「聖地」めぐり。技術評論社「Software Design」誌に『MySQL GISへの招待』連載中。 Oracle Certified Professional, MySQL 5.7 Database Administrator 認定資格者。測量士補。