JOINING TABLES

Joining data using a common attribute is an important and useful function in GIS. There are two ways of joining data in a GIS, via an attribute from a table or spatially based on how the data intersects with other data in space. You can also relate data from one table to another in order to understand how they are related. Rather than appending data from one table into another, a relate allows you to interact with the data between the two tables. Find out more about joins and relates in the ArcGIS Pro documentation.

We will perform an attribute join between our two spatial data layers in order to have all of the language data for Vancouver in one table.

Renaming Fields

First we need to rename the generic fieldnames such as VALUE0 with the appropriate language names. Make sure the attribute tables for househldPopMotherTong_joinTbl and variable_names are open.

1 Right-click on the attribute table name for househldPopMotherTong_joinTbl and select New Horizontal Tab Group.

This will stack the two tables on top of one another to make them easier to view simultaneously.

2 Right-click on the VALUE0 fieldname and select Fields to open up the Fields View.

3 Referencing the information from the variable_names table, double-click in the VALUE0 field under the Field Name column and change it to aborigLangs. Change the Alias to Aboriginal Languages.

Field names have character limits and cannot have spaces, but aliases can.

4 Change the other three field names and aliases for values 1, 2, and 3.

5 Click on Save under the Fields tab at the top of the map.

You should see the changes you made reflected in the househldPopMotherTong_joinTbl attribute table.

Join Data

The next step is to join this dataset to the househldPopMotherTong_joinToThis dataset in order to have all of the language for Vancouver in one table, which will make the data easier to manage.

1 Examine the fieldnames in the attribute tables of these two datasets and see if you can find a common field to perform the join on.

Joining two tables together requires that they have a field in common, and this is often an identifier field. In our two tables, the spatial_id field is the identifier for Census tracts, and this is the field we’ll use to join our two datasets.

2 Close any open tables and save your map in the upper left.

3 From the Contents panel on the left, right-click on the househldPopMotherTong_joinToThis layer and select Joins and Relates and then Add Join.

4 Select spatial_id as the Input Join Field (there may be a warning indicated by an exclamation point in a triangle about indexing. We can ignore that for this join).

5 If it is not selected automatically, select househldPopMotherTong_joinTbl as the Join Table and spatial_id* as the **Join Table Field for this table.

6 Click OK to create the join.

7 Open the attribute table for the househldPopMotherTong_joinToThis layer and scroll all the way to the right to see whether this table has the new fields you added.

Copy Features

Note that this is a temporary join and has not altered the underlying dataset. In order to ensure this join is permanent, we’ll create a new feature class by using the Copy Features tool.

1 Close the attribute table and click on the View tab at the top and select Geoprocessing. The geoprocessing pane will open on the right.

2 In the search field, type copy features and click on the first tool at the top, the Copy Features tool in the Data Management toolbox.

3 Use househldPopMotherTong_joinToThis for the Input Features and navigate to the Intro.gdb to create a new feature class called popMotherTongTractVan and click Run.

The new feature class that is created with the join you added will appear in the Contents pane.

4 Unjoin the table from the househldPopMotherTong_joinToThis layer by right-clicking on this layer in the Contents Pane and selecting Joins and Relates > Remove Join.

5 Uncheck other layers and open the attribute table for the new feature class you just created.

Delete Fields

Now that we have a complete dataset for the language data, we can delete extraneous fields, such as all the duplicate spatial_id and name fields.

1 Under the Data tab at the top of the map, click on Fields.

This is another example of how there is more than one way to perform a task in ArcGIS Pro and most GIS software.

2 From the Fields View, right-click on the grey tab on the far left of the first FID you come to and select Delete. Do the same for the spatial_id and name fields directly below it. They’ll appear with strikethrough text.

3 Scroll down through the whole table and delete any FID and spatial_id and name fields, except those at the very top of the table. You can also delete by left-clicking on the grey tab of the far left of the next FID field, holding down SHIFT and clicking the grey tab on the far left of the name field two rows below.

This will select three rows which you can then delete together.

4 There may be one OBJECTID field toward the bottom which you can also delete, along with extraneous Shape_length and Shape_Area fields. Any fields which are greyed out you will be unable to delete and are necessary for establishing the geometry of the layer.

5 Click Save at the top of the map under the Fields tab and close the Fields View and save your map.

If you look at your attribute table now, you’ll see that it has been cleaned up and is easier to understand.

Exercise On Your Own

We want to join one more dataset to our table which contains attributes for the total population by Census tract, also downloaded from SimplyAnalytics accessed through the UBC library. This will allow us to further analyze the language data we have.

1 Click the Add Data dropdown arrow from the Map tab and select Data, the first option.

2 Navigate to the Intro.gdb and add the totalPopTract feature class to your map.

3 Using the same steps as above, join the totalPopTract table to the househldPopMotherTong layer using spatial_id and then export the data to a new feature class and call it popMotherTongTractVan.

Every time you create a new dataset, try and name it something that identifies what it is. Data management in GIS can be unruly, and having a good file naming system is essential.

4 Delete any extraneous fields that got added toward the bottom of the attribute table in the Fields View.

5 Rename the VALUE0 Alias field to Total Pop, save your edits, and exit the Fields View.

In the next section, now that we have a complete dataset, we’ll further analyze this data.

---