Table Joins, Relates, and Querying Attribute Data

Table Joins, Relates, and Querying Attribute Data

Why Attribute Data Matters in Surveying and GIS

A shapefile or feature class stores two things: geometry (the shape and location of each feature) and attributes (descriptive information in a table). In land surveying and parcel mapping, the geometry might be a parcel boundary polygon, but the owner name, assessed value, and legal description live in a separate database maintained by the county assessor. Connecting those two datasets is one of the most common GIS tasks in this trade.

Table Joins

A table join appends columns from one table onto another based on a shared field called the join key or common field. In ArcGIS Pro, you right-click the target layer in the Contents pane, choose Joins and Relates → Add Join, and specify:

• Input Join Field — the field in your spatial layer (e.g., `PARCEL_ID` in the shapefile)
• Join Table — the CSV or standalone table (e.g., the assessor export)
• Join Table Field — the matching field in that table (e.g., `APN`)

ArcGIS Pro performs a one-to-one join by default: each record in the spatial layer is matched to at most one record in the join table. If a parcel ID appears multiple times in the join table, only the first match is used unless you use a one-to-many relate instead.

Verifying the join is essential. Spot-check at least five records by clicking individual features and confirming the owner name and assessed value match the raw CSV. A successful join shows populated attribute values; a failed match shows `<Null>`.

#### Data Type Mismatches

The most common join failure is a data type mismatch. If the shapefile stores `PARCEL_ID` as a numeric integer field but the CSV stores `APN` as text (string), the join silently returns no matches. The fix is to convert one field so both share the same type. In ArcGIS Pro, use the Calculate Field tool or Add Field plus Field Calculator to create a new text version of the numeric field, then rejoin on the converted field. Always check field types in the Fields view before joining.

Relates vs. Joins

A relate links two tables without appending columns. It is used when the relationship is one-to-many — for example, one parcel might have many tax payment records. Relates let you navigate between related records interactively but do not add columns to the attribute table. Use a join when you need the data in one flat table for analysis or export; use a relate when you need to browse a one-to-many relationship without duplicating rows.

Definition Queries

A definition query filters which features are displayed and available for analysis without deleting any data. The syntax follows standard SQL `WHERE` clause rules. In ArcGIS Pro, open Layer Properties → Definition Query and use the query builder or type SQL directly.

Common patterns:

| Purpose | Example SQL |

|---|---|

| Single value | `"LAND_USE" = 'Residential'` |

| Numeric range | `"ASSESSED_VAL" >= 100000 AND "ASSESSED_VAL" <= 500000` |

| Compound OR | `"ZONE" = 'R1' OR "ZONE" = 'R2'` |

| NOT | `"OWNER" <> 'UNKNOWN'` |

| Wildcard | `"OWNER" LIKE 'SMITH%'` |

Field names are enclosed in double quotes; text values in single quotes. The `LIKE` operator with `%` matches any sequence of characters — useful for partial name searches. Always record the exact SQL you write and the feature count returned so you can reproduce and audit your work.

Select by Attribute

Select by Attribute (Map tab → Select by Attributes in ArcGIS Pro) applies the same SQL logic interactively to select a subset of features — highlighted in cyan on the map. Unlike a definition query, it does not hide unselected features; it simply marks a subset. You can then export the selection, calculate statistics on it, or use it as input to another tool.

Select by Location

Select by Location answers spatial questions: Which features from Layer A are within a certain distance of features in Layer B? Common relationship types include Intersect, Within a Distance, and Completely Within. For survey work, a typical use case is: select all control monuments within 500 feet of a proposed road centerline. After running the tool, verify the selection count in the status bar and in the attribute table header. Export the selection using Export Features (right-click the layer → Data → Export Features) to create a permanent feature class.

QGIS Equivalents

QGIS uses the same logical SQL syntax in its Select Features by Expression dialog and Layer → Filter (definition query equivalent). Joins are performed via Layer Properties → Joins. Field type handling is similar — mismatches cause failed joins. Knowing both platforms makes you more versatile on the job.

Data Integrity and Professional Responsibility

In surveying, attribute data feeds legal descriptions, tax records, and boundary decisions. A wrong join or an unverified query result can produce an incorrect map that affects property rights. Always:

• Verify joins by spot-checking multiple records against the source data.
• Save a copy of the original data before any join or field calculation.
• Document your SQL queries and record feature counts so your workflow is auditable.
• Never submit a deliverable based on unverified joined data.

File and Equipment Safety

This is a software-based lesson. Maintain good data hygiene: work in a project folder with clear naming conventions, never edit the original source files, and back up your project regularly. If working in a computer lab, follow your instructor's log-on and data-storage procedures so your work is not lost between sessions.