[AccessD] code to find the population within a radius of a zip code

Stuart McLachlan stuart at lexacorp.com.pg
Fri May 4 17:38:59 CDT 2007


On 5 May 2007 at 1:19, Borge Hansen wrote:

> I am using a code that will find a subset of records based on their geocode
> within a near enough square,
> 
> ...and querying the recordset like this
> 
> ....find me all record instances where Lat of record is between
> "northernmost Lat" and "southernmost Lat" and Long of record is between
> "most western Long" and "most eastern Long"
> 
> Interested to see your code based on a circle construct.....

Try this for size:

Function arcsin(X As Double) As Double
arcsin = Atn(X / Sqr(-X * X + 1))
End Function

Function KmDistance(Lat1 As Double, Lon1 As Double, Lat2 As Double, Lon2 As 
Double) As Double
Lat1 = Lat1 * 0.01745329252  'Convert Degrees to Radians
Lon1 = Lon1 * 0.01745329252
Lat2 = Lat2 * 0.01745329252
Lon2 = Lon2 * 0.01745329252
KmDistance = arcsin(Sqr(Sin((Lat1 - Lat2) / 2) ^ 2 + Cos(Lat1) * Cos(Lat2) 
* Sin((Lon1 - Lon2) / 2) ^ 2)) * 12733.414
End Function

Static Function LatStore(Optional Lat as Double = 999) as Double
Dim Store as Double
If Lat <> 999 then Store = Lat
LatStore = Store
End Function

Static Function LonStore(Optional Lon as Double = 999) as Double
Dim Store as Double
If Lon <> 999 then  Store = Lon
LonStore = Store
End Function


Find all locations within 500km of My House:

LatStore DLookup("Lat","TblLocations","Loc Name = 'My House")
LonStore DLookup("Lon","TblLocations","Loc Name = 'My House")

Select * from tblLocations where KMDistance(Lat,Lon,LatStore(),LonStore()) 
< 500



-- 
Stuart





More information about the AccessD mailing list