Distance Between Zipcodes

[stapuff]

I have a table with Customer orders that have already shipped. What I am try to find out is if the order actually shipped from the closest warehouse (by miles) or not using latitude / longitude. I currently have 6 warehouses.

I have the lat / long formula working in Excel (for a couple of years now) and would like to get it working in Access if possible.

My zipcode Ship To table is in the following format:

Ship To Zip: 46514
Ship To Lat: 41.718730
Ship To Long: -85.937024


My zipcode Ship From table is in the following format:

Ship From Zip: 60439
Ship From Lat: 41.70743
Ship From Long: -87.975568


The Excel formula I use below returns 633.572201 miles.

3963*ACOS(COS(RADIANS(90-(Ship To Lat*24)))*COS(RADIANS(90-(Ship from Lat*24)))+SIN(RADIANS(90-(Ship To Lat*24)))*SIN(RADIANS(90-(Ship From Lat*24)))*COS(RADIANS(24*(Ship From Long-Ship To Long)))))

Any suggestions to incorporate this into Access would be appreciated.

Thanks,

Kurt

 

[jackd]

I have a table with Customer orders that have already shipped. What I am try to find out is if the order actually shipped from the closest warehouse (by miles) or not using latitude / longitude. I currently have 6 warehouses.

I have the lat / long formula working in Excel (for a couple of years now) and would like to get it working in Access if possible.

My zipcode Ship To table is in the following format:

Ship To Zip: 46514
Ship To Lat: 41.718730
Ship To Long: -85.937024


My zipcode Ship From table is in the following format:

Ship From Zip: 60439
Ship From Lat: 41.70743
Ship From Long: -87.975568


The Excel formula I use below returns 633.572201 miles.

3963*ACOS(COS(RADIANS(90-(Ship To Lat*24)))*COS(RADIANS(90-(Ship from Lat*24)))+SIN(RADIANS(90-(Ship To Lat*24)))*SIN(RADIANS(90-(Ship From Lat*24)))*COS(RADIANS(24*(Ship From Long-Ship To Long)))))

Any suggestions to incorporate this into Access would be appreciated.

Thanks,

Kurt

stapuff,

Maybe I'm missing something, but if I type your 2 zip codes into google maps
60439 and 46514, it tells me these are about

Driving directions
118 mi – about 2 hours 9 mins

Elkhart, IN 46514
USA

Illinois 60439
USA

Please let us know if your 633.572201 miles is for this drive.

I think your number is about 5 times larger than it should be. But, maybe I've misunderstood part of your post.

Also, zip codes are "area locators" not points. Unless you have geographic centres of zip codes, your numbers will be approximate. So your 6 places of decimal are a little much.
I still think your distance calculation is too high.

You can test your numbers by putting 2 zip codes into google maps. See if it makes sense.
I have done distance calculations using formula in Access.

 

[stapuff]

jackd -

I appreciate the post back and clarification.

The formula I originally posted is incorrect.

I have tested the following formula, which returns 105.1281097
3958.2*((2*ASIN(SQRT((SIN((RADIANS(AH9)-RADIANS(AK9))/2)^2)+
COS(RADIANS(AH9))*COS(RADIANS(AK9))*
(SIN((RADIANS(AI9)-RADIANS(AL9))/2)^2)))))

as far as the 6 places of decimal - that is how the data was given to me and the approximate is what I am looking for. The warehouses are spread out enough that this should really be a huge issue.

Any thoughts would be appreciated.

Thanks,

 

[jackd]

jackd -

I appreciate the post back and clarification.

The formula I originally posted is incorrect.

I have tested the following formula, which returns 105.1281097
3958.2*((2*ASIN(SQRT((SIN((RADIANS(AH9)-RADIANS(AK9))/2)^2)+
COS(RADIANS(AH9))*COS(RADIANS(AK9))*
(SIN((RADIANS(AI9)-RADIANS(AL9))/2)^2)))))

as far as the 6 places of decimal - that is how the data was given to me and the approximate is what I am looking for. The warehouses are spread out enough that this should really be a huge issue.

Any thoughts would be appreciated.

Thanks,

Can you tell me again what fields are in your new formula?

Lat Long To Lat Long from etc. and AI9 and AL9

 

[stapuff]

jackd -

Yaaaaaaaaaahoooooooooooooooooo!!!!

Solved this one. Andrew Fergus deserves a KISS.

This is how I did it.

Goto http://www.mrexcel.com/forum/showthread.php?t=286239&highlight=Radians
Download Andrews .dll and follow the instructions. I used the manual way to register the .dll listed under the Note on the bottom.

Goto to Tools / Marco / VB Editor
Paste the following:

Public Function ArcSin(InputVal As Double) As Double
Dim ATF As XLTrigFuncs.XLFunctions
Set ATF = New XLTrigFuncs.XLFunctions
ArcSin = ATF.ArcSin(InputVal)
Set ATF = Nothing
End Function


Public Function Radians(InputVal As Double) As Double
Dim ATF As XLTrigFuncs.XLFunctions
Set ATF = New XLTrigFuncs.XLFunctions
Radians = ATF.AngleRadians(InputVal)
Set ATF = Nothing
End Function


I then added Mileage field
Mileage: 3958.2*((2*ArcSin(Sqr((Sin((RADIANS([Ship_Lat])-RADIANS([Rec_Lat]))/2)^2)+Cos(RADIANS([Ship_Lat]))*Cos(RADIANS([Rec_Lat]))*(Sin((RADIANS([Ship_Long])-RADIANS([Rec_Long]))/2)^2)))))

Bingo!


Thanks,


Kurt

 

[jackd]

Here's a link that you might find useful. Doesn't apply directly to your question, but I think you'll find it interesting.

http://www.allplaces.us/dbz.cgi?z1=60434&z2=46514

 

[stapuff]

Re: Userdefined Error -Distance Between Zipcodes

I am now trying to bring the data into Excel and am getting a function "ArcSin" undefined error. How do I get around this?

Currently 110,000 rows of data in Access. Excel only accepting the 65,000 rows.

Thanks,

Kurt

 

[jackd]

Re: Userdefined Error -Distance Between Zipcodes

I am now trying to bring the data into Excel and am getting a function "ArcSin" undefined error. How do I get around this?

Currently 110,000 rows of data in Access. Excel only accepting the 65,000 rows.

Thanks,

Kurt

Stapuff,

ArcSin is a Math/Trig function in Excel. It was defined in Andrew's functions.
It is available in Excel.
I'm not an Excel user, but I think you can set the appropriate reference to the Excel Library in Access and do your calculations there, since those Functions AecSin and Radians should be available.

Why are you putting data into Excel now? I thought you were trying to get your stuff working in Access.

 

[stapuff]

jackd

ArcSin is a Math/Trig function in Excel. - you are correct, however, in Excel known as ASIN
It is defined in Andrew's functions (in Access). - you are correct.
It is available in Excel. - you are correct.

I'm not an Excel user, but I think you can set the appropriate reference to the Excel Library in Access and do your calculations there, since those Functions AecSin and Radians should be available. - I have Access doing the calculations using Andrew's add-in and all references I think I need are set.

Why are you putting data into Excel now? Row limitations in Excel being a big factor. I thought you were trying to get your stuff working in Access. - Again - it does work in Access.

In a previous post - I mentioned 110,000 records. That is only based on the last 18 months. I really need to go back 3 yrs, which Excel 2003 can not handle 110,000 and now adding more.


Kurt

 

[jackd]

So why not just use Access? I'm not sure if you have a problem or you're just confirming you have solved the problem.