Skip To Content

Join tabular data to spatial data

To convince officials of the homelessness problem in one of the most economically successful states in the country, you will need data. In this lesson, you will use ArcGIS Pro to join tabular data on homelessness from the federal government to spatial data of the United States from the Living Atlas.

Explore the data

First, you will download and open an ArcGIS Pro project containing the data needed to complete this lesson.

If you do not have ArcGIS Pro or an ArcGIS account, you can sign up for an ArcGIS free trial.

  1. Download the Homelessness compressed folder.
  2. Right-click the downloaded folder and extract it to a location you can easily find, such as your Documents folder.
  3. Open the Homelessness folder. If you have ArcGIS Pro installed on your machine, double-click Homelessness.aprx to open it. If prompted, sign in using your licensed ArcGIS account.

    The map appears, showing the United States.

    Map of the United States

    The orange layer, USA_States_Generalized, is provided by the Living Atlas. You can find out more about this data on the layer's details page.

  4. In the Contents pane, right-click Sheet1$ and choose Open.

    Open Sheet$1 from the context menu

    Note:

    If there is a red exclamation mark next to the standalone table you may need to install the Microsoft Access Database Engine driver. Alternatively, add the table use the Excel to Table geoprocessing tool

    The table appears. Sheet1$ is the first sheet in a spreadsheet, Homeless Data.xlsx, provided in the download folder. This spreadsheet was adapted from the PIT Counts by State files published by the United States Department of Housing and Urban Development, which are available on the HUD Exchange site. Point-in-Time (PIT) counts are the annual practice of counting people experiencing homelessness on a single night in January. The National Alliance to End Homelessness offers an additional explanation of Point-in-Time counts.

    Table view of Sheet1$

    The table contains five fields:

    • State: Abbreviation of state name
    • Change: Change in total homeless counts between 2012 and 2013
    • Pop13: Total population in 2013
    • Homeless13: Total homeless count in 2013
    • ObjectID: Identifier number added by ArcGIS Pro

    Note:

    If you want to learn how the original spreadsheet was converted into this simpler form, you can read this ArcMap lesson: Download homelessness data.

  5. Find the row representing North Dakota (abbreviated to ND).

    ND row selected in the table

    In 2013, North Dakota had 2,069 total homeless people.

    What does this mean in a national context? North Carolina (NC) had nearly six times the number of homeless as North Dakota, and some states had even more. Many of these states have significantly higher population than North Dakota, which may explain their higher homeless totals.

    The more dramatic number is in the Change column. The value of 2.007 represents a homeless population increase of more than 200 percent. Such a sharp increase indicates the problem in North Dakota may be more severe than the total count suggests.

  6. Close the Sheet1$ table.
  7. In the Contents pane, right-click USA_States_Generalized and choose Attribute Table.

    The attribute table appears.

    Attribute table

    The attribute table contains data describing the features of the layer. Some of the fields in this table include the state name, the state's abbreviation (STATE_ABBR), and the region of the country the state is in. The abbreviation field has identical data to the State column in your Homeless Data table, and so can be used to join the two tables together.

Join the homelessness data to the spatial layer

A table join takes data from one table and connects it to data from another. You cannot join any two tables; both tables must share a field of common values. Otherwise, the software will not be able to identify which records in the join table match which records in the target table. In this case, the tables you want to join have common records of state name abbreviations.

  1. If necessary, open the attribute table of USA_States_Generalized.

    There is a lot more data here than you need. It distracts from the work you need to do to map homelessness. Before you add even more data with a join, you will clean up this table to show only what you need.

  2. In the Contents pane, right-click USA_States_Generalized, point to Design, and choose Fields.

    The Fields table shows all fields for the layer. The Visible column shows which fields are visible in the attribute table.

  3. Click the check box next to the Visible column header to turn off all fields.

    Uncheck all fields as visible on layer

  4. In the Visible column, check the boxes next to the following fields to turn them on:
    • STATE_NAME
    • STATE_ABBR
  5. On the ribbon, on the Fields tab, in the Changes group, click Save.

    Save button on the Fields tab on the ribbon

  6. Close the Fields table and confirm that in the attribute table only the STATE_NAME and STATE_ABBR fields remain visible.

    The other fields have been turned off but not removed entirely.

  7. In the Contents pane, right-click USA_States_Generalized, point to Joins and Relates, and choose Add Join.

    The Geoprocessing pane opens to the Add Join tool.

  8. For Layer Name or Table View, confirm that USA_States_Generalized is chosen. Change Input Join Field to STATE_ABBR.
  9. For Join Table, choose Sheet1$.
  10. For Output Join Field, confirm that State is chosen.

    Add Join tool

  11. Click Run to join the tables.
  12. In the attribute table for USA_States_Generalized, you should see five new fields added.

    Table with original and joined fields

  13. Close the attribute table.
  14. On the toolbar at the top of the ribbon, click the Save button.

    Save button on the Quick Access Toolbar

In this lesson, you joined stand-alone table data from a spreadsheet to a spatial layer of the United States. In the next lesson, you will prepare your joined data for publication and publish it to ArcGIS Online.