GoogleMapを使ったプロダクト作成のためにSRID、緯度経度、GeoHash、GeometryをMySQL8.0で理解する
概要＆目的
MySQL8.0のGeo関数を使うと、レコード同士の距離計算、Polygonの中にPointが含まれているかなどもSQLで計算できるということで調べてみました。
地図をWebアプリに乗せるときのSRIDはどれか、 GeoHash、 Geometry型とはなにか、 GoogleMapの仕様の理解が進むと幸いです。
後半では実際にSQLを叩いて、GeoHash、POINT型両方でレコード間の距離を測ってみています。
動作確認したバージョン
MySQL 8.0.20 Ruby on Rails 6.0.3.2
事始め
GoogleMapにピンを置くプロダクトを作っていました。
（イメージ：浅草寺から200メートル以内にある観光スポットのピンを立てる、等）
やりたいこと
- MySQLに地点の情報を保存して、FEからRoRなどAPI経由で地点を取得したい。
- SRIDという概念を知りたい、活用したい。
- GoogleMapにわたすべきSRIDがEPSG:4326で良いのか確信を持ちたい。
- SQL文でレコード同士の距離を測定したり、xxメートル以内のレコードを抽出したい。
参考
以下の記事がとても勉強になりました。
MySQLのGeo関数と、Geometry型、GeoHashについて
MySQL 8.0(以下、MySQL)以降では ST_ から始まるGeo関数があります。
※MySQL5.7と関数名が違うので注意。
※2020/07現在、日本語訳はMySQL5.6まで。
MySQLで地点を保持する方法は大きく2つ：Geometry型、GeoHash（String型）
-
Geometry型で保存
- Point型：ランドマークを指定したいならこれで十分
- LineString型：境界線とかを指定したいならこれ
- Polygon型：「代々木公園」みたいなある程度大きいエリアを保存したいとき使う
-
GeoHash（String型、以下GeoHash）で保存：Point型しか使わないならこれが良さそう
- Point型と1対1に対応したHash値
- BigQueryなど外部サービスから緯度経度情報をMySQLに移す場合、扱いやすいStringで移管できる
- 基本的に、近い点同士のGeoHashは近い文字列となる（※必ずではない）
GeoHashのHash値生成についてはこちらを参照ください。
位置情報の保存はSRIDとセットで行う
Geometry型、GeoHashどちらを扱う場合でも、「どのフォーマットで保存した位置情報なのか」が無いと点どうしの比較も特定の地図への投影も出来ないため、SRIDの指定が実質必要になってきます。
SRIDとは
空間参照 ID (SRID) は、特定の座標系、許容値、および解像度に関連付けられた一意の ID です、 SRID の設定方法またはそれが表す内容は、データを格納するために使用するデータベースによって変わります。European Petroleum Survey Group (EPSG) で定義されたものを始めとして、さまざまな標準 SRID が認められています。
SRIDとはつまり「緯度経度(35.111, 135.222)を何かしら別の地図に投影するときに必要な変換の基準値」。
EPSGなど標準化機構が、楕円体である地球上のポイント（GoogleEarthの緯度経度など）を地図（GoogleMapのWebメルカトル図法や、正距方位図法など）に投影する際に、地図ごとに対応関係を整理してくれています。
緯度経度自体は厳密には EPSG:4326 に属する値ですが、これが実質基準・起点になっています。
世界測地系の緯度経度は GPS で使われてるから、それが標準になってるのでしょうか。
例えばMySQLのPOINT型は ST_GeomFromText('POINT(35.11 139.22)', 4326) の様に指定すると、地点(35.11 139.22)をEPSG:4326形式でバイナリ形式に保存できます。
（確認の際は ST_AsText(カラム名)とバイナリ⇒Stringに変換）
※SRIDを指定しないとSRIDには0が指定されますが、使い勝手が良くないので保存の際に特定のSRID指定をおすすめします。
参考）EPSGが異なるSRID同士を変換してくれるWebツールを提供してくれています。気になる方は見てみてください。
Transform coordinates - GPS online convertor
補足：Geometry型を使うとき
MySQLにGeometry型で保存するとき、SPATIAL INDEXを貼ると検索速度が上がるようです。
（こちらの記事が「SRIDとはなんぞや」を調べるきっかけになりました。）
SRIDは色々在るけど、EPSG:4326でOKなのか？
Webアプリ制作に限る場合、特にGoogleMapのようなサービスを使う場合には、SRIDとしては4326, 3857(, 900913※)あたりを考えれば十分です。
GoogleMapにプロットするポイントのデータならば 4326 形式でMySQL保存しておけば良いです。
(※900913は現在では3857に移管されていて、考慮は不要です。もともとはGoogleが作成したSRIDで、googleを文字っているという噂です。)
MySQLに保存するデータのSRIDは4326で良いのか調査：「緯度経度」とはそもそも何か
緯度経度
SRIDを調べて気になったのが、「緯度経度」 と、EPSG:4326の "lat lon" 、EPSG:3857の "XY軸"の単語の関係性でした。
4326ではlongitude, latitude入力欄があり、3857にはX, Y欄があります。
Wikipediaによると
「緯度」= 「地理緯度」=「地球を回転楕円体で近似したときに、その地点における楕円体面の法線と赤道面とがなす角度（φ）」
ということでした。Unitはdegree。
ではXY軸とは？
ざっくり言えば、ある地点の緯度経度を2次元Mapに射影したときの対応先に当たる値です。
Webメルカトル図法などでは変換値をX coordinate, Y coodinateとして使っています。
単位はmetre:メートルです。（metreとはイギリス表記？）
ゼロ点は緯度経度同様、赤道と子午線（グリニッジ）です。
Unit: metre
Prime meridian: Greenwich
Webメルカトル図法は、地図に詳しい会社のプロダクトオーナーの方からこちらの記事を教えていただきました。Webメルカトルについてとても詳しく説明されているのでご興味在る方は是非御覧ください！
この段階では、MySQLに保存すべきデータのSRIDは4326，3857どちらでも良いことになります。「使う地図がどのSRIDでの位置データを扱うか」はまた別の話、地球をどう平面に展開するかと位置をナニで表すかは別問題 ですね。
GoogleMapには4326のフォーマットを渡す？
GoogleMap上のポイントはWebメルカトル図法、EPSG3857で表現されています。
ただ、アドレスバーを見ていただけば分かる通り、GoogleMapAPIとのやりとりに使うのはEPSG: 4326である「緯度経度」です。
# 元のURL
<https://www.google.co.jp/maps/place/東京スカイツリー/@35.7161339,139.7772237,13z/data=!4m5!3m4!1s0x60188ed0d12f9adf:0x7d1d4fb31f43f72a!8m2!3d35.7100712!4d139.8106813?hl=ja>
# 緯度経度とその対応
<https://www.google.co.jp/maps/place/東京スカイツリー/@[今表示しているMapウィンドウの中心緯度],[今表示しているMapウィンドウの中心経度>],[MapのZoomレベル]/data=!4m5!3m4!1s0x60188ed0d12f9adf:0x7d1d4fb31f43f72a!8m2!3d[東京スカイツリーの緯度]!4d[東京スカイツリーの経度]?hl=ja
つまりGoogleMapを使うときは以下が実施されているということだと思います。
地球をどう平面に展開するか ⇒ 3857, GoogleMapの内部で緯度経度を変換してプロットしてくれる
位置をナニで表すか ⇒ ユーザーは4326形式でデータを渡す、または地点の座標情報を 4326で受け取ることができる（ここを気にすればいい）
ここまでで、一旦結論としては以下となります。
GoogleMapに表示するデータはMySQL8.0ではEPSG: 4326形式で保存しておけばいい
さすがに全然コードが出てこなかったので、最後にGeometry型とGeoHashそれぞれをSRIDを4326として扱ったデモをしたいと思います。
デモ：2地点間の距離をST_Distance_Sphere で測る
地点1：東京スカイツリー
35.7100712, 139.8106813
地点2：浅草寺
35.7147651, 139.7966553
距離はおおよそ1370mです。
GeoHashの場合
- まずST_GeoHashでGeoHash値生成する。
- 第2引数でGeoHashの文字数を指定できる。
- GCPのBigQueryとの連携の文字数Maxが20だったので20を指定。
- Databaseには、
ST_GeoHash(POINT(139.8106813, 35.7100712), 20)などでINSERTすることになります。 - （GeoHashを見たいのでわけていますが、実際にはサブクエリなど使うと短く書けます。）
-- 東京スカイツリーのGeoHash
mysql> SELECT ST_GeoHash(POINT(139.8106813, 35.7100712), 20);
+------------------------------------------------+
| ST_GeoHash(POINT(139.8106813, 35.7100712), 20) |
+------------------------------------------------+
| xn77jkz40zzbnux0g6my |
+------------------------------------------------+
1 row in set (0.00 sec)
-- 浅草寺のGeoHash
mysql> SELECT ST_GeoHash(POINT(139.7966553, 35.7147651), 20);
+------------------------------------------------+
| ST_GeoHash(POINT(139.7966553, 35.7147651), 20) |
+------------------------------------------------+
| xn77jjsvd4v2me43vu5m |
+------------------------------------------------+
1 row in set (0.00 sec)
-- 距離測定
mysql> SELECT ST_Distance_Sphere(
-> ST_PointFromGeoHash('xn77jkz40zzbnux0g6my', 4326),
-> ST_PointFromGeoHash('xn77jjsvd4v2me43vu5m', 4326)
-> ) AS distance;
+--------------------+
| distance |
+--------------------+
| 1369.6906911109868 |
+--------------------+
1 row in set (0.00 sec)
おおよそGoogleMapと同じ距離ですね。
POINT型の場合
- Databaseには、
ST_GeomFromText('POINT(35.7100712 139.8106813)', 4326)で生成するGeometry型をINSERTすることになります。
mysql> SELECT ST_Distance_Sphere(
-> ST_GeomFromText('POINT(35.7100712 139.8106813)', 4326),
-> ST_GeomFromText('POINT(35.7147651 139.7966553)', 4326)
-> ) AS distance;
+--------------------+
| distance |
+--------------------+
| 1369.6906911109868 |
+--------------------+
1 row in set (0.00 sec)
GeoHashだと緯度経度がサッと確認できず面倒かなと思いきや、POINT型などGeometryの子クラスの場合、DBに入れた中身がバイナリ形式になって文字化けしてしまいます。
人間が読める形式にするには上記のように ST_AsText(geometry) を噛ます必要があるので、GeoHash、Geometry型どちらも緯度経度を確認するには一工夫が必要です。
-- POINT型を読む場合
mysql> SELECT ST_AsText(ST_GeomFromText('POINT(35.7100712 139.8106813)', 4326));
+-------------------------------------------------------------------+
| ST_AsText(ST_GeomFromText('POINT(35.7100712 139.8106813)', 4326)) |
+-------------------------------------------------------------------+
| POINT(35.7100712 139.8106813) |
+-------------------------------------------------------------------+
1 row in set (0.00 sec)
余談1：Ruby on Rails 6 (RoR6), RoR5を使っている場合はGeometry型の扱いが面倒
RoR6ではActiveRecord (AR)がmysqlとやり取りするのにmysql2を使いますが、2020/07時点でGeometry型を扱うのは苦手？と思っています。
ARがGeometry型を扱うためのGemは過去、RoR4時代に有ったようなのですが、CommitやPRが止まってます。
今は有志の方がforkしてRoR6に対応されています。
余談2：Geo関数の実装はPostgreSQLのほうが進んでいる？
3年ほど前の記事ですが、以下の記事がありました。
今でも以下は当てはまっているようです。特にRailsを使う方にとってはGemが生きているのは大事かもしれません。
- ドキュメント ( HTML / PDF / EPUB ) が完璧(PostGIS)
- PostGIS の使いやすさ (Geography 対応)
- AR 専用 gem, activerecord-postgis-adapter あり、その上、ずっとメンテナンスされている
また、PostgreSQLでは ST_DistanceSpheroid という「地球を楕円体と見たときの2点距離間計算」を実施してくれる関数を持っています。
MySQLでは真球用の ST_Distance_Sphere しかないため、精度を求めるならPostgreSQLが選択肢に入るのかもしれません。
個人的には距離は概算で良いのと、 ST_DistanceSpheroid は楕円を考慮する都合上、計算量が上がるようなので、慣れているMySQLを選択しています。
結論
- GoogleMap上にプロットする位置情報はEPSG:4326形式で保存してあげれば十分OK
- Point型のみ扱うなら、GeoHash（String型)の利用が便利
- MySQLにはいろいろなGeo関数が有る
- PostgreSQLにはST_DistanceSpheroid()関数があったり、MySQLよりできる幅が広そう以上長々とお読み頂きありがとうございます！
GIS関連のアプリケーション製作で細かい点の実装に迷い、結構時間を使いました。
当記事で誰かの地図アプリ作りの助けになったら幸いです。
（誤り、訂正箇所ありましたら、コメントいただけると幸いです）
