Database Cardinality Issues in GIS
Learning Objectives
- To understand table joins
- To determine and map the number of jobs created in each North Carolina House and North Carolina Senate District
Problem
Given the results of an industrial extension jobs survey, it is requested to report to each North Carolina state senator and house member, the number of jobs created within their district. The survey database contains the number of jobs created for each record of input, whether the record represents each new company, new quarter, new job, or other occurrence which needs to be included within the database.
Analysis Procedures
To determine jobs created within districts, 2002 NC Senate and NC
House polygon data was joined with the survey database (Excel file). This data, along with ArcMap software was used
to produce maps showing not only district results, but also a comparison
between neighboring districts. Because
tabular data from the database cannot be joined directly to polygon data, the number of jobs was first joined to zip code point data collected through the
NCSU spatial libraries. North Carolina Senate and House Districts along with zip codes
shapefiles were downloaded from the NCSU spatial libraries website (keyword search: "Voting") and added to
an ArcMap document in that order. District data was provided in the NC State Plane, NAD83 coordinate
system with units of meters. The zip
codes shapefile included all US codes, and was provided in the World Geodetic
System of 1984, WGS84 geographic coordinate system with map units in decimal
degrees. Statewide zip codes were
selected by attributes called “STATE” and exported to a usable shapefile with the coordinate
system of the data frame.
To join
attribute data tables, a common field in a common format must be
available. Both tables share zip code
values. This operation employs a
many-to-one relationship because we are associating multiple employment data
(or jobs) to a single zip code. It
allows us to determine the number of jobs created per zip code. In order to perform an attribute join, the field common to both
tables was checked and verified to have text properties. The jobs survey database was then summarized
according to the zip code (string) field. The resulting summary table showed the sum of jobs created for each
unique zip code in N. Carolina. This
table was finally joined to the NC zip codes table exported earlier to enable
geoprocessing. In order to determine the
number of jobs created per Senate and House district, a spatial join of the NC zip
codes to districts shapefiles was necessary. In summary, the methods used here take the total of all jobs created per
zip code and, after establishing a spatial relationship, further sums them
according to district. By determining the number of zip codes per
district, we in turn, know the total number of jobs created per district. The results were verified, symbolized and
labeled for presentation.
Results
The maps below show the number of jobs created in the state of North Carolina by Senate Districts and House Districts, respectively.
Click images to enlarge.
Application and Reflection
Joining tables is necessary in a GIS in order to associate data for geoprocessing or queries or symbolizing patterns. For example, in conducting a stream inventory for classification purposes, a many-to-one relationship exists when a table of inventoried streams is joined to another table containing stream types. Each stream can only be classified as one type, and can be uniquely symbolized on a map. Joining building addresses to parcels, or school locations to census tracts, are examples of a spatial join that are useful for zoning and planning.