The scenario for this lesson's optional assignment is to map the results of the 2012 presidential election at the county level for a state of your choice. You can use the classification scheme I used in my voter registration map or come up with your own. Here are the data you'll need to complete the project:
Note that the data for some states appear to be unreliable (e.g., Colorado, Utah, Wyoming). The data were downloaded from The Guardian in the UK, which unfortunately was the only place I could find free nationwide county-level results. Be sure the state you select passes the "smell" test.
Here's a basic outline of the major steps to complete:
- In ArcMap, use the Mashup toolbox to export a CSV file of the county polygon coordinates for your selected state.
- Import the coordinate data and election data into a new Access database.
- Examine the coordinate and election data and develop a plan for how you will store the data in MySQL. Be sure to identify key fields that can be used to associate the election information with the county geometries. (The FIPS codes in the election data don't match those in the counties shapefile because they lack the two-digit state code. You'll need to work out how to fix this.)
- Create the tables you envisioned in the previous step in your MySQL database using either phpMyAdmin or the MySQL Workbench.
- You should already have an ODBC data source associated with your MySQL database after completing the lesson.
- In your Access database, use the ODBC data source to add links to the MySQL tables you created.
- Build an Append query to add the coordinate data to your MySQL table.
- Build another Append query to add the election data to your MySQL table.
- Write a PHP script that retrieves the data required for your mashup and output the data in XML format. You may find it helpful to use the Access query GUI to generate the necessary SQL. Important notes:
- The PHP script must be uploaded to the special php folder that was added to your personal web space (e.g., phpjed124)
- To run the script, you must use the phpdb.aset server (i.e., enter a URL like https://phpdb.aset.psu.edu/phpjed124/get_cities.php).
- Add a sidebar that lists the vote percentages for each candidate in each county.
If this election brings up bad memories for you, you're welcome to map a different election. You may also feel free to map the election results at the voting district level rather than at the county level.
When selecting a state/county to map, beware of multi-part and donut polygons. These types of polygons require a different kind of storage in the database, different methods of retrieval from the database and different methods for turning them into Google Maps Polygon objects.
If you're very comfortable with the material from this lesson, you may want to try mapping a place that has some of these special polygons for an extra challenge. Otherwise, I'd recommend sticking with a place that's comprised of all simple polygons. If you're unsure whether your place of interest includes any special polygons, go ahead and export its coordinates to CSV. The tool's dialog box will warn you if any were encountered.
This project is optional and is not graded. However, it can be submitted to the instructor for feedback and/or to earn up to 5 points added to your overall course grade (on a 100-point scale).
- Post the map with data extracted from your MySQL database along with a clickable sidebar listing selected attribute information to your e-portfolio.
- Below the map, include some reflection on what you learned from the lesson and/or any concepts that you found to be confusing (minimum 200 words).