Assignment 8 – Spatial Relationships

assignment

Due date: Apr 2, 2019

Introduction

  • 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 8 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.

Questions

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 (data files used for Class 7) and load it into the gep664 database using the PostGIS shapefile loader or the database manager in QGIS. It’s in NAD83 (4269). After you load it, you’ll need to transform it to NYSP (2263) before you can use it. Refer to the in-class notes for class 7 for the steps we took to do this.

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. Use the census_tracts and zctas layers.

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.

4. Modify your statement in question 3 to select just the tracts in ZCTA 10468. Create an additional field where you calculate the total area of the census tracts (i.e. the area of the entire tract, regardless of whether the area is inside or outside the ZCTA).

Part II – Spatial Join

5. 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 decending order by ZCTA number.

6. Create a new spatial table called nyc.bedford_subways that contains all the subway stations in ZCTAs 10458 and 10468. The table should contain: stop id, name, trains, ZCTA, and subway geometry. Be sure to assign stop_id as the primary key. Create the table using spatial relationships, not by manually selecting stations yourself. Then create a spatial index for the new layer. Provide all SQL statements in your answer.

Part III – Spatial Joins and Summaries

To refresh your memory when answering these questions, refer back to the slides and the course exercises. 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.

7. 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))

8. Take the query you wrote in question 8 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). Provide this 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).

Attribution-NonCommercial-ShareAlike 4.0 International

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