CEE 8813 · GIS Lecture 3 · Table operations
An attribute table holds the facts about every feature on the map. Put 480 crashes, a teaching subset of the lecture's 1,491-crash CSV, on the map, then sort, query, add and calculate fields and summarize them, in the order ArcGIS Pro does it. Each step shows what changes on the map and what changes in the stored data.
Collision.shp on the map
World view · longitude/latitude grid, U.S. states only
Where the points land
What you are seeing
Drag to pan, scroll or pinch to zoom. Click a point to identify it. Projection: Georgia Statewide Lambert.
The crash file Collisions Dataset.csv is a plain table with a latitude column and a longitude column. Tell ArcGIS which column is X, which is Y, and which coordinate system the numbers are in (slides 3–7).
Collisions Dataset.csv · 26 columns · 1,491 rows (480 in this lab) · first rows of six columns
Export Features
CSV header → shapefile field name
ArcGIS replaced the characters a shapefile does not allow (spaces, #, :, /, parentheses) with underscores, put an F in front of names that did not start with a letter, and cut every name to 10 characters (slide 14).
In ArcGIS Pro, Display XY Data runs the XY Table To Point tool, which writes a new feature class that already has an ObjectID. In ArcMap (the 2022 slides) it made a temporary XY event layer without one, so slide 6 exports the layer to a shapefile to get a unique ID. The lab file is that shapefile, Collision.shp.
Click any column header in the attribute table below to sort by that field. Click again to reverse. Sorting changes only the view. Every record keeps its FID and its values, and the order stored in Collision.dbf stays the same (slide 8).
To change the stored order you need the Sort geoprocessing tool, which writes a new, sorted copy of the data. The table view never rewrites the file.
Export the XY layer first. Queries and field calculations need a unique ID for every record, and the export is what adds it (slide 6).
Build a where clause from a field, an operator and a value. Text values go in single quotes. In LIKE, % stands for any run of characters and _ for exactly one. The selected records turn cyan on the map and in the table (slides 9–11).
SQL as ArcGIS Pro writes it
The same query in ArcMap, as on slide 9
Whether = and LIKE match upper and lower case can depend on the data source. When you are not sure, compare in one case with UPPER(), as the lab does when Match case is off. AND is evaluated before OR, as in any SQL.
Export the XY layer first. Queries and field calculations need a unique ID for every record, and the export is what adds it (slide 6).
Think about the field type before you add the field (slide 13). Then Calculate Field fills it from an expression (slide 14). If any records are selected, only those rows are calculated.
Add Field
| Type | Holds | In this table |
|---|---|---|
| Short | Whole numbers from −32,768 to 32,767 | # of vehicles (1 to 5) would fit |
| Long | Whole numbers up to about ±2.1 billion | Collision_ IDs here (1001 to 1480) would fit in Short, but GDOT's 7-digit report numbers need Long |
| Float | Decimals, about 7 significant digits | A rate such as injuries per vehicle |
| Double | Decimals, about 15 significant digits | Latitude 33.76507, MilePoint_ |
| Text | Letters and digits up to a set length (254 at most in a shapefile) | Manner_of_, and route IDs such as 0893255801I whose leading zero a number field would drop |
| Date | A date; a shapefile keeps the date but not the time of day | Date_and_T came in as Text, so it sorts as text (step 2) |
Field names in a shapefile: at most 10 characters, letters, digits and underscores only, starting with a letter, and not a reserved word such as DATE, ORDER or FID. ArcGIS Pro 3.2 and later also offer Big Integer, Date Only, Time Only and Timestamp Offset.
Calculate Field
The same expression in ArcMap's Field Calculator (VB Script, slide 14)
Arcade, ArcGIS Pro's other expression language
Pick a numeric field to summarize. Like ArcGIS, the lab returns statistics for the selected set when one exists, and for every record otherwise (slide 15).
Slide 2 lists the one-table operations in this order. Each card names the slides and the ArcGIS Pro command that does the same thing.
A CSV has coordinates but no geometry. Display XY Data reads the X field as longitude and the Y field as latitude, in the geographic coordinate system you choose. Slide 7: select a GCS and remove any PCS.
ArcGIS Pro: right-click the table › Display XY Data (the XY Table To Point tool), then Export Features. ArcMap: Display XY Data, then Export Data.
Sorting reorders the rows on screen. It has no effect on the original data.
ArcGIS Pro: right-click a field header › Sort Ascending or Sort Descending, or double-click the header. The Sort geoprocessing tool writes a new dataset instead.
A where clause picks the records that meet a condition, such as fatalities ≥ 1 or a manner of collision containing “Angle”. Show the selected records, switch the selection or clear it.
ArcGIS Pro: Map tab › Select By Attributes; in the table, Show Selected Records, Switch and Clear.
Choose the type before you add the field. Once a field holds data, ArcGIS will not change its type; you add a new field and calculate it again.
ArcGIS Pro: the Add Field tool, or the Fields view of the table (Data Design › Fields).
Writes the result of an expression into a field, for every record or only the selected ones. Shapefile field names are cut to 10 characters, so “# of Fatalities (Collision Table)” becomes F__of_Fata.
ArcGIS Pro: Calculate Field, Python 3: !F__of_Fata! * 5 + !F__Serious! * 3 + !F__Visible! * 1. ArcMap: Field Calculator, VB Script: [F__of_Fata]*5 + [F__Serious]*3 + [F__Visible]*1.
Often a first step in data analysis and a chance to think critically about the data. Statistics are returned only for the selected set.
ArcGIS Pro: right-click a field header › Statistics, or the Summary Statistics tool.
Slides 16–22 (Join and Relate) are the next step: they work on two tables at once, such as these crashes and the county table.