?
Solved

Parameter Query using zip code and distance to find records within radius and the distance to zipcode

Posted on 2005-03-23
11
Medium Priority
?
725 Views
Last Modified: 2012-06-21
Hi Experts,
I have a table of zipcodes with latitude and longitude and a table of members that I have complete addresses for including their zipcodes. I want to build a query that prompts for the zipcode (center point of my search) and the distance in miles (radius) for which I want to find all members within and calculate the distance for each member to the entered zipcode.

My tables are linked tables from SQL (not sure if that matters). I've seen answers here, but they all use functions that (arccos) that don't seem to be available in access 2000. I don't have the depth of knowledge that I should have with access (I'm a wizard based person in most cases) to figure out how to put the pieces together. Any help would be greatly appreciated!

Here's my tables:
The members table is EPMembers with the following pertinent fields:
FirstName
LastName
Zip

The zipcode table is ZipCodes with the following fields:
ZipCodezip
ZipCodeLongitude (e.g. data -67.139696000000001)
ZipCodeLatitude (e.g. data 18.288685000000001)

The query would prompt for [Enter zipcode] and then [Enter search radius in miles] and then would return a list of members and the distance they are from the center.

Thanks,
Allan
0
Comment
Question by:arstark
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
11 Comments
 
LVL 2

Expert Comment

by:unicorn1824
ID: 13615100
I found this to help you calculate arccos...

http://www.eng-tips.com/viewthread.cfm?qid=65875

That's a start.. I'd have to think some more about how to proceed from here.
0
 
LVL 2

Expert Comment

by:unicorn1824
ID: 13615136
OK, try this...

Your prompt results go into a file with zipcode ceter and search radius (in miles). The query you'd create would use this table and the zipcode/lat & long table. Use the zipcode input and the zipcode in the field to calculate the distance, then use the radius as a criteria.
0
 

Author Comment

by:arstark
ID: 13615316
Thanks for the link, I've seen that formula on other answers here on EE, but I'm not sure how to implement it.

I tried adding "application.acos" to see if that would help, no luck. Here's exactly what I have so far, it's doesn't run because "it has an undefined function", also I haven't figured out how to add the radius prompt.


SELECT 3959*application.acos((Sin([startZip].[ZipCodeLatitude]/57.3)*Sin([endZip].[ZipCodeLatitude]/57.3))+(Cos([startZip].[ZipCodeLatitude]/57.3)*Cos([endZip].[ZipCodeLatitude]/57.3)*Cos(([startZip].[ZipCodeLongitude]-[endZip].[ZipCodeLongitude])/57.3))) AS exactDistance
FROM ZipCodes AS endZip, Zip_Codes AS startZip
WHERE (((endZip.ZipCodeZip)=[Enter Zip))
ORDER BY exactDistance;
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 2

Expert Comment

by:unicorn1824
ID: 13615460
I found this...

http://www.zipcodeworld.com/samples/distance.vb.html

The idea is you want to use the radius as a criteria in your query; that is, the distance calculated should be less than or equal to the distance entered.
0
 
LVL 58

Expert Comment

by:harfang
ID: 13615559
so now we have answers... i come too late.

This is a module to compute distances "as the crow fies":

-----------------------------------------------------------------------
Option Compare Database
Option Explicit

Function ACos(X As Double) As Double
On Error Resume Next
    ACos = 0#
    ACos = Atn(-X / Sqr(-X * X + 1)) + 2 * Atn(1)
End Function

Function DistLatLon(LatA As Double, LonA As Double, LatB As Double, LonB As Double) As Double

    Const Radius = 6371 ' km or 3959 miles
    Const Deg2Rad = 3.14159265358979 / 180#
   
    Dim Angle As Double

    ' convert to radians:
    LatA = LatA * Deg2Rad
    LonA = LonA * Deg2Rad
    LatB = LatB * Deg2Rad
    LonB = LonB * Deg2Rad
   
    ' Cosine of the angle:
    Angle = ACos(Cos(LatA) * Cos(LatB) * Cos(LonB - LonA) + Sin(LatA) * Sin(LatB))
    DistLatLon = Angle * Radius

End Function
-----------------------------------------------------------------------

Then a readable version of arstark's query:

(for debugging:)

PARAMETERS [Enter Location:] Text, [Enter Max Distance:] Double;
SELECT B.*, DistLatLon([A].[dblLatitude],[A].[dblLongitude],[B].[dblLatitude],[B].[dblLongitude]) AS Dist
FROM tblGeoLocations AS A, tblGeoLocations AS B
WHERE (((DistLatLon([A].[dblLatitude],[A].[dblLongitude],[B].[dblLatitude],[B].[dblLongitude]))<=[Enter Max Distance:]) AND ((A.Name)=[Enter Location:]))
ORDER BY DistLatLon([A].[dblLatitude],[A].[dblLongitude],[B].[dblLatitude],[B].[dblLongitude]);

(later:)
PARAMETERS [Enter Location:] Text, [Enter Max Distance:] Double;
SELECT B.*
FROM tblGeoLocations AS A, tblGeoLocations AS B
WHERE (((DistLatLon([A].[dblLatitude],[A].[dblLongitude],[B].[dblLatitude],[B].[dblLongitude]))<=[Enter Max Distance:]) AND ((A.Name)=[Enter Location:]));

But the field and table names are a little different here :)
0
 

Author Comment

by:arstark
ID: 13615931
Ok, here's what I've got...

I created 2 modules and copy pasted Functions from harfang

Next I used the query you created and changed it to reflect my table:

PARAMETERS [Enter Location:] Text ( 255 ), [Enter Max Distance:] IEEEDouble;
SELECT B.*
FROM ZipCodes AS A, ZipCodes AS B
WHERE (((DistLatLon([A].[ZipCodeLatitude],[A].[ZipCodeLongitude],[B].[ZipCodeLatitude],[B].[ZipCodeLongitude]))<=[Enter Max Distance:]) AND ((A.ZipcodeZip)=[Enter Location:]));

Seems like I'm almost there, however I'm getting a data type mismatch?

My linked table has:
ZipCodeZip as text
ZipCodeLatitude as number
ZipCodeLongitude as number

Can't see why I'm getting a mismatch?
0
 
LVL 58

Accepted Solution

by:
harfang earned 2000 total points
ID: 13616133
Perhaps you should start like this:

SELECT B.*, DistLatLon(0,0,[B].[ZipCodeLatitude],[B].[ZipCodeLongitude]) As Dist
FROM ZipCodes AS B

Then:

PARAMETERS [Enter Location:] Text ( 255 );
SELECT B.*, DistLatLon([A].[ZipCodeLatitude],[A].[ZipCodeLongitude],[B].[ZipCodeLatitude],[B].[ZipCodeLongitude]) As Dist
FROM ZipCodes AS A, ZipCodes AS B
WHERE (((A.ZipcodeZip)=[Enter Location:]));

and then and the sort order and criteria on the Dist column...

Another idea... make sure you have no Null values in the table... that can be a problem since the function does not accept Nulls.
If needed I can rewrite it to accept nulls.

Cheers!
0
 

Author Comment

by:arstark
ID: 13616166
Looks like I had some bad data(missing longitudes and latitudes) in the zip code table, the query is now running and looks to be returning right info.  
However, I still need to show the calculated distance in  the query results.

Here's my query so far...

PARAMETERS [Enter Location:] Text ( 255 ), [Enter Max Distance:] IEEEDouble;
SELECT B.*, EPMembers.FirstName, EPMembers.LastName
FROM ZipCodes AS A, ZipCodes AS B INNER JOIN EPMembers ON B.ZipCodeZip = EPMembers.Zip
WHERE (((DistLatLon([A].[ZipCodeLatitude],[A].[ZipCodeLongitude],[B].[ZipCodeLatitude],[B].[ZipCodeLongitude]))<=[Enter Max Distance:]) AND ((A.ZipCodeZip)=[Enter Location:]) AND ((B.ZipCodeLatitude) Is Not Null) AND ((B.ZipCodeLongitude) Is Not Null));
0
 

Author Comment

by:arstark
ID: 13616239
I got it, I think...

PARAMETERS [Enter Location:] Text ( 255 ), [Enter Max Distance:] IEEEDouble;
SELECT B.*, EPMembers.FirstName, EPMembers.LastName, DistLatLon([A].[ZipCodeLatitude],[A].[ZipCodeLongitude],[B].[ZipCodeLatitude],[B].[ZipCodeLongitude]) AS Distance
FROM ZipCodes AS A, ZipCodes AS B INNER JOIN EPMembers ON B.ZipCodeZip = EPMembers.Zip
WHERE (((DistLatLon([A].[ZipCodeLatitude],[A].[ZipCodeLongitude],[B].[ZipCodeLatitude],[B].[ZipCodeLongitude]))<=[Enter Max Distance:]) AND ((A.ZipCodeZip)=[Enter Location:]) AND ((B.ZipCodeLatitude) Is Not Null) AND ((B.ZipCodeLongitude) Is Not Null))
ORDER BY DistLatLon([A].[ZipCodeLatitude],[A].[ZipCodeLongitude],[B].[ZipCodeLatitude],[B].[ZipCodeLongitude]);

Harfang,
You were right, there were nulls in the table and I just didn't have the calculated distance showing in my results it was there.  

Does the query look ok to you?
0
 
LVL 58

Assisted Solution

by:harfang
harfang earned 2000 total points
ID: 13616797
Looks ok.

Come to think of it, one should never design a VB function used in queries that does not accept Null values... So here is an updated version, with Null accepted as entry:

Function DistLatLon(LatA, LonA, LatB, LonB)

    Const Radius = 6371 ' km or 3959 miles
    Const Deg2Rad = 3.14159265358979 / 180#
   
    Dim Angle As Variant
   
On Error GoTo DistLatLon_Error
   
    ' convert to radians:
    LatA = LatA * Deg2Rad
    LonA = LonA * Deg2Rad
    LatB = LatB * Deg2Rad
    LonB = LonB * Deg2Rad
   
    ' Cosine of the angle:
    Angle = ACos(Cos(LatA) * Cos(LatB) * Cos(LonB - LonA) + Sin(LatA) * Sin(LatB))
    DistLatLon = Angle * Radius
    Exit Function
   
DistLatLon_Error:
    DistLatLon = Null
   
End Function

The only problem is that JetEngine will not know that the result is a number. So it is best to use it always as:

     DistLatLon( ....... ) + 0

The +0 will "explain" to JetEngine that the column will be a number (for formatting and such).

Good Luck
0
 

Author Comment

by:arstark
ID: 13616928
Harfang,
Many thanks! Your answers did the trick and your time and help is greatly appreciated!
Allan
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question