Checklist: Adding coordinate values from a spreadsheet

12-11-2018 11:11 PM
Labels (1)
Regular Contributor
2 0 501

Importing data from a spreadsheet

There has been an increase in queries regarding the import of coordinate values from spreadsheets into ArcGIS Desktop and ArcGIS Pro. With that in mind, find a checklist that hopefully will encourage, when dealing with this type of data, the understanding of coordinate values, their implications of how they are managed and the relationship to coordinate reference systems in ArcGIS software.

Check List

  1. Check coordinates are in the order you expect (e.g. Lat/Long or Long/Lat or X, Y; Y, X and W, E; -X, -Y etc)

  2. Check there are no NULLs or other errors
    1. Check for NULLS, text not number, unrealistic precision, outliers…
    2. Confirm the coordinates make sense (e.g. if you are working with Arctic DMS coordinates a negative latitude value should be a red flag for you)

  3. Check you know the units 
    1. Latitude and Longitude are angles therefore = Geographic Coordinate System
    2. XY coordinates (e.g. meters) are linear measurements therefore = Projected Coordinate System
    3. Convert the data you have into a workable format (e.g. DMS to DD = D + (m/60) + (s/3600) with additional formulae including the consideration of negation per hemisphere)
  4. Check you know the correct coordinate reference system
    1. Find a reference for the data that explicitly says the CRS
    2. If you can't find one then beare in mind the following:
      1. Commonly but not always, global datasets are in WGS84 (possibly because GPS utilises WGS84)
      2. Typically but not always, linear units will be in a UTM projection
      3. If you really do not know the correct CRS
        1. Try not to guess – there’s a lot of CRS out there
        2. Go back to source they might know but didn’t include the relevant information in the data
        3. Based on the coordinate values try to place the data in a region/country and look for the most relevant CRS for that region
        4. If you had to make an educated guess say so in any metadata so no one is fooled and makes an important decision on the information later

Loading data to a map (ArcGIS Pro or ArcMap)

  1. Make sure Map Document data frame for ArcMap is blank (in ArcGIS Pro map properties the default is Web Mercator Auxiliary Sphere)
  2. Assign the correct CRS to Map Document/Map
  3. Add data to Map Document/Map
  4. Check against base map and data where location is known

This blog has not accounted for additional items like transformations but it should act as a checklist of things to look out for when bringing data in from a spreadsheet.

About the Author
Geologist, Geospatial Expert and TAP Certified GIS Trainer working as an Independant Consultant. I have special interests in Geology, Exploration, Coordinate Reference Systems, Geodesy and Natural Science.