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 (the North American students at least, based on postal code centroids) using Postgres/PostGIS and QGIS. Included in the data you downloaded at the beginning of the lesson was a counties shapefile and a comma-separated values file called postal_codes.txt that stores U.S. and Canadian postal codes along with the coordinates of their centroids.

Right-click here to download the class roster with postal codes.

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.

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.