DBで緯度経度から距離を計算する【ST_Distance_Sphere】

FUNCTION ST_Distance_Sphere does not exist

DBから緯度経度で距離を測りたかったので調べたら、
ST_Distance_Sphereというのが出てきたので、使ってみると

FUNCTION ST_Distance_Sphere does not exist

とでてきました。
調べてみると、関数作ってやってるのがちらほら見つかったので、
それを試してみました!

ちなみに、今回はGoogle Mapと比べたときに近いものを正とすることにしました!

ST_Distance_Sphere対応策1

CREATE FUNCTION st_distance_sphere(pt1 POINT, pt2 POINT)
RETURNS double(10,2)

RETURN 6371 * 2 * ASIN(
    SQRT(
        POWER(SIN((ST_Y(pt2) - ST_Y(pt1)) * pi()/180 / 2), 2) +
        COS(ST_Y(pt1) * pi()/180 ) *
        COS(ST_Y(pt2) * pi()/180) *
        POWER(SIN((ST_X(pt2) - ST_X(pt1)) * pi()/180 / 2), 2)
    )
);

https://stackoverflow.com/questions/59370894/mariadb-no-equivalent-function-for-st-distance-sphere

GoogleMapは、距離を測れるので、測って、そのポイントの座標をコピーしてみました! file
file
file
file

実行
SELECT  st_distance_sphere(ST_GeomFromText('POINT(35.41675663857636 139.4444246043602)', 4326), ST_GeomFromText('POINT(35.4273603630893 139.49807795163338)', 4326));

file

ST_Distance_Sphere対応策1の結果

GoogleMapでの距離(km) SQLで測定した距離(km) sql
5 6.03292 SELECT st_distance_sphere(ST_GeomFromText('POINT(35.41675663857636 139.4444246043602)', 4326), ST_GeomFromText('POINT(35.4273603630893 139.49807795163338)', 4326));
10 12.25690 SELECT st_distance_sphere(ST_GeomFromText('POINT(35.359669523998576 139.41483952535674)', 4326), ST_GeomFromText('POINT(35.362399076944264 139.5250489789686)', 4326));
20 23.025.88 SELECT st_distance_sphere(ST_GeomFromText('POINT(35.4413621153992 139.32684838854084)', 4326), ST_GeomFromText('POINT(35.362399076944264 139.5250489789686)', 4326));
30 34.16355 SELECT st_distance_sphere(ST_GeomFromText('POINT(35.490389024457265 139.23357231444186)', 4326), ST_GeomFromText('POINT(35.362399076944264 139.5250489789686)', 4326));
30(日本海あたり) 39.17912 SELECT st_distance_sphere(ST_GeomFromText('POINT(40.05580988260422 132.26834129722005)', 4326), ST_GeomFromText('POINT(40.04769173982489 131.9160370118804)', 4326));
30(オーストラリア) 32.85582 SELECT st_distance_sphere(ST_GeomFromText('POINT(-28.178954178842375 129.48291506251567)', 4326), ST_GeomFromText('POINT(-28.264354611366894 129.7733308598895)', 4326));
16,703.25(日本からブラジル) 16436.75247 SELECT st_distance_sphere(ST_GeomFromText('POINT(35.70570779301804 139.72368714593006)', 4326), ST_GeomFromText('POINT(-8.317515171375423 -53.089768109862995)', 4326));

近しい数値がでていますが、なんか納得できないですね、原因もわからないですし

ST_Distance_Sphere対応策2

CREATE FUNCTION st_distance_sphere2(latitude_1 double, longitude_1 double, latitude_2 double, longitude_2 double)
RETURNS double
return 6371 * acos(
    cos(radians(latitude_2))
    * cos(radians(latitude_1))
    * cos(radians(longitude_1) - radians(longitude_2))
    + sin(radians(latitude_2))
    * sin(radians(latitude_1))
);

https://blogenist.jp/2019/09/02/9037/#2

式を見ても正直わかりませんが、さっきのよりわかる人にはわかる感じですね。
おそらく球面距離計算がこれなんですかね?

まあ、さておき実行
SELECT st_distance_sphere(35.41675663857636, 139.4444246043602, 35.4273603630893, 139.49807795163338);

ST_Distance_Sphere対応策2の結果

GoogleMapでの距離(km) SQLで測定した距離(km) sql
5 5.0026400718603306 SELECT st_distance_sphere(35.41675663857636, 139.4444246043602, 35.4273603630893, 139.49807795163338);
10 9.998605479547313 SELECT st_distance_sphere(35.359669523998576, 139.41483952535674, 35.362399076944264, 139.5250489789686);
20 19.995053606802657 SELECT st_distance_sphere(35.4413621153992, 139.32684838854084, 35.362399076944264, 139.5250489789686);
30 30.000736562945722 SELECT st_distance_sphere(35.490389024457265, 139.23357231444186, 35.362399076944264, 139.5250489789686);
30(日本海あたり) 30.00017732408251 SELECT st_distance_sphere(40.05580988260422, 132.26834129722005, 40.04769173982489, 131.9160370118804);
30(オーストラリア) 29.996710971661383 SELECT st_distance_sphere(-28.178954178842375, 129.48291506251567, -28.264354611366894, 129.7733308598895);
16,703.25(日本からブラジル) 16703.21959731636 SELECT st_distance_sphere(35.70570779301804, 139.72368714593006, -8.317515171375423, -53.089768109862995);

ほぼ、同じですね、
ということは、地球は楕円型でスフィアではなくスフィロイドというらしいのですが
Googleでもこんな感じでやってるのかな?

6371ってなに?

地球の半径だそうです!
https://www.google.com/search?q=地球の半径は何キロですか

4326ってなに?

ちなみに、4326というのは「空間参照系」「WGS84・緯度経度座標」というものらしいです。

名称(空間参照系 空間参照系識別子(SRID)
世界測地系(JGD2011)・緯度経度座標系 EPSG:6668
世界測地系(JGD2011)・平面直角座標系Ⅰ系~ⅩⅨ系 EPSG:6669~6687
世界測地系(JGD2011)・UTM座標系(ZONE51~55) EPSG:6688〜6692
世界測地系(JGD2000)・緯度経度座標系 EPSG:4612
世界測地系(JGD2000)・平面直角座標系Ⅰ系~ⅩⅨ系 EPSG:2443~2461
世界測地系(JGD2000)・UTM座標系(ZONE51~55) EPSG:3097~3101
旧日本測地系(TOKYO Datum)・緯度経度座標系 EPSG:4301
旧日本測地系(TOKYO Datum)・平面直角座標系Ⅰ系~ⅩⅨ系 EPSG:30161~30179
旧日本測地系(TOKYO Datum)・UTM座標系(ZONE51~55) EPSG:102151~102156
WGS84・緯度経度座標 EPSG:4326
WEBメルカトル座標系 EPSG:3857

https://lonlat.info/spatialreferencesystem/

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です