MySQLのGIS関数でST_DISTANCEとST_DISTANCE_SPHEREを使ってみた
MySQLでGIS(地理情報システム, Geographic Information System)パッケージを使ってみた。
単に2点間の距離を計算して近い順に出力するだけなのですが。
カラムとしては Geometry型で宣言。
データはそのまま簡単にINSERTできないので型変換処理を行った形でST_GeomFromText('POINT(緯度 経度)')として緯度、経度データをINSERTする。
ちなみに POINT()の中の緯度、経度の区切りはカンマではなく スペースで区切る点注意します。
で2点間の距離を計算するには、
ST_DISTANCE( point1, point2)でできるらしい。
ちなみに GoogleMapで東京駅と渋谷駅の2点間の距離は ほぼ 6.42kmだった。
INSERT INTO LOCATION (NAME, P) VALUES ('東京駅', ST_GeomFromText('POINT(35.68135227790528 139.76740326253673)')); INSERT INTO LOCATION (NAME, P) VALUES ('渋谷駅', ST_GeomFromText('POINT(35.65871263580799 139.70141567824763)')); SELECT ST_DISTANCE(( SELECT P FROM LOCATION WHERE NAME='東京駅'), (SELECT P FROM LOCATION WHERE NAME='渋谷駅'))
とやったところだいぶ想定よりも小さい値(0.06976327597386225)が出力された。
どうも調べると ST_GeomFromTextの際に単位系を設定していなかったためらしい*1 ユークリッド距離というものが計算されているらしく、求めてるメートル単位の計算にはならないらしい。
メートル単位にするには ST_GeomFromText()にSRIDというもので球面距離を示す4326を指定すると良いらしい。
で改めて以下のようなデータを投入しやり直してみる。
INSERT INTO LOCATION (NAME, P) VALUES ('東京駅', ST_GeomFromText('POINT(35.68135227790528 139.76740326253673)', 4326)); INSERT INTO LOCATION (NAME, P) VALUES ('渋谷駅', ST_GeomFromText('POINT(35.65871263580799 139.70141567824763)', 4326)); SELECT ST_DISTANCE(( SELECT P FROM LOCATION WHERE NAME='東京駅'), (SELECT P FROM LOCATION WHERE NAME='渋谷駅'))
うーん、参考にしたページではうまくメートル単位で正しく出力されたのだが...
ということで改めてもう一つの ST_DISTANCE_SPHERE()を使用してみると今度はエラーが出てうまくいかない。
POINTの緯度、経度の指定を逆にしてみる。
INSERT INTO LOCATION (NAME, P) VALUES ('東京駅', ST_GeomFromText('POINT(139.76740326253673 35.68135227790528 )', 4326)); INSERT INTO LOCATION (NAME, P) VALUES ('渋谷駅', ST_GeomFromText('POINT(139.70141567824763 35.65871263580799 )', 4326)); SELECT ST_DISTANCE_SPHERE(( SELECT P FROM LOCATION WHERE NAME='東京駅'), (SELECT P FROM LOCATION WHERE NAME='渋谷駅'))
あっさりメートル単位で距離が出力された(6470.655413168728)。
性能的には精度は落ちるが負荷が少ないのでうまくいけばST_DISTANCEを使用したかったのだが、今のところST_DISTANCE_SPHEREしか 正しく値を返さないので当面こちらでやることにする。
うーん、とはいえ緯度、経度を差し替えるとうまくいくという点はなんか納得できないなぁ。本家ドキュメント*2やここ*3でもMySQLは経度、緯度の順って書いてあるのに。
