Database Cardinality: Tabular and Spatial Joins
Problem: You will need to create two maps visualizing job creation in North Carolina. The first map will show job creation based on house district. The second map will depict job creation by Senate district. The data given to solve this problem is a single excel document.
Analysis Procedures:
To solve this problem we needed supplemental data. First, we needed the NC House and Senate district lines from NC One Map. Specifically, these are NC Senate Districts – Rucho Senate 2 (Download) and NC House Districts – Lewis-Dollar-Dockham 4 (Download). USA Zip Code points were retrieved from ARCGIS.com. Data layers were then opened in ARCmap. NC Zip codes were isolated and exported into a new layer using select by attribute. The remainder of this problem as solved using data aggregation, one-to-one joins, and spatial joins.
Within the excel table we can see that the newly created jobs have a Zip Codes Field, since we are using the US zip codes for this assignment we will first need to do a join to give our job creation some spatial context. To do this we will need to first simplify the ZIP_String field and create a sum of jobs created within each zip code using the summarize tool only on zip code within NC. This new table can then be joined to the Zip points layer. At this stage, we will have three layers to work with, the house district, the senate districts, and the new layer showing only the zip codes, I will refer to this as Zip_Job_Created.
The following steps were then repeated for each the senate layer and the house layer. Zip_Job_Created will be spatially joined to the house and senate districts layer separately to create the two maps. Number labels were added using properties and for clarity I used an expression under labeling to leave any district with “0” jobs created without a label. These districts were also depicted as a standalone group without any color on the map.
Results:
The following map depicts jobs created by house district
The following map depicts jobs that were created by senate district
Application and Reflection: The main concept in this assignment is learning to take tabular data that has been collected and use it to find answers to other questions.
Problem Description:Another scenario could be if we wanted to relate development within zip codes to watershed regions to answer questions such as, what regions have the highest rates of new development. Specifically, the state of Virginia would like to better understand where sources of pollution in the Chesapeake Bay may be increased due to increased development,
Data Needed:This data could easily be obtained through USGS and state websites and joined with a spatial join. The user would need to have a shapefile of the VA watersheds and tabular data of new developments with the coordinates. However, if there was additional information collected in tabular form about things such as runoff or erosion, that data could be added using the skills we learned in this assignment.
Analysis Procedures: To solve this problem there are multiple options but the best would be to use Display X, Y Coordinates on the developments table, then perform a spatial join to join the data points to the watersheds layer.
Additional Resources from ESRI: