distance_sphere（自作 MySQL 距離計算関数）
MySQL5.6でこれが実装されたのかされていなのかについてはよくわかりませんが、２点間の距離をもとめるためだけに、OPEN GISとか、何かモジュールを入れたりするのも面倒（いや、実際にはその手順をよくわかっていない）なのでストアドファンクションで作ってみました。
distance_sphereとは
PostgreSQLでは、そこそこ有名な。POST GIS そのPOST GISに含まれる点間距離関数です。私も、一歩でも近づきたいという気持ちから、distance_sphereという同じ名前でストアドファンクションにしてみましたが、よくよく考えて考えてみると、紛らわしい・・。
単純には、経度・緯度から直線の距離をメートルで返しますよという関数なのですが、地球は丸い！ということを考慮してｗ
ヒュベニの距離計算式というものをベースに作ってみたものが、以下のそれです。まぁ、内容について、あれはこうだのどうだのヒュベニさんって誰なのとか聞かれてもさっぱりわからないのでご了承願います。
ちなみに公式は、以下のサイトに載ってました
また、以下がストアドファンクションのソースになります。MySQLのコンソールにコピペしてくれればきっと入ります。
- distance_sphere
DROP FUNCTION IF EXISTS 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 ;
さっそく使ってみよう！！
実際、プログラムに書けばいいじゃんとか言われたらおっしゃる通りなのですが、僕的には、SQLの中でこれを混ぜたいと思っていたので、そういう使い方を見せたいと思います。
以下２つのSQLサンプルを書きます
- 品川駅（高輪口）から御殿山トラストシティまでの直線距離を求める
select distance_sphere(35.628770,139.737295,35.621438,139.736902) ;
+------------------------------------------------------------+
| distance_sphere(35.628770,139.737295,35.621438,139.736902) |
+------------------------------------------------------------+
| 814.2787425846 |
+------------------------------------------------------------+
814メートルでした。
- では、品川駅（高輪口）を起点として半径500メートル以内にある、レストランを近いもの順に表示
seletct
shop_name,
distance_sphere(35.628770,139.737295,lat,lng) as distance
from
shops
where
distance_sphere(35.628770,139.737295,lat,lng) <= 500 AND
category = 'レストラン'
order by
distance_sphere(35.628770,139.737295,lat,lng) ;
すんませんテストデータ作っていないので、結果はなしでｗ
まとめ
このストアドファンクションを利用すればいろいろな場面で活躍できるようになると思います。たとえば彼女とのデートの際、行くお店に迷った時、さっとこんなSQLで近いお店をリストアップなんてしてみれば、きっと惚れ直すこと必至です。
ちなみに、距離系のスマホアプリっていくつか出ているけど、メガヒットしたアプリってそんなにないね。。マネタイズが難しいのかな。