Get bearing from 2 lat and long values

Tom A · Nov 19, 2015

Hi, I am getting lat and long co-ordinates from Google using their places API. I want to be able to get the bearing between two points.

The below javascript code works but I'm not sure how to port it to VBA:

Code:

  /**
  * Calculate the bearing between two positions as a value from 0-360
  * @param lat1 - The latitude of the first position
  * @param lng1 - The longitude of the first position
  * @param lat2 - The latitude of the second position
  * @param lng2 - The longitude of the second position
  *
  * @return int - The bearing between 0 and 360
  */
//http://stackoverflow.com/questions/11415106/issue-with-calcuating-compass-bearing-between-two-gps-coordinates
function bearing (lat1,lng1,lat2,lng2) {
  var dLon = (lng2-lng1);
  var y = Math.sin(dLon) * Math.cos(lat2);
  var x = Math.cos(lat1)*Math.sin(lat2) - Math.sin(lat1)*Math.cos(lat2)*Math.cos(dLon);
  var brng = this.toDeg(Math.atan2(y, x));
  return Math.round(360 - ((brng + 360) % 360));
  }


  // Converts numeric degrees to radians
  /**
  * Since not all browsers implement this we have our own utility that will
  * convert from degrees into radians
  *
  * @param deg - The degrees to be converted into radians
  * @return radians
  */
  function toRad(deg) 
  {
  return deg * Math.PI / 180;
  }


//convert from radians into degrees
  /**
  * Since not all browsers implement this we have our own utility that will
  * convert from radians into degrees
  * @param rad - The radians to be converted into degrees
  * @return degrees
  */
  function  toDeg (rad) {
  return rad * 180 / Math.PI;
  }

Thanks,
Tom

NARAYANK991 · Nov 19, 2015

Hi Tom ,

I do not know whether the calculations are correct , but I think the following code is a faithful reproduction of the code you posted :

Code:

Sub CallingRoutine()
'  Washington D.C. : 38.9047° N, 77.0164° W
'  New York        : 40.7127° N, 74.0059° W

     MsgBox bearing(38.9047, 77.0164, 40.7127, 74.0059)
End Sub

Function bearing(lat1, lng1, lat2, lng2)
'
'  * Calculate the bearing between two positions as a value from 0-360
'  * @param lat1 - The latitude of the first position
'  * @param lng1 - The longitude of the first position
'  * @param lat2 - The latitude of the second position
'  * @param lng2 - The longitude of the second position
'  *
'  * @return int - The bearing between 0 and 360
'  */
'  http://stackoverflow.com/questions/11415106/issue-with-calcuating-compass-bearing-between-two-gps-coordinates
   dLon = (lng2 - lng1)
  y = Math.Sin(dLon) * Math.Cos(lat2)
  x = Math.Cos(lat1) * Math.Sin(lat2) - Math.Sin(lat1) * Math.Cos(lat2) * Math.Cos(dLon)
  brng = toDeg(WorksheetFunction.Atan2(y, x))
  bearing = Math.Round(360 - ((brng + 360) \ 360))
End Function


Function toRad(deg)
'  Converts numeric degrees to radians
'
'  * Since not all browsers implement this we have our own utility that will
'  * convert from degrees into radians
'  *
'  * @param deg - The degrees to be converted into radians
'  * @return radians
'  */

   toRad = deg * WorksheetFunction.Pi / 180
End Function

Function toDeg(rad)
'  //convert from radians into degrees
'  /**
'  * Since not all browsers implement this we have our own utility that will
'  * convert from radians into degrees
'  * @param rad - The radians to be converted into degrees
'  * @return degrees
'  */

   toDeg = rad * 180 / WorksheetFunction.Pi
End Function

Narayan

Hui · Nov 19, 2015

I don't think that answer is correct as it returns an answer of Zero degrees fr the sample data and the answer should be either about 225 or 45 deg or 3.9 or 0.8 Radians

Have you read: http://www.movable-type.co.uk/scripts/latlong.html

Tom A · Nov 20, 2015

Hi Narayank, Thanks

The javascript code I'm using is based off the website the other person provided and I'm using that code on my site.
I'm getting 360 with your code with whatever lat and long values I use. I noticed the long values you are using are positive and much higher than the ones I have. I'm based in the UK and my long values are usually -1.000000 etc.

NARAYANK991 said:

Hi Tom ,

I do not know whether the calculations are correct , but I think the following code is a faithful reproduction of the code you posted :

Code:

Sub CallingRoutine()
'  Washington D.C. : 38.9047° N, 77.0164° W
'  New York        : 40.7127° N, 74.0059° W

     MsgBox bearing(38.9047, 77.0164, 40.7127, 74.0059)
End Sub

Function bearing(lat1, lng1, lat2, lng2)
'
'  * Calculate the bearing between two positions as a value from 0-360
'  * @param lat1 - The latitude of the first position
'  * @param lng1 - The longitude of the first position
'  * @param lat2 - The latitude of the second position
'  * @param lng2 - The longitude of the second position
'  *
'  * @return int - The bearing between 0 and 360
'  */
'  http://stackoverflow.com/questions/11415106/issue-with-calcuating-compass-bearing-between-two-gps-coordinates
   dLon = (lng2 - lng1)
  y = Math.Sin(dLon) * Math.Cos(lat2)
  x = Math.Cos(lat1) * Math.Sin(lat2) - Math.Sin(lat1) * Math.Cos(lat2) * Math.Cos(dLon)
  brng = toDeg(WorksheetFunction.Atan2(y, x))
  bearing = Math.Round(360 - ((brng + 360) \ 360))
End Function


Function toRad(deg)
'  Converts numeric degrees to radians
'
'  * Since not all browsers implement this we have our own utility that will
'  * convert from degrees into radians
'  *
'  * @param deg - The degrees to be converted into radians
'  * @return radians
'  */

   toRad = deg * WorksheetFunction.Pi / 180
End Function

Function toDeg(rad)
'  //convert from radians into degrees
'  /**
'  * Since not all browsers implement this we have our own utility that will
'  * convert from radians into degrees
'  * @param rad - The radians to be converted into degrees
'  * @return degrees
'  */

   toDeg = rad * 180 / WorksheetFunction.Pi
End Function

Narayan

NARAYANK991 · Nov 20, 2015

Hi Tom ,

There was a problem with the original code , since all values are to be in radians ; dLon needed to be converted to radians.

There was a second problem in my conversion ; I had used the \ symbol instead of the MOD function.

Try this revised code :

Code:

Function bearing(lat1, lng1, lat2, lng2)
'
'  * Calculate the bearing between two positions as a value from 0-360
'  * @param lat1 - The latitude of the first position
'  * @param lng1 - The longitude of the first position
'  * @param lat2 - The latitude of the second position
'  * @param lng2 - The longitude of the second position
'  *
'  * @return int - The bearing between 0 and 360
'  */
'  http://stackoverflow.com/questions/11415106/issue-with-calcuating-compass-bearing-between-two-gps-coordinates
   dlon = toRad(lng2 - lng1)
  y = Math.Sin(dlon) * Math.Cos(lat2)
  x = Math.Cos(lat1) * Math.Sin(lat2) - Math.Sin(lat1) * Math.Cos(lat2) * Math.Cos(dlon)
  brng = toDeg(WorksheetFunction.Atan2(y, x))
  bearing = Math.Round(((brng + 360) Mod 360))
End Function

The site I referred to is this :

http://www.movable-type.co.uk/scripts/latlong.html

Narayan

NARAYANK991 · Nov 20, 2015

Hi ,

More mistakes ; all angles need to be converted to radians ; also , very crucially , the Excel ATAN2 worksheet function needs to have its arguments reversed !

Try this :

Code:

Function bearing(lat1, lng1, lat2, lng2)
'
'  * Calculate the bearing between two positions as a value from 0-360
'  * @param lat1 - The latitude of the first position
'  * @param lng1 - The longitude of the first position
'  * @param lat2 - The latitude of the second position
'  * @param lng2 - The longitude of the second position
'  *
'  * @return int - The bearing between 0 and 360
'  */
'  http://stackoverflow.com/questions/11415106/issue-with-calcuating-compass-bearing-between-two-gps-coordinates
   dLon = toRad(lng2 - lng1)
  y = Math.Sin(dLon) * Math.Cos(toRad(lat2))
  x = Math.Cos(toRad(lat1)) * Math.Sin(toRad(lat2)) - Math.Sin(toRad(lat1)) * Math.Cos(toRad(lat2)) * Math.Cos(dLon)
  brng = toDeg(WorksheetFunction.Atan2(x, y))
  bearing = Math.Round(IIf(brng >= 0, brng, 360 + brng), 0)
End Function

Note the clarification given in the website :

This formula is for the initial bearing (sometimes referred to as forward azimuth) which if followed in a straight line along a great-circle arc will take you from the start point to the end point.

For final bearing, simply take the initial bearing from the end point to the start point and reverse it (using θ = (θ+180) % 360).

Narayan

Tom A · Nov 21, 2015

Thank you very much Narayank! I'm getting results close enough to my javascript code.
The vba function is giving results that can vary up to 18 degrees so far from the javascript function.

Is that to do with the accuracy of the pie and Atan / Cos functions used in Excel?
and re your note about the initial bearing, I'm blind and wanting to get an idea of where places are relative to my current location so I think this function does that.

NARAYANK991 said:

Hi ,

More mistakes ; all angles need to be converted to radians ; also , very crucially , the Excel ATAN2 worksheet function needs to have its arguments reversed !

Try this :

Code:

Function bearing(lat1, lng1, lat2, lng2)
'
'  * Calculate the bearing between two positions as a value from 0-360
'  * @param lat1 - The latitude of the first position
'  * @param lng1 - The longitude of the first position
'  * @param lat2 - The latitude of the second position
'  * @param lng2 - The longitude of the second position
'  *
'  * @return int - The bearing between 0 and 360
'  */
'  http://stackoverflow.com/questions/11415106/issue-with-calcuating-compass-bearing-between-two-gps-coordinates
   dLon = toRad(lng2 - lng1)
  y = Math.Sin(dLon) * Math.Cos(toRad(lat2))
  x = Math.Cos(toRad(lat1)) * Math.Sin(toRad(lat2)) - Math.Sin(toRad(lat1)) * Math.Cos(toRad(lat2)) * Math.Cos(dLon)
  brng = toDeg(WorksheetFunction.Atan2(x, y))
  bearing = Math.Round(IIf(brng >= 0, brng, 360 + brng), 0)
End Function

Note the clarification given in the website :

Narayan

NARAYANK991 · Nov 21, 2015

Hi Tom ,

From what I have understood , the following are the steps :

1. To get the initial bearing , given the following latitudes and longitudes of the two cities involved , assuming we are using Washington D.C. and New York as the two cities :

Washington D.C. : 38.9047° N, 77.0164° W
New York : 40.7127° N, 74.0059° W

The first call to the bearing function will use the following line of code :

MsgBox bearing(38.9047, -77.0164, 40.7127, -74.0059)

Remember that North and East are in the positive direction , while South and West are in the negative direction , which accounts for the signs in the above call.

The function will return the initial bearing value of 51 degrees.

Putting these same values in the website ( http://www.movable-type.co.uk/scripts/latlong.html ) gives the following result :

Initial bearing:
051°01′32″

2. To get the final bearing , follow what the website says :

take the initial bearing from the end point to the start point and reverse it (using θ = (θ+180) % 360).

The second call to the bearing function will use the following line of code :

MsgBox bearing(40.7127, -74.0059,38.9047, -77.0164)

The function will return the bearing value of 233 degrees.

Using this as the value of θ , and using the equation given viz. (θ+180) % 360 , we get :

(233 + 180) Mod 360

which gives : 413 Mod 360 i.e. 53.

Putting these same values in the website ( http://www.movable-type.co.uk/scripts/latlong.html ) gives the following result :

Final bearing:
052°57′12″

Narayan

Get bearing from 2 lat and long values

Tom A

Member

NARAYANK991

Excel Ninja

Hui

Excel Ninja

Tom A

Member

NARAYANK991

Excel Ninja

NARAYANK991

Excel Ninja

Tom A

Member

NARAYANK991

Excel Ninja