Hack 12. Create a Distance Grid in Excel

Create tables showing the distance and bearing between pairs of points, using a simple add-in library for Excel.

Creak out of the car, road blurred eyes, accelerator stiffened joints, then wander dazed to the large grid that shows you at "Forgotten Rest Area #17" and the distance to anywhere you'd want to be. The joy of gas station city-distance grids! Look down the left column for "Where I am" and across to find "The promised end of the trip," and read the measure of your pain. Now all the fun and excitement can be yours again, in the comfort of your very own home!

Start by downloading http://www.mappinghacks.com/data/spreadsheet/sample.xls, as described in [Hack #11] . The sheet labeled train grid, shown in Figure 1-23, depicts a sample distance grid for a set of waypoints of train stations at Disneyland. The grid shows the straight-line distance between any two points in yards. Look down column B and locate the Main Gate in cell B24, then move across to column G and read the distance from the Main Gate to the Tomorrowland train station, which is 445 yards.

Figure 1-23. The distance between train stations at Disneyland

The distance grid represents the "as the crow flies" distance. Calculating road distance requires routing algorithms and databases that are beyond the scope of this hack.

The train grid sheet also includes a bearing grid to show your straight-line course from point to point. Remember your mnemonics of direction: Never (North, 0 degrees) Eat (East, 90 degrees) Smelly (South, 180 degrees) Worms (West, 270 degrees) (or "Never Eat Sour Watermelons").

Take a look at the next worksheet, labeled equator grid, as shown in Figure 1-24. This is the same basic table, but this one shows the distance and direction within one degree of the equator and the prime meridian: from (0, 0) to (1N, 0), (1S, 0), (0, 1W), (0, 1E), (1N, 1E), and (1S, 1W). This sheet displays distances in statute miles, as opposed to yards. The conversion factor is set in the cell labeled conversion_factor (C14).

Figure 1-24. Distance and bearings near the equator and the prime meridian

The point of this test sheet is to do a simple sanity check on the data. How far is it from (0, 0) to (1N, 0)? It should be 69 statute miles. And what is the bearing? Due North, or 0 degrees. And from (1S, 1W) to (0, 0) should be approximately the Pythagorean distance of (692 + 692), or 97.58 statute miles.

1.13.1. Making Your Own Grid

While a distance grid of the train stations of Disneyland may be fascinating to Disneyphiles, you are likely more interested in the stories intrinsic to your own personal landmarks and waypoints. The sheets named Your_Data_Here, Your Distance Grid, and Your Bearing Grid are for you! They start out showing yet more waypoints from Disneyland because, after all, everyone should have the tools to determine that it is about 152 yards from the Enchanted Tiki Room to the entrance to Pirates of the Caribbean, but you can paste up to 96 points into Your_Data_Here and automatically get a distance grid in Your Distance Grid and a bearing grid in Your Bearing Grid.

The limit of 96 points is purely arbitrary. Feel free to examine the structure of the sheets and extend them to infinity and beyond. To use these sheets, paste your own point name, latitude, and longitude into Your_Data_Here (Figure 1-25) starting at B5:D5 and going as far as B100:D100.

Figure 1-25. Enter your points into this sheet

The posDist() function returns distance in nautical miles. To convert these to statute miles, you need to enter the conversion factor of (approximately) 1.15 into cell C3. To convert to yards, enter 2025 (which is the current default), and for feet, 6075. You can also enter a compass declination in cell F3. This number will be added to the values in the Bearing table to correct for the difference between true and magnetic north.

1.13.2. Let's Go Diving

One potential use of this grid might be to enter the waypoints for underwater diving sites in your area of interest. Chuck Tribolet has a nice page of GPS resources for diving Monterey Bay, California at http://www.garlic.com/~triblet/swell/gps.html, including a list of dive locations with latitudes and longitudes. As a diver, I might find it useful to have distance and bearing grids on hand for these locationsthat way, after a dive, I can quickly consider distance in my decision of where to dive next. Figure 1-26 shows an example distance grid for this use case. Similarly, Figure 1-27 shows a sample bearing grid.

Figure 1-26. Distance grid of selected Monterey area dive sites

Figure 1-27. Bearings between selected Monterey area dive sites

While these tables do provide the literal distance and bearing between points, they don't consider actual barriers to navigation, such as the absence of roads or the presence of cliffs. You might think that you could get home to the Breakwater Ramp in Monterey Harbor from a dive at the south end of Point Lobos by steering a course of 22 degrees. Unfortunately for you, there are a number of miles of nonaqueous dirt between these points. Happy diving, and watch out for rocky points!