[AccessD] Distance between zip codes

Jim Dettman jimdettman at verizon.net
Tue May 23 08:37:54 CDT 2017


For those that want it, here's the VBA code.  Do be aware that there are
variations.  Calculating distance is not as an exact science as one might
think (the earth is not truly round).   But for the purposes of this, it's
close enough.

 As far as performance, I had a list of about 10,000, and it's reasonable to
simply calculate all the distances to the given point for each, then filter
on the radius.

 If you have a large list, you can make an optimization by calculating the
lat/long for your radius for North, East, South, and West, then filtering
the list based lat/long pairs (North vs South, East vs West) first.   This
in effect draws a square around your radius the circle and minimizes the
number of distance calcs you need to make (everyone in the square vs the
entire list). 

 I'm sure there is probably a fancy way of determining if a given lat/long
pair is within the circle or not, but I never uncovered one in my research
and  I didn't try very hard once I discovered that the distance calc was
fairly fast.

 Jim.

Public Function CalcDist(lat1Degrees As Double, lon1Degrees As Double,
lat2Degrees As Double, lon2Degrees As Double) As Double

          Const RoutineName = "CalcDist"
          Const Version = "1.0.0.0"

          Dim lat1Radians As Double
          Dim lon1Radians As Double
          Dim lat2Radians As Double
          Dim lon2Radians As Double
          Dim AsinBase As Double
          Dim DerivedAsin As Double

10        On Error GoTo Error_Procedure

          'Convert each decimal degree to radians
20        lat1Radians = (lat1Degrees / 180) * 3.14159265359
30        lon1Radians = (lon1Degrees / 180) * 3.14159265359
40        lat2Radians = (lat2Degrees / 180) * 3.14159265359
50        lon2Radians = (lon2Degrees / 180) * 3.14159265359
60        AsinBase = Sin(Sqr(Sin((lat1Radians - lat2Radians) / 2) ^ 2 +
Cos(lat1Radians) * Cos(lat2Radians) * Sin((lon1Radians - lon2Radians) / 2) ^
2))
70        DerivedAsin = (AsinBase / Sqr(-AsinBase * AsinBase + 1))

          'Get distance from [lat1,lon1] to [lat2,lon2]
          ' Earth's mean radius.
          ' Use 3443.89849 for nautical miles
          ' Use 6371 for KM
          ' Use 3958.756 for miles
80        CalcDist = 2 * DerivedAsin * 3958.756

Exit_Procedure:
90        On Error Resume Next

100       Exit Function

Error_Procedure:
110       UnexpectedError ModuleName, RoutineName, Version, Err.Number,
Err.Description, Err.Source, VBA.Erl
120       Resume Exit_Procedure

End Function

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Kaup, Chester
Sent: Tuesday, May 23, 2017 09:01 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Distance between zip codes

Here is something else you might look at

https://www.zipcodesoft.com/distance-between-zip-codes

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
jack drawbridge
Sent: Tuesday, May 23, 2017 7:53 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Distance between zip codes

[This email message was received from the Internet and came from outside of
Kinder Morgan]


Rocky,

There is a sample database (mdb format) at
https://access-programmers.co.uk/forums/showpost.php?p=1382593&postcount=65

that demos the use of 2 zips. 2 addresses, 2 lat/Long or any combination to
find distance between them.
As others have said, I don't know of anything you can just drop in.

jack

On Tue, May 23, 2017 at 8:40 AM, Stuart McLachlan <stuart at lexacorp.com.pg>
wrote:

> Don't know of anything you can drop in, but here's a list of zip codes 
> with lat/lon.
>
> I've got some code that calculates distance between any two lat/lon 
> locations. (I wrote it for
> sailing)
>
> It would be fairly easy to write a  procedure thatsteps through the 
> data and locates matching codes, but it probably won't be very fast.
>
> Interesting project. Give me a day or so and I'll see f I can come up 
> with something  :)
>
>
>
>
>
> On 23 May 2017 at 4:37, Rocky Smolin wrote:
>
> > Dear List:
> >
> >
> >
> > I have a client who wants to know all of the zip codes that lie 
> > within a certain radius of a selected zip code.
> >
> >
> >
> > I seem to recall some discussion about this on the list but it could 
> > have been 15 years ago now.
> >
> >
> >
> > Is there some tool available that can be dropped into and Access FE 
> > (using 2010 now).
> >
> >
> >
> > MTIA
> >
> >
> >
> >
> >
> > Rocky Smolin
> >
> > Beach Access Software
> >
> > 760-683-5777
> >
> >  <http://www.bchacc.com> www.bchacc.com
> >
> >  <http://www.e-z-mrp.com> www.e-z-mrp.com
> >
> > Skype: rocky.smolin
> >
> >
> >
> >
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



More information about the AccessD mailing list