Calculating Distances

[The_Doc_Man]

Starting from the WIKIPEDIA article on great circle calculations, I was able to implement their EXACT formula (not Haversine based) in about 30 minutes. You know what took the most time? If you have degrees, minutes, and seconds of arc, you need to convert that to radians first. THAT is the hard part. After that, it is all sines, cosines, and exactly one ATN (Arc Tangent) function. Couple of terms get squared. One Square Root involved. A simple division, some multiplication, and that's it. The WIKIPEDIA article even gives you the radius of the earth to use in determining the length of the chord. And they give you a sample calculation so you can test your algorithm.

Their advice if you want to do it their way is, all math must be DOUBLE format because of the differences involved. But (they say) their formula is not an approximation, it is exact. If you are doing their way, it helps to know PI to DOUBLE precision. Such as

PI = 3.141592565689793 (roughly speaking).

Now, having looked at the formula with a (slightly) more educated eye, because I worked in that industry for a while, I can say confidently that as long as you avoid two extreme cases, the math doesn't even involve unusually small or unusually large numbers, so "catastrophic cancellation" errors won't be likely. After all, SIN and COS functions range from zero to one. No gargantuan values there.

The cases to avoid using their exact formula because of extremes in the math:

1. One of the points is smack-dab on the north or south pole. That makes some of the cosines unusually small, leading to possible issues in the denominator of the one big division that they have. But usually folks don't get within 10 degrees of either one of the poles, so no great issues there.

2. The two reference points are antipodal i.e. exactly half-way around the world from each other. Said another way, the two points and the center of the earth form a straight line rather than defining a plane.

In all other cases, the result is pretty straight-forward.

 

[gemma-the-husky]

JBB

thanks a bunch, works great, and the numbers look sensible

a couple of things

i moved your expn1, expn2 calcs into a modular function

i happened to have a route to map out, with two points were the same. in that case your expn1/expn2 calcs generated a div by zero error, so i have added in code to check whether lat=lat2 and long1-long2, and return 0 as the distance in that case

secondly,
your code calculates the base distances in nautical miles, which you then have a translation factor, eg to translate to kilometres

I cannot see any other constant in there, so does that mean that a nautical mile is not an arbitrary measure, but is effectively a geographical constant related to the size of the earth.

[edited ... just looked this up, and sure enough, found this on a website concerned with navigation

A Nautical Mile is 1/60th of a degree or one minute of latitude.

Hence i can see how the various divs in your functions hang together. Without checking the math in detail, I assume from this that the calcs treat the globe as a perfect sphere, and therefore there will be some real world differences in that

a) the globe is not a perfect sphere
b) there are height diffs (variants from sea level) between points

these are not an issue for me, but i just wanted to get it straight. (perhaps your math includes some distortion factor already - i just wasnt sure)


Thanks again

this has been really useful

 

[PaulO]

Hi JBB

Really getting into this now ... just one question though ...

The 'miles' you've calculated ... are they imperial or nautical? I'd originally assumed imperial. The only reason I ask is that I'm struggling to reconcile your conversion rate of 1 mile - 1.852 km ... is this correct?

 

[gemma-the-husky]

i think that conversion (1.852) is nautical miles to klicks

nautical miles to statute miles is approx 1.15

statute miles to klicks is approx 1.6

 

[The_Doc_Man]

And don't forget that the nautical mile definition (as one minute of arc on the surface of the Earth) is only valid on a "great circle" of the Earth. On lesser circles, one minute of arc is smaller than a nautical mile.

 

[gemma-the-husky]

yes,

i haven't the math to check, but i i presume the haversine? formula johnbigbooty used in this thread, in his sample dbs, evaluates the angle formed between the two surface points and the centre of the earth (which is therefore always a great circle) although non-spherical distortions in the earth will presumable affect the calculation.

 

[John Big Booty]

That's correct the formula I have used returns Nautical miles along a great circle route between any two point.

 

[alexyaronyc]

John, I have a similar question about distance. I have a table with two addresses (Address 1 & Address 2). Is there a function I can embed into a query that will return the distance (in miles) between these two points? Would I have to convert the addresses into Lat/Long coordinates? If so, is there a way to do that in a query?
Thank you.

 

[The_Doc_Man]

First, alexyaronyc, this thread was last updated in 2008, fourteen years ago. Big John Booty hasn't posted since 2016.

Second, to answer that question as gently as possible and given what you say you have, you would have to build a Public function (which is OK, because public functions can be called in a query) with the two addresses as inputs. Inside the function, you WOULD have to convert the addresses to Lat/Lon coordinates and return a distance as SINGLE or DOUBLE (probably better as a DOUBLE) distance.

This forum sometimes sees folks talking about using Google Maps to get information for their project. Apparently there is a way for the Google Map pages to convert addresses to formal Lat/Lon positions. The numbers could then be tossed into a Great Circle formula for distance OR you would let Google Maps tell you the driving distance. Note that this service of Google Maps, when used programmatically, isn't free.

At the bottom of this page there is an article that delves into the Google Maps functions as a resource.

Calculate Driving Distance Between 2 Coordinates

I am using Access 2013 and am developing a form where the user provides two coordinates. And the form will calculate the driving distance between these 2 coordinates. I'm not sure where to start. Any input/sample would be greatly appreciated. Thanks.

www.access-programmers.co.uk

This might be a place to start. AND you would probably be better off to start a new thread once you have researched the topic using this forum's SEARCH feature for "distance between two points"