Print Page   |   Sign In   |   Register
TL v67n3: Mapping copy
Share |

Mapping the Difference, Part 2: Library Workshops for Mapping Education and a Guide to Preparing Location Coordinates


This article, the second in a two-part series on web mapping education in libraries, provides tutorials for preparing location coordinates for mapping projects. Read more about web mapping education in libraries and best practices for mapping workshops in part 1 of the series.


Web mapping and mapping education have recently flourished as technology has become more available and accessible. Libraries are not only places where users can access mapping technology resources, but they also provide educational learning opportunities for those resources. Part 1 of the “Mapping the Difference” article series discusses web mapping education in libraries, introduces some commonly used mapping applications, and provides best practices for mapping workshops. Part 2 focuses on content that can be used in mapping workshops, and specifically provides tutorials for preparing location coordinates for mapping projects and for using mapping applications such as Google Earth, ArcGIS Online, Batchgeo, and Fusion Tables.

The article will also discuss the importance of spreadsheets and quality data preparation before maps are created. The basic principles that should be followed in preparing data, no matter the mapping software selected, can easily be met using a spreadsheet with a data quality checklist. This is an early challenge that will direct the final outcome of a finished map, determining if it is an effective visualization. To overcome this challenge, data needs to be set up correctly. Spreadsheets are an optimal tool for this; however, spreadsheets and mapping software have a learning curve. The tutorials provided in this article can help beginning users successfully create maps in a short amount of time.

The Pivotal Point: Spreadsheets

Offering educational resources and services is the mission of libraries. This mission easily extends to mapping education and related research. The use of spreadsheets are helpful for more than just accounting and business practices. Spreadsheets can be a mapping alternative to specialized GIS software (Whyte, 2011). However, when spreadsheets are combined with other library resources, the results can open up a much wider field of analytical opportunities. For example, the interoperability of spreadsheets to connect with databases and GIS software is an advancement to research analysis capabilities (Coyle, 2011). As a result, “connections between objects and attributes that might not have been thought of previously can appear in clusters or at distances on a map that when analyzed and applied can be used to enhance institutional value and resources” (Coyle, 2011, pp. 529-530).

One of the most critical decisions of map making is selecting data for the map and the geographic data format to be used. The storage formats of map data include shapefiles, text files, comma-separated values (CSV) files, or GPX files. Although there are multiple formats to plot points on a map, tabular data from spreadsheets is one of the most accommodating methods for the average user. The spreadsheet can be easily exported as a CSV file. A CSV file stores tabular data such as numbers and text (Wikipedia, 2017). This method is the focus of this article, offering a simple way for new users to begin mapping with confidence.

Although different mapping applications offer a variety of tutorials1, they often gloss over spreadsheet creation. Additionally, if you want to compare how data will look in different mapping applications in order to have an assortment to choose from for your final publication, it is helpful to be able to quickly plot those data points. This guide will share the spreadsheet specifications for a selection of mapping applications, allowing you to quickly navigate between the data requirements so you can view your map in multiple applications before having to spend an overabundance of time on any one application for trial comparisons. Additionally, the details provided in this article can be used as a development resource by those planning to offer mapping workshops.

How to Get Started

Before starting a mapping project, it is important to establish the right mind set. You might have an idea of how a desired map may look when complete, but it takes effort and data manipulation to get the map into shape. In describing well-structured data, Camoes (2011) states that storing and presenting data are not the same. It may take a little time and tweaks along the way to get the right data visualization. Therefore, a map is only as good as its data. For this reason, this article’s focus is on the spreadsheet set up, a process which can vary depending on the mapping application used. Only a selection of the available mapping applications will be included in this overview.

Available spreadsheet software includes Microsoft Excel, iWork Numbers, Google Sheets, LibreOffice Calc, and OpenOffice Calc. Both Excel and Google Sheets are used in the tutorials in this article. No matter which spreadsheet option you use, a spreadsheet data quality checklist for mapping applications should, at the minimum, include:

  • Data desired (subject or project specific content)
  • Short file name without spaces; use of underscores permitted (e.g., tn_libraries)
  • Only one header row in each file and/or tab
  • First row (header row) only containing names of the fields
  • No introductory text (spreadsheet should start with just a single header row)
  • Column names that only include letters, numbers, and underscores
  • Appropriate column header names (Name, Address, City, State, Zip, Lat, Long, URL, etc.)
  • Use of ALL CAPS or capital letters to differentiate words (do not use spaces)
  • Accuracy of names, spelling, punctuation, etc.
  • No blank cells or blank rows
  • No odd characters, hyphens, or extra spaces in spreadsheet cells
  • Columns with latitude and longitude coordinates that are formatted as a Number with 6 decimal places
  • Location details in accurately defined filed names (ie: city in city field; country in country field)
  • Valid URLs (if using)

Adhering to the above points is optimal for achieving quality results. Without using a data quality checklist like this, the chances of null or ineffective results increase, as does the amount of time you will spend preparing any map related products.

With spreadsheet data quality in mind, you are ready to find or use data to map. If you don’t have your own data sets, you can use a variety of resources to find large data sets for practice or for specific research projects. Much of the data that is publicly available (without licensing requirements) is government data at the local, state, or federal level. Government agencies such as the Tennessee Geographic Information Council2, United States Geological Survey (USGS)3 and the U.S. Census Bureau4 make data publicly available on their websites. Before continuing with the tutorials in this article, be sure to have a data set ready.

Example Tutorial: Spreadsheet > ArcGIS Online>Story Map

Some web applications, such as ArcGIS Online’s Story Map Tour, allow you to download a CSV template, a blank canvas with pre-determined columns of field names for you to fill in the content. These field names include name, description, icon color, longitude (x) coordinates in decimal degrees, latitude (y) coordinates in decimal degrees, picture URL, thumbnail URL, and video URL. If your spreadsheet has physical addresses instead of coordinates, those addresses will need to be geocoded5 prior to adding the CSV file to the web map in ArcGIS Online (ways to geocode are described later in this article). This type of data manipulation requires quality control, hence the advantage of using a spreadsheet. Reviewing the data before a map is created allows you to examine the content, including location data, and edit it before you create map versions.


Figure 1. Header row of Story Map Tour's CSV template.Figure 1. Header row of Story Map Tour's CSV template.


The template can be modified to include any field names you desire for your project. However, it is best practice to keep the latitude and longitude coordinates intact. Figure 2, an example spreadsheet named “State of Tennessee Four Year Public Institutions,” shows such a modification.


Figure 2. Author's modified template example of data on Tennessee university libraries.Figure 2. Author's modified template example of data on Tennessee university libraries.


Once uploaded, the coordinates in the spreadsheet allow the locations to be georeferenced in ArcGIS Online and in the web application being used. Figures 3 and 4 show the public four-year universities in the state of Tennessee that were mapped with the spreadsheet data in Figure 2. 


Figure 3. Web map in ArcGIS Online.Figure 3. Web map in ArcGIS Online.


Figure 4. Story Map web mapping application.Figure 4. Story Map web mapping application.


If your spreadsheet data does not have latitude and longitude coordinates but has other location data (such as a physical address or zip code, city and state, or country name) then you may need to have that location data georeferenced. There are a variety of geocoding (also called georeferencing) services available, and the cost of this service has decreased considerably over the years, with some of the online services now being free (Goldberg, Wilson, & Knoblock, 2007). Some of the free options have access restrictions such as a limit to the number of records allowed to run per day, and others are pay-services. For a list of geocoding services, visit

Two free options for obtaining geocodes for multiple records are BatchGeo and services through Google. If you only need a few geocodes, you can do this manually by using an online map (e.g., Google Maps, Bing Maps, or MapQuest). Search for your location and right click to obtain the latitude and longitude coordinates which are two sets of six digits. Note that this can be time consuming if you have multiple records, and you may want to use a georeferencing service for such instances.

Example Tutorial: Spreadsheet > BatchGeo

With the modified spreadsheet (discussed earlier) still open, copy the data. Then go to and paste the data in the box as directed and click “Map Now.” Alternatively, you can use BatchGeo’s spreadsheet template to create a new spreadsheet (this is a link directly above the Map Now button).


Figure 5. Snippet of BatchGeo's spreadsheet template (header row only).Figure 5. Snippet of Batchgeo's spreadsheet template (header row only).


Figure 6. Author's spreadsheet data for the BatchGeo example.Figure 6. Author's spreadsheet data for the BatchGeo example.


Figure 7. Snippet of BatchGeo's homepage where you copy and paste your spreadsheet data. This example uses the author’s spreadsheet data from Figure 6.Figure 7. Snippet of BatchGeo's homepage where you copy and paste your spreadsheet data. This example uses the author’s spreadsheet data from Figure 6.


Next you will need to validate your data. You are not required to set every data field, but it is critical to make sure the location is accurately identified; otherwise, nothing will be mapped. In Figure 8, the state field was selected from the drop down menu for the state validation. The same would be done for city and zip code if applicable.

Figure 8. Snippet of BatchGeo's validation options where the author set location field for Figure 8. Snippet of BatchGeo's validation options where the author set location field for "state."


Once the validations are set, your map will populate with location pins as shown in Figure 9.


Figure 9. Snippet of author's data in BatchGeo's map.Figure 9. Snippet of author's data in BatchGeo's map.


There are interactive features built in to BatchGeo including the ability to move the map around, zoom in on a portion of a map, or click on a location pin to retrieve more information (data from your spreadsheet) in a pop-up text box. 


Figure 10. One of BatchGeo's interactive features, a pop-up text box.Figure 10. One of BatchGeo's interactive features, a pop-up text box.


It is important to note that BatchGeo requires you to supply an email address in order to save any georeferences you create. There is no charge for acquiring the geocodes for your locations via this process. If you choose to save your data, then you can download the file and upload it to another GIS software application for further analysis, printing, or sharing.

Example Tutorial: Spreadsheet > Google Fusion Tables

Fusion Tables is another free resource for geocoding address locations. Fusion Tables is one of Google’s experimental data visualization web applications (Google, 2017). It is a free product of Google but requires a Google Drive account. Either sign in to your Google account or create one for free at Then you can select “New” and upload your previously created or modified spreadsheet template (this tutorial uses the same Tennessee Libraries spreadsheet as an example) or you can create a new spreadsheet with Google Sheets. Alternatively, if you do not want your spreadsheet saved in Google Drive, just skip this step. You can upload your spreadsheet from your computer directly.

From your Google Drive click “New,” select “More” at the bottom of the menu, and then select “Fusion Tables.” If Fusion Tables is not listed, select “Connect more apps” and search for and add Fusion Tables. Once in Fusion Tables, upload or select your spreadsheet of map data. You may have to allow Google’s synchronization service to view your data. You can always remove or deny this in your account settings after creating your tables if you no longer want Google to have access to your data.

The benefit to using Fusion Tables is that Google will automatically detect location data (Google has its own geocoding service). Click on the “Map of [your file name]” and a map will populate with your data points from the spreadsheet. Now you can download the data, including the georeference codes Google added for you behind the scenes. The downloaded file, whether in CSV or KML format, can later be uploaded to a variety of GIS applications for further analysis. For this tutorial, the file was saved as KML6 and then imported into Google Earth.


Figure 11. Select your newly-created and geocoded map in Google Fusion Tables.Figure 11. Select your newly-created and geocoded map in Google Fusion Tables.


Figure 12. Download options for a Google Fusion TableFigure 12. Download options for a Google Fusion Table.


The KML file just downloaded can be overlaid on real time imagery for further analysis with Google Earth Pro. For this, you will need to have downloaded Google Earth Pro as previously explained in Part 1. Then open the KML file in Google Earth where it will read the file and zoom to the area your data represents because of the geocoded data (see Figure 13).


Figure 13. Author’s geocoded map as viewed in Google Earth Pro.Figure 13. Author’s geocoded map as viewed in Google Earth Pro.


Example Tutorial: USGS Dataset > KML file > Google Earth Pro

The Fusion Tables tutorial above included the use of a spreadsheet. That tabular data was then geocoded and downloaded as a KML file, which keeps the geocodes intact for use in other geospatial software, and uploaded to Google Earth Pro. This tutorial, instead of using researcher-created data (such as the example of data on Tennessee Libraries), uses a publically available dataset.

One of the mission areas for the U.S. Geologic Survey (USGS) is natural hazards. This tutorial begins at the USGS Natural Hazards web page, which can be reached from the USGS homepage at (as shown in Figure 14, click on “Science” in the top navigation menu, then click on “Natural Hazards”). From the Natural Hazards webpage, navigate to the Earthquake Hazards program page as shown in Figure 15.


Figure 14. Selecting Natural Hazards from the Science category on the USGS homepage.Figure 14. Selecting Natural Hazards from the Science category on the USGS homepage.


Figure 15. Selecting the Earthquake Hazards program from the USGS website.Figure 15. Selecting the Earthquake Hazards program from the USGS website.


Next, click on the “Latest Earthquakes” link in the upper right of that webpage. This takes you to a map showing the most current readings of earthquakes. Settings on this page can be modified along the right. The view in Figure 16 was set to auto update and to show earthquakes of all magnitudes in the U.S. during the last 7 days. To download the data for your selection, click on the “Click for more information” down arrow at the upper left to reveal the download button for the data file for the earthquakes in the map area.


Figure 16. USGS Earthquake Hazard program data is downloadable.Figure 16. USGS Earthquake Hazard program data is downloadable.


Depending on your desired analysis, select what type of file format you prefer for the data file download. For this tutorial, select “KML - Color by Depth” so you can easily see the variation of earthquake depth (see Figure 17).


Figure 17. USGS data download options.Figure 17. USGS data download options.


After you have downloaded the KML file, open it in Google Earth Pro. Using a publically available dataset like this can show a lot of information, as demonstrated in pop-up text box in Figure 18. Of course, if you are using your own data, it may not look like this, and that is why a publicly available dataset is used in this tutorial--to show options available and different views of data that can help further research and analysis.


Figure 18. USGS data shown in Google Earth Pro.Figure 18. USGS data shown in Google Earth Pro.


The map image in Figure 18 shows a number of orange dots representing earthquakes in the U.S. southwest region that have a depth of 0-33 km (see map legend). To view other locations and depths, simply move the map around and click on various location points to learn more from the pop-up text data. By scrolling left across the Pacific Ocean, we can see the location points change in color to yellow and red in Southeast Asia, indicating greater depths of earthquake activity.


Figure 19. USGS data shown in Google Earth Pro with greater depths of earthquake activity.Figure 19. USGS data shown in Google Earth Pro with greater depths of earthquake activity.



1. An example of a mapping application tutorial is “Create a Story Map Tour from a Google Sheet” published on the ARCGIS Blog on August 12, 2015. It is available at

2. TN GIC has data collections on census, evaluation, land use, soils, water, and wetland data sets, among others. You simply select the collection desired and download the data, there may even be an option for a Shapefile batch download which is useful for uploading to Google Earth, ArcGIS or other advanced mapping applications. Available at Please note that not all datasets come packaged neatly in a ready-to-use state and will require searching for terms of use or descriptions that will help you describe the data used in your research including the source of the data.  The University of Tennessee Knoxville also curated a list of various data sources available at

3. Science Datasets from USGS are available at

4. For example this is a list of data sets available on employment data from the Census Bureau

5. Geocoding or georeferencing is the process of transforming a description of a location, such as coordinates or an address, in order to create geographic attributes that can be used for spatial analysis and identification. To learn more about geocoding, visit

6. Keyhole Markup Language (KML) is an XML notation for expressing geographic annotation and visualization within a web-based browser such as Google Earth.


Camões, J. (2016). Data at work: Best practices for creating effective charts and information graphics in Microsoft Excel. San Francisco, CA: New Riders.

Cole, D. (2011, October 11). Mapping a Google Doc spreadsheet. Retrieved from

Coyle, A. (2011). Interior library GIS. Library Hi Tech, 29(3), 529-549. 10.1108/07378831111174468

Goldberg, D. W., Wilson, J. P., & Knoblock, C. A. (2007). From text to geographic coordinates: The current state of geocoding. URISA Journal, 19(1), 33-46. Retrieved from

Google. (2017). Google Fusion Tables support. Retrieved March 9, 2017, from

Wikipedia. (2017). Comma-separated values. Retrieved March 4, 2017, from

Whyte, B. (2011). On the use of spreadsheets such as Excel for mapping. Cartographica, 46(1), 41-50.



A. Miller, Digital Scholarship Librarian at Middle Tennessee State University, can be reached at


creative commons attribution no commercial




Membership Software Powered by YourMembership  ::  Legal