how to calculate distance based from lat long

[Budjit]

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(l at1)*Cos(lat2)* Cos(long2-long1))*6371

What formula can i use to replace this and make it work on ms access?

[ADezii]

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.

'Make sure to 1st set a Reference to the Microsoft Excel XX.X Object Library
Dim objExcel As Excel.Application
  
Set objExcel = CreateObject("Excel.Application")

With objExcel.Application.WorksheetFunction
  dist = .Acos(Sin(lat1) * Sin(lat2) + Cos(lat1) * Cos(lat2) * Cos(long3 - long1)) * 6371
End With

MsgBox dist

objExcel.Quit
Set objExcel = Nothing

[NeoPa]

Isn't that rather complicating matters? Wouldn't Pythagoras be an acceptable, and much simpler, approach?

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.

[ADezii]

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.

[NeoPa]

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.

[Budjit]

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.

[Budjit]

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.

[NeoPa]

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.

[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.

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.