使用Microsoft Access按距离对邮政编码进行排序，可减少计算时间

Question

我有一个关于使用Microsoft Access根据与用户输入的邮政编码的距离对我的客户端进行排序的问题(请参见下表中的第一个表)。我使用大圆距离公式(见下面的代码)来计算邮政编码之间的距离。我有一个美国当前每个邮政编码的表格，以及它的纬度和经度坐标(见下面的第二个表格)。该函数获取每个客户的邮政编码，从第一个和第二个表中获取纬度和经度，并用它来计算每个客户之间的距离。这是我正在运行的SQL查询，它调用GreatCircleDistance公式：

PARAMETERS [Zip Code] IEEEDouble;
SELECT Clinics.Clinic, [US Zip Codes].ZIP, 
[US Zip Codes].LAT, [US Zip Codes].LNG, 
GreatCircleDistance([Zip Code],[LAT],[LNG],True,True) AS Distance
FROM [US Zip Codes] INNER JOIN Clinics ON [US Zip Codes].ZIP = Clinics.[Clinic ZIP];

用于此查询的大圆距离公式如下所示：

Private Const C_RADIUS_EARTH_KM As Double = 6370.97327862
Private Const C_RADIUS_EARTH_MI As Double = 3958.73926185
Private Const C_PI As Double = 3.14159265358979
Function GreatCircleDistance(ZipCode As Double, _
Latitude2 As Double, Longitude2 As Double, _
ValuesAsDecimalDegrees As Boolean, _
ResultAsMiles As Boolean) As Double
Dim lat1 As Double
Dim lat2 As Double
Dim long1 As Double
Dim long2 As Double
Dim X As Long
Dim Delta As Double
If ValuesAsDecimalDegrees = True Then
X = 1
Else
X = 24
End If
' convert to decimal degrees
'POTENTIAL PROBLEM
lat1 = DLookup("[US Zip Codes].LAT", "[US Zip Codes]", "[US Zip Codes].ZIP = '" & ZipCode & "'") * X
long1 = DLookup("[US Zip Codes].LNG", "[US Zip Codes]", "[US Zip Codes].ZIP = '" & ZipCode & "'") * X
'POTENTIAL PROBLEM
lat2 = Latitude2 * X
long2 = Longitude2 * X
' convert to radians: radians = (degrees/180) * PI
lat1 = (lat1 / 180) * C_PI
lat2 = (lat2 / 180) * C_PI
long1 = (long1 / 180) * C_PI
long2 = (long2 / 180) * C_PI
' get the central spherical angle
Delta = ((2 * ArcSin(Sqr((Sin((lat1 - lat2) / 2) ^ 2) + _
Cos(lat1) * Cos(lat2) * (Sin((long1 - long2) / 2) ^ 2)))))
If ResultAsMiles = True Then
GreatCircleDistance = Delta * C_RADIUS_EARTH_MI
Else
GreatCircleDistance = Delta * C_RADIUS_EARTH_KM
End If
End Function

Function ArcSin(X As Double) As Double
' VBA doesn't have an ArcSin function. Improvise.
ArcSin = Atn(X / Sqr(-X * X + 1))
End Function

这是我所有客户的第一张桌子。为了简单起见，我只添加了三条，但文件中大约有2000条记录：

Clinic            City        State     Clinic ZIP
Clinic #1       Lakeland        FL         33809
Clinic #2        Smyrna         TN         37167
Clinic #3       Kissimmee       FL         34747
...

这是第二张表，上面有每个美国邮政编码。记录在案的邮政编码略多于41000个：

ID       ZIP         LAT           LNG
1       00501      40.8133      -73.0476      
2       00601       18.18       -66.7522
3       00602      18.3607      -67.1752
4       00603      18.4544       -67.122
...

现在这种方法可以工作，但计算输入的邮政编码和每个2000多个客户端的邮政编码之间的距离需要很长时间。我认为问题出在大圆距离公式中，当我使用DLookUp函数来获取用户输入的邮政编码的纬度和经度时。有人知道我如何减少计算时间吗？感谢

Answer 1

由于需要比较两对不相关的坐标，请考虑在MS Access中使用逗号分隔的FROM子句中的交叉联接查询。然后将LAT和LNG的两对传递到距离公式中。

但是，请确保包含WHERE子句，以过滤作为参数传入的特定邮政编码(否则将运行两个集合的笛卡尔乘积)。这应该返回一个单行结果集，以便对内部联接的所有行重复：

PARAMETERS [ZipCodeParam] TEXT(255);
SELECT c.Clinic, us.ZIP, us.LAT, us.LNG, 
p.ZIP, p.LAT, p.LNG,
GreatCircleDistance(p.[LAT], p.[LNG], us.[LAT], us.[LNG], True, True) AS Distance
FROM [US Zip Codes] p, 
([US Zip Codes] us
INNER JOIN Clinics c
ON us.ZIP = c.[Clinic ZIP])
WHERE p.ZIP = ZipCodeParam;

当然，调整函数参数并删除不需要的DLookUp调用：

Function GreatCircleDistance(Latitude1 As Double, Longitude1 As Double, _
Latitude2 As Double, Longitude2 As Double, _
ValuesAsDecimalDegrees As Boolean, _
ResultAsMiles As Boolean) As Double
...
lat1 = Latitude1 * X
lng1 = Longitude1 * X
lat2 = Latitude2 * X
lng2 = Longitude2 * X
...
End Function

Answer 2

Dlookup代码可能是问题所在。你的DLookup代码看起来不正确，无法在我的系统上运行。这个代码有效：

lat1=DLookup("LAT"，"[美国邮政编码]"，"Zip="&ZipCode)*X

lat1=DLookup("LNG"、"[美国邮政编码]"、"Zip="和ZipCode)*X

https://www.techonthenet.com/access/functions/domain/dlookup.php