For those who are racking their brains (like I did) not knowing how to convert coordinates to radians. Here are the formulas/function and a breezy breakdown for it all.
To convert latitudinal and longitudinal coordinates into miles first you must convert those coordinates into radians for excel. That can be done in two ways. Apply the =RADIANS() function to your latitudinal and longitudinal values. Or by using the formula =(Value)/180)*PI(). NOTE: Ensure your cells are formatted to the correct data type to prevent error messages and incorrect outputs.
After successfully converting the coordinates into radians. From there I found two formulas (with help from the great and powerful @kweaver ) 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. The second formula 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 in the results between the two formulas in my data set below.
To be clear, if you are looking for the distance in populated areas such as cities with buildings and roads to navigate, this calculation will be even further from accurate. For example:
The image above (from Gmaps) shows the distance between two stations, displaying both the direct distance also known as "line distance" or "as-the-crow-flies" distance and calculates to roughly 570.14m or 0.354 mi. While the actual navigable distance calculates to 0.5mi. Considering it would be impossible to navigate your bicycle through buildings, the practical route along navigatable roads is longer.
Both the formulas above could only solve for "line distance"/"as-the-crow-flies" distances. The latitudinal and longitudinal coordinates won't take into consideration urban navigation factors such as buildings and roads. It sounds obvious, but it was a discovery process for me.
Now, if we would compare the line distance of 0.354mi results (between the two points above) given by Google Maps and then compare them to the results received by the output of our two formulas there is still a slight difference. With the nautical miles formula output of 0.327mi and formal miles have an output of 0.376mi. As you can see, there are still slight differences between both results. However, when comparing to the actual navigable distance of 0.5mi, there is an even larger discrepancy which could definitely skew results.
With hundreds of thousands of rows of geographical data, I didn't know if it would be possible to 'add-in' some sort of Google API that could measure the practical navigable distance between the two points by using, for example, street names. In cases where data is missing i.e. no street names and only having the latitudinal and longitudinal coordinates, I didn't know how it would be possible to have an API or some program that would recognize the practical navigable distance between two coordinates and populating/automating thousands of results at a time. Understandably, I assume, it also depends 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 actually blew my mind and I had confirmed with their team that it could solve for my specific case and similar case scenarios. Here are Video 1 and Video 2 that depicts how their product operates and how to implement it into excel to solve for Geocoding. Either way, I would like to find out what other programs/products are out there from your guys' experience. Let the hunt begin!
To convert latitudinal and longitudinal coordinates into miles first you must convert those coordinates into radians for excel. That can be done in two ways. Apply the =RADIANS() function to your latitudinal and longitudinal values. Or by using the formula =(Value)/180)*PI(). NOTE: Ensure your cells are formatted to the correct data type to prevent error messages and incorrect outputs.
After successfully converting the coordinates into radians. From there I found two formulas (with help from the great and powerful @kweaver ) 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. The second formula 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 in the results between the two formulas in my data set below.
| 2021_divvy_tripdata_untrimmed.latlong.xlsx | ||||
|---|---|---|---|---|
| T | U | |||
| 1 | distance_nautical_miles | distance_miles | ||
| 2 | 0.448086001 | 0.515082493 | ||
| 3 | 0.435197309 | 0.500266724 | ||
| 4 | 0.327865023 | 0.37688643 | ||
| 5 | 1.076581483 | 1.237548758 | ||
| 6 | 0.556630394 | 0.63985612 | ||
| 7 | 0.67961762 | 0.781232033 | ||
| 8 | 0.903116151 | 1.038147403 | ||
| 9 | 0.448644956 | 0.515725022 | ||
| 10 | 1.07913815 | 1.240487688 | ||
December_2020 | ||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| T2:T10 | T2 | =3443.8985*(ACOS(SIN(N2)*SIN(R2)+COS(N2)*COS(R2)*COS(S2-O2))) |
| U2:U10 | U2 | =3958.82*((2*ASIN(SQRT((SIN((N2-R2)/2)^2)+COS(N2)*COS(R2)*(SIN((O2-S2)/2)^2))))) |
To be clear, if you are looking for the distance in populated areas such as cities with buildings and roads to navigate, this calculation will be even further from accurate. For example:
The image above (from Gmaps) shows the distance between two stations, displaying both the direct distance also known as "line distance" or "as-the-crow-flies" distance and calculates to roughly 570.14m or 0.354 mi. While the actual navigable distance calculates to 0.5mi. Considering it would be impossible to navigate your bicycle through buildings, the practical route along navigatable roads is longer.
Both the formulas above could only solve for "line distance"/"as-the-crow-flies" distances. The latitudinal and longitudinal coordinates won't take into consideration urban navigation factors such as buildings and roads. It sounds obvious, but it was a discovery process for me.
Now, if we would compare the line distance of 0.354mi results (between the two points above) given by Google Maps and then compare them to the results received by the output of our two formulas there is still a slight difference. With the nautical miles formula output of 0.327mi and formal miles have an output of 0.376mi. As you can see, there are still slight differences between both results. However, when comparing to the actual navigable distance of 0.5mi, there is an even larger discrepancy which could definitely skew results.
With hundreds of thousands of rows of geographical data, I didn't know if it would be possible to 'add-in' some sort of Google API that could measure the practical navigable distance between the two points by using, for example, street names. In cases where data is missing i.e. no street names and only having the latitudinal and longitudinal coordinates, I didn't know how it would be possible to have an API or some program that would recognize the practical navigable distance between two coordinates and populating/automating thousands of results at a time. Understandably, I assume, it also depends 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 actually blew my mind and I had confirmed with their team that it could solve for my specific case and similar case scenarios. Here are Video 1 and Video 2 that depicts how their product operates and how to implement it into excel to solve for Geocoding. Either way, I would like to find out what other programs/products are out there from your guys' experience. Let the hunt begin!
