Page History

...

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.

Notice the Geoprocessing pane has appeared to the right of the Map WindowThe 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, in the for ‘X Field’ drop-down menu, make sure , ensure the Longitude field is selected.
2. For ‘Y Field’, make sure ensure the Latitude field is selected.

Notice in the ‘Spatial Reference’ drop-down menu, it defaults to the particular projection being displayed in our active data frame, which is currently WGS 84 Web Mercator Auxiliary Sphere. This is the particular projection of the basemap, automatically generated by ArcGIS, is the same projection used by most web mapping sites, including Google and Bing.

...

1. Observe that the XY Coordinate Systems Available is open to Geographic Coordinate Systems > World.
2. Observe that the selected coordinate system is WGS 1984 and click OK.
3. Ensure that your window matches that below and click OK.

1. Click Run.

The points should now appear on top of the United States.

...

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 XYData geodatabase.  Exporting to a feature class will allow you to reuse this points layer in other future map documents without having to go through the display XY data process each time.

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

Notice that the output feature class defaults to your default geodatabase (C:\Users\gistrain\Desktop\XYTutorialData\XYData.gdb).

1. For ‘Output Feature Class’, click the Browse button, make sure the layer is within XYData.gdb, and rename the feature class “ClimateProtectionAgreementCities”. Click Save.
2. Make sure your Geoprocessing pane looks like this below and click Run.

Since you are now using 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$ table and select Remove.

If you’d like to get a better view of your points, use the navigation tools on the Map tab to zoom in to the continental United States.

...

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. On At the right side bottom of the Map WindowGeoprocessing pane, click the Catalog pane.
2. Double-click Expand the Databases folder and double-click the XYData geodatabase .
3. Expand the XY geodatabase to expand its contents.

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

1. Drag Right-click the US_States feature class into the Map Displayand 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 of the attributes.

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 attribute table.
2. In the Contents pane, right-click the US_States layer and select Joins and Relates > Spatial Join.
3. For the ‘Join Features’ drop-down menu, select the ClimateProtectionAgreementCities layer.

Notice that by default, each polygon will be given a count field showing how many points fall inside it, so you do not need to pick an additional statistic.  If, for example, your cities layer contained an attribute listing the population of each participating city, then, when performing the spatial join, you could check the ‘Sum’ statistic 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. At the top of the Contents pane, right-click the US_States_WithCityCounts layer and select Attribute Table.
2. Notice the newly-added Join_Count field.  This field tells you how many participating cities are contained within each state.
3. Close the attribute table.

...

1. On the Desktop, double-click the XYTutorialData folder XY folder.
2. Double-click Cities_ClimateProtectionAgreementMayors 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 the University of Southern California GIS Research Laboratory at https://webgis.usc.edu/Services/Geocode/About/GeocoderList.aspx.

1. Minimize or Restore Down Excel.
2. On the Desktop, double-click the Mozilla Firefox icon.
3. In the location bar, type “gpsvisualizer “gpsvisualizer.com”com”.
4. At the top of the ‘GPSVisualizer’ window, click Geocode an address.

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.

1. In the Home tab, click Copy.

1. Return to GPSVisualizer.
2. In the ‘Or paste your data here:’ box, delete all existing text.
3. Right-click in the ‘Or paste your data here:’ box and select Paste to copy in the city data from your Excel worksheet.
4. Click Convert.

...

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.)

1. Double-click the XYTutorialData folder XY folder.
2. Rename your file “LatLongMayors” and click Save.
3. Return to Excel.

1. At the bottom of the worksheet click the Insert Worksheet button.

...

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

...

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.

1. Click column D to highlight the entire column.
2. At the top of the Excel window, click the Data tab.

1. 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, uncheck Tab and 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 (°).

1. Click Next >.
2. 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.

...

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 section, 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.

...

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) 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).
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 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.