UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> Find distance between two zip codes using long and lat    
 
   
JustXtreme
post Oct 27 2004, 10:57 AM
Post #1

UtterAccess Addict
Posts: 281
From: Florida



I am trying to make a query that finds the distance in miles between two zip codes using the longitude and latitude for each. I have a table with zips and lat and long, and also a text file with a formula for doing this. However, i cant figure out how to get this formula into Access. Please Help.
Go to the top of the page
 
+
preston
post Oct 27 2004, 10:58 AM
Post #2

UtterAccess Ruler
Posts: 1,692
From: Nevada



empty zipfile
Go to the top of the page
 
+
JustXtreme
post Oct 27 2004, 01:27 PM
Post #3

UtterAccess Addict
Posts: 281
From: Florida



Ahhh, sorry, i think the file may have been to large, it works on my pc. I exported my zips table as a csv, so maybe it will work.
Go to the top of the page
 
+
JustXtreme
post Oct 28 2004, 07:46 AM
Post #4

UtterAccess Addict
Posts: 281
From: Florida



Can anyone help me with this???
Go to the top of the page
 
+
LakeGator
post Oct 28 2004, 08:13 AM
Post #5

UtterAccess VIP
Posts: 1,526
From: Tampa, Florida



Sure, someone can help.

I have copied the pseudo code from your example and created VBA code to do the same things. I am not sure the formula is correct but it should get you fairly close.

I left the pseudo code as comments in the VBA.

All you need to do is to add this to a module and then call CalcDist passing the decimal longitudes and latitudes associated with the ZIP codes.

Hope this helps.


CODE
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Calculate the distance between two latitudes and longitudes
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function CalcDist(dblLat1 As Double, dblLon1 As Double, dblLat2 As Double, dblLon2 As Double) As Double

Const cnsPI = 3.1415926535

Dim dblRadLat1 As Double
Dim dblRadLat2 As Double
Dim dblRadLon1 As Double
Dim dblRadLon2 As Double
Dim dblTheta As Double
Dim dblRadTheta As Double
Dim dblDist As Double

'<cfset radlat1 = Evaluate((pi() * lat1)/180)>
'<cfset radlat2 = Evaluate((pi() * lat2)/180)>
'<cfset radlon1 = Evaluate((pi() * lon1)/180)>
'<cfset radlon2 = Evaluate((pi() * lon2)/180)>
dblRadLat1 = cnsPI * dblLat1 / 180
dblRadLat2 = cnsPI * dblLat2 / 180
dblRadLon1 = cnsPI * dblLon1 / 180
dblRadLon2 = cnsPI * dblLon2 / 180
'<cfset theta = lat1-lat2>
'<cfset radtheta = Evaluate((pi() * theta)/180)>
dblTheta = dblLat1 - dblLat2
dblRadTheta = cnsPI * dblTheta / 180
'<cfset dist = Evaluate((60 * 1.1515) * (180 / pi()) * (ACos((Sin(radlat1) * Sin(radlat2)) + (Cos(radlat1) * Cos(radlat2) * Cos(radtheta)))))>
CalcDist = (60 * 1.1515) * (180 / cnsPI) * Acos((Sin(dblRadLat1) * Sin(dblRadLat2)) + (Cos(dblRadLat1) * Cos(dblRadLat2) * Cos(dblRadTheta)))
End Function

''''''''''''''''''''''''
' Compute the Arc Cosine
''''''''''''''''''''''''
Function Acos(dblRadian As Double) As Double

Acos = Atn(-dblRadian / Sqr(-dblRadian * dblRadian + 1)) + 2 * Atn(1)
End Function
Go to the top of the page
 
+
JustXtreme
post Oct 28 2004, 09:02 AM
Post #6

UtterAccess Addict
Posts: 281
From: Florida



Awesome! thanks for your help. Do you suppose you could give me like a quick rundown on how to incorporate that into a query? I have never been this deep into access before, I have only really messed with queries and tables. I greatly appreciate your help!
Go to the top of the page
 
+
JustXtreme
post Oct 28 2004, 09:18 AM
Post #7

UtterAccess Addict
Posts: 281
From: Florida



For Example, I would like to be able to type in a store zip code and get all of our employees within 20 miles. I have a database called STORE with all store address information, and a database called DEM with all employee informations. Both tables include the ZIP field.
Go to the top of the page
 
+
JustXtreme
post Oct 28 2004, 09:57 AM
Post #8

UtterAccess Addict
Posts: 281
From: Florida



Ok, i have copied the code into a module called Distance. I don't understand what you meant by "call CalcDist passing the decimal longitudes and latitudes associated with the ZIP codes." How do I go about doing this? Sorry to be a pain.
Go to the top of the page
 
+
LakeGator
post Oct 28 2004, 11:38 AM
Post #9

UtterAccess VIP
Posts: 1,526
From: Tampa, Florida



In order to call the function from a query the function must be declared Public so add Public in front of the Function statements in the VBA code.

Here is an example of using the function in a Select query:

CODE
SELECT ZIP, State, City, CalcDist(161.39,60.89,Lon,Lat) AS Miles FROM tblZIPcodes;


You will want to change the table and field names to match your table and field names. You will also want to change the starting coordinates to be other than Lake County as I have no idea whether you have a store there.

Have fun.
Go to the top of the page
 
+
JustXtreme
post Oct 29 2004, 10:26 AM
Post #10

UtterAccess Addict
Posts: 281
From: Florida



I tried doing that and I got an error. I shrunk down the database so i could fit it into a zip file, would you mind looking at it and seeing what I am doing wrong. I just need a query to prompt for a store zip code and a demonstrator zip code and then tell me the distance in miles between the two. I included the zip codes table with the longitude and latitude numbers. Also included are samples of the DEM table and the STORE table. Thanks for the help, i really appreciate it.
Go to the top of the page
 
+
JustXtreme
post Oct 29 2004, 12:07 PM
Post #11

UtterAccess Addict
Posts: 281
From: Florida



Also, I dont know how possible it is, but how would i set up a query to give me all addresses within so many miles of a store zip code? Is this possible? Thanks again for the help.
Go to the top of the page
 
+
LakeGator
post Oct 29 2004, 10:57 PM
Post #12

UtterAccess VIP
Posts: 1,526
From: Tampa, Florida



Let’s back up a little in regard to what it is that you are attempting to accomplish. You seem to want to find stores near others. You do not need the exact distance nor will you get it using ZIP codes given that ZIP codes are quite often irregular geometric shapes. Moreover, computing the distance gives a straight line distance which ignores minor details like roads, rivers, oceans and gulfs.

What I suggest is to not compute the distance at all but, rather, find all the stores that have coordinates that fall inside a square of a certain size with the target store being in the center. The SELECT becomes quite simple now once you determine the minimum and maximum for the longitude and latitude values that make up the perimeter of the square.

Let’s review the geometry used for the third rock from the sun. We use longitude and latitude normally measured in degrees. Every degree of latitude (north/south) is roughly 69.05 miles in length. Longitude is a bit trickier because our particular planet is a sphere rather than a cylinder. Each degree of longitude is the same 69.05 at the equator but gets smaller the farther away from the equator. For instance, at the latitude of Jacksonville, Florida (latitude approximately 30.28) each degree of longitude is approximately 59 miles. If you go all the way up to Chicago (latitude near 41.69) each degree of longitude is approximately 51 miles.

Using the more pleasant latitude of Jacksonville the minimum and maximum latitudes and longitudes are calculated using a formula like:

Latitude = (1 / 69.05) * Distance
Longitude = (1/59) * Distance

If you want to create a search for stores within 25 miles from the center of beautiful downtown Lake City, Florida (ZIP code 32055, longitude 82.65, latitude 30.16) you would have something like:

Select * FROM Store WHERE Latitude > 29.80 And Latitude < 30.52 And Longitude > 82.23 And Longitude < 83.07

You can still compute the straight line distance once you have the stores in the box if you really wish.

Hope this helps.
Go to the top of the page
 
+
mishej
post Oct 29 2004, 11:42 PM
Post #13

Retired Moderator
Posts: 11,289
From: Milwaukee, WI



I couldn't find any sample code though I'm sure I saw some at one time.

Here is an explanation of the process of measuring distances:
http://www.census.gov/cgi-bin/geo/gisfaq?Q5.1

Some sample VB app here, however no code so "consumer beware":
http://www.directionsmag.com/devel/devel_articles.php?ID=1

Microsoft says:
http://msdn.microsoft.com/library/default....APILocation.asp

This site looked interesting but I can't say anything about its accuracy:
http://www.jcsm.com/ACXFunc.asp
Go to the top of the page
 
+
JustXtreme
post Nov 1 2004, 11:14 AM
Post #14

UtterAccess Addict
Posts: 281
From: Florida



LakeGator, I can't figure out where you got the numbers in the select formula. (29.80, 30.52, 82.23, and 83.07). Everything else makes sense, just want to make sure I fully understand before I try to implement it. Thanks for all of your help.
Go to the top of the page
 
+
JustXtreme
post Nov 1 2004, 03:02 PM
Post #15

UtterAccess Addict
Posts: 281
From: Florida



Nevermind, I figured that part out. I am going to attempt to make this all work in Access. Thanks again.
Go to the top of the page
 
+
JustXtreme
post Nov 1 2004, 04:43 PM
Post #16

UtterAccess Addict
Posts: 281
From: Florida



Okay, i made 2 tables called dzips and szips. Dzips contains Name, zip, lat, lon. Szips contains Store_ID, zip, lat, lon. I want to make a query that prompts for Store_ID and Distance. I am having trouble with the equation however. Here is what I have tried so far.

dzips.lat > (szips.lat - ((1/69.05) * [dist])) and dzips.lat < (szips.lat + ((1/69.05) * [dist])) and

dzips.lon > (szips.lon - ((1/59) * [dist])) and dzips.lon < (szips.lon + ((1/59) * [dist]))

I can't figure out how to make this work. Am I doing this all wrong?
Go to the top of the page
 
+
LakeGator
post Nov 1 2004, 07:27 PM
Post #17

UtterAccess VIP
Posts: 1,526
From: Tampa, Florida



I assume your SQL is the entire WHERE clause of a SELECT. I created a dzips table that contains the fields to which you refer and created a query:

CODE
SELECT dzips.Store_ID, dzips.ZIP, dzips.City, dzips.State
FROM dzips
WHERE dzips.lat > (szips.lat - ((1/69.05) * [dist])) and dzips.lat < (szips.lat + ((1/69.05) * [dist])) and
dzips.lon > (szips.lon - ((1/59) * [dist])) and dzips.lon < (szips.lon + ((1/59) * [dist]))


I was prompted for szips.lat, dist and szips.lon and Access nicely returned a list of stores within that range. I suspect you want to have this information gathered in a more smooth manner. I would think that using a form to get the Store_ID which will give the szips.lat and szips.lon and also the desired dist value. You can fairly easily create a dynamic query from that data.

Hope this helps.
Go to the top of the page
 
+
JustXtreme
post Nov 2 2004, 11:39 AM
Post #18

UtterAccess Addict
Posts: 281
From: Florida



WOOOO! I got it working, thanks for all of your help!!!!!!!!!!
Go to the top of the page
 
+
LakeGator
post Nov 2 2004, 12:00 PM
Post #19

UtterAccess VIP
Posts: 1,526
From: Tampa, Florida



Good. Glad I was able to help a little.
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 23rd April 2014 - 05:06 PM