Calculating Distances (1 Viewer)

Is there any way that MS Access can calculate distances between two landmarks, each pin-pricked by a precise Latitude and Longitude co-ordinate?

I've read that there is a Haversine formula/methodology to do this but can that be translated into code that's usable by Access?

What format are you storing the Lat, Long in? ie. Decimal degrees, degrresa and decimal minutes etc.

This piqued my curiosity, so I set about seeing just how I would do it. Attached is my first effort. It has no validation or error trapping so be careful when entering way points.

Wow, I'm impresesd - Nice work! It got my curiosity too but when I looked at the maths involved I passed!

The maths isn't too tricky so long as you're careful. The interesting part was learning how to get around VBA's lack of an ArcCos (ACOS) function.

Hi JBB

The format in which I'm storing the data is, for example
Latitude 50 43 53.73N
Longitude 01 20 57.87W

... using an input mask of my own design being >00\ 00\ 00\.00L

Not quite sure what you mean by Way Point requirements ... to clarify I want point-to-point distances in miles and yards, as a bird would theoretically fly!

So that you know what I'll be looking to achieve is multiple distances between the same fixed point and multiple destinations all of whose latitude and longitude co-ordinates are all stored in a 'locations' table in the above format e.g. 01 20 57.87W

I've looked at your (fabulous) solution and wonder how it could be amended to achieve this? Maybe the FRM_LatLongEnt form could be used as the vehicle to enter/select the 'from' destination co-ordinates with the macro then calculating distances between this 'from' and all the 'to' destinations held in the locations table?

If I get a chance I'll have another look at the DB tomorrow and see what I can come up with.

As an aside there is no need to store distances in the table as these can all be calculated as required. This reduces the amount of storage space the DB consumes and gives you far more flexibility in the long term.

i was looking at this, among other mapping issues

theres loads of stuff out there indicating that

a) there are multiple geo coordinate systems not just one, so lat/long may not be an absolute

b) the earth's curvature will affect the distance (ie its not quite pythagorus, although it probably is for relatively small distances

c) some solutions also take into account height above sea level, as this affects the distance

I didn't need distances for my project currently, but there were loads of code examples available to generate the calculation

Hi Gemma

The range of distances I will be encountering will be between 20 - 1500 miles ... so hopefully even in the upper extremity I wouldn't encounter some of the problems to which you refer? I think the calculation needs to avoid such 'realities' as mountain ranges which would increase the distances slightly, so we'll need to assume that it's a bird's flying distance at ground level.

Gemma you are absolutely correct, however Lat Long is pretty well the default, using WSG 84 as the datum. Converting to other co-ordinate systems is way beyond the scope of this project (from my point of view at least). As for the accuracy of the calculations, I guess it all depends on what you wish to do with the results, also bear in mind that hand held GPS systems can only guarantee an accuracy of +- 10m.

PaulO, Ive added a new option to the Distance calculation form that will now return distances to all other way points from the origin way point. There is still some polishing that can be done, I'll repost when I get a chance.

If you are after a way point management system then have a look here it's a pretty good product in my experience.

Hi John

Some attachments showing 1) how I'm storing data and 2) problem in Add Waypoint macro.

Ideally I will want to append results into another table if that's possible as there will be multiple distance calculations for each Member (from multiple Waypoints).

But I can already see that the system works great!

Hi Paul

Sorry, my apologies there was an error in one of the append queries, It's now fixed I hope. I've also added some validation and error trapping, that "should" ensure that only valid way points can be added.

There is also the ability to show the distance of all way points from a selected central way point. The distance page also shows the co-ordinates for each way point. Yes it is in Degrees and Decimal Minutes, but if you have a look at how I've done that You should be able to figure out how to add additional text boxes to show the co-ordinates in Degrees Minutes and Seconds.

Having worked in the satellite navigation industry over 20 years ago, my math is as rusty as it ever gets. However, here are things you need to know.

There are three main methods of changing Lat/Lon into "flat" coordinates. Each induces distortion because it "projects" the coordinates on a sphere onto something flat, an x-y map. The nature the projection is different in each case. If you use a projection-based method, your goal would be to choose the method that gives you the most accurate results for the two endpoints. Which is best done by chosing a reference point for any of the projections as being halfway between the two end-points.

Using Mercator's projection, you get a cylindrical map for which the "east-west" component distances become more distorted the farther north or south you go from the reference latitude. Usually the distortion makes the things towards the poles look larger (wider) than they should.

Using Lambert's projection, you get a conical map for which the "east-west" component distances become more distorted the farther north or south you go from the reference latitude, and the distortion depends on whether it was north or south.

Using the Cassini-Soldner projection, you get a pincushion map for which all distances are distorted the farther you go from the refernece point, and the distortion exaggerates incredibly along the diagonals of that map.

In all cases, the conversion is relatively simple and ArcCos is not that hard to live without. But the harder part is knowing how to compensate for the distortion. Some aircraft navigation programs have something called a "Great Circle" route calculator, which approximates the distance between two Lat/Lon positions by computing the chord of a circle that passes through those two points and the center of the earth. (Three points define a plane, after all...)

It has been too long and my reference books were lost in Katrina, so I can't give you all of the formulas. However, if you search the Internet for Great Circle calculations, you might find something useful. And as far as ArcCos(x) is concerned, you can find an equivalence formula based entirely on ArcTan(x), which VBA can handle.

JBB ... I've started converting all the Waypoint co-ordinates stored in Lat and Long format into the decimal equivalents and hope tomorrow to build a mass update calculation using your formulas and forms ... watch this space but so far it's looking good!

Doc Man ... wonderful stuff though way above my head at first pass! The only question I have is whether I should be paying any attention to the Earth's natural distortions as I believe I'm striving to achieve a 'point-to-point' distance assuming the Earth has no distortions.

PaulO:

Unfortunately, the Earth's distortions become difficult to manage since you suddenly need to know EXACTLY where each one is. You must consider two major factors here.

First, most of the trig routines are handled by subroutines that use what are called "telescoping series approximations" - i.e. SIN(x), even in double precision, isn't exactly accurate. (Though it is pretty good.) The errors for some angles will be very small indeed. But as a practical matter, some of the errors can get fairly big near 45 degrees. Which by odd chance happens to coincide with the area on the Earth where that surface distortion can become an issue.

Second, the Earth is not a sphere, it is an oblate spheroid, and taking the bulges and sags into account is really ugly. Fortunately, the errors of the first point I made often tend to make the non-spherical corrections immaterial. As a first approximation, don't count the differences for anything much.

Here is the part that MUST stand out in your mind... the farther apart those two points are, the greater the effects of ANY distortion, and that is true for ANY projection. You have suggested that you might have distances up to 1500 miles apart. Given that this is over 5% of the circumference of the earth (roughly speaking), you can expect distortions to really be an issue. I might consider seriously seeking that great-circle algorithm. It is a pain in the toches but it won't distort quite as quickly as some of the projections.

As to the projections, here's a real-world image of how you would make them.

For Mercator, stick a really bright neon light bulb in the Earth parallel to the polar axis. Now take a cylinder of paper. Let the light project out through the earth (I said it was a REALLY bright bulb.) The image cast on the cylinder is a Mercator projection.

For Lambert, stick a bright light bulb in the center of the earth. Now take a cone of paper and put it over the earth like a lampshade. (Hmmm... brings back vague memories of some fun parties.... but I digress). The image on the conical paper is a Lambert projection.

For Cassini-Soldner, stick the bright ligh bulb in the center of the earth. Now take a flat piece of paper and tack it to the earth at the reference coordinate. The image on that piece of paper is a Cassini-Soldner projection.

For the great circle calculation, take a circle the size of the Earth's equator. Stick it through the center of the earth so that it slices the center and the two coordinates. The chord (geometric sense of that word) of the circular slice is the shortest distance between the two points that stays on the surface of the earth.

Great explanation DOC. Taking into account the various local geodes and other possible variations, is just way beyond the scope of this project. In the end though I guess the accuracy that is required depends greatly on the end use of the data, and from what Paul has said it would seem that the great circle approximation, that I've used should suffice. I did find the work around for the lack of an ACOS function

Paul, you shouldn't need to convert your co-ordinates as the input form "should" accept any of decimal degrees, degrees and decimal minutes, or degrees minutes and seconds. The three queries that store this data to the table convert those three systems to radians. Simply because it made the maths a little easier, when it came to calculating the distances. It should be a fairly simple matter to convert that back to degrees minutes and seconds. The only reason I haven't done it was lack of time.

The view all button now works.

Thanks John ...

I'll take a look shortly. Considering your "lack of time" you've done a great job and gone way beyond my expectations when I created this post!

It was a topic that, as I mentioned earlier, piqued my curiosity. It has been a long time since I've played around with spherical geometry. Back then the sort of navigational, and remote sensing technology, we have available today, was limited to the military, or those with very deep pockets.