[AccessD] Longitude/Latitude

MartyConnelly martyconnelly at shaw.ca
Fri Sep 29 20:31:35 CDT 2006


 Here is one that supplies an access table on a monthly basis.
http://zipinfo.com/products/z5ll/

To calculate distances using great circle method
between lat long's. Shortest distance on a sphere.

Const PI As Double = 3.14159265358979
Const Circumference As Double = 40123.648 'kilometers
Const MilesPerKilometer As Double = 0.6214

Sub test()
    Debug.Print Distance(32.815, -117.135866, 37.79333, -122.554722, True)
End Sub

Public Function Distance( _
    ByVal Latitude1 As Double, _
    ByVal Longitude1 As Double, _
    ByVal Latitude2 As Double, _
    ByVal Longitude2 As Double, _
    Optional Miles As Boolean) As Double
'courtesy Lyle Fairfield
'assumes we are getting latitude-longitude
'as degrees with fractions expresed as decimals
'if minutes-seconds then use Sexagesimal function to convert
Dim CosArc As Double
Dim Arc As Double
Latitude1 = Radians(Latitude1)
Longitude1 = Radians(Longitude1)
Latitude2 = Radians(Latitude2)
Longitude2 = Radians(Longitude2)
CosArc = (Sin(Latitude1) * Sin(Latitude2)) + _
    (Cos(Latitude1) * Cos(Latitude2) * Cos(Longitude1 - Longitude2))
If Abs(CosArc) = 1 Then
    Arc = PI
Else
    Arc = Atn(-CosArc / Sqr(-CosArc * CosArc + 1)) + 2 * Atn(1)
End If
Distance = Arc / PI / 2 * Circumference
If Miles = True Then Distance = Distance * MilesPerKilometer
Distance = Round(Distance, 2)
End Function

Private Function Radians(ByVal degrees As Double) As Double
    Radians = PI * degrees / 180
End Function

Public Function SexagesimalToDecimal(ParamArray Sexagesimals()) As
Double
'    send this function degrees, minutes, seconds
'    degrees as convenience; degrees will not be changed
    Dim z As Long
    For z = 0 To UBound(Sexagesimals()) - LBound(Sexagesimals())
        SexagesimalToDecimal = SexagesimalToDecimal + Sexagesimals(z)/ 
60 ^ (z)
    Next z
End Function

Sub testsex()
    Debug.Print Distance(SexagesimalToDecimal(39, 56, 58),
SexagesimalToDecimal(-75, 9, 21), 35.667, 139.75)
' Philly to Tokyo 10916.89 km
' SanFrancisco to SanDiego  460.99 miles
End Sub

Steve Capistrant wrote:

>Hello all,
> 
>Sorry if this has been asked recently and I didn't pay attention.  What options exist for programmatically inserting longitude and latitude values on an address record?  I was told that Google had some way to return that information from their database, but can't seem to find it (perhaps if I paid for a business account).  This is a task I'd do regularly (perhaps twice a month) as data gets uploaded from a local Access db into a website, where the website is coded to calculate distance between addresses.  
> 
>Ideally, it would be SO cool to due this by connecting to a website and looping through the recordset of an Address table.  But another option I suppose is getting occasional downloads of a reference table, and use that as my lookup.
> 
>Steve Capistrant
>Symphony Information Services
>scapistrant at symphonyinfo.com
>www.symphonyinfo.com
> 
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada




More information about the AccessD mailing list