Due Date: Apr 4, 2017
- In this exercise you’ll be working with the gep664 database in the nyc schema. It’s assumed that you have also loaded the ZCTA shapefile into this schema (which we did in our previous class). You’ll also need to use QGIS.
- This exercise is worth 6 points. Each question is worth 1 point each.
- Be sure to follow the proper conventions for SQL statements and the guidelines for submitting assignments, as posted on the course website. 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. At the beginning of class hand in a paper copy of your SQL file; you don’t have to print out and hand in the map.
This assignment will give you additional practice with spatial relationships and joins, and you’ll pull together several concepts that you have learned thus far in the course.
You’ll be working with data in the nyc schema; this includes the ZCTA feature that we loaded from a shapefile in our previous class. If you don’t have the ZCTA file download it from the course website (under Class 7 data files) and load in into the gep664 database using the shapefile loader under PgAdmin – Plugins menu or the database manager in QGIS.
For the following, provide: the SQL statement, the row count, and any specific deliverable that the questions asks for.
Part I -Spatial Relationships
There are 339 census tracts and 25 ZCTAs in the Bronx. ZIP Codes / ZCTAs often do not align well with other census or legal geographies. In this exercise you’ll test and see how well they do or do not align with census tracts. Pay careful attention to the relationship you’re being asked to test.
1. Select all census tracts (the tractid and tractnum) that are within ZCTAs in the Bronx.
2. Select all census tracts (the tractid and tractnum) that overlap ZCTAs in the Bronx. Use DISTINCT to remove tracts that overlap more than one ZCTA.
3. Select all census tracts (the tractid and tractnum) that have their geographic centroid within ZCTAs in the Bronx.
Part II – Spatial Join
4. Write a SELECT statement where you select the subway stations (stop id, name, and trains) for the entire city and assign the stations the ZCTA code that they are within. For this statement, use the JOIN clause and make it a left outer join so that you keep all subway records on the left whether they have a matching ZCTA or not. Sort the result in descending order by ZCTA number.
Part III – Spatial Joins and Summaries
To refresh your memory when answering these questions, refer back to the slides and the Boundless tutorial. When writing these longer statements, construct them bit by bit, getting one piece to work before moving on to add the next piece.
In the nyc schema in the course database:
- census_tracts is a spatial table with census tract geometry; it also contains a column that identifies the Neighborhood Tabulation Area (NTA) that each tract is part of.
- census2010_tracts contains data from the 2010 Census for each census tract.
- census2010_lookup contains the list of census variables and the codes used to identify them as column headings in the census2010_tracts table.
5. Identify the codes for the variables that represent the total population, and the total population who are 16 years old and over. Then write a SELECT query where you relate these variables to the census tract geographies and create a summary for NTAs, so each row in the result is for an NTA and the columns represent: total population, total population 16 and over, and the percent of the NTA’s population that is 16 and over for each NTA. To avoid division by zero, select only tracts that have an over 16 population greater than zero. In order to get decimals for your percent total, remember to use the CAST operator around your integers:
(CAST(value1 as numeric))/(CAST(value 2 as numeric))
6. Take the query you wrote in question 5 and save it as spatial view called ntas_workforce (remember to do this, you must add the geometry to the select clause and union it – refer back to the slides). Or if you prefer, you can create a new table and insert the data into it (instead of creating a view). Provide the full query as your answer. Then, using QGIS connect to the course database and add this view to the map. Symbolize the NTAs to display a graduated map showing the percent of the population who are 16 and over. Classify the data using equal intervals with 5 categories. It’s normal if you have some “holes” in your map, as these represent unpopulated areas. You don’t have to create a fancy, finished map (with a layout etc) – just take a screenshot of the window and save the result as an image file (under Project menu – Save as Image). When naming the file, use the same convention as the sql template (i.e. use your name and the assignment number).