Extra Credit – Review

assignment

Due Date: Apr 30, 2019

Introduction

  • In this optional exercise you’ll be working in the nys schema within the gep664_2019 database.
  • This exercise is worth 3 points. Each question is worth 1 point.
  • Be sure to follow the proper conventions for SQL statements and the guidelines for submitting assignments, as documented in the SQL Homework Guidelines. Failure to do so will result in point deductions.
  • For this assignment you will need to submit a SQL file and a map as an image file. When you submit your electronic assignment to the Box, attach the SQL file and the image as attachments in the same message.

Background

In this exercise you’ll be working in the nys schema within the gep664_2019 database. This schema includes tables with county-level census data that describe the economy and labor force in NY State. You’ll be using these county-level tables:

metros
counties that are part of metropolitan areas that fall within NY State
resworkers
number of workers who live in the county and their place of work from the 2012-2016 American Community Survey

Refer to the metadata table and back to assignment 3 if you want more information about these tables. You will also need to download a shapefile of NY state counties from the Data and Software page, Extra Credit data under Data for Homework.

Questions

For the following, provide the SQL statements and the record count that is automatically returned by pgAdmin.

1. Create a view called nys.metro_work_outside where you join the metro table to the resworkers table. For the CBSAs that are classified by type as Metropolitan Statistical Area, summarize the number of resident workers in one column and the number of workers who work outside their state of residence in another column by metro area. Sort the results by metro name in alphabetical order. The final result should represent one row for each metro (NOT one row for each county).

2. Load the shapefile of the NY State counties into the nys schema. When loading it you must define the system as NAD 83 SRID 4269 and rename the table counties. After the table is loaded, write an ALTER TABLE / COLUMN statement to transform the geometry to NYSP Central Zone, SRID 2261.

3. By joining the resworker and counties tables, create a new spatial table called nys.working_incounty that contains: county ID, county name, total resident workers, resident workers who work in their county of residence, a new column with the percentage of resident workers who work in their county of residence, and geometry of the counties. Remember there are multiple methods that you can use to create a new table from existing data – choose any one. To calculate the percentage you’ll need to use the CAST operator that we’ve seen before:

(CAST(column1 as numeric) / CAST(column2 as numeric))*100

Once you’ve created the table, add it to QGIS and create a map of the percentage of workers who work in their county of residence.

Attribution-NonCommercial-ShareAlike 4.0 International

This entry is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International license.