Fun with Tables & Queries:

1. Today you’ll learn about tables and then several helpful functions: • Queries, Calculations, Summarizing & Joining tables together Fun with Tables & Queries: Review: What is attribute data? What is spatial data? Please note that portions of the material in this lecture come from Maribeth Price, author of your text.

2. Attribute table Stores attributes of map features Associated with a spatial data layer Has special fields for spatial information Standalone table Stores any tabular data Not associated with spatial data Can be brought in from: Text files, spreadsheets, GPS files, database files Types of tables

3. Parts of a table Options menu Title Right-click field name to get menu Field Records Status bar

4. Field types

5. Editing fields Open Editor toolbar Type edits in fields Start editing, save edits, stop editing

6. Adding a field • In ArcMap

7. Field characteristics Length = 10 Maple St. Maple Stre • Length • The total characters a text field can store • Precision • The total width of digits a numeric field can store • Scale • The number of decimal places 156 1985.128 -1922.5600 0.001 0.00001

8. Things you can do with your data:1. SQL Queries (can be done in table mode or map mode) Two ways to select features • Selecting by Attribute • Queries based on one or more fields in the table • Find all wells with chloride concentration > 100ppm • Selecting by Location • Queries based on the relationship of features to another layer • Find all wells that lie inside industrial parcels

9. A AND B A OR B A B A XOR B A NOT B A. Select by Attribute: Boolean expressions 0 1 Blue circles represent areas where condition holds (True) = 1

10. Some valid queries [POP1990] > 1000000 “STATE_NAME” = ‘Alabama’ [POP2000] >= [POP1990] Note: DBF tables have field names enclosed in quotes Geodatabase tables have field names enclosed in brackets

11. Show selected

12. Multiple Criteria Queries • Multiple criteria queries using AND or OR • “STATE_NAME” = ‘Alabama’ OR “STATE_NAME” = ‘Texas’ Remember—you test each feature separately: Right Wrong “Land-use” = ‘RES’ AND “Land-use” = ‘COM’ “Land-use” = ‘RES’ OR “Land-use” = ‘COM’ “Pop2000” ≥ 5000 OR “Pop2000” < 9000 “Pop2000” ≥ 5000 AND “Pop2000” < 9000

13. $125,000 $125,000 $125,000 $125,000 Multiple conditions • Use parentheses to enforce order of evaluation (“LU” = ‘RES’ or “LU” = ‘COM’) and “Value” > 100000 “LU” = ‘RES’ or (“LU” = ‘COM’ and “Value” > 100000) $75,000 $75,000 $75,000 $75,000 RES COM

14. The Like Operator • “NAME” LIKE ‘%(D)%’ • Finds all of the (D) Democrats • % is wildcard (for shapefile) • * is wildcard (for geodatabase) • Ignores Don or Danforth • “NAME” LIKE ‘%New %’ • Would find New Hampshire and New York, but not Newcastle or Kennewick

15. B. Select By Location Select counties that completely contain state capitals Select counties that are within 200 miles of Denver

16. Selecting by location

17. Intersect

18. Within distance of

19. After doing a selection, you can create a NEW shapefile with just the selected attributes.

20. Other things you can do with your table: 2. Calculating fields Add a new field if necessary Consider whether you need decimal places!

21. Calculate Right-click field to calculate Enter expression

22. Summarizing tables • Calculate statistics for groups of features in a table • Groups by unique values in the specified field • User chooses statistics to calculate • Produces another table as output with groups and stats Historic major earthquakes How many earthquakes in each state? Total deaths and damage in each state? Average magnitude in each state?

23. Example • Group earthquakes by state field • Determine total deaths, total damage, and average magnitude of earthquakes in each state.

24. How to summarize Right-click State field Sum Deaths Sum Damage Average Mag Average MMI

25. Summarize Output Table Count field always generated automatically

26. 4. Joining tables: You have a standalone table with information about mortality due to earthquakes. • Can we now create a map of deaths by state? Standalone table

27. Joining tables Source table Destination table States layer attributes

28. US Earthquake Deaths by State Oops, what do you think happened here? Join summarize output to states layer to create map of deaths

29. Join facts • Joins are temporary relationships between tables • Tables must share a common field • Treats the two tables as a single table • Original stored data is not affected • Can be removed when no longer needed

30. One-to-one joins Destination table Source table Each record in the destination table matches exactly one record in the source table. We call this a cardinality of one-to-one.

31. Rule of Joining Each record in the destination table must match one and only one record in the source table. One to one Destination table Source table Many to one

32. One-to-one States to Governors Countries to capitals One-to-many States to cities Districts to schools Types of Cardinality • Many to one • Cities to states • Schools to districts • Many-to-many • Students to classes • Stores to customers

33. One to many ? Destination table Source table Violates the Rule of Joining Record to join to destination is ambiguous Must use a relate instead

34. Relates • Similar to a join except that • The tables remain separate • Items selected in one table may be highlighted in the related table

35. States Related tables Congressional Districts

36. How to join or relate tables