Latitudinal & Longitudinal Accuracy for Navigatable Distances (API/PowerBI/Tableau)

[zgoldflo]

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. Input the =RADIANS() function to your latitudinal and longitudinal values. Or by using the formula =(Value)/180)*PI(). Once successfully converting coordinates into radians. I found two formulas (with the help of @kweaver ) to convert those coordinates into miles. Ensure your cells are in the correct data type format to prevent error messages and incorrect outputs.

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 (thanks @kweaver !) and could be retrieved by downloading the spreadsheet. I believe 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))))). This seems to be more accurate to the traditional mile.

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 mile formula output of 0.327mi and traditional mile 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!

2021_divvy-tripdata.xlsx
	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	start_station_name	end_station_name	start_station_id	end_station_id	start_lat	start_lng	start_lat_radians	start_lng_radians	end_lat	end_lng	end_lat_radians	end_long_radians	distance_nautical_miles	distance_miles
2					41.79	-87.59	0.729547627	-1.528733892	41.8	-87.6	0.729547627	-1.528908425	0.448086001	0.515082493
3		Wentworth Ave & 63rd St		KA1503000025	41.78	-87.62	0.729198561	-1.529257491	41.78009517	-87.6297085	0.729200222	-1.529426936	0.435197309	0.500266724
4	Larrabee St & Armitage Ave	Sedgwick St & Webster Ave	TA1309000006	13191	41.918084	-87.643749	0.731608582	-1.529671989	41.922167	-87.638888	0.731679844	-1.529587148	0.327865023	0.37688643
5	Wabash Ave & Adams St	Peoria St & Jackson Blvd	KA1503000015	13158	41.879472	-87.625688	0.730934675	-1.529356765	41.8776416	-87.64961779	0.730902729	-1.529774419	1.076581483	1.237548758
6	Clarendon Ave & Leland Ave	Broadway & Argyle St	TA1307000119	13108	41.967968	-87.650001	0.732479222	-1.529781107	41.973815	-87.65966	0.732581271	-1.529949688	0.556630394	0.63985612
7	Clark St & Newport St	Lincoln Ave & Belmont Ave	632	TA1309000042	41.94447967	-87.6547855	0.732069273	-1.529864612	41.93926267	-87.6682715	0.731978219	-1.530099987	0.67961762	0.781232033
8	Ellis Ave & 60th St		KA1503000014		41.78514467	-87.6010685	0.729288353	-1.528927074	41.78	-87.62	0.729198561	-1.529257491	0.903116151	1.038147403
9					41.72	-87.62	0.728151364	-1.529257491	41.72	-87.63	0.728151364	-1.529432024	0.448644956	0.515725022
10					41.79	-87.6	0.729373094	-1.528908425	41.8	-87.58	0.729547627	-1.528559359	1.07913815	1.240487688
11	Wolcott (Ravenswood) Ave & Montrose Ave	Clark St & Montrose Ave	TA1307000144	KA1503000022	41.961406	-87.676169	0.732364693	-1.530237825	41.961588	-87.666036	0.73236787	-1.53006097	0.453031576	0.520767511
12	Southport Ave & Irving Park Rd	Sheridan Rd & Montrose Ave	TA1309000043	TA1307000107	41.95417783	-87.664245	0.732238538	-1.530029712	41.96173933	-87.65485933	0.732370511	-1.529865901	0.618521829	0.711001379
13					41.95	-87.65	0.732165621	-1.529781089	41.95	-87.65	0.732165621	-1.529781089	7.25747E-05	0
14	Western Blvd & 48th Pl		594		41.80566533	-87.68333417	0.729646506	-1.53036288	41.78	-87.69	0.729198561	-1.530479221	1.571331214	1.806272001
15	Aberdeen St & Monroe St	Morgan St & Polk St	13156	TA1307000130	41.880419	-87.655519	0.730951204	-1.529877414	41.871737	-87.65103	0.730799674	-1.529799066	0.559189806	0.642798209
16	New St & Illinois St	Field Blvd & South Water St	TA1306000013	15534	41.890847	-87.618616	0.731133207	-1.529233335	41.88634906	-87.61751655	0.731054703	-1.529214146	0.274798843	0.315885953
17	Clark St & Armitage Ave	Lake Shore Dr & Wellington Ave	13146	TA1307000041	41.91832017	-87.63629733	0.731612704	-1.529541933	41.936675	-87.63686317	0.731933056	-1.529551808	1.103550857	1.268550512
December_2020

Cell Formulas
Range	Formula
N2	N2	=R2
O2,N3:O17	O2	=RADIANS(M2)
R2:S17	R2	=(P2/180)*PI()
T2:T17	T2	=3443.8985*(ACOS(SIN(N2)*SIN(R2)+COS(N2)*COS(R2)*COS(S2-O2)))
U2:U17	U2	=3958.82*((2*ASIN(SQRT((SIN((N2-R2)/2)^2)+COS(N2)*COS(R2)*(SIN((O2-S2)/2)^2)))))

 

[offthelip]

Note: your equations for the great circle distance between two points in T2 and U2 are both approximations based on using a spherical model of the Earth. A closer approximation to the real earth shape is the spheriod which is a flattened sphere, thus the radius of the earth varies depending of how far from the equator you are. If you look at wikipedia it gives you the Earths radius of 3950 miles at the poles and 3963 miles at the Equator. So 3958.82 for the earth radius is correct somewhere on the earth but not in most places. Giving a figure which is accurate to 50 feet when in reality it varies by 13 miles implies an accuracy which doesn't exist!!

 

[zgoldflo]

Note: your equations for the great circle distance between two points in T2 and U2 are both approximations based on using a spherical model of the Earth. A closer approximation to the real earth shape is the spheriod which is a flattened sphere, thus the radius of the earth varies depending of how far from the equator you are. If you look at wikipedia it gives you the Earths radius of 3950 miles at the poles and 3963 miles at the Equator. So 3958.82 for the earth radius is correct somewhere on the earth but not in most places. Giving a figure which is accurate to 50 feet when in reality it varies by 13 miles implies an accuracy which doesn't exist!!

So you are saying that my number in U2 should be 3963 to get a more accurate measurement of up to 50 feet? For some reason, I highly doubt that. I also did see that Wikipedia page, but wasn't sure what would be more accurate. I saw you mentioned that the 'spheroid' would offer a closer approximation, is that the 3963 number? Either way, the comment above doesn't really address the crux of the issue. Which is what would be the most accurate manner to calculate longitudinal and latitudinal coordinates into navigable miles.

 

[offthelip]

So you are saying that my number in U2 should be 3963 to get a more accurate measurement of up to 50 feet?

No I am not saying that, The 50 feet comes from the fact that the number 3958.82 miles is accurate to 50 feet, i.e it is approximately 50 feet larger than 3958.81 .
Really I just wanted to point out that the calculations you are using for the spherical calculations in T2 and U2 are only approximations.
I have no idea how to work out the distance when trying to work out the navigable distance. I would suggest that probably the easest way of doing it it to write an interface to some on line tool that already has the facility to work out routes, e.g. google maps

 

[zgoldflo]

No I am not saying that, The 50 feet comes from the fact that the number 3958.82 miles is accurate to 50 feet, i.e it is approximately 50 feet larger than 3958.81 .
Really I just wanted to point out that the calculations you are using for the spherical calculations in T2 and U2 are only approximations.
I have no idea how to work out the distance when trying to work out the navigable distance. I would suggest that probably the easest way of doing it it to write an interface to some on line tool that already has the facility to work out routes, e.g. google maps

Hey again! Actually, what is awesome about my thread is that I actually included a tool (end of thread) that could be used for that purpose! Check out CDX Technologies and the products they have (free trial as well). My mind was personally blown and frankly, I was giddy with excitement that such a tool exists. In summary, the tool could be used exactly for the case scenario I was looking to solve for (and much more) - check out these YouTube videos detailing some of their available functionalities vid_1 & vid_2 (related to my case scenario).

Frankly, I would classify myself at best as 'beginner level' in excel. The above thread was my 'blind man' journey through trigonometric excel functions to solve for miles from lat/long coordinates. I had basically zero background knowledge. I want this post to serve as a valuable resource for excel users who are completely new to this concept. Honestly, I am terrible at math. If I was able to somehow figure this out (with help from the great and powerful @kweaver ) really anyone can do it with some persistence.

As you might have assumed, I did eventually realize that the distance in miles offered by the above functions was not exact and only offered an approximate distance at best. The new navigation term I learned was 'as the crow flies'. Meaning, the lat/long conversion into miles provided by the formulas above only could solve for the distance in a straight line i.e. 'from point (a) to point (b)' which would not take into consideration the actual navigable path. In comes CDX technologies to solve for that.

Thanks again for your comment!