Geospatial Analysis Skills: Review and Self-Assessment

Working with Database Tables

Table of Contents

  1. Geospatial Analysis Skills: Review and Self-Assessment
  2. Setting up Your Workspace
  3. Datums, Projections, and Spatial Reference Systems
  4. Working with Database Tables
  5. Adding XY Data
  6. Proximity and Overlay Operations

As you learned previously, a significant component of geospatial data resides in a database. Understanding the elements, structure, and organization of a database helps one work with geospatial data more effectively. A database is a collection of individual entities stored in a highly structured way. Entities are unique objects or features represented in the database. One of the roles of a database is to ensure that each of these entities remains unique and separate from other entities. When working with geospatial data entities are usually made up of geographic units such as cities, counties, states, and countries. Entities also have attributes. Attributes describe the properties of entities. For example,  an attribute for a specific county might include the total area in square miles.

You also learned that a database stores entities and attributes as tabular data. When discussing databases, one refers to a row in the table as a record and a column as a field. Each field has a unique field name located in the top row of the table called a header row. Each field also has an attribute type. An attribute type describes the nature of the attribute in the manner in which the database stores it in memory. Attribute types typically include numbers, strings, and dates. A string is a data type that represents text.

Skill Drill: Performing a Table Join

Using the skills you learned earlier, navigate to the National Historical Geographic Information System (NHGIS) website. Download a CSV table containing county-level data that relates to Sex by Age for the year 2010 (Figure 1.6). Save the file to your original folder and decompress the zipped folder.

Figure 1.6: Use the filters to narrow down the list of source tables.

Previously, you learned that a table join is when you establish a one-to-one relationship between two tables using a common attribute field. In this step, you must create a table join between the California counties layer and the csv table containing the sex by age data. The joining of the two tables requires a key. As you learned earlier, a key is a common field, one that exists on both tables, and is used to associate them together. The NHGIS provides an attribute field that serves as a key called GISJOIN (Figure 1.7).

Figure 1.7: Both the CSV table and the California Counties attribute table have a field called GISJOIN.

Hint: If you forgot how to perform a table join, review the skills you learned in the Chapter 7 Tutorial Mapping Food Deserts in Southern California, from the Geospatial Concepts Text.

After performing a table join, export the layer as a new shapefile called Sex_by_age.shp. Save the new shapefile to your working folder. When done, add the new shapefile to the map document.

Skill Drill: Data Classification

Recall from previous tutorials that a CSV file is one of the simplest forms of GIS data. It consists of a single table. As a result, it does not contain the metadata you would typically find in GIS data. Instead, the NHGIS stores the metadata in a separate text file called a codebook. This text file is located in the same folder as the CSV file. Locate the codebook file, which stores the metadata, and use it to perform the next few steps.

Previously, you learned that data classification is used to simplify and understand numeric data. Through classification, it is possible to identify patterns and trends or to alter data by creating a subset of specific classes. Classification is mostly organizing data in groups. One undertakes classification by creating a series of class intervals, a range of values that do not overlap.

In ArcMap, symbolize the Sex by Age layer using graduated colors. Use the field that represents the male population as the primary value on which to base the classification. Use the field that represents the total population to normalize the data. Use the quantile method of classification and seven classes. The result should be a choropleth map that displays the percentage of males per capita using seven class intervals.

Hint: If you forgot how to symbolize data on the map, review the skills you learned in the Chapter 2 Tutorial Mapping Earthquakes in Northern California, from the Geospatial Concepts Text.

In your Microsoft Word document, record the answers to the following questions.

14. Which California counties fall into the highest class interval?

15. Which California counties fall into the middle class interval?

16. Which California counties fall into the lowest class interval?

Hint: For questions 14-16, consider changing the colors of the highest, middle, and lowest class interval. Also, turn on the labels to make it easier to identify the counties.

Skill Drill: Using the Field Calculator

Open the attribute table for the Sex by Age layer and locate the Shape_Area field. Perform a Calculate Geometry operation to update the field so that it displays square kilometers.

Next, add a field to the attribute table called FemPerSqKm. Change the field type to Long Integer (Figure 1.8). Leave all other default settings and click OK.

Figure 1.8: The long integer field type represents a large number with no decimal places.

Use the Field Calculator on the FemPerSqKm field. Divide the field that represents the female population by the Shape_Area field. The result should be the female population density of each county expressed as females per square kilometer.

Hint: If you forgot how to use the field calculator, review the skills you learned in the Chapter 7 Tutorial Mapping Food Deserts in Southern California, from the Geospatial Concepts Text.

In your Microsoft Word document, record the answers to the following questions.

17. Which California county has the highest female population density?

18. How many females per square kilometer does this county have?

Hint: You will find the answers to questions 17-18 in the attribute table.