GEOG 868
Spatial Database Management

Project 3: Mapping the Class Roster

PrintPrint

Project 3: Mapping the Class Roster

For Project 3, I would like you to map the hometowns of everyone on the class roster using Postgres/PostGIS and QGIS. Included in the data you downloaded at the beginning of the lesson were U.S. state and counties shapefiles, along with a comma-separated values file called postal_codes.txt that stores U.S. and Canadian postal codes and the coordinates of their centroids. 

Right-click here to download the class roster with postal codes.  (If there are any students from outside the U.S. and Canada, they will appear at the bottom of the file with a special code.  There will be a matching record at the bottom of the postal_codes.txt file.)

Here are the broad steps you should follow:

  1. Import the counties shapefile into Postgres.
  2. Create a table to store the postal code centroids.
  3. Load the centroid data from the text file.
  4. Add a geometry column and use an UPDATE query to populate it with POINT geometries.
  5. Create a table to store the class roster.
  6. Load the class roster from the text file.
  7. Determine a way to associate the geometries in the postal codes table with the records in the roster table.
  8. Use QGIS to create a map of the hometowns. Include state and county boundaries for some context.

Tips:

  • The copy command can be used to load data from a text file into a table. Use the Postgres documentation to determine the correct usage for this command. Navigate to the command's page in the documentation either through the Table of Contents or by using the Search function. Pay particular attention to the following options: FORMAT, HEADER, DELIMITER, and QUOTES. The copy command expects the columns of the "to" table to match and be in the same order as the columns of the "from" file.
  • You can assume that the centroid coordinates are in the NAD83/Geographic coordinate system.
  • Assuming your postal codes table contains columns named lat and lon you could use the following expression in an UPDATE statement to create a textual representation of the POINT geometry:
      
    'POINT(' || lon || ' ' || lat || ')'

    Note that the double-pipe character string (||) is the concatenation operator in Postgres.
  • A table/view must include a column that contains unique values in order for it to be added as a layer in QGIS. This column could be one that you've explicitly defined as a PK, but not necessarily.  Also, this column need not be numeric.  (Earlier versions of QGIS required tables/views to include an integer primary key column.)
  • There are multiple ways to address step 7, some more well-designed than others. The ideal design would allow you to make changes to the class roster table (e.g., add new students or change erroneous postal codes) and those changes will automatically be reflected in QGIS (i.e., it should not be necessary to update a geometry column when a new student is added). Coming up with this design will be worth 10% of the total project points.
  • Draw the state and county boundaries in light hues, so they don't distract from the main thing the map is meant to convey.
  • If there are students from outside North America, create one map focused on North America and another that shows the whole class.  You'll probably want to exclude the county boundaries for this second map.

Deliverables

This project is one week in length. Please refer to Canvas Calendar for the due date.

  1. In the Project 3 Dropbox, upload a write-up that includes the map and describes the process you used to produce it. Your write-up will be evaluated according to the following criteria:
    • Workflow (60 of 100 points)
    • Write-up quality (20 of 100 points)
    • Aesthetic quality of the map (10 of 100 points)
    • Ease of making updates/insertions to the class roster (10 of 100 points)
  2. Complete the Lesson 3 quiz.