3.3 Converting Geographic Coordinates

Researchers often have to convert coordinates of points on the earth from one format into another.  This also provides us with the opportunity to bring in code from outside Excel and to learn more about the mathematical capabilities of VBA.

Open Excel and enter the VBA environment.  Open the example text file “BRTE.txt”.  This file contains the coordinates of the locations of cheat grass (Bromus tectorum) in Colorado, USA.  The grass is originally from the steppes of Russia and is an invasive species in the United States.  Notice the three columns labeled “GPSRegion”, “GPSEasting”, and “GPSNothing”.  The GPSEasting and GPSNorthing are the coordinates of the point in the Universal Transverse Mercator (UTM) projection.  This projection is commonly used on Geographic Positioning Devices (GPS) and provides the coordinate in meters. 

We are going to convert these coordinates into the geographic projection. Then we will convert the coordinates into degrees, minutes, and seconds.

The geographic projection is the most commonly used system for describing locations on the earth.  This system divides the earth into 360 degrees, starting with -180 degrees in the middle of the Pacific Ocean and increasing to the east with 0 at the “prime meridian” in Greenwich England.  The values increase until we reach 180 degrees again in the middle of the Pacific Ocean.  These east-west positions are known as “Longitude”.  The north south position is defined as “Latitude” and begins with -90 degrees at the South Pole and increase to 0 at the equator and continues to 90 at the North Pole. 

The problem with geographic coordinates is that they can be expressed in a variety of formats.  Instead of using positive and negative numbers, the older system indicated if the points were east or west of the prime meridian with an E or a W, and used a N or S to indicate if the points were North or South of the equator  Thus -100 degrees was recorded as 100 degrees east or just 100 E.

When we are working with geographic positions in science we almost always use “decimal degrees”, where we express the longitude and latitude as a floating point number.  However, the older system was to break up each degree into 60 minutes and then each minute into 60 seconds.  Thus we may have data recorded as 100 degrees, 30 minutes, 12 seconds north or simply 100° 30’ 12” N.  This notation can be found on old maps and nautical charts throughout the world.

The UTM projection divides the earth into a series of 60 north-south strips or “zones” each of which is 6 degrees wide.  These zones start at 180 degrees west, in the middle of the Pacific Ocean, and are numbered from 1 to 60. The continental United Sates falls in zones 9 through 19.  UTM divides the earth into 22 east-west regions but, as you will see, we don’t actually need these.

In our table, the GPSRegion column defines the zone number, 12 in this case.  The ‘n’ however is not a UTM Zone Letter but instead an indication that we are ‘north’ of the equator.  This is commonly used with ‘n’ designating ‘north’ and ‘s’ designating ‘south’.  This data is from Colorado, and if you examine the figure below you’ll see that zone 12 does pass through Colorado but region ‘n’ is actually far south of the United States.  This is one of the most common mistakes made when recording UTM data.  The recorder has actually entered ‘n’ for north instead of the region which should be ‘S’ or ‘T’.  The good news is that we don’t need to know the region to find the actual location of the coordinate, only the zone and whether we are north or south of the equator.

 

In the UTM system, the north-south position is referred as a “Northing”.  If we are in the northern hemisphere the Northing is the number of meters we are from the equator.  If we are in the southern hemisphere the Northing is the number of meters we are from the South Pole.  The east-west position is referred to as an “Easting” and is measured from an imaginary line running thru the middle of the zone.  The only tricky part is that the middle of the zone is arbitrarily set at 500,000 meters.  As we move to the west of the middle of the zone the value decreases and as we move to the east the value increases.  Because the zones are 600,000 meters wide at their widest the Easting’s are always positive.

Diagram of UTM Easting and Northing.  The Northing is shown as starting at 0 at the equator and increasing as we move north.  The easting starts at 500,000 in the middle of the zone and decreases to the west.  This diagram is not to scale.

(? Add the zone lines around the diagram)

1.  

We typically think of the earth as a sphere, but in reality it bulges somewhat at the equator, making it an ellipsoid.  Over the past decades, we have been using satellite data to obtain better measurements of the amount of “flattening” the earth has.  This has resulted in a new set of “Datums” that approximate the shape of the earth.  This includes the World Geodetic System 1984 (WGS84) and most recently the High Accuracy Reference Network (HARN).  There is little difference between these two systems. I recommended WGS84, as it is widely supported by GPS devices. 

In the past, surveyors would create Datums at national levels that approximated the curvature of the earth within a national boundary.  This resulted in the North American Datums of 1927 (NAD27) and 1983 (NAD83) which are actually based on measurements by Lewis and Clark.  The difference in measurements between a NAD27 coordinate and a WGS84 coordinate can be in the thousands of meters.

If you were worried about having to write the code to convert UTM coordinates to geographic coordinates, don’t be.  We have provided a Visual Basic module that contains the functions you’ll need.  Do the following to include the module in your workbook:

1. Right click on “Modules” and select “Import File…”
2. Browse to the “UTMConverter.bas” and click ‘OK’

The file should appear under “Modules”.  Double click on the file and take a look at its contents.  One of the best ways to learn a new language is to examine other programmers’ work.  This limits the scope of what you are looking at to the portions of the language that folks actually use, and helps you to know you are learning from a working example.

As you scroll down you will notice the file is broken into a number of sections.  At the top is some documentation on where the code came from.  Next are the public and private constants.  Then we have the private functions and public functions.  We will learn about the meaning of ‘private’ and ‘public’ a little later. 

We’ll be using the “UTM_GetGeographicFromUTM()” function.  Read the header blocks at the start of this function to learn how to call it.  As you can see the documentation associated with code is an important part of programming.  Always include a header block in each file describing what the purpose of the file is and who the author is.  Each function that can be called from outside the file should have a header block that explains what the function does, what its input parameters are, and what it returns.  This is the minimum documentation that should be included with any software.

Instead of typing in the all the code below, copy the UTM_GetGeographicFromUTM() function from the “UTMConverter” file and paste it into a new subroutine.  Then create “Dim” statements for each of the parameters.  Now clean up to code to look like the following and then run the program.

Sub Convert()
    Dim i As Integer
    Dim Easting As Double
    Dim Northing As Double
    Dim Longitude As Double
    Dim Latitude As Double
   
    For i = 2 To 387
        Easting = CDbl(Cells(i, 4))
        Northing = CDbl(Cells(i, 5))
       
        Call UTM_GetGeographicFromUTM(Easting, Northing, UTM_WGS_84, _
            12, False, Longitude, Latitude)
           
        Cells(i, 10) = Longitude
        Cells(i, 11) = Latitude
       
    Next i
   
End Sub

When you switch back to your spreadsheets, you should see two columns filled with numbers that look like proper latitudes and longitudes.  Their values should be near 37 and -112 respectively.

In this case we have specified the Datum as UTM_WGS_84 and the zone as 12, and the “South” parameter as “false”.  We could parse the zone from the spreadsheet and find the values for the zone and “South”.  All the data in this spreadsheet are in the same datum, which is good because it is much more challenging to convert between Datums (products such as ESRI’s ArcMap can be used for this).

Now that we have converted our coordinates we’re done, right?  Nope, we still need to test our program to make sure the coordinates are correct.  Fortunately with the Internet this has become very easy.

Type “UTM Converter” into Google and click on one of the entries there.  Copy the coordinates from the spreadsheet into the converter and convert the coordinates.  The numbers should match to about 5 decimal places.