Data joins between a survey table and the Senate and House district maps

Problem

The North Carolina (NC) General Assembly had commissioned a survey on industrial extension jobs. They are now requesting that a report be made to each state Senator and House Member highlighting the exact number of in state jobs created in their district as well as how neighboring districts have performed. The survey data is stored in a common Excel format. In order to fulfill their request, two maps are to be generated for Senate and the House respectively.

Analysis Procedure

Foremost, the state Senate and the House district boundaries are needed as they form the basis for the mapping of job data. Next, the job data is examined and the field of interest is located: EMPLOY_SUM, which represents the number of jobs created by each company. A quick scan across the data shows that there are three other accompanying fields to EMPLOY_SUM: COUNTY, ZIPCODE, and ZIP_Text, that can be used to reference EMPLOY_SUM on district boundaries. In general, ZIP codes are usually used as they are more accurate in representing the company’s location. ZIP Code Points is recommended over ZIP Code Areas because it also includes ZIP Codes that have no associated area such as post office box ZIP Codes and single site ZIP Codes such as government, buildings, or organizations. The Zip Codes Points for the NC region will be required as a layer on the district boundaries as it acts as the intermediary between EMPLOY_SUM (whose ZIP codes will be used to reference it) and the district boundaries. As there are multiple companies per ZIP code, the data needs to be aggregated before Table Join can be used to link the EMPLOY_SUM to the district polygons using Zip codes as the intermediary. As a precaution, the fields that are used in the join has to be verified for matching field type in order for Table Join to function.

The state Senate and the House district boundaries can be downloaded from the NC General Assembly (NCGA) or NC One Map. Here, the boundaries were from NC One Map. Select by attributes only NC state and saved the selection as a separate layer. The field ZIP_Code was found to be text.

Next the Excel data was examined and the EMPLOY_SUM and ZIPCODE were found to be numeric, while ZIP_Text was text. The field that needed to be aggregated had to be numeric, so EMPLOY_SUM can and was aggregated per ZIP code using Summary Statistics. Now, the aggregated EMPLOY_SUM can be linked to NC ZIP Code Points using Table Join. The ZIP fields used to join aggregated EMPLOY_SUM and NC Zip Code Points must be text. Now that the aggregated EMPLOY_SUM is added to NC ZIP Code Points, the latter can be linked to the district polygons using Spatial Join. Finally, the aggregated EMPLOY_SUM (job data) can be displayed on the district maps as requested. As a side note, ArcGIS Pro project the ZIP codes’ Web Mercator coordinate system on-the-fly to match the projected coordinate system set by the district boundaries (NAD 1983 StatePlane North Carolina FIPS 3200) because they were the first to be loaded on the system. The ZIP code from the ZIP Codes Points layer is in text format, hence EMPLOY_SUM is aggregated per ZIP_Text

Workflow Diagram

Results

Application and Reflection

The table join is used to join .csv, dbase, xls tables to a feature layer. Spatial joins are usually for joining point feature to polygon feature.

Problem Description For my GIS510 final project, I have to map the pH value that I have in .csv format to the map of Charlotte. My final project is to show the acid level of the Brownfields in the Charlotte region.

Data Needed I collected soil samples from the list of Brownfields from the NC Department of Environmental Quality. Then I sent the soil samples to the Department of Agriculture Agronomic Services for testing. The report is converted into .csv format.

Analysis Procedures The pH value of the Brownfields is table joined to the map of Charlotte with the Brownfields already geocoded and spatial joined to the land parcels of Charlotte Mecklenburg County. You can see more details on Brownfields.