[AccessD] Zipcodes within a radius

JC

...first you have to have a zipcode table that includes the lat long of each 
zip ...I have one but its a year old and needs to be updated ...on my todo 
list ...if you need it, I can e it.

...I have this code from an experts exchange thread in my code collection 
but have not gotten around to actually implementing it so consider it an 
aid, not a final solution ...if you do get it working I'd appreciate 
feedback.

...the following gives you a query where you must enter your current 
location (in QueryLatitude and QueryLongitude). It will show you all codes 
sorted by distance with additional fields that tell you whether the distance 
is less than 5, 10, 20 or 50 miles.

It's a series of 4 queries.

Query qDistance1: Converts all distances from degrees to radians. Input is 
table tPostcode.

SELECT tPostcodes.Areacode, [Latitude]*3.1415926/180 AS LatRad, 
[Longitude]*3.1415926/180 AS LonRad, [QueryLatitude]*3.1415926/180 AS 
QLatRad, [QueryLongitude]*3.1415926/180 AS QLonRad, tPostcodes.Latitude, 
tPostcodes.Longitude
FROM tPostcodes;

Query qDistance2: Calculates the ArcCos of the distance between each 
postcode office and the coordinates (QueryLatitude, QueryLongitude) 
according to the formulae you were given.

SELECT qDistance1.Areacode, 
Sin([LatRad])*Sin([QLatRad])+Cos([LatRad])*Cos([QLatRad])*Cos([LonRad]-[QLonRad]) 
AS cosd
FROM qDistance1;

Query qDistance3: Calculates the distance in miles. This extra step is 
required because Access doesn't support the ACos function, so we have to 
calculate ACos through ATan.

SELECT qDistance2.Areacode, 
Abs(1.852/1.61*60*180/3.141526*2*Atn(Sqr((1-[cosd])/(1+[cosd])))) AS 
DistMiles
FROM qDistance2
ORDER BY Abs(1.852/1.61*60*180/3.141526*2*Atn(Sqr((1-[cosd])/(1+[cosd]))));


Query qDistance4: Finds out whether a post office is in the range of 5, 10, 
20 or 50 miles within the post office.

SELECT qDistance3.Areacode, qDistance3.DistMiles, [DistMiles]<5 AS Dist05, 
[DistMiles]<10 AS Dist10, [DistMiles]<20 AS Dist20, [DistMiles]<50 AS Dist50
FROM qDistance3;

How to use this:
Execute qDistance4 by passing QueryLatitude and QueryLongitude as 
parameters. From the result set, choose those results that satisfy your 
criteria, i.e. Dist5, Dist10, Dist20 or Dist50 are set.

...hth

William Hindman
----- Original Message ----- 
From: "JWColby" <jwcolby at colbyconsulting.com>
To: "'Access Developers discussion and problem solving'" 
<accessd at databaseadvisors.com>
Sent: Thursday, March 22, 2007 3:42 PM
Subject: [AccessD] Zipcodes within a radius


> Does anyone know how to get the zip codes with a radius of an address?
>
> John W. Colby
> Colby Consulting
> www.ColbyConsulting.com
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>