Preparing Data for Input into Geospatial Applications: GIS Data Preparation
To format headers on Spreadsheets for geospatial data, follow the following best practices:
- Use Clear and Descriptive Names: Choose concise, meaningful names that clearly describe the contents of each column (e.g., Latitude, Longitude, Site_Name, Population).
- Avoid vague headers like Data1 or Info, which provide little context.
- Avoid Special Characters and Spaces: Use underscores (_) or camel case to separate words (e.g., Site_Name or SiteName).
- Avoid using symbols like &, #, %, or punctuation, as these can cause errors in some GIS software.
- Limit Header Length: Keep headers short but descriptive (e.g., Elevation_m instead of Elevation_in_Meters), especially if exporting to formats like Shapefile, which have character limits.
- Use Unique Column Names: Ensure each column has a unique name to avoid confusion during data imports and joins.
- Check for Leading or Trailing Spaces: Remove any unnecessary spaces at the beginning or end of the header names, as these can cause data to import incorrectly.
- Avoid Numeric-Only Headers: Avoid starting headers with numbers (e.g., use Year_2023 instead of 2023), as some software may misinterpret these as values.
Convert ‘Degrees, Minutes, Seconds’ to Decimal Degrees on a Spreadsheet (Recommended)
To convert coordinates from a minute-based system (like DMS - Degrees, Minutes, Seconds) to decimal degrees in Excel, you can use the following formula: =Degree + (Minute/60) + (Second/3600). Replace "Degree", "Minute", and "Second" with the cell references containing those values. Looking at the following symbols:
|
|
|
- Duplicate tab: Duplicate the tab with your data before cleaning to preserve the original data as a backup for reference, recovery, and data integrity.
- Prepare your data: Ensure your coordinates are in separate columns (e.g., Degrees, Minutes, and Seconds) in your Excel sheet.
- Split the content from the DMS cell into separate cells
- Select the DMS whose contents are to be split (start with Latitude, then Longitude)
- Insert 3 columns to the right where data from the split cells will be copied
- Go to Data → Data Tools → Text to Columns
- Select the delimiter or delimiters to define the places where you want to split the cell content (in this case ‘Space’) and then select Apply.
Note:
- After you have split the cells, delete the symbols for Degrees, Minutes, and Seconds (0 ’ ”)
- Do this for the rest of the cells (including the Longitude column)
- Create new columns: In the new columns, add headers that you will easily recognize for the new latitude and longitude data and in the first cell, enter the formula above i.e. =Degree + (Minute/60) + (Second/3600), replacing "Degree", "Minute", and "Second" with the actual cell references in your data.
- For example, if your degrees are in cell A2, minutes in B2, and seconds in C2, the formula would be: =A2 + (B2/60) + (C2/3600)
- Copy the formula down: Drag the fill handle (small square at the bottom right of the formula cell) down to apply the formula to the rest of your coordinate data.
- Format the result: You may need to adjust the formatting of the new column (e.g., to show decimal places as desired). The precision of the DMS values should be four significant figures after the decimal point.
Note: the decimal editing function on Excel can be found under ‘Home’ in the ‘Number’ tab.
Convert ‘Degrees, Minutes, Seconds’ to Decimal Degrees on ArcGIS Pro
- Save your Excel sheet as comma-separated values (CSV UTF-8)
Note: When preparing CSV files for GIS, it’s important to use UTF-8 encoding to ensure proper display of special characters, like accents and non-Latin scripts, and to maintain compatibility across different GIS platforms like QGIS, ArcGIS Pro, and ArcGIS Online. This encoding standard supports a wide range of characters, reducing the risk of data corruption, misinterpretation, or import errors when sharing files across systems or collaborating internationally.
- Add your CSV to ArcGIS Pro
- Click the Add Data button on the Map tab, navigate to your working folder where you saved the CSV file, and add it to the map.
- The layer can be seen on the Contents pane on the left.
- Select Analysis on the Menu Bar and select Tools, this will open the Geoprocessing pane on the right.
- Search for "Convert Coordinate Notation" in the search bar and select the tool.
- Configure the tool in the ‘Parameters’ section:
- Input Table/Feature Class: From the dropdown, select the table/CSV containing the coordinates you want to convert.
- Input Coordinate System: Specify the current coordinate system of the input data.
- Output Feature Class: Click on Browse, select the destination where you want to save your output data, rename the file, and click OK.
- X Field (Longitude) and Y Field (Latitude): Specify the fields containing the longitude and latitude values, respectively.
- Output Coordinate System: Specify the coordinate system that you want for the output data.
- Input & Output Coordinates Format: Set the output coordinate notation to "DD" (Decimal Degrees).
- Exclude records with invalid notations: Check the box to exclude the empty rows
- Run the tool: Click "Run" to execute the conversion.
- Your data points will be saved as Shapefiles.
- Follow the steps in this guide to symbolize your map.
Read more about Best Practices When Using Excel Files with ArcGIS
Convert ‘Degrees, Minutes, Seconds’ to Decimal Degrees Online
You can use the Montana State University coordinate units converter: https://rcn.montana.edu/resources/converter.aspx to convert coordinates in degrees, minutes, and seconds to decimal degrees and vice versa.
