MySQL　二点間の距離を正確に出す！～ distance_sphere（）

いきなりですが、「MySQLで２点間の緯度・経度から距離算出」が！！前から気になっていたことがありました、 PostgreSQLのPostGISにはあって、MySQLのジオメトリ関数にはない関数、distance_spheroid というもので、回転楕円体で計算で、点間距離を算出する関数なのですが、これが、MySQLには実装されていないため、PostGISを使うか、 MySQLを使うかずっと悩んでいたのですが、MySQLのストアドファンクションを利用して、このdistance_spheroidが実現できないかな？と考えていたところ、ほぼ誤差ゼロで二点間の距離が算出できるようなものができましたので、ここに掲載したいと思います。

もとはといえば

OKILABの方が、当方と同じ理由でMySQL向け距離関数 distance_sphere(), distance_spheroid()という関数を提供してくれていたのですが、バージョンが、5.1対応なので、5.5でPostGISと似たようなことをやりたいな？と思っていた僕は、無理やりストアドプロシージャ（ストアドファンクション）でそれを実装しました。また、より正確な距離を出したかったため、二点間の距離計算を、「ヒュベニの距離計算式」で実装しました。また、正確な距離を出しているか否かにつきましては、http://www.kanzaki.com/docs/sw/geoinfo.html#calc-dist のサイトを参考にさせていただきました。

正直なところ計算式の意味は、全くといって良いほど理解できなかったのですが、一つ一つ噛み砕いて作成したストアドファンクションが以下のものになります。また、このファンクションは、テーブルにアクセスしている部分は全くないので、皆様の環境でも入るかと思います。

余談ではありますが、MySQLには、ストアドプロシージャとストアドファンクションの二種類がありまして、ストアドプロシージャは、CALLで呼び出す形となります、今回は、クエリーの一環として呼び出したかったため、ストアドファンクションで作成しました。（利用方法につきましては後述します）

エラートラップとか、測定基準（日本測地系なのか世界測地系なのか）とか、なんも考慮していません。ちなみに、測定基準は世界測地系基準です。理由は、最近のスマートフォンとか、Googleがそうだから、日本測地系やんなくてもいいかなあ・・と思って（汗　つーか、これくらいの関数は、oracleさんも実装してほしいよ・・

計算ベースは、ヒュベニの計算式というものをベースに作成しております。

あと、検証は、MySQL5.1と5.5で行いましたが、バージョンあんまし関係なく使えるかもしれません

[distance_sphere]

DROP FUNCTION distance_sphere;

DELIMITER //
CREATE FUNCTION distance_sphere(lat1 decimal(30,10), lng1  decimal(30,10), lat2  decimal(30,10), lng2  decimal(30,10)) RETURNS decimal(30,10) DETERMINISTIC
BEGIN
  DECLARE p decimal(30,10);
  DECLARE m decimal(30,10);
  DECLARE n decimal(30,10);
  DECLARE a decimal(30,10);
  DECLARE meter decimal(30,10);
  DECLARE wlat1 decimal(30,10);
  DECLARE wlng1 decimal(30,10);
  DECLARE wlat2 decimal(30,10);
  DECLARE wlng2 decimal(30,10);

  DECLARE latidiff decimal(30,10);
  DECLARE longdiff decimal(30,10);

  DECLARE wx decimal(30,10);
  DECLARE wy decimal(30,10);

  SET wlat1 = lat1 * (PI()/180);
  SET wlng1 = lng1 * (PI()/180);

  SET wlat2 = lat2 * (PI()/180);
  SET wlng2 = lng2 * (PI()/180);

  SET p = ((wlat2 + wlat1) / 2);
  SET latidiff = wlat2 - wlat1;
  SET longdiff = wlng2 - wlng1;

-- # M:子午線曲率半径
  SET m = 6335439 / SQRT(POWER(1 - 0.006694 * sin(p) * sin(p), 3));

-- N:卯酉線曲率半径
  SET n = 6378137 / SQRT(1 - 0.006694 * sin(p) * sin(p));

-- 2点間の距離(m)
  SET wx = m * latidiff;
  SET wy = n * cos(p) * longdiff;

  SET meter = SQRT(wx * wx + wy * wy);

  RETURN meter;

END;
//
DELIMITER ;

これをMySQLの任意のスキーマ内で実行すれば登録は完了します。

[利用例] 四谷二丁目から丸の内線四谷三丁目駅の距離を計測する * 書式 distance_sphere(lat1,lon1,lat2,lon2）

mysql> SELECT distance_sphere(35.687577,139.724501,35.68796,139.7201);
+------------------------------------------------------+
| distance_sphere(35.687577,139.724501,35.68796,139.7201) |
+------------------------------------------------------+
|                                       400.6290516355 |
+------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

400メートルで結果を返しております。

実際この距離が正しいのかどうかわからないのでこちらのサイトで適切な距離なのか確認しました。 http://www.kanzaki.com/docs/sw/geoinfo.html#calc-dist

かなり正確な、距離が出せているかと思います。ただし、このUDFを利用すると、索引は無効になってしまうので、 SQLで利用する際は、事前に値を絞り込んでから実行したほうがよりよい結果になるのではないかと思います。

Database JUNKY

MySQL,MariaDBを中心としたブログです

MySQL　二点間の距離を正確に出す！～ distance_sphere（）

もとはといえば

distance_sphere