Calculate Distance Between Two Addresses 1

[JOEYB99]

I am using Access 2010 and am developing a form where the user provides a full address - street number, street name, city, province and postal code.

I want a control to calculate the distance from this address and a fixed address.

I'm not sure where to start.

Any input would be greatly appreciated.

 

[dhookom]

Are you looking for as the crow flies or by driving on roads?

I would think you might need to code some type of web service like The Google Distance Matrix API

Duane
Hook'D on Access
MS Access MVP

 

[JOEYB99]

Thanks Duane.

I do not know if it will be 'as the crow flies' or a distance by roads.

Thanks for the API link. I'm not familiar with doing code for something like this. What is involved here?

 

[dhookom]

I'm not familiar with the method and would have to search for additional information on how to implement it. I wouldn't spend any time researching until you have the very basic understanding of what is meant by "distance".

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

What if the crow has to roll a tire?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[JOEYB99]

Okay then Dhookom and Skip, if you are interested.

I need to know the distance 'as the crow flies'. I have confirmed that calcluation basis for my task at hand.

Any help you can provide in implementing a solution would be greatly appreciated.

 

[MajP]

You would have to use a great circal function (Haversine) like this, if you knew the lat long. If you had a grid location you could convert that to lat long. But since you have an address I think you will have to uses something like the google API/web service to convert an address to lat long. I do not have any experience doing that, but there is plenty of info on the web.

Public Function polarDistanceTwo(decLatStart As Single, decLongStart As Single, decLatEnd As Single, decLongEnd As Single) As Single
   Const decToRad = 3.14159265358979 / 180
   Const radiusOfEarth = 3963.1
   'radiusOfEarth =3963.1 statute miles, 3443.9 nautical miles, or 6378 km
   Dim radLatStart As Single
   Dim radLongStart As Single
   Dim radLatEnd As Single
   Dim radLongEnd As Single
   radLatStart = decLatStart * decToRad
   radLongStart = decLongStart * decToRad
   radLatEnd = decLatEnd * decToRad
   radLongEnd = decLongEnd * decToRad
   If Sin(radLatStart) * Sin(radLatEnd) + Cos(radLatStart) * Cos(radLatEnd) * Cos(radLongStart - radLongEnd) > 1 Then
      polarDistanceTwo = 3963.1 * ArcCos(1)
   Else
      polarDistanceTwo = 3963.1 * ArcCos(Sin(radLatStart) * Sin(radLatEnd) + Cos(radLatStart) * Cos(radLatEnd) * Cos(radLongStart - radLongEnd))
   End If
End Function


Function ArcCos(X As Single) As Single
    If Abs(X) <> 1 Then
        ArcCos = 1.5707963267949 - Atn(X / Sqr(1 - X * X))
    Else
        ArcCos = 3.14159265358979 * Sgn(X)
    End If
    'ArcCos = Atn(-X / Sqr(-X * X + 1)) + 2 * Atn(1)
End Function