Obtaining the Tutorial Data

Before beginning the tutorial, you will copy all of the required tutorial data onto your Desktop. Option 1 is best if you are completing this tutorial in one of our short courses or from the GIS/Data Center and Option 2 is best if you are completing the tutorial from your own computer.

OPTION 1: Accessing tutorial data from Fondren Library using the gistrain profile

If you are completing this tutorial from a public computer in Fondren Library and are logged on using the gistrain profile, follow the instructions below:

1. On the Desktop, double-click the Computer icon > This PC > GISData (\\smb.rdf.rice.edu\research\FondrenGDC) (O:) > GDCTraining > 1_Short_Courses > Mapping_XY_Data.
2. To create a personal copy of the tutorial data, drag the XY folder onto the Desktop.
3. Close all windows.

OPTION 2: Accessing tutorial data online using a personal computer

If you are completing this tutorial from a personal computer, you will need to download the tutorial data online by following the instructions below:

1. Click XY.zip above to download the tutorial data.
2. Open the Downloads folder.
3. Right-click XY.zip and select Extract All....
4. In the 'Extract Compressed (Zipped) Folders' window, accept the default location into the Downloads folder and click Extract.
5. Drag the unzipped XY folder onto your Desktop.
6. Close all windows.

Mapping XY Data in ArcGIS Pro

Exploring the Data

First, you will explore the XY data for this exercise that is contained in an Excel file.

1. On the Desktop, double-click the XY folder.
2. Double-click Coordinates_ClimateProtectionAgreementMayors.xls to open the file with Excel.

This Excel worksheet provides a list of cities whose mayors signed the U.S. Conference of Mayors Climate Protection Agreement as of September 12, 2011. This list was obtained from the Climate Protection Center website at http://www.usmayors.org/climateprotection/list.asp. Notice that the worksheet also contains the latitude and longitude of the cities in decimal degrees.

1. Close Excel.

Displaying XY Data

Now that you have examined the tabular data in Excel, it is time to display the XY data of city locations on a map.

1. Within the XY folder, double-click the XY.aprx project file to open the project in ArcGIS Pro.
2. In the Catalog pane on the right, expand the Folders folder.
3. Expand the XY folder.
4. Expand the Coordinates_ClimateProtectionAgreementMayors.xls Excel file to expand it and view the individual worksheets.

1. In the Catalog pane, right-click the Sheet1$ worksheet table and select Add To New Map.
2. In the Contents pane on the left, right-click the Sheet1$ standalone table and select Display XY Data.

The Geoprocessing pane should open on the right in a new tab overlaying the Catalog pane. In the 'Make XY Event Layer' tool, notice that 'XY Table' is already set to the Sheet1$ table, since that was the table used to open the tool.

1. In the Geoprocessing pane, for ‘X Field’, ensure the Longitude field is selected.
2. For ‘Y Field’, ensure the Latitude field is selected.

Notice in the ‘Spatial Reference’ drop-down menu, it defaults to GCS_WGS_1984, which is the most commonly used geographic coordinate system for mapping latitude and longitude and happens to be the correct one to select in this case.

1. Ensure that your 'Copy Features' tool parameters match those shown below and click OK.
   
   
   
   
2. Click Run.

The points should now appear on top of the United States and the map should automatically zoom to the extent of the points, since they were the first layer added.

Exporting XY Data

At this point, you are simply visualizing the data contained in the Excel spreadsheet, but you have not yet created a standalone point feature class. Since the points appear to be in reasonable locations (rather than in another country or the middle of the ocean), you will want to export them to a new feature class in your XY geodatabase.  Exporting to a feature class will allow you to reuse this points layer in other future maps without having to go through the display XY data process each time.

1. Right-click the Sheet1$_Layer and select Data > Export Features.

In the Geoprocessing pane, in the 'Copy Features' tool, notice that for 'Input Features' Sheet1$_Layer is already selected, since that was the table used to open the tool.

1. For ‘Output Feature Class’, rename the feature class from Sheet1_Features_CopyFeatures to “ClimateProtectionAgreementCities”.
2. Ensure your Geoprocessing pane appears as shown below and click Run.
   
   

Since you now have a permanent feature class, you may remove your temporary Sheet1$ layer and the corresponding Excel table.

1. Right-click the Sheet1$_Layer layer and select Remove.
2. Right-click the Sheet1$ standalone table and select Remove.

If you’d like to get a better view of your points, use the navigate tools on the Map tab to zoom in to the continental United States. You have successfully mapped point locations using XY coordinates.

Converting Addresses to XY Coordinates

If all of your data already comes with a list of the latitudes and longitudes of the points you’d like to map, then you are ready to go straight into ArcGIS Pro, but what if you have a list of cities or addresses that you’d like to map, but you don’t know their corresponding coordinates? This section will show you one automated method of generating such coordinates based on address locations.

On the actual Climate Protection Center website, only the city is listed for each participating mayor. First, you will explore the data for this exercise that is contained in an Excel file.

1. On the Desktop, double-click the XY folder.
2. Double-click Cities_ClimateProtectionAgreementMayors.xls to open the file with Excel.

Notice this file is identical to the worksheet you used earlier to map the participating city locations, except that it is missing the latitude and longitude information. In such an instance where you would like to map these cities, you would first have to obtain their coordinates, so you will use an online geocoder. There are many online geocoders, but the one you will be using in this course is called GPSVisualizer. For your future reference, a list of geocoders is maintained by Texas A&M University GeoServices at http://geoservices.tamu.edu/Services/Geocode/OtherGeocoders/.

1. Minimize or Restore Down Excel.
2. On the Desktop, double-click the Google Chrome icon.
3. In the location bar, type “gpsvisualizer.com” and press Enter.
4. At the top of the ‘GPSVisualizer’ window, click Geocode addresses.
   
   

Here, you will see several options for geocoding addresses. Option 2 allows you to geocode multiple addresses and should be used for standard street addresses, but Option 3 allows you to geocode simple locations and is recommended if you are mapping data such as ZIP codes, cities, or states. Since your data table only contains city names, you will use option 3.

1. Under ‘3. Geocode simple tabular data’, click the link to the text/GPX conversion utility.
2. Return to the Excel spreadsheet.
3. Click the Select All button in the top left corner of the spreadsheet.
   
   
   
   
4. In the Home tab, click Copy.
5. Return to GPSVisualizer.
6. In the ‘Or paste your data here:’ box, delete all existing text.
7. Right-click in the ‘Or paste your data here:’ box and select Paste to copy in the city data from your Excel worksheet.
8. Click Convert.

Once the website is finished geocoding your cities, the latitudes and longitudes will be displayed.

1. Towards the top of the window, right-click the link that says following link and select Save link as….  (Yours will have a different download number than shown below.)
   
   
   
   
2. Double-click the XY folder.
3. For 'File name:' rename your file “LatLongMayors” and click Save.
4. Return to Excel.
5. At the bottom of the worksheet, click the New sheet button.
   
   
   
   
6. Click the Data tab.
7. Under the ‘Get External Data’ section, click From Text.
8. Navigate to select your saved LatLongMayors text file (Desktop > XY > LatLongMayors) and click Import.
9. In the ‘Text Import Wizard’ window, ensure that Delimited is selected and click Next >.

A delimiter is simply a character, such as a tab, space, or comma that tells Excel when to move the text into the next column.

1. In the ‘Delimiters’ box, check Tab and ensure that the column break lines appear in the correct locations in the ‘Data preview’ box, as shown below, and click Next >.
   
   
   
   
2. Click Finish.
3. In the 'Import Data' window, click OK.

You could now delete any unnecessary columns and save this Excel worksheet to map the XY coordinates just like you did in the first exercise.

Optional Exercise: Converting DMS to Decimal Degrees

Occasionally, you will come across latitude and longitude coordinates listed in Degrees, Minutes, Seconds (DMS) format (27°52'35.1" N, 93°48'54.1" W). However, ArcGIS requires coordinates to be in decimal degrees (DD) format (27.3574, -93.75346). This section will show you how to convert coordinates from DMS to DD using Excel.

1. On the desktop, double-click the XY folder.
2. Double-click Flower Garden Banks NMS Buoy Locations.xlsx to open the file with Excel.

This Excel worksheet contains the coordinates of all the buoys located in the Flower Garden Banks National Marine Sanctuary. The coordinates were obtained from http://flowergarden.noaa.gov/visiting/buoyboundary.html. This dataset provides a perfect example of when it would be absolutely necessary to map point locations using XY coordinates. In the middle of the ocean, there are no physical landmarks or addresses, so location data must be collected using latitude and longitude.

Before you can covert DMS coordinates to DD coordinates, it is necessary to separate out the degrees, minutes, and seconds components into their own columns. In Excel, you can divide single cells into multiple columns using either delimiters or fixed width break lines. You will try both methods.

Converting Text to Columns Using Delimiters

1. Click cell D2 to select it.
2. In the formula bar, highlight the degree symbol (°) and press Ctrl+C to copy it.
   
   
   
   
3. Click column D to highlight the entire column.
4. In the ribbon, click the Data tab.
5. Under the Data Tools section, click the Text to Columns button.
   
   
   

1. In the ‘Convert Text to Columns Wizard’, ensure that Delimited is selected and click Next >.
2. In the ‘Delimiters’ box, check Other:.
3. Click the text box to the right of 'Other:' to locate your cursor there and press Ctrl+V to paste the degree symbol (°).
   
   
   
   
4. Click Next >.
5. Click Finish.

Notice that everywhere the delimiter (in this case, the degree symbol) used to be located, the data has now been split into another column and the delimiter has disappeared.

You could repeat this same process twice more, using the apostrophe symbol (‘) and the quotation marks symbol (“) as the respective delimiters. If your coordinates data had differing numbers of decimal places in each row, repeating this method would be necessary, but, in this case, each set of coordinates has exactly the same number of characters, so using fixed width break lines is possible.

Converting Text to Columns Using Fixed Width Break Lines

1. Click column E to highlight the entire column.
2. On the Data tab, under the Data Tools group, click the Text to Columns button.
3. In the ‘Convert Text to Columns Wizard’, select Fixed width and click Next >.
4. In the ‘Data preview’ box at the bottom, click on both sides of the apostrophe symbol (‘) to add break lines.
5. Click on both sides of the quotation mark symbol (“) to add break lines.

If a break line is in the wrong location, you can click and hold and drag it to a new location. If you need to delete a break line, you can double-click on it.

1. Ensure that the break lines in the ‘Data preview’ box are placed like those shown below and click Next >.
   
   
   In order to import this information into GIS, the columns without the degrees and minutes information should be excluded, and you can do this by using the Do not import (skip) command. 
   
   
2. In the ‘Data preview’ box, click the second column containing the apostrophes to select it.
3. In the ‘Column data format’ box, select Do not import column (skip).
   
   
   
   
4. Repeat steps 2 and 3 above with the fourth column containing the quotation marks and the fifth column containing the letter W.
5. Click Finish.

Your spreadsheet should now appear like that shown below. If you have any additional columns, showing because they were not marked to be skipped, delete them now.

Using the DMS to DD Conversion Formula

Now that your degrees, minutes, and seconds components are each in their own column, you can use the following formula to convert them into decimal degrees: DD = D+(M/60)+(S/3600).

1. In cell G2, type “=” to indicate the start of a formula.
2. Type “-(” (negative sign, open parenthesis) to indicate these coordinates are west of the prime meridian.
3. Click cell D2 (93 degrees).
4. Type “+(” (plus sign, open parenthesis).
5. Click cell E2 (35 minutes).
6. Type “/60)+(” (division sign, 60, close parenthesis, plus sign, open parenthesis).
7. Click cell F2 (49.7 seconds).
8. Type “/3600))” (division sign, 3600, close parenthesis, close parenthesis).
9. Ensure your cell says “=-(D2+(E2/60)+(F2/3600))” and press Enter to finish the formula.
10. Click cell G2 containing the formula you just entered.

Hover your cursor over the small black box in the bottom right corner of the highlighted cell as shown below. Notice that your cursor changes from a thick white cross to a thin black cross.

1. Double-click the black box in the bottom right of cell G2 to copy the formula down the table.
   
   

Your longitude coordinates are now in decimal degrees format; however, the cells currently contain formulas, which cannot be read by ArcGIS. To solve this problem, you will copy the values of the equations to a new column.

1. Click column G to select the entire column and press Ctrl + C to copy it.
2. Right-click column H and select Paste Special….
3. Select Values and click OK.

Click cell G2 and notice the formula bar contains a formula. Click cell H2 and notice the formula bar contains the actual numeric value of the formula. Since ArcGIS will be using the values you have just pasted in column H, you no longer need columns D through G.

1. Click column D.
2. Hold down Shift and click column G to select columns D through G.
3. Right-click column G and select Delete.
4. Click cell D1 and type “Longitude” and press Enter.

If you wish to test yourself on what you have just learned, insert many columns between column C and column D and practice converting the latitude field from DMS to DD format.

Bonus Exercise: Spatial Join

At this point, all of the cities appear on the map, but there are many urban areas that are so densely covered with overlapping points that it becomes difficult to tell exactly how many points there are and to see the underlying data, such as city and state names. In addition, while you can see the spatial distribution of the points, you are not provided with any sort of useful summary of the data. Performing a spatial join will allow you to discover how many participating cities are located in each state, or even county.

1. At the bottom of the Geoprocessing pane, click the Catalog tab.
2. Expand the Databases folder.
3. Expand the XY geodatabase.

Notice that the ClimateProtectionAgreementCities feature class you just exported is now contained in this geodatabase.

1. Right-click the US_States feature class and select Add To Current Map.

You will now examine the US_States layer’s attribute table.

1. In the Contents pane, right-click the US_States layer and select Attribute Table.
2. Scroll to the right and browse through all attribute fields.

The goal of performing a spatial join is to add a numeric field to the end of this attribute table that tells you how many participating cities are contained within each state.

1. Close the US_States table view.
2. In the Contents pane, right-click the US_States layer and select Joins and Relates > Spatial Join.

The Spatial Join tool will open within the Geoprocessing pane. For 'Target Features', the US_States layer is already selected, since that is the layer from which you launched the tool.

1. For ‘Join Features’, use the drop-down menu to select the ClimateProtectionAgreementCities layer.
2. For the ‘Output Feature Class’, rename the feature class from US_States_SpatialJoin to “US_States_WithCityCounts”.

The 'Field Map of Join Features' describes how the features will be summarized as they are joined. The first half of the list of fields displays the attributes of the states layer, ending with the Shape_Area field. The second half of the list of fields, beginning with the Latitude field, displays the attributes of the cities layer. A count field indicating how many city points intersect with each state will automatically be provided. Since many cities will be appended to each state, it does not make sense to generate summary statistics about the city fields, because variables like latitude, longitude, and name cannot be averaged. By default, the table would output only the attributes of the first city encountered within each state, which could be very misleading. Therefore, you will remove all the city attributes from the output fields.

Hypothetically, if your cities layer contained an attribute listing the population of each participating city, then, when performing the spatial join, you could use the Sum Merge Rule on the population field to calculate the total population residing in participating cities in each state and then calculate what percentage of the state’s total population reside in these participating cities. In this case, you do not have such population data available, so you will stick with the default Join Count attribute.

1. Within 'Output Fields', click the Latitude field.
2. Hold down Shift and click the last State field, so that all four city fields (Latitude, Longitude, City, State) are selected.
   
3. Right-click the selected State field and select Remove.
4. Click Run.

The new layer should appear at the top of your Contents pane. 

1. At the top of the Contents pane, right-click the US_States_WithCityCounts layer and select Attribute Table.
2. Notice the third column contains the newly-added Join_Count field.  This field tells you how many participating cities are contained within each state.
3. Close the US_States_WithCityCounts table view.

Since the new layer contains all of the same information as the old US_States layer, plus the new Join_Count field, you no longer need the US_States layer.

1. In the Contents pane, right-click the US_States layer and select Remove.
2. Right-click the US_States_WithCityCounts layer and select Symbology.
3. In the Symbology pane, use the Symbology drop-down menu to select Graduated Colors.
4. For 'Field’, use the drop-down menu to select the Join_Count field.

You can now easily tell which states have the largest number of participating cities, though this display does not take into consideration things such as city density and population density or the percentage of the total state population participating.

1. Above the ribbon, on the Quick Access toolbar, click the Save button.
   
   
   
   
2. Close ArcGIS Pro.