無料ブログはココログ

2018年8月18日 (土)

Excel VBA(Google mapの位置情報から2点間の距離を求めるマクロ)

今回も引き続きExcel VBAネタです。
今回は、タイトルの通り、Google map上の位置情報(緯度、経度)を与え2点間の距離を返す関数を作成してみました。

ここ最近ずっと複数ルートの効率的なルート作成を考えていまして、Aルートを走った後にBルートへ行ったほうが近いのかCルートへ行ったほうが近いのかはたまたDルートか、google map上で試行錯誤していると楽しいのだけれど時間がかかってしまって一発で求められないのかと。複数ルートの効率的な全体を取り纏めたルートを作成するのに頭を悩ませていました。

そこで、前回の記事で各ルートの緯度経度情報は取得できることが分かったので、各ルートの開始点と終了点の座標情報(ルート名、緯度、経度)を一覧化して、それぞれの距離を総当たりで求め、最短のルートを求めてみました。その際に作成したのが2点間の位置情報(緯度、経度)を与えてその距離を返す関数です。

数式自体はネットから拝借しました(一部数値を見直していますが)。この式は地球上の2点間を平面上と近似しています。地球は厳密には楕円体ということで、3次元の複雑な式になるようですが、国土地理院の距離を求めるサイトと比較して近い値が得られるので良しとしました。
※私もよくわかっていないので説明が間違っていたらすみません。

以下に関数を示します。戻り値の単位はKmとなります。
'-----------------------------------------------------
' 位置情報(緯度、経度)を与えて2点間の距離を返す関数
'-----------------------------------------------------
'pMoto_Ido   ・・・自分の緯度
'pMoto_Keido ・・・自分の経度
'pSaki_Ido   ・・・相手の緯度
'pSaki_Keido ・・・相手の経度
Function f_Getkyori(ByVal pMoto_Ido As Double, ByVal pMoto_Keido As Double, ByVal pSaki_Ido As Double, ByVal pSaki_Keido As Double) As Double
    f_Getkyori = 6378.137 * _
        Application.WorksheetFunction.Acos( _
            Cos(Application.WorksheetFunction.Radians(pMoto_Ido)) * Cos(Application.WorksheetFunction.Radians(pSaki_Ido)) * _
            Cos(Application.WorksheetFunction.Radians(pSaki_Keido) - Application.WorksheetFunction.Radians(pMoto_Keido)) + _
            Sin(Application.WorksheetFunction.Radians(pMoto_Ido)) * Sin(Application.WorksheetFunction.Radians(pSaki_Ido)) _
        )

End Function

'-----------------------------------------------------
' テスト
'-----------------------------------------------------
Sub test_saitan()
    '東京駅と宗谷岬との距離
    MsgBox f_Getkyori(35.6811673, 139.7670516, 45.5229025, 141.9365949) & "Km"

    '東京駅と佐多岬との距離
    MsgBox f_Getkyori(35.6811673, 139.7670516, 30.9950472, 130.6615718) & "Km"
End Sub

■東京駅と宗谷岬との距離
(1)マクロ実行結果

Tokyosouya_vba

(2)国土地理院サイト

Tokyosouya

■東京駅と佐多岬との距離
(1)マクロ実行結果

Tokyosata_vba

(2)国土地理院サイト
Tokyosata

上記の関数を使って以下のようなルート表(イメージ)が出来たので、複数のルートを纏めた全体のルートが作りやすくなりました。表の前提として、各ルートには複数の経由地があって、各ルートの開始点と終了点のみを抽出しているということです。経由地が2から始まっているものがあるのは、1が自宅の場合、自宅を除外しているためです。終了点が自宅の場合も除外しています。

<ルート表イメージ>
ルート名  経由順  緯度・経度         | 最短ルート名  経由順  緯度・経度
----------------------------------------------------------------
Aルート    1         35.xxxx,139.xxxx  | Bルート          5         35.xxxx,140.xxxx
Aルート    8         35.xxxx,139.xxxx  | Bルート          5         35.xxxx,140.xxxx
Bルート    2         35.xxxx,140.xxxx  | Cルート          7         36.xxxx,139.xxxx
Bルート    5         35.xxxx,140.xxxx  | Cルート          1         36.xxxx,139.xxxx
Cルート    1         36.xxxx,139.xxxx  | Bルート          5         35.xxxx,140.xxxx
Cルート    7         36.xxxx,139.xxxx  | Bルート          2         35.xxxx,140.xxxx

見方としては、Aルートの経由地1も経由地8も共にBルートの経由地5が一番近いことが分かります。Bルートの経由地5はCルートの経由地1が一番近いことが分かります。
このことから、Aルート→Bルート→Cルートか、Cルート→Bルート→Aルートを作成すれば良いことが分かります。

各ルートに10カ所近い経由地が設定されている場合、単純に1つの全体ルートは作成できませんが、いくつかに分割して全体のルートを作成する手助けになります。

これまでの試行錯誤よりも効率的に全体のルートを把握できて便利です。試しにAルートにBルートの経由地5をプロットしてみると最も近くに表示されますので実用上問題ないことが確認されました。

«Excel VBA(Google mapのルートを逆転させるマクロ)