Isaac-
Function acos(Rad)
If Abs(Rad) <> 1 Then
acos = pi / 2 - Atn(Rad / Sqr(1 - Rad * Rad))
ElseIf Rad = -1 Then
acos = pi
End If
End Function
If Rad = +1, then the function is returning a Null, which could cause your other
calculations to blow up. Also, your calculation of acos is not clear as to what
operations you expect to occur first. In VBA, multiplication and division take
precedence over addition and subtraction, so the expression is being evaluated
as:
(pi / 2) - Atn((Rad / Sqr(1 - (Rad * Rad))))
Is that what you want?
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Lahaina, HI)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Isaac
Sent: Tuesday, January 31, 2012 8:51 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Error received in VBA calculation of distance between
geo coordinates
Pros: I know this is a shot in the dark, but my math skills are just not up to
debugging this function. Thank you in advance for any
thoughts/comments/suggestions:
We have a database of property locations which are geocoded and when a property
is selected, one of the things the database does is it provides a list of
neighboring properties, sorted by distance. Occasionally the database will throw
an error: "Run-time error '5': Invalid procedure call or argument".
I choose to debug and it takes me to the function below (I used apostrophes to
break out the function where the error is)
I know it is a mathematical error in that my function is generating a value that
is out of range, but I can't figure out why one property (one set of
coordinates) throws the error and why another does not.
For example a property with the coordinates of 40.9454923 , -72.8867217 throws
the error while 40.9567969 , -72.9755829 does not. I can't figure out how to do
the math in my head. I know there is a way to let the VBA editor go through the
code step by step and display the values in the immediate window, but I can't
get it to go through all the steps.
Thanks,
Isaac Richter
______________________________________
Option Compare Database
Option Explicit
' This routine calculates the distance between two points (given the
latitude/longitude of those points). It is being
'used to calculate distance between two ZIP Codes or Postal Codes
'Definitions
' South latitudes are negative, east longitudes are positive
'Passed to function
'lat1, lon1 = Latitude and Longitude of point 1 (in decimal degrees)
'lat2, lon2 = Latitude and Longitude of point 2 (in decimal degrees)
'unit = the unit you desire for results where 'M' is statute miles (default)
' 'K' is kilometers
' 'N' is nautical miles
Const pi = 3.14159265358979
Global PropertyLat
Global PropertyLong
Public lngMap As Long
Global lngComparablesDistance As Long
Function Distance(lat1, lon1, lat2, lon2, unit)
Dim theta, dist
theta = lon1 - lon2
dist = Sin(deg2rad(lat1)) * Sin(deg2rad(lat2)) + Cos(deg2rad(lat1)) *
Cos(deg2rad(lat2)) * Cos(deg2rad(theta))
dist = acos(dist)
dist = rad2deg(dist)
Distance = dist * 60 * 1.1515
Select Case UCase(unit)
Case "K"
Distance = Distance * 1.609344
Case "N"
Distance = Distance * 0.8684
End Select
End Function
*****************************
Below this is where the error is
*****************************
' This function get the arccos function from arctan function
'
Function acos(Rad)
If Abs(Rad) <> 1 Then
acos = pi / 2 - Atn(Rad / Sqr(1 - Rad * Rad))
ElseIf Rad = -1 Then
acos = pi
End If
End Function
*****************************
Above this is where the error is
*****************************
' This function converts decimal degrees to radians
'
Function deg2rad(Deg)
deg2rad = CDbl(Deg * pi / 180)
End Function
' This function converts radians to decimal degrees
'
Function rad2deg(Rad)
rad2deg = CDbl(Rad * 180 / pi)
End Function
'Demo
'response.Write distance(32.9697, -96.80322, 29.46786, -98.53506, "M") & "
Miles<br>"
'response.Write distance(32.9697, -96.80322, 29.46786, -98.53506, "K") & "
Kilometers<br>"
'response.Write distance(32.9697, -96.80322, 29.46786, -98.53506, "N") & "
Nautical Miles<br>"
Function GetDistance(Latitude, Longitude)
If ((Not IsNull(PropertyLat)) And (Not IsNull(PropertyLong))) Then
GetDistance = Distance(PropertyLat, PropertyLong, Latitude, Longitude,
"M")
End If
End Function
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar