Return to Excel Statistics Page

Geographic Information Systems

Keith Greiner

August 8, 2020

A purpose of statistics is to reveal information and relationships that otherwise could not be seen. The slope of a regression line speaks volumes about the relationship between two variables. Distributions show the relationships within a collection of values. Tests of significance reveal likelihood that two or more things occur in a related manner. Factor analysis reveals latent relationships between collections of values. And so on and so forth.

Geographic Information System (GIS) mapping reveals important information about data as it relates to places. It provides a richness of understanding that could not possibly be seen without the physical dimensions of a map. One of my favorite is a map of applicants and recipients of disaster relief assistance after a statewide flood event. (See document my ED503640 at https://eric.ed.gov as of May 23, 2017).

Here we see the Iowa counties where people qualify for flood assistance as well as the applicants for FEMA assistance, and recipients of flood-related student financial aid. It is immediately observable, that there were applicants in counties where apparently, there was no recorded impact of the disaster. The patterns of applicants include lines of data that correspond to major river systems.

The map has four layers;

1. County outlines,
2. Color coding that indicates which counties have residents who are qualified for assistance,
3. Point locations indicating people who have applied for FEMA assistance, and
4. Point locations where people received a student financial aid related to the flood disaster.

With all these items displayed, we can see the interrelationships of all four variables.

A full discussion of geographical information systems is beyond the scope of this document. However, as a starting point, I suggest the following important sources of additional information.

1. Microsoft Excel and Microsoft Access are essential for GIS analytical work. They are needed to collect, organize, consolidate, and prepare data for import into GIS tools described below.

2. The U. S. Census Bureau has a GIS tool for demographic analysis, located at: https://www.census.gov/geography/interactive-maps.html . While many other Federal and State agencies also publish GIS information, including necessary shape files and data files, there is concern on May 23, 2017 that some critical data sources will be defunded by the Trump administration. Updates will be added as needed.

3. Google Earth Pro is a helpful tool to plot the locations of addresses. It is available free of charge at: https://www.google.com/earth/download/gep/agree.html . Google Earth Pro accepts upload of .csv files having specific headings including Address, City, State, and Zip. I found it most useful when those four variables are included in a single variable labeled address. I have not had success loading latitude and longitude data. Changes and improvements are constantly being added.

4. ArcMap from Esri.com. This commercial product is available for a variety of prices, depending on your situation. Students can try it out with a 90 day free trial, and an annual subscription (limited to non-profit personal use). The Esri site is at: http://www.esri.com. The ArcMap product does an excellent job of co-mapping filled in geographic areas and points that have latitude and longitude data. Geocoding is not available to people using the personal not-for-profit subscription.

5. Texas A & M University offers a suitable alternative for geocoding, for those who do not have the resources to use the Esri gecoder. The Texas A & M geocoder may be found at: http://geoservices.tamu.edu/Services/Geocode/ . You can start by converting up to 2,500 addresses to latitude and longitude data at no charge and if you participate in the Partnership program, you can reset the 2,500 counter. Other options are available at a charge. I prefer the batch geocoder tool. With that, you upload an address file, allow the tool to do its job, and then download your completed, geocoded file. Be sure to have a distinctive name for your file, as the geocoder may seemingly truncate or change the file name. The TAMU tool also can convert latitude and longitude to addresses: a tool that would be helpful when using Google Earth Pro. In my tests using the TAMU geocoder, there has been a 100% error-free conversion of the address string to a decimal latitude and longitude value.

Notes

This is not a comprehensive guide. Working with GIS software often takes a lot of experimentation and corresponding frustration until you become familiar with that particular tool. Following are notes from my experience with a few software items. Your experience may be very different. Notes will be added as new experiences are identified.

From Excel to Google Earth Pro -- Latitude and Longitude

Transferring latitude and longitude data from Excel to Google Earth Pro can be tricky. I started using Excel for Mac and soon discovered that the transfer failed. After considerable investigation and experimentation, I found that five conditions were necessary for the decimal latitude and longitude to be transferred from Excel to a .csv file, and from .csv file to Google Earth Pro. The conditions were…

1. I needed a separate sheet just to prepare the data.
2. The data needed to be formatted in only two columns, A, and B, with latitude in column A and longitude in Column B.
3. The first row of each column contained the name of the column.
4. Latitude and longitude had to be expressed in decimal values. Some sources suggest other formats will work, but that wasn’t my experience.
5. The sheet created for this transfer had to saved from Excel to a .csv file, using Excel for Windows. Excel for Mac did not work. Not even if I used the Windows .csv option with the Mac. You can try it from a Mac, but it simply did not work for me.
6. When the .csv file was loaded to Google Earth Pro, I was not shown a dialog that might be used to define which column was which. Instead, I was presented with a dialog that asked if I wanted to use an existing template. Since no such template existed, the answer was "No".

The following image shows how the Excel sheet was set up. Each latitude and longitude entry corresponds to the location of a state capitol in the U. S. There is no descriptive information in this sheet. The descriptive information is on other sheets in the Excel workbook that contains the data. Also, of course, this is not a complete list; only the first 22 states in an alphabetical list.

The complete data set, loaded to Google Earth Pro, resulted in the image shown below. State capitols may be seen as dots in each state.

After successfully creating the latitude and longitude data for the image shown above, I used to same steps, and a Windows version of Excel to add two columns: one for the state and another for the capitol city. The following image shows the Excel side of that data, which was then saved as a .csv file from Excel for Windows, and then was successfully imported into Google Earth Pro. The success was replicated on a Mac with the save-as option of "Windows Comma Separated (.csv)". Once the capitol cities were visible, I could right-click on each city's marker and see the latitude, longitude, state name, and capitol city.

From Excel to Google Earth Pro -- Addresses

Google Earth does an excellent job of accepting addresses. For addresses, I simply stored the addresses in an Excel sheet with one column containing a full street address, city, state and zip. The addresses could then saved as a .csv file on a Windows or Mac computer, and imported into Google Earth Pro. There, they were displayed as shown below. When using the Mac, use the save-as option and select "Windows Comma Separated (.csv)". I was also able to export from Google Earth as a kml or kmz file. Google Earth accepted a maximum of 800 address records. The resulting map, shown below, has newly found amateur radio license records between December 31, 2015 and December 31, 2016. That definition of "newly found" is intended to be accurate about the process while suggesting that these are likely to be individuals who did not have an amateur radio license on December 31, 2015, but did have a license on December 31, 2016. The map shows a sample of 800 data points even though the original file has over 25,000 addresses. A random sample was used in an effort to give each of the 25,000 an equal chance to be shown and to show a distribution of points that highly likely represents the full data set. If all 25,000 were shown, the map would be so full that patterns would no longer be visible. The amateur radio map is from my site at: https://sites.google.com/site/amateurradiodata/ .

Finding Latitude and Longitude

Here, I am going to show you two ways to obtain the latitude and longitude of an address. The first method uses Google Maps. Just open Google Maps and in the top-left dialog box, type the street address of the location. The map will place a marker at the location. Now follow the steps in the image shown below; a) right click on the marker, b) select “What’s here?”, c) read the latitude in the shaded text that pops up after you click “What’s here?”.

In the image shown below, I located the latitude and longitude of the Standard Rock Lighthouse by first entering the name of the lighthouse into the dialogue box at the top left of the screen. The lighthouse is offshore and has to street address, but Google still found it.

The second method is to use the Texas A & M site: http://geoservices.tamu.edu/Services/Geocode/ . Select the “Interactive Geocoding” tab and enter the address. You will be provided with latitude, longitude, and many other helpful data items.

Return to Excel Statistics Page