Creating associations among tables
Table joins are used to append data from one table or layer to another. In this way we can extend existing map data or map new data by combining raw table information with a map layer. Joins allow us to bring together non-spatial data and unattributed geometry (like census tracts and census tables).
Goals
In this tutorial, you will learn to:
Extend analytical possibilities by appending different datasets
Define a relationship between two tables
Remove joins
1. Start a new project
In order to get started, you must first create a project. ArcGIS Pro allows you to assemble all the resources required to complete a project in one place. As you learned so far, a project contains maps, layouts, tasks, and connections to servers, databases, tools, and folders.
Go to the Start menu and open ArcGIS Pro and sign in using your Clemson ID.
To do this, click the Sign In menu
at the top corner.
Click on Enterprise login. In the Your ArcGIS organization’s URL box, enter clemson so that your URL reads: clemson.maps.arcgis.com. Select Continue.
A new window appears. Click on Clemson University.
The Clemson University regular login screen appears. Enter your Clemson username and password.
ArcGIS Pro automatically opens the start page. Here you find options to either open an existing project or create a project using one of the available templates. These templates provide a starting point for the project. Additional maps, scenes, and catalog views can be added to your project at any time, regardless of the initial template. On the start page, under Blank Templates, click Map.
On the Create a New Project dialog box, in the Name box, type Joins
To save a project to a different location click the Browse button
and browse to the folder Working_with_Tabular_Data on the C Drive.
2. Connect to the workshop folder
Under the Catalog pane right-click Folders and click Add Folder Connection .
Navigate to :\\Working_with_Tabular_Data and click Ok.
The folder connection appears in the Catalog pane in the Folders category .
3. Set the coordinate system for your map
When you start a new GIS project, it is very important to work in the right coordinate system. In our case, we are going to work with data in the continental United States. According to the coordinate systems section we covered, which coordinate system would be more appropriate for this project: geographic or projected? Since in this exercise, we want to look at the density of the COVID19 cases and population in each county, we will need to use a coordinate system that preserves the area.
In order to set up the correct coordinate system, go to the Contents pane, right-click on Map.
Click Properties.
The Map Properties dialog box opens. Click the Coordinate Systems tab.
The boxes below the Current XY and Current Z headings show the current horizontal and vertical coordinate systems of the map or scene, respectively.
Click Details for the horizontal coordinate system to see how it is defined. As you see described, your map has the WGS 1984 Web Mercator Auxiliary Sphere as the default projected coordinate system.
To change the horizontal coordinate system, click the button below the Current XY.
The XY Coordinate Systems Available box shows you the current system and all the possible available options under:
Layers
Geographic coordinate system
Projected coordinate system
You have to choose an appropriate coordinate system from the corresponding Coordinate Systems Available list. You can also enter a search term in the Search box to help locate a specific coordinate system.
In our case, we want to select a projected coordinate system, so we expand the Projected Coordinate System dropdown.
Click on Continental.
Expand the North America tab.
Click on North America Albers Equal Area Conic.
Click Ok.
4. Add data to your map
Before starting any work we want to first inspect our tables and geometry to understand what’s in them. Start by adding data to your map.
Under the Catalog pane click on Project, expand
Folders and then expand Working_with_Tabular_Data and expand TabularData.gdb. Right-click on Counties_demographics and add it to your map (#1). Repeat this process for the COVID19 layer. You should see the layer added to your map.
In Catalog pane, click on Portal (#2).
Click on Living Atlas (#3).
Type COVID 19 in the search box (#4)
Double click on COVID-19 Cases the US and add Cases to your map (#6).
Open the attribute table for both layers you added to your map and inspect the data.
What is the common field between the two datasets?
5. Join your datasets
You can Join a layer to another layer or table based on a common field. Feature layers, table views, and raster layers with a raster attribute table are supported. Here, we will join the CPVID19 and Counties_demographics --which both are a feature class dataset. Then, we will look at how to connect an Excel dataset to our GIS dataset.
To join two datasets, you need to find a common filed between the two. In this case, you can see that we have the ID field in Counties_demographics and State/County FIPS Code in COVID19 dataset represent the FIPS code (the Federal Information Processing Standard Publication is a five-digit Federal Information Processing Standards code which uniquely identified counties and county equivalents in the United States, certain U.S. possessions, and certain freely associated states). We will use this field to connect the two datasets. ArcGIS uses those unique IDs and appends the records from one table to another anywhere it finds two ids in common (based on how we set the join up). Right click on the Counties_demographics layer and select Joins and Relates > Add Join
.
In the Add Join window that opens the top drop-down under Layer Name or Table View should be Counties_demographics.
In drop-down Input Join Field box select the ID field.
In Join Table drop-down select to COVID19.
In Output Join Field box select the State/County FIPS Code field.
Check the box Keep All Target Feature. This way, all records in the input layer or table view will be included in the output. This is the default. If you leave it unchecked, only those records in the input that match a row in the join table will be present in the result.
Right-click on the Counties_demographics layer and open the attribute table again. Scroll to the right to inspect the newly appended data.
Note that you did not create a new layer when making this join. Instead, this is a dynamic join between the Counties_demographics feature class and the COVID19. It is not permanent and exists only in this map document.
With the table open, scrolled to the right, right-click on the Counties_demographics layer and select Joins and Relates > Remove Join
. In Remove Join window, select the join you want to remove (COVID19) and click Run. The appended information disappears.
Rejoin the COVID19 dataset to Counties_demographics. We’ll go over making a join permanent later in the next section.
6. Make a join permanent
Joins created in an ArcGIS document are dynamic and must be exported to make them permanent. You can create a permanent copy of your join. Right-click on the layer Counties_demographics (#1) and select Data (#2) > Export Feature
(#3).
Rename the Output Feature Class to Counties_demographics_COVID19 (#4).
Click on Run (#5)
Open a new blank map. Bring in the original Counties_demographics layer and your newly exported copy. Open both attribute tables and compare. You should see that only the exported copy has retained the joined attribute information.
Click the Save button to save your progress.
7. Exercise on your own
Using what you learned so far, create a map of COVID19 cases, and one of the variables of your choice in the Counties_demographics layer.