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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   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



Posts: 281
Joined: 22-April 04
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



Posts: 1,697
Joined: 28-December 00
From: Nevada


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



Posts: 281
Joined: 22-April 04
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



Posts: 281
Joined: 22-April 04
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
Joined: 11-January 04
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.
Oleft 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



Posts: 281
Joined: 22-April 04
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



Posts: 281
Joined: 22-April 04
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



Posts: 281
Joined: 22-April 04
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
Joined: 11-January 04
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



Posts: 281
Joined: 22-April 04
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



Posts: 281
Joined: 22-April 04
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
Joined: 11-January 04
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.
That 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
Joined: 25-September 02
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



Posts: 281
Joined: 22-April 04
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



Posts: 281
Joined: 22-April 04
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



Posts: 281
Joined: 22-April 04
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.
zips.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]))
Ocan'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
Joined: 11-January 04
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:
!--c1-->
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



Posts: 281
Joined: 22-April 04
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
Joined: 11-January 04
From: Tampa, Florida


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


Custom Search
RSSSearch   Top   Lo-Fi    3rd September 2015 - 08:28 PM