Hello,

I have a need to calc the distance between two zip codes. I have the lat and long for both an seem to be stuck.

SELECT [Supply Facility and ZIP table lat long].[Fac name], [Supply Facility and ZIP table lat long].[Fill Lat], [Supply Facility and ZIP table lat long].[Fill Long], [ATL DAL Damage data by ZIP 4_23].Type, [ATL DAL Damage data by ZIP 4_23].[Damage Cost of Sales], [Free-zipcode-database-Primary].Lat AS [Delivery Lat], [Free-zipcode-database-Primary].Long AS [Delivery Long], [ATL DAL Damage data by ZIP 4_23].[Fac Nm], [ATL DAL Damage data by ZIP 4_23].[Ord Num], [ATL DAL Damage data by ZIP 4_23].[Dlvy Pgm Nm], "3958.73926185*((2*ASIN(SQRT((Sin((RADIANS([Delivery Lat])-RADIANS([Fill Lat]))/2)^2)+Cos(RADIANS([Delivery Lat]))*Cos(RADIANS([Fill Lat]))*(Sin((RADIANS([Delivery Long])-RADIANS([Fill Long]))/2)^2)))))" AS Miles, (Sin([x1])*Sin([X2]))+(Cos([x1])*Cos([X2]))*Cos(Abs([Y1]-[Y2])) AS TempDistanceValue, ([TempDistanceValue2]/0.01745329252)*80 AS [Total Distance], [Free-zipcode-database-Primary].City, [Free-zipcode-database-Primary].State, [Supply Facility and ZIP table lat long].Zipcode, [ATL DAL Damage data by ZIP 4_23].[first 5 of zip], Atn(-[TempDistanceValue]/Sqr(-[TempDistanceValue]*[TempDistanceValue]+1))+2*Atn(1) AS TempDistanceValue2, [Fill Lat]*0.1745329252 AS x1, [Delivery Lat]*0.1745329252 AS X2, [Fill Long]*0.1745329252 AS Y1, [Delivery Long]*0.1745329252 AS Y2
FROM ([Supply Facility and ZIP table lat long] INNER JOIN [ATL DAL Damage data by ZIP 4_23] ON [Supply Facility and ZIP table lat long].[Fac num as text] = [ATL DAL Damage data by ZIP 4_23].[Fil Fac Num]) INNER JOIN [Free-zipcode-database-Primary] ON [ATL DAL Damage data by ZIP 4_23].[first 5 of zip] = [Free-zipcode-database-Primary].Zipcode;


Any ideas? Thanks!!

How are you stuck?

Can you provide some detail on your table structure (table names, field names, relationships)?

I see some quotes in this expression that I assume should not be there

"3958.73926185*((2*ASIN(SQRT((Sin((RADIANS([Delivery Lat])-RADIANS([Fill Lat]))/2)^2)+Cos(RADIANS([Delivery Lat]))*Cos(RADIANS([Fill Lat]))*(Sin((RADIANS([Delivery Long])-RADIANS([Fill Long]))/2)^2)))))"

shoei20,

This topic comes up once in a while. You could try this demo in our Code Archive, or use the Search button at the top of the page, and use keywords like "latitude" and "distance" to see other discussions on the topic.

The demo is a straight-line calculator. If you're dealing with distances of less than a couple hundred miles, it'll probably do just fine. If you need a calculation using an earth-is-a-sphere sort of accuracy instead, the math gets more involved, as you can see in this older discussion.

Hope this helps,

Dennis

The formula with quotes around it works in Excel but does not translate to Access. So that is a place holder, reminder of what worked. Trying to calc the great circle distance between a home and destination.

Thanks!!

I will check this out. I did do a couple of searches, but did not see these. Thanks!

Found a link to a Microsoft demo database that contains a bunch of coded formulas, including some Latitude/Longitude stuff.

Go here to get it.

These demos are pretty old, but the math should be sound.

Of course, this sort of math is dependent on knowing the radius of your sphere.

Hope this helps,

Dennis

I have the module in my database, just not sure how to 'attach' this to my query. I have never done this before. usually just nest calcs in a query.

Thanks!

This would be the portion of the module that i need to incorporate.

Function GreatArcDistance(Lat1 As Double, Lon1 As Double, Lat2 As Double, Lon2 As Double, Radius As Double) As Double
'
' Calculates the Great Arc (shortest) distance between 2 locations on the globe.
'
' Uses functions from Trigonometry
'
Dim X1 As Double, Y1 As Double, Z1 As Double, X2 As Double, Y2 As Double, Z2 As Double
Dim CosX As Double, ChordLen As Double
LatLongToXYZ Lat1, Lon1, Radius, X1, Y1, Z1
LatLongToXYZ Lat2, Lon2, Radius, X2, Y2, Z2
ChordLen = Sqr((X1 - X2) * (X1 - X2) + (Y1 - Y2) * (Y1 - Y2) + (Z1 - Z2) * (Z1 - Z2))
CosX = 1 - ChordLen * ChordLen / (2 * Radius * Radius)
Debug.Print X1, Y1, Z1
Debug.Print X2, Y2, Z2
Debug.Print ChordLen, CosX
If CosX = 1 Or CosX = -1 Then
GreatArcDistance = 0
Else
GreatArcDistance = Sqr(1 - CosX * CosX) * Radius * PI() / 2
End If
End Function

shoei20,

Okay, in your query, you have two latitude fields, and two longitude fields. You'd insert a calculated query field along these lines:

GADistance: GreatArcDistance([Fill Lat], [Fill Long], [Delivery Lat], [Delivery Long], 3959)

Notice how you treat a user-defined function just the same as any other function. In fact, if your function is properly installed, as you type the open parenthesis after "GreatArcDistance", Access should prompt you with "Lat1, Lon1, Lat2, Lon2, Radius" as you're typing in the function's arguments.

Note: the function requires your Latitude/Longitude values to be Double precision. If your Latitude and Longitude fields are not Double datatypes, you'll need to convert the values, using the CDbl() function.

GADistance: GreatArcDistance(CDbl([Fill Lat]), CDbl([Fill Long]), CDbl([Delivery Lat]), CDbl([Delivery Long]), 3959)

Hope this helps,

Dennis

THANK YOU SIR!!! My knowledge is growing!!

shoei20,

User defined functions can be a great way of simplifying those nested calculations in your SQL code. Plus, you can add comments to remind you of how/why you are doing something, and complex IF/THEN structures.

Dennis

Growing pains! Function GreatArcDistance(Lat1 As Double, Lon1 As Double, Lat2 As Double, Lon2 As Double, Radius As Double) As Double

Getting a compile error. I defined these fields in my query. So fill lat = lat1

SELECT [Supply Facility and ZIP table lat long].[Fac name], [Supply Facility and ZIP table lat long].[Fill Lat] AS LAT1, [Supply Facility and ZIP table lat long].[Fill Long] AS LON1, [Free-zipcode-database-Primary].Lat AS Lat2, [Free-zipcode-database-Primary].Long AS Lon2, GreatArcDistance([lat1],[lon1],[lat2],[lon2],3959) AS GADistance, GreatArcDistance(CDbl([Lat1]),CDbl([lon1]),CDbl([lat2]),CDbl([lon2]),3959) AS GADistance2
FROM ([Supply Facility and ZIP table lat long] INNER JOIN [ATL DAL Damage data by ZIP 4_23] ON [Supply Facility and ZIP table lat long].[Fac num as text] = [ATL DAL Damage data by ZIP 4_23].[Fil Fac Num]) INNER JOIN [Free-zipcode-database-Primary] ON [ATL DAL Damage data by ZIP 4_23].[first 5 of zip] = [Free-zipcode-database-Primary].Zipcode;


this is what i tried. What changes do i need to make within the module? like x1 = fill lat?

From what I can tell (this is just a guess), two lines need to be commented out (shown in green). Also I could not find a function in VBA for Pi, so i created the variable pi and set its value (see blue)

Function GreatArcDistance(Lat1 As Double, Lon1 As Double, Lat2 As Double, Lon2 As Double, Radius As Double) As Double
'
' Calculates the Great Arc (shortest) distance between 2 locations on the globe.
'
' Uses functions from Trigonometry
'
Dim X1 As Double, Y1 As Double, Z1 As Double, X2 As Double, Y2 As Double, Z2 As Double
Dim CosX As Double, ChordLen As Double
Dim pi As Double
pi = 3.14159265358979

'LatLongToXYZ Lat1, Lon1, Radius, X1, Y1, Z1
'LatLongToXYZ Lat2, Lon2, Radius, X2, Y2, Z2
ChordLen = Sqr((X1 - X2) * (X1 - X2) + (Y1 - Y2) * (Y1 - Y2) + (Z1 - Z2) * (Z1 - Z2))
CosX = 1 - ChordLen * ChordLen / (2 * Radius * Radius)
Debug.Print X1, Y1, Z1
Debug.Print X2, Y2, Z2
Debug.Print ChordLen, CosX
If CosX = 1 Or CosX = -1 Then
GreatArcDistance = 0
Else
GreatArcDistance = Sqr(1 - CosX * CosX) * Radius * pi / 2
End If
End Function

The Pi function is located in the Trigonometry code module in the Microsoft demo database.

You'll probably need to copy that module as well, rather than assume you only need the one function.

Hope this helps,

Dennis

That was the fix!! Now the mileage does not seem accurate. 2371 miles from Atlanta to Puerto rico.

Thanks again!!

I did the same thing just a few weeks ago, my function looks like this:



This works fine and to my surprise also at reasonable speed...

I seem to be getting a 'syntax error comma in wrong'.

GeoDist: ([LAT1],[Lat2],[LON1],[Lon2])

Thanks!

First of all, the order of your parameters is not correct - it should be lon1, lat1, lon2, lat2.

Where exactly do you get the error? Did you run through the code step-by-step? What are the values of the parameters?


Btw: what localization of Access do you use?

Thank you! I figured out my issue, rookie mistake!!

Good to hear - do you get correct results then?

I just tried Atlanta-Puerto Rico and the result is 1535,75 Miles...

For others who may follow this thread there is good material re great circle distance at
http://www.cpearson.com/excel/LatLong.aspx

Results are better, good as the crow flys distance calc.

Thanks again!