Derive bearing & direction from a declared decimal degree position

[Aussiebear]

Following on from the "Angle between two coordinates" thread I was interested in trying to find the bearing to and distance to from the current position.

If your current position is 22.840850 S & 150.944467 E and you are targeting a new position of 20.844200 S & 150.938867 E, how do I determine the -
bearing and distance? From the "Angle between two coordinates" thread I now know that the angle is found by =ATAN2(Difference in X, Difference in Y)*180/Pi
In this case its =Atan@(1.99665, 0.0056)*180/3.14159265358979 or 0.160697 degrees which in real terms is useless to me. Primarily because its from the point of origin -
rather than from the centre point and X axis is Zero degrees rather than the top of Y axis.

Chip Pearson has a distance calculator for the difference in Latitude & Longatude which I'd like to use for the distance calculation, but its the bearing that I'm currently confused by.

Any assistance is greatly appreciated.

[SamT]

The X Axis = 90o, so the bearing = D - Result, where D is the difference (E - N) between North(~0o) and East(90o), where North is either Magnetic North or True north, depending on your navigation methods..

x = D - (.Atan2((y2 - y1), (x2 - x1)) * 180# / .Pi)

[Aussiebear]

Thank you

[Aussiebear]

Using the following data X1= 22.840850, X2= 22.831667, Y1= 150.944467, Y2= 150.997000 and using your formula without the dots, I end up with a resulting negative figure -19.781751 which then rounds down to -20 degrees. Do I simply subtract that from 360 to get my true bearing?

[SamT]

That would be bearing 20o south of East. Correct?
Subtract -20 from 90 gives 110o.
If my caffeine deprived brain is working.

Run the math for the coordinates of the north and south poles and see what you get.

[Paul_Hossler]

These are based on aviation, but air and water are the same aren't they??? :think:

All I did was to put the math into VBA (there was a link XLS, but it was above my head)

There was a worked example for LAX to JFK and this gives the same numbers (always a nice thing to have happen)




Option Explicit

'http://williams.best.vwh.net/avform.htm#Crs
'A sample implementation of many of these formulae in the form of an Excel spreadsheet can be found here. (link)
'The formulae are in VBA macros, for readability, so you will need to enable macros to have them work.
'If you are unable to open the spreadsheet with macros disabled (to check for viruses) etc, then you may
'need to patch your Excel. Try http://office.microsoft.com/downloaddetails/Xl8p9pkg.htm for Excel 97 SR-2.
'If you decide to program up these formulae, you'd be well-advised to look at the implementation notes
'and check your results against the worked examples and spreadsheets.

Function DistanceAndBearing(Lat1 As Double, Long1 As Double, Lat2 As Double, Long2 As Double) As Variant

Const Epislon As Double = 10 ^ -6
Dim Lat1Rad As Double, Long1Rad As Double, Lat2Rad As Double, Long2Rad As Double
Dim BearingRad As Double, DistRad As Double

'(1) = distance, v(2) = bearing
Dim v(1 To 2) As Double

With Application.WorksheetFunction

Lat1Rad = .Radians(Lat1)
Long1Rad = .Radians(Long1)
Lat2Rad = .Radians(Lat2)
Long2Rad = .Radians(Long2)

DistRad = .Acos(Sin(Lat1Rad) * Sin(Lat2Rad) + Cos(Lat1Rad) * Cos(Lat2Rad) * Cos(Long1Rad - Long2Rad))
v(1) = DistRad * 180 * 60 / .Pi

If Cos(Lat1Rad) < Epislon Then
If Lat1Rad > 0 Then
BearingRad = .Pi ' starting from N pole
Else
BearingRad = 2 * .Pi ' starting from S pole
End If

ElseIf Sin(Long2Rad - Long1Rad) < 0# Then
BearingRad = .Acos((Sin(Lat2Rad) - Sin(Lat1Rad) * Cos(DistRad)) / (Sin(DistRad) * Cos(Lat1Rad)))

Else
BearingRad = -.Acos((Sin(Lat2Rad) - Sin(Lat1Rad) * Cos(DistRad)) / (Sin(DistRad) * Cos(Lat1Rad)))
End If


v(2) = .Degrees(BearingRad)


End With

DistanceAndBearing = v
End Function

[Aussiebear]

Thanks Sam & Paul. I'm currently away choking on coal dust, and my iPad doesn't allow me to play with excel sheets. Will have a play with it on return to civilisation next tuesday

[Aussiebear]

I've just about done my head in here over this issue. Using the following values Lat1 =-22.654233, Lat2 =-22.629500, Lon1 = 150.960400, Lon2 = 150.178500, in cells B1:B4, I am getting vastly different answers using a number of suggestions.

Sam's suggestion of using =90-(ATAN2((Lat2-Lat1),(Lon2-Lon1)))*180/3.14159 gives a result of 6.469839; but then I started on the first beer; and

Paul's suggestion of the function DistanceAndBearing entered as an array formula =DistanceAndBearing(B1,B3,B2,B4) gives a #Name error result. Is it because I've failed to set the ranges correctly?

Function DistanceAndBearing(Lat1 As Double, Long1 As Double, Lat2 As Double, Long2 As Double) As Variant
Const Epislon As Double = 10 ^ -6
Dim Lat1Rad As Double, Long1Rad As Double, Lat2Rad As Double, Long2Rad As Double
Dim BearingRad As Double, DistRad As Double
'(1) = distance, v(2) = bearing
Dim v(1 To 2) As Double
With Application.WorksheetFunction
Lat1Rad = .Radians(Lat1)
Long1Rad = .Radians(Lon1)
Lat2Rad = .Radians(Lat2)
Long2Rad = .Radians(Lon2)
DistRad = .Acos(Sin(Lat1Rad) * Sin(Lat2Rad) + Cos(Lat1Rad) * Cos(Lat2Rad) * Cos(Long1Rad - Long2Rad))
v(1) = DistRad * 180 * 60 / .Pi
If Cos(Lat1Rad) < Epislon Then
If Lat1Rad > 0 Then
BearingRad = .Pi ' starting from N pole
Else
BearingRad = 2 * .Pi ' starting from S pole
End If
ElseIf Sin(Long2Rad - Long1Rad) < 0# Then
BearingRad = .Acos((Sin(Lat2Rad) - Sin(Lat1Rad) * Cos(DistRad)) / (Sin(DistRad) * Cos(Lat1Rad)))
Else
BearingRad = -.Acos((Sin(Lat2Rad) - Sin(Lat1Rad) * Cos(DistRad)) / (Sin(DistRad) * Cos(Lat1Rad)))
End If
v(2) = .Degrees(BearingRad)
End With
DistanceAndBearing = v
End Function


So I went looking elsewhere, and despite the desk now being utterly covered in empty stubbies, ended up at Experts Exchange.com where I found this bearing formula;
'=Degrees(ATAN2(COS(Radians(Lat1))*SIN(RADIANS(Lat2))-SIN(RADIANS(Lat1))*COS(RADIANS(Lat2))*COS(RADIANS(Lon2-Lon1)),SIN(RADIANS(Lon2-Lon1))*Cos(Radians(Lat2)))
Result = -88.187583 decimal degrees. I have no idea if this is right or wrong at this point so I grabbed a rather generous rum and dry and ventured forth;

I ended up looking at Answers.Microsoft.com and found the following themes;
'=ATAN2(COS(LAT1)*SIN(Lat2)-Sin(Lat1)*COS(Lat2)*COS(Lon2-Lon1),SIN(Lon2-Lon1)*COS(lat2))
Resulting 1.77779398 radians or 101.860178 degrees. Doesn't even come close to matching any of the above. ( I've PM the Bride by now to see if she would supply another rum, but no such luck... lovely girl but does have a minor fault me thinks)

Being the ultimate glutton of mental anguish, I tried yet another theme and this one is a bell ringer ( anything looks good after 6 of my rums);
'=IMARGUMENT(Complex(Lat1-Lat2),(Lon1-Lon2))*180/3.14159+180
By now Excel was telling me it didn't recognise the function.... so after much glaring and thumping the desk as one does in these times, I tried breaking it down.
IMARGUMENT apparently returns an expression as an angle in radians, whereas Complex converts real and imaginary numbers into a complex number.
Excel then threw a spanner in the works by giving a #Value error.

Anyone want to try and save my kidneys, there's a book attached.

[Paul_Hossler]

G'day --

1. I moved the Paul Function to a standard module so that the worksheet would find it

2. When I did that, there were 2 undefined variables that Option Explicit caught (Lon1 instead of Long1, Lon2 instead of Long2)

3. Did you Select the two cells on the worksheet and Control-Shift-Enter?

Seems to work (see picture and attachments) or at least return numbers. I'm going by the source's convention for North lat being +, etc.

It's easy enough to break it into 2 functions if you want the results separately or use INDEX()

(Good luck -- we can't have you getting lost at sea :rotlaugh:

[Paul_Hossler]

Also, when you use IMARGUMENT, it needed a little tweak

[SamT]

Following on from the "Angle between two coordinates" thread I was interested in trying to find the bearing to and distance to from the current position.

If your current position is 22.840850 S & 150.944467 E and you are targeting a new position of 20.844200 S & 150.938867 E, how do I determine the -
bearing and distance? From the "Angle between two coordinates" thread I now know that the angle is found by =ATAN2(Difference in X, Difference in Y)*180/Pi
In this case its =Atan@(1.99665, 0.0056)*180/3.14159265358979 or 0.160697 degrees which in real terms is useless to me. Primarily because its from the point of origin -
rather than from the centre point and X axis is Zero degrees rather than the top of Y axis.

Chip Pearson has a distance calculator for the difference in Latitude & Longatude which I'd like to use for the distance calculation, but its the bearing that I'm currently confused by.

Any assistance is greatly appreciated.



So I looked at Google maps and input those two coordinates and it appears as if the second position is due north of the first. My eyeballs are only calibrated to +- 13.7 degrees, but a heading of .160697 should get you there.

Are you comparing any of the calculated results to what you see on your charts?

For comparison to reality, note that google puts the first position about 10 land miles north of The Kepples and the second on the west side of Cockatoo Reef.

[SamT]

You can use this online calculator to check your results.
http://www.coastalboating.net/Resources/Navigation/Calculators/Mercator.html

I did use google maps to compare some of your results to real life and it kinda looked to me as if the result use the top of the Y axis as north and the results as the deviation from north. I don't know what that really means, 'cuz it is google.

Other resources
http://www.ngs.noaa.gov/PC_PROD/Inv_Fwd/
http://gpsinformation.net/main/transformation.htm

[Aussiebear]

Did you Select the two cells on the worksheet and Control-Shift-Enter? :omg2: I think I skipped that bit in the manual.

[Aussiebear]

Having had a quick play around with your amended workbook Paul I have found that;

Paul's Function result is 88.1875835
Experts Exchange result is -88.18758348, and
Sam's result if I remove the "90-" at the start of the formula -88.188320296

Which numbers are correct? Am I correct in assuming they are all coming off the x axis? Is there something within Paul's function that has caused the positive result?

[SamT]

Hard to say without knowing what the result should be. What is the bearing from one point to the other in real life?

All I know for sure is that addition or subtraction involving Zero/360, 90, 180 or 270 is required

[Paul_Hossler]

Apologies, I entered your longitude as West (positive). For East longitudes, the input is negative, just like for South latitudes.

(I added the bold from the web site I referenced)

With that change in input, the bearing is -88. something. I would expect the distance would be in NM



Option Explicit

'http://williams.best.vwh.net/avform.htm#Crs
'A sample implementation of many of these formulae in the form of an Excel spreadsheet can be found here. (link)
'The formulae are in VBA macros, for readability, so you will need to enable macros to have them work.
'If you are unable to open the spreadsheet with macros disabled (to check for viruses) etc, then you may
'need to patch your Excel. Try http://office.microsoft.com/downloaddetails/Xl8p9pkg.htm for Excel 97 SR-2.
'If you decide to program up these formulae, you'd be well-advised to look at the implementation notes
'and check your results against the worked examples and spreadsheets.

'For the convenience of North Americans I will take North latitudes and West longitudes as positive and South and East negative.
'The longitude is the opposite of the usual mathematical convention. True course is defined as usual, as the angle between
'the course line and the local meridian measured clockwise.

' Since by definition, one nautical mile subtends one minute (=1/60 degree) of arc,

Function DistanceAndBearing(Lat1 As Double, Long1 As Double, Lat2 As Double, Long2 As Double) As Variant

Const Epislon As Double = 10 ^ -6
Dim Lat1Rad As Double, Long1Rad As Double, Lat2Rad As Double, Long2Rad As Double
Dim BearingRad As Double, DistRad As Double

'(1) = distance, v(2) = bearing
Dim v(1 To 2) As Double

With Application.WorksheetFunction

Lat1Rad = .Radians(Lat1)
Long1Rad = .Radians(Long1)
Lat2Rad = .Radians(Lat2)
Long2Rad = .Radians(Long2)

DistRad = .Acos(Sin(Lat1Rad) * Sin(Lat2Rad) + Cos(Lat1Rad) * Cos(Lat2Rad) * Cos(Long1Rad - Long2Rad))
v(1) = DistRad * 180 * 60 / .Pi

If Cos(Lat1Rad) < Epislon Then
If Lat1Rad > 0 Then
BearingRad = .Pi ' starting from N pole
Else
BearingRad = 2 * .Pi ' starting from S pole
End If

ElseIf Sin(Long2Rad - Long1Rad) < 0# Then
BearingRad = .Acos((Sin(Lat2Rad) - Sin(Lat1Rad) * Cos(DistRad)) / (Sin(DistRad) * Cos(Lat1Rad)))

Else
BearingRad = -.Acos((Sin(Lat2Rad) - Sin(Lat1Rad) * Cos(DistRad)) / (Sin(DistRad) * Cos(Lat1Rad)))
End If


v(2) = .Degrees(BearingRad)

End With

DistanceAndBearing = v
End Function




14325

[Aussiebear]

Paul, yes the distance is correct, its just that I've yet to gain confidence in the bearing data. Should it be something similar to Sam's initial suggestion that the bearing is from the (EAST) X axis or is it truly from the (NORTH) Y axis? Also I read somewhere that North and East are positive and that South & West are negative.

[Paul_Hossler]

I was using the source material's conventions, since my only sailing experience involves a little yellow rubber ducky and a bath tub



For the convenience of North Americans I will take North latitudes and West longitudes as positive and South and East negative.



But using the original macro ...



'http://williams.best.vwh.net/avform.htm#Crs
'A sample implementation of many of these formulae in the form of an Excel spreadsheet can be found here. (link)
'The formulae are in VBA macros, for readability, so you will need to enable macros to have them work.
'If you are unable to open the spreadsheet with macros disabled (to check for viruses) etc, then you may
'need to patch your Excel. Try http://office.microsoft.com/downloaddetails/Xl8p9pkg.htm for Excel 97 SR-2.
'If you decide to program up these formulae, you'd be well-advised to look at the implementation notes
'and check your results against the worked examples and spreadsheets.

'Latitude: North +, South -
'Longitude: East +, West -

Function DistanceAndBearing(Lat1 As Double, Long1 As Double, Lat2 As Double, Long2 As Double) As Variant

Const Epislon As Double = 10 ^ -6
Dim Lat1Rad As Double, Long1Rad As Double, Lat2Rad As Double, Long2Rad As Double
Dim BearingRad As Double, DistRad As Double

'(1) = distance, v(2) = bearing
Dim v(1 To 2) As Double

With Application.WorksheetFunction

Lat1Rad = .Radians(Lat1)
Long1Rad = .Radians(Long1)
Lat2Rad = .Radians(Lat2)
Long2Rad = .Radians(Long2)

DistRad = .Acos(Sin(Lat1Rad) * Sin(Lat2Rad) + Cos(Lat1Rad) * Cos(Lat2Rad) * Cos(Long1Rad - Long2Rad))
v(1) = DistRad * 180 * 60 / .Pi

If Cos(Lat1Rad) < Epislon Then
If Lat1Rad > 0 Then
BearingRad = .Pi ' starting from N pole
Else
BearingRad = 2 * .Pi ' starting from S pole
End If

ElseIf Sin(Long2Rad - Long1Rad) > 0# Then
BearingRad = .Acos((Sin(Lat2Rad) - Sin(Lat1Rad) * Cos(DistRad)) / (Sin(DistRad) * Cos(Lat1Rad)))

Else
BearingRad = -.Acos((Sin(Lat2Rad) - Sin(Lat1Rad) * Cos(DistRad)) / (Sin(DistRad) * Cos(Lat1Rad)))
End If


v(2) = .Degrees(BearingRad)


End With

DistanceAndBearing = v
End Function





... and round numbers test (Due East, etc.) the bearing appears to correct (or at least reasonable) to my land lubber's eye

14334

[SamT]

Also I read somewhere that North and East are positive and that South & West are negative.


http://www.vbaexpress.com/forum/attachment.php?attachmentid=14325&d=1441503050

As long as you stay within about 1600 land miles to the East of Australia, try entering the longitude in positive values. Longs are given in degrees from Greenwich, positive to the East and negative toward the USA.

Top of the head / Napkin calculations:
That means that Long2 above is about .8o West of Long1 and that Lat2 is about 0.024o North of Lat1. If you head due East, (90o on your compass,) you would eventually come real close to your destination

There are 60 nautical miles in each degree North/South and at the equator, 60 nautical miles in each degree East/West. At 22oS, Nautical Miles East/West are about 10% shorter/Degree or about 54nm/Degree.

Your destination is about (60x0.024 =) 1.44nm north and (54x.8=) 43.2nm West of the starting point.

For checking VBA formulas, you can get an approximation with simple math

EWnm = (Long1 - Long2) x 54nm
NSnm = (Lat1 - Lat2) x 60nm
Distance (hypotenuse) = Square Root(NSnm2 + EWnm2) [(NSnm2 + EWnm2) ^0.5]
Heading = Tan(NSnm / EWnm)

[Aussiebear]

@Paul, so for all my results if the value is negative then I should take the value from 360?

@Sam, I'm not nuclear powered my friend...

That means that Long2 above is about .8o West of Long1 and that Lat2 is about 0.024o North of Lat1. If you head due East, (90o on your compass,) you would eventually come real close to your destination.

[Paul_Hossler]

Well, the way I read it ...

If the bearing is Positive (0 <= B <= 180), it's East of north, and

If the bearing is Negative (0 >= B >= -180), it's West of north

So if it were -90 (due West) and you added 180, your bearing would be 270

Is that the way it works?



Option Explicit

'http://williams.best.vwh.net/avform.htm#Crs
'A sample implementation of many of these formulae in the form of an Excel spreadsheet can be found here. (link)
'The formulae are in VBA macros, for readability, so you will need to enable macros to have them work.
'If you are unable to open the spreadsheet with macros disabled (to check for viruses) etc, then you may
'need to patch your Excel. Try http://office.microsoft.com/downloaddetails/Xl8p9pkg.htm for Excel 97 SR-2.
'If you decide to program up these formulae, you'd be well-advised to look at the implementation notes
'and check your results against the worked examples and spreadsheets.

'Latitude: North +, South -
'Longitude: East +, West -

Function DistanceAndBearing(Lat1 As Double, Long1 As Double, Lat2 As Double, Long2 As Double) As Variant

Const Epsilon As Double = 10 ^ -6

Dim Lat1Rad As Double, Long1Rad As Double, Lat2Rad As Double, Long2Rad As Double
Dim BearingRad As Double, DistRad As Double

'(1) = distance, v(2) = bearing
Dim v(1 To 2) As Double

With Application.WorksheetFunction

Lat1Rad = .Radians(Lat1)
Long1Rad = .Radians(Long1)
Lat2Rad = .Radians(Lat2)
Long2Rad = .Radians(Long2)

DistRad = .Acos(Sin(Lat1Rad) * Sin(Lat2Rad) + Cos(Lat1Rad) * Cos(Lat2Rad) * Cos(Long1Rad - Long2Rad))
v(1) = DistRad * 180 * 60 / .Pi

If Cos(Lat1Rad) < Epsilon Then
If Lat1Rad > 0 Then
BearingRad = .Pi ' starting from N pole
Else
BearingRad = 2 * .Pi ' starting from S pole
End If

ElseIf Sin(Long2Rad - Long1Rad) > 0# Then
BearingRad = .Acos((Sin(Lat2Rad) - Sin(Lat1Rad) * Cos(DistRad)) / (Sin(DistRad) * Cos(Lat1Rad)))

Else
BearingRad = 2 * .Pi - .Acos((Sin(Lat2Rad) - Sin(Lat1Rad) * Cos(DistRad)) / (Sin(DistRad) * Cos(Lat1Rad)))
End If

If Long1 = Long2 And Lat1 <= Lat2 Then BearingRad = 0#

v(2) = .Degrees(BearingRad)

End With

DistanceAndBearing = v
End Function


Then this will give you a bearing from 0 to 359.999 -- I put a check in for Going North because I thought the 360 degrees looked ugly

Based on my test data for NEWS it seems to look ok:


14335

[Aussiebear]

Paul, this my last opportunity for a few days to respond here to this thread. I have attached a sample workbook to show you the results I'm obtaining. I notice that your results are all positive whereas mine are both positive and negative. Do you have a method of plotting out the series of marks on a graph to visualise the results?

Sam, entering both sets of Lat Long without the correct Negative Positive formats gives different results

[Aussiebear]

Paul, I have found that if I add 180 to the bearing result in your function the results are very close to those obtained in the uk movable scripts results.

[Aussiebear]

uhoh.... spoke to soon.

The mathematics is killing me. The variances in the results is like trying pick the winner of the melbourne cup but you don't know who is running.

[Paul_Hossler]

That is certainly confusing: works for some and not for others

I'm trying to see if there is anything unusual about the bottom table data

[Aussiebear]

Since your in the mood to be confuzzled ( Aussie slang for Confused status at Def Con 1).... I ran into a site deathpod3000.wordpress.com, where the author suggests that the spherical law of cosines can be used to compute distance between 2 lattitude longitude points.
The law of cosines can be expressed in Excel as:
=ACOS((COS(LAT1)*COS(LAT2)*COS((-1*LON2)-(-1*LON2)))+(SIN(LAT1)*SIN(LAT2)))*r
All latitude & Longitude data needs to be converted into radians by using
=Degrees*PI()/180.

So if you were using for example -23.40155, 151.155533, -23.3979, 151.151717 this becomes -0.40834096, 2.638161733, -0.408370393, 2.638095132 and applying the above formula gives the result of 0.349524637 when r = 3959 (3959 for miles, 6371 for klms))

From there the author goes on to derive both DLat (Lat2-Lat1), DLon (Lon2-Lon1), a =(SIN(DLat/2))^2+COS(Lat1)*COS(Lat2)*(SIN(DLon/2))^2, & c =2*ATAN2(SQRT(1-a),SQRT(a)). By multiplying a & c we arrive at the Haversine Distance,( which just happens to match the Law of Cosines Distance 0.34952637.

Next the author gets a little funky (if trigonometry can be called that) to calculate the interim bearing by using
=MOD(ATAN2((COS(Lat1)*SIN(Lat2))-(SIN(Lat1)*COS(Lat2)*COS(Lon2-Lon1)), SIN(Lon2-Lon1)*COS(lat2)),2*PI()) which in turn is converted into bearing degrees by using *180/PI() giving a result of 316.183425 degrees.

Anybody still reading this?

So the bearing and distance is 316.183425degrees and 0.34952637 miles

[Paul_Hossler]

1. I made all S lats negative, and all E longs positive (col C and D)
2. I suspect that there was a data entry error on row 18 because the differences were so large (Col T and U) between the function and moveable-type.com. When I entered I got different numbers

14425


3. I took the mod 360 out of the move-able type code since that returns Longs. I wanted Doubles



Option Explicit
'http://www.movable-type.co.uk/scripts/latlong.html
'signed decimal degrees without compass direction, where negative indicates west/south (e.g. 40.7486, -73.9864)
Function DistanceAndBearing(Lat1 As Double, Long1 As Double, Lat2 As Double, Long2 As Double) As Variant

Const Epislon As Double = 10 ^ -6
Dim Lat1Rad As Double, Long1Rad As Double, Lat2Rad As Double, Long2Rad As Double
Dim BearingRad As Double, DistRad As Double

'(1) = distance, v(2) = bearing
Dim v(1 To 2) As Double

With Application.WorksheetFunction

Lat1Rad = .Radians(Lat1)
Long1Rad = .Radians(Long1)
Lat2Rad = .Radians(Lat2)
Long2Rad = .Radians(Long2)

DistRad = .Acos(Sin(Lat1Rad) * Sin(Lat2Rad) + Cos(Lat1Rad) * Cos(Lat2Rad) * Cos(Long2Rad - Long1Rad))
v(1) = .Degrees(60# * DistRad) ' convert to nm

BearingRad = .Atan2(Cos(Lat1Rad) * Sin(Lat2Rad) - Sin(Lat1Rad) * Cos(Lat2Rad) * Cos(Long2Rad - Long1Rad), Sin(Long2Rad - Long1Rad) * Cos(Lat2Rad))


'Since atan2 returns values in the range -p ... +p (that is, -180° ... +180°),
'to normalise the result to a compass bearing (in the range 0° ... 360°, with -ve values
'transformed into the range 180° ... 360°), convert to degrees and then
'use (?+360) % 360, where % is (floating point) modulo.
'For final bearing, simply take the initial bearing from the end point to the start
'point and reverse it (using ? = (?+180) % 360).

BearingRad = .Degrees(BearingRad)
BearingRad = BearingRad + 360#
If BearingRad >= 360# Then
v(2) = BearingRad - 360
Else
v(2) = BearingRad
End If


End With

DistanceAndBearing = v
End Function





Look over

[Paul_Hossler]

I thought I'd throw this in also, I got tired of converting DMS to Decimals, esp. since there seems to be many ways of writing them. It's kind of brute force, but I'm wordy and prefer simple, straight through code





Option Explicit

Sub drv()
MsgBox DMS2Dec("123.45")
MsgBox DMS2Dec("-123.45W")
MsgBox DMS2Dec("123.45W")
MsgBox DMS2Dec("N34.56")
MsgBox DMS2Dec("145.67w")
MsgBox DMS2Dec("23°45'30.123""n")
MsgBox DMS2Dec("W123°45'30.123""")
MsgBox DMS2Dec("W123:30:30.000")
MsgBox DMS2Dec("23.30.30.123n")
If IsError(DMS2Dec("aaaaaaan")) Then
MsgBox "Oops"
Else
MsgBox DMS2Dec("aaaaaaan")
End If
End Sub

'Degrees/Minutes/Seconds to Decimal Degrees
'If W or S passed, then negative
'If E or N or nothing, then assumed positive
'Examples:
' N34.56 or 34.56N or 34.56
' 145.67W or W145.67 or 145.67
' 23°45'30.123N
' W123:30:30.000
' 23.30.30.123N

Function DMS2Dec(s As Variant) As Variant
Dim n As Long
Dim bSouthOrWest As Boolean
Dim dDegrees As Double, dMinutes As Double, dSeconds As Double
Dim v As Variant

On Error GoTo NiceExit

'South or West is negative
n = InStr(1, s, "N", vbTextCompare)
If n > 0 Then
bSouthOrWest = False
s = Left(s, n - 1) & Right(s, Len(s) - n)
End If
n = InStr(1, s, "E", vbTextCompare)
If n > 0 Then
bSouthOrWest = False
s = Left(s, n - 1) & Right(s, Len(s) - n)
End If
n = InStr(1, s, "S", vbTextCompare)
If n > 0 Then
bSouthOrWest = True
s = Left(s, n - 1) & Right(s, Len(s) - n)
End If
n = InStr(1, s, "W", vbTextCompare)
If n > 0 Then
bSouthOrWest = True
s = Left(s, n - 1) & Right(s, Len(s) - n)
End If


'if only number
If IsNumeric(s) Then
'negitive passed with S or W in already
If CDbl(s) < 0 Then
DMS2Dec = IIf(bSouthOrWest, CDbl(s), -CDbl(s))
Else
DMS2Dec = IIf(bSouthOrWest, -CDbl(s), CDbl(s))
End If
Exit Function
End If


' 123°45'30.123"
n = InStr(1, s, Chr(176), vbTextCompare)
If n > 0 Then
dDegrees = CDbl(Left(s, n - 1))
s = Right(s, Len(s) - n)
n = InStr(1, s, "'", vbTextCompare)
If n > 0 Then
dMinutes = CDbl(Left(s, n - 1))
s = Right(s, Len(s) - n)
End If
n = InStr(1, s, Chr(34), vbTextCompare)
If n > 0 Then
dMinutes = CDbl(Left(s, n - 1))
End If

DMS2Dec = IIf(bSouthOrWest, -1#, 1#) * (dDegrees + dMinutes / 60# + dSeconds / 3600#)
Exit Function
End If

'123:45:67.890"
n = InStr(1, s, ":", vbTextCompare)
If n > 0 Then
v = Split(s, ":")
If UBound(v) >= 0 Then dDegrees = CDbl(v(0))
If UBound(v) >= 1 Then dMinutes = CDbl(v(1))
If UBound(v) >= 2 Then dSeconds = CDbl(v(2))

DMS2Dec = IIf(bSouthOrWest, -1#, 1#) * (dDegrees + dMinutes / 60# + dSeconds / 3600#)
Exit Function
End If


'123.45.67.890"
v = Split(s, ".")
If UBound(v) >= 0 Then dDegrees = CDbl(v(0))
If UBound(v) >= 1 Then dMinutes = CDbl(v(1))
If UBound(v) >= 2 Then dSeconds = CDbl(v(2))
If UBound(v) >= 3 Then dSeconds = CDbl(v(2) & "." & v(3))

DMS2Dec = IIf(bSouthOrWest, -1#, 1#) * (dDegrees + dMinutes / 60# + dSeconds / 3600#)
Exit Function

NiceExit:
DMS2Dec = CVErr(xlErrNA)
End Function

[SamT]

From: Expected end of statement error (http://www.vbaexpress.com/forum/showthread.php?53786-Expected-end-of-statement-error)


Lat1.Value = CRad(DMS2Dec(txtLat1.Value, "."))


Function DMS2Dec(DMSDegree As String, Delimiter As String) As Double
Dim tmp As Variant
tmp = Split(DMSDegree, Delimiter, 3)
DMS2Dec = CDbl(tmp(0)) + CDbl(tmp(1)) / 60 + CDbl(tmp(2)) / 3600
End Function


Function CRad(DecimalDegree As Double) As Double
Const rad As Double = 3.14159265358979 / 180
CRad = DecimalDegree * rad
End Function

[Paul_Hossler]

@AB - did you have a chance to try the function in #27?

[Aussiebear]

Yes Paul, its in the workbook. I'm a bit tied up at the moment with life

[Aussiebear]

@Paul, Sam & P45cal, I owe you guys an apology, for misleading you in this issue. But before you rush out to find a good hanging tree, may I offer in my mere mortal defence that I failed to understand the initial data correctly.

The GPS string eg. 22.40.070, is degree, minute decimal value, but I had treated it as degree minute seconds value, so when trying to convert to decimal degrees rather than using the

="-"&LEFT(A1,2)&"."&(MID(1,4,2)*60+(RIGHT(1,3)*3600))

which would have have worked IF it was degree minute seconds, I should have been using

="-"&LEFT(A1,2)+(RIGHT(A1,6)/60)-TRUNC(RIGHT(A1,6)/60)


This makes the new data and the results derived there from, much closer to the data that I have arrived at using my Navionics app. When I say "closer" the distance is close but still varies, but the bearing is woeful. Will post an example workbook this weekend.

[SamT]

Function DegreesDecimal(GPS_Reading As String) As Double
Dim Temp As Variant
Temp = Split(GPS_Reading, ".")
DegreesDecimal = -1 * (Temp(0) + CDbl("." & Temp(1) & Temp(2)) / 60)
End Function