CEE 8813 · GIS Lecture 3 · Table operations

Join and Relate Lab

A join copies fields from a source table onto a destination table through a shared key. It is only safe when every destination row finds one and only one source row. Pick a scenario and follow the lines between the two tables to see when a join quietly goes wrong, and when a Relate is the right tool.

Add Join

Steps

Destination target · receives the fields

Source join table · gives the fields

Each line joins a visible destination row to a matching source row. Green: its only match. Orange: one of several. Red stub: no match. Purple: the selected row. Dashed lines and the ↑ ↓ counts are matches scrolled out of view. Sorting changes only the view, never the stored table (slide 8).

What you are seeing

Cardinality · computed over all records

1 : M One-to-many destination : source
Valid join
Destination rows
0no match
0exactly one
0more than one
Source rows
0used by no row
0used once
0used more than once

Map · destination coloured by number of matches

Click a feature on the map to select it, the same as clicking its row. Scroll or pinch over the map to zoom, drag to pan.

Joined table preview

ArcGIS Pro’s Add Join has Keep All Target Features (ArcMap calls it Keep all records). Recent Pro releases also offer a Join Operation option; its default, Join one to first, is how ArcMap always behaved. Joined field names carry their table name, as in GA_Counties.POPULATION. The last column is added by this page to show how many source rows matched; ArcGIS does not show it.

Try this in class

  1. The page opens on the trap. GA_Counties is the destination and many counties match several crashes. In the joined table Fulton shows a single crash, marked “1 of 128”. Click DeKalb and Gwinnett in the destination table and watch their crashes light up on the map and in the source table. That highlighting is a Relate.
  2. Switch Join Operation to “Join one to many”. The joined table grows from 159 rows to 568 because each county repeats once per crash. Why would summing POPULATION over this table now be wrong?
  3. Press “Summary Statistics first, then join”. Each county now finds at most one summary row (1 : 1) and Fulton gets FREQUENCY 128. What should the 91 counties showing <Null> really say?
  4. Open scenario 1 and untick Keep all target features. The joined table drops from 480 to 477 rows. Find the three crashes that vanished; sorting by the key puts their empty county names first.
  5. Open scenario 3. DeKalb’s 1,113 road segments turn red on the map. Fix the key with FIPS_KEY, then try the version where County_Cod was read as a number and see which two counties survive.
  6. Open scenario 4 and click a crash on SR 155: all 36 curves on that route light up. Then switch to free mode and predict the cardinality of D7_Curve_Inventory_GT.g_rte_cod → GA_Counties.COUNTYFIPS before you set it.

The ArcGIS Pro tools behind this page

ToolWhat it does hereArcMap (2022 slides)
Add JoinA temporary join on a key, with Keep All Target Features and, in recent releases, Join Operation.Joins and Relates › Join
Remove JoinTakes the join away. The stored tables were never changed.Joins and Relates › Remove Join(s)
Add RelateLinks the tables without merging them. Selecting rows in one highlights the related rows in the other.Joins and Relates › Relate
Validate JoinReports how many records match and whether the join is one-to-one or one-to-many, before you join.Validate Join button in the Join dialog
Summary StatisticsCounts crashes per county (Case Field Area__Coun) so the join becomes one-to-one.Summarize, or Summary Statistics
Add Field · Calculate FieldBuilds a clean key such as FIPS_KEY.Add Field · Field Calculator
Export Features · Export TableWrites the joined result to a new file, which makes it permanent.Data › Export Data

Back to Lecture 3: operations on two tables (slides 16–22)

The lecture’s join and relate slides, tied to what the lab above shows and to the ArcGIS Pro tool names.

Join factsSlides 16–17

  • A join is a temporary relationship between two tables.
  • The tables must share a common field, the key. Its values must be identical in both tables, and its type compatible.
  • ArcGIS then treats the two tables as one: the destination’s rows with the source’s fields added on, named like GA_Counties.POPULATION.
  • The stored data is not affected. Nothing is written into Collisions.shp.
  • It can be removed with Remove Join when you no longer need it.
  • To keep the result, export it with Export Features or Export Table.

Cardinality and the rule of joiningSlides 18–20

  • Always read cardinality destination first. Collisions → GA_Counties is many-to-one; GA_Counties → Collisions is one-to-many. Same tables, same key, opposite answer.
  • The rule of joining: each record in the destination table must match one and only one record in the source table. One-to-one and many-to-one joins follow it.
  • One-to-many breaks it, because the record to bring into Fulton’s row is ambiguous. ArcGIS still runs the join and quietly attaches the first match. Many-to-many is ambiguous on both sides. Use a Relate.

RelatesSlides 21–22

  • A relate is like a join, except that the tables remain separate.
  • Records selected in one table are highlighted in the related table, as when you clicked Fulton above. The slides’ example selects the New England states and finds their representatives.
  • In ArcGIS Pro use Add Relate, then open the related records from the attribute table. In ArcMap it is Joins and Relates › Relate.
  • Use a relate for one-to-many and many-to-many keys. If you need one number per destination row, summarise the source first and then join.

1 : 1One-to-one

Census_Counties → GA_Counties on the FIPS code. Valid join.

1 : MOne-to-many

GA_Counties → Collisions on county name. Breaks the rule; relate or summarise.

M : 1Many-to-one

Collisions → GA_Counties on county name. Valid join.

M : MMany-to-many

Collisions ↔ D7_Curve_Inventory_GT on the route ID. Do not join; use a Relate.

Pitfalls students hit

  • Case and spacing. “DeKalb”, “Dekalb” and “DeKalb ” (with a trailing space) are three different keys. Codes such as FIPS make safer keys than names because they have one spelling.
  • Number versus text. Add Join pairs Text with Text and numbers with numbers. Turning the text “089” into a number keeps 89 and loses the zero, so a key built from it no longer matches. Keep codes as Text.
  • The first-match rule. A one-to-many join does not fail. It keeps one source record per destination row and drops the rest without a warning. Check the cardinality first; Validate Join reports it.
  • Joins are temporary. They live in the layer in your map, and Remove Join takes them away. Export the layer to make the joined fields permanent.
  • <Null> is not zero. After a join, <Null> means no source row matched. With Keep all target features unticked those rows disappear instead, and your counts shrink without notice.