Decrease computation time using Microsoft Access to sort Zip Codes by distance

Question

I have a question on using Microsoft Access to sort my clients (see first table below) by distance from a user-input zip code. I am using the great circle distance formula (see code below) to calculate the distance between between zip codes. I have a table of every current zip code in the US with its Latitude and Longitude Coordinate as well (see second table below).The function takes the Zip Code of each client and grabs the Latitude and Longitude from the first and second table and uses it to calculate the distance between each. Here is the SQL Query I am running that calls the GreatCircleDistance Formula:

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];

The Great Circle Distance Formula used for this query is shown below:

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

Here is the first table that has all of my clients. I only added three for simplicity but there are about 2000 records on file:

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

Here is the second table that has every US Zip Code. There are a little over 41,000 Zip Codes on record:

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
...

Now this method works but it takes way to long to calculate the distance between the input Zip Code and the Zip Code of each of the 2000ish client Zip Codes. I think the problem is in the Great Circle Distance Formula when I use DLookUp function to get the latitude and longitude of the user-input Zip Code. Does anyone know how I can decrease computation time for this? Thanks

Take a look at these questions: https://stackoverflow.com/q/11112926/8597922, https://stackoverflow.com/a/3922497/8597922. They appear to deal with a similar problem and might help you. — Victor K, Jan 04 '19 at 21:11
Can you limit the results up front? E.g. if you are recommending say the closest location, you can limit based on a net difference of latitude and longitude as your first filter. Depending on the data, it may be possible to limit the results to the same state for instance. You also could consider a hand rolled memoization (https://en.wikipedia.org/wiki/Memoization) technique for caching previously searched results. — Ryan Wildry, Jan 04 '19 at 21:47
@Parfait The user-input zip code is passed as a parameter in the great circle distance function. `DLookup` is then used to get the latitude and longitude of the user-input Zip Code — Preston White, Jan 05 '19 at 04:39
@Parfait In other words, `DLookup` is then used to get the latitude and longitude of the user-input Zip Code (one single set of coordinates). LAT and LNG are the latitudes and longitudes of each client. They change throughout the duration of the program. — Preston White, Jan 05 '19 at 04:47
@Parfait Those are the latitude and longitude coordinates from each Zip Code from each Client in the database. I am using `DLookup` to grab a single set of coordinates from the user-input Zip Code. I agree that `DLookup` is redundant but I don't know how I can get the coordinates from the Zip Code parameter without doing it this way. — Preston White, Jan 05 '19 at 05:34

score 2 · Accepted Answer · answered Jan 05 '19 at 17:36

Since you need to compare two pairs of unrelated coordinates, consider a cross join query which in MS Access uses comma separated sources in FROM clause. Then pass in both pairs of LAT and LNG into distance formula.

However, be sure to include WHERE clause to filter on specific zip code passed in as a parameter (otherwise you run the cartesian product of both sets). This should return a one-row resultset to repeat for all rows of inner join:

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;

Of course adjust function parameters and remove unneeded DLookUp calls:

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

That was it, Thank you – Preston White Jan 05 '19 at 18:00 — Preston White, Jan 05 '19 at 18:00

score 0 · Answer 2 · answered Jan 05 '19 at 11:02

The Dlookup code may be the problem. your DLookup code doesn't look correct and wouldn't run on my system. This code worked:

lat1 = DLookup("LAT", "[US Zip Codes]", "ZIP = " & ZipCode) * X

lat1 = DLookup("LNG", "[US Zip Codes]", "ZIP = " & ZipCode) * X

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

Decrease computation time using Microsoft Access to sort Zip Codes by distance

2 Answers2