Dec 13 2021 03:35 PM
Dec 13 2021 03:35 PM
I am trying to convert the lat and long coordinates I have into nautical miles. I read here that I had to convert my lat and long (in degrees) to radians which I did by using the =RADIANS function. My situation is as follows:
I have a start and finish lat and long coordinates.
What I am trying to do is find the distance in miles between start and finish.
When entering the =RADIANS function for the start lat and long I was able to get the RADIANS output (hopefully it is correct). However, with the end lat and long I get this format (above image) when inputting the exact function! I don't know how to remedy the issue for the end lat and long. What am I doing wrong here? I believe the data type for both start and end are the same and shouldn't affect the output of the function.
Additionally, I also read here that I should use the =ACOS formula. I have not succeeded in finding clear-cut directions anywhere to get the answer that I need. I did review Thomas Blum's YouTube video here but that still didn't help me with my RADIANS problem and I wasn't transposing any data like he was in the video.
If anyone has simple clear-cut directions step-by-step on how to transform lat and long coordinates into nautical miles (or km) I would greatly appreciate it!
Dec 13 2021 07:08 PM
@zgoldflo so you have a couple of things going on here. First it appears your cell format is set to date and that is why excel is trying to give you some date for the value.
As for the concept of radians is the angle as a ration or portion of a full circumference. So given a basic circle the circumference is 2*pi*radius so if you go 2pi radians you have gone a full circumference. So the angle in radians * radius is the length travelled. In the general case you will also need to be careful of which one you subtract from which and if you 'loop' around. for example if you are at 1 deg and go to 364 deg did you travel 363 deg or only 2?
But in you specific case I would suggest taking the absolute value of the difference 1st and then convert to radians and multiple by the radius (in what ever unit you want).
Finally you will need to use Pythagorean's theorem to get the diagonal i.e. sqrt(x^2 + y^2).
Dec 14 2021 07:36 AM
@mtarler Thanks for trying to help. I don't think you're answer is very clear or actionable. Either way, I succeeded in converting my coordinates into radians. From there I found two formulas to convert those coordinates into miles.
The first formula I found was from here and had the following sequence =3443.8985*(ACOS(SIN(N2)*SIN(R2)+COS(N2)*COS(R2)*COS(S2-O2))). I believe this formula solves for nautical miles which is longer than the traditional mile.
The second formula I found here and could be retrieved by downloading the spreadsheet. I believe the second formula (utilized the 'P-Theorum' you discussed in your answer which) in this case solves for the radius of the earth in miles =3958.82*((2*ASIN(SQRT((SIN((N2-R2)/2)^2)+COS(N2)*COS(R2)*(SIN((O2-S2)/2)^2))))).
As you can see, there is a slight difference between the two formulas.
To be clear, if you are looking for the distance in populated areas with buildings and roads to navigate, this calculation will be even further from accurate. For example:
The image above shows the distance between two stations, displaying both the direct distance (roughly 570.14m or 0.354 mi) and navigatable distance (0.5mi). Considering it would be impossible to navigate your bicycle through buildings, the practical route along navigatable roads is longer.
Even if we would compare the direct distance (0.354mi) results given by Google Maps and then compare them to the results received by the output of our two formulas there is also a slight difference. With the nautical miles formula output of 0.327mi and formal miles output of 0.376mi. As you can see, there are still slight differences between both results. However, when comparing the actual navigatable distance (0.5mi), there is an even larger discrepancy which could definitely skew results.
With (hundreds of) thousands of rows of data, I don't know if it would be possible to 'add-in' some sort of Google API that could measure the practical navigatable distance between the two points by using, for example, street names. Furthermore, with missing data (i.e. no street names) and only having the latitudinal and longitudinal coordinates, I don't know how it would be possible to have an API that would recognize the practical navigatable distance between two coordinates (i.e. navigating using available roads, not passing through buildings). Understandably, it would also depend on how accurate the coordinates are and whether those coordinates could attach to the most approximate address.
I did see a product by CDX Tech that seemed interesting and perhaps could solve this issue. Either way, I would like to find out what other options are out there. Let the hunt begin!
Dec 14 2021 09:19 AM