By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,967 Members | 1,179 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,967 IT Pros & Developers. It's quick & easy.

how to calculate distance based from lat long

P: 3
I have a distance formula that works in excel but does not work on msaccess because some of the functions are not available like ACOS.

dist= Acos(Sin(lat1)*Sin(lat2)+Cos(lat1)*Cos(lat2)*Cos(l ong2-long1))*6371

What formula can i use to replace this and make it work on ms access?
Nov 10 '11 #1
Share this Question
Share on Google+
8 Replies


ADezii
Expert 5K+
P: 8,679
You can actually make the existing Formula work within Access. Sin() and Cos() are supported whereas ACos() is not, but you can execute this Function within Access using Automation Code. The following Code will work, assuming you substitute Values for the actual Variables, of course.
Expand|Select|Wrap|Line Numbers
  1. 'Make sure to 1st set a Reference to the Microsoft Excel XX.X Object Library
  2. Dim objExcel As Excel.Application
  3.  
  4. Set objExcel = CreateObject("Excel.Application")
  5.  
  6. With objExcel.Application.WorksheetFunction
  7.   dist = .Acos(Sin(lat1) * Sin(lat2) + Cos(lat1) * Cos(lat2) * Cos(long3 - long1)) * 6371
  8. End With
  9.  
  10. MsgBox dist
  11.  
  12. objExcel.Quit
  13. Set objExcel = Nothing
Nov 10 '11 #2

NeoPa
Expert Mod 15k+
P: 31,709
Isn't that rather complicating matters? Wouldn't Pythagoras be an acceptable, and much simpler, approach?

Expand|Select|Wrap|Line Numbers
  1. Dist = Sqr((Lat2-Lat1)^2+(Long2-Long1)^2)
Euclidean geometry teaches us many things and gives all these tools, but we still need to select the simplest one.
Nov 11 '11 #3

ADezii
Expert 5K+
P: 8,679
Wouldn't Pythagoras be an acceptable, and much simpler, approach?
I don't think that we are talking about the Pythagorean Theory (Hypotenuse) here are we? The 2 Expressions are not equivalent, are they?

P.S. - The Formula posted by the OP will calculate the Distance between 2 Latitudes/Longitudes.
Nov 11 '11 #4

NeoPa
Expert Mod 15k+
P: 31,709
I suppose I've never dealt with them in detail. They must be angle co-ordinates rather than distance ones then. Surely, even in that case, a mean (or otherwise constant) value for the radius of the Earth will always introduce a fair amount of inaccuracy, no? I'm assuming that's the significance of the 6,371 value in the formula.

PS. Understanding that the Earth is not quite spheroid led me to the conclusion that absolute accuracy was not an attainable aim - hence my equally imprecise suggestion based on an approximation. Clearly it would need different parameters from what's available though.
Nov 11 '11 #5

P: 3
Phytagoras should be ok as i don't need the exact distance rather the nearest to a certain point. Also, sqr is also not in ms access 2007

@ADezii How can i write that syntax on MS Access?...What i have is a excel spread sheet with column of lat1, long1, lat2, long2 and would like to output a table with additional column with distance within access.
Nov 12 '11 #6

P: 3
I guess my issues is how i could make sqr or acos or other functions in excel for that matter available on ms access 2007.
Nov 12 '11 #7

NeoPa
Expert Mod 15k+
P: 31,709
Sqr() actually is available (See Access 2007 VBA Programming For Dummies) to all of Office 2007 via the VBA library (in the class Math).

For Excel worksheet functions I believe one would use SQRT() instead though.
Nov 12 '11 #8

ADezii
Expert 5K+
P: 8,679
How can i write that syntax on MS Access?...What i have is a excel spread sheet with column of lat1, long1, lat2, long2 and would like to output a table with additional column with distance within access.
  1. This can easily be done via a Query, but I must first know how are you accessing the Data in the Excel Spreadsheet, Linked, Automation Code, etc.?
  2. I also need to know the specific Data Type for Longitude and Latitude. Kindly post some Sample Data for these Values as it wouold exist in the Spreadsheet.
Nov 12 '11 #9

Post your reply

Sign in to post your reply or Sign up for a free account.