GEOG 868
Spatial Database Management

Project 3: Mapping the Class Roster

PrintPrint

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 zip 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.
  • Tables must have a primary key column defined in order to be added as a layer in QGIS. Also it is important that this primary key column be an integer.
  • 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 the course Calendar in Canvas for the due date.

  1. In the Project 3 Drop Box in Canvas, 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.