I did something really cool at work today, but first a little background. I am the only person at work with any MS Access database skills so periodically I will get tapped to help out departments around the office. That isn't a problem because I enjoy doing it. The latest database had absolutely nothing to do with GIS.

Its purpose is to track engineering consultants that want to do work for us. It has a data entry function that lets the user enter all of the relevant information about a given consultant, both objective and subjective, crunches out a score, and then creates a printout that has the contractors with the highest total score sorted to the top and the lowest scores at the bottom. It also divides the contractors into 16 different groups depending on what type of work they do, but that isn't important to this story.

It took me a day to create the database. A day to scrap it and start over because my original design was not as efficient and flexible as it needed to be, and then two days to make and test changes that the guy that needs the database asked for.

This morning he called me and said he wanted to know if I could add a field to show the city of each contractor. He said the reason is that when deciding on a contractor to hire, even if they have the absolute highest score out of all the other applicants, if they are 200 miles away that might just present too many logistical problems. He didn't want it to affect the scoring in any way. He just wanted their city listed on the report so he could keep that in mind when making decisions.

What he wanted to accomplish with this addition is totally valid, I just didn't like the way he asked me to execute it. The problem to me is that it assumes you know where all of the cities in the region are. I like to design things by making as few assumptions as possible. I don't want to assume that whoever is going to use this report is going to know where every city is in relation to our office. Maybe the guy that replaces him will be recruited from another state and knows nothing about the local geography.

Think…think…think… Got it!

I found a GIS layer of every ZIP code in the United States. I loaded that into my GIS application and performed a routine to find the geographic center (centroid) of each ZIP code and then write the latitude and longitude of each centroid to the table. I then exported just the resulting table, no graphics or maps or anything, into my contractor database. I think there were about 45,000 different ZIP codes.

I then wrote a Visual Basic routine that finds the ZIP in the table for a given contractor, pulls the lat and lon from the table, and then calculates the distance from the center of the contractor's ZIP code to the vestibule of our office. NOW I can report definite numbers that anybody can make sense of and no one needs to know where any a city is to understand if a contractor is 15 minutes away or three hours away. I can sort by distance now too which is more useful than sorting my city name. How cool is that?

What do the calculations look like that find the distance between two lat and lon coordinates? Glad you asked.

Function CalcDist(Lat, Lon)
Dim OurLat, OurLon, p1, p2, p3, TempNum, EarthRadius, KiloToMiles
EarthRadius = 6378.1
KiloToMiles = 0.621371192

' convert from degrees to radians
Lat = Lat * (3.14159265358979 / 180)
Lon = Lon * (3.14159265358979 / 180)

OurLat = XX.XXX * (3.14159265358979 / 180)
OurLon = -XX.XXX * (3.14159265358979 / 180)

p1 = Cos(Lat) * Cos(Lon) * Cos(OurLat) * Cos(OurLon)
p2 = Cos(Lat) * Sin(Lon) * Cos(OurLat) * Sin(OurLon)
p3 = Sin(Lat) * Sin(OurLat)

TempNum = p1 + p2 + p3
' This is the derived function of arccosine
'Atn((-1 * TempNum) / Sqr((-1 * TempNum) * TempNum + 1)) + 2 * Atn(1))

CalcDist = ((Atn((-1 * TempNum) / Sqr((-1 * TempNum) * TempNum + 1)) + 2 * Atn(1))) * EarthRadius * KiloToMiles
End Function

The toughest part was that the Visual Basic module in Access does not have an arccosine function, so I had to search around on the Internets. The formula to calculate arccosine is the entire line below the line of text that starts "This is the derived…"

The weird thing is when I was testing the logic of the code I was using an Excel spreadsheet and Excel has and arccosine function. Go figure.

Lat and lon are angular measurements (measurements of angles)** and computers like to work in radians, so the four lines where I am multiplying a lat or lon by pi divided by 180º is converting degrees to radians.

I created the constant called EarthRadius to, of course, hold the average radius of the Earth in kilometers. I could have used miles but I started out using miles and then I wouldn’t have to multiply by the KiloToMiles constant I created but I didn't so sue me.

To help you better understand the code these are what the Visual Basic functions are:

Atn = arctangent
Cos = cosine
Sin = sine
Sqr = square root

Lines that start with a quotation mark ( ' ) are comments just so it is easier to read when I go back in a couple months to make changes to it.

On the lines where I am create the variables OurLat and OurLon for the lat and lon of our office I X'd that actual coordinates out for this post to prevent my stalkers from stalking me.

If any real programmers are reading this, yeah I know it is not as tight as it could be but I think it is pretty good considering.

** Look forward to another intriguing post from me soon splaining how latitude and longitude are actually angular measurements.