Instead of having Lab #3 next week (when Tuesday follows a Monday schedule), we are releasing Problem Set A on Thursday, February 15, 2018.

This is due by 5PM on Thursday, March 8, 2018 via Stellar.

Purpose:

In this exercise, we will examine and map some of the housing-related zoning variances in East Boston.

Most of PSet A involves SQL queries that can be done in phpPgAdmin, PgAdmin III or MS-Access using the East Boston tables from Lab #2 and the zoning variance data described in lecture and in the 'Zoning Database' portion of the SQL NOTES (http://mit.edu/11.521/www/sqlnotes). In order to map the zoning variances, you will need the methods that we will utilize in the Lab #4 exercise (next Tuesday), but you do not need to wait until you do Lab #4 to answer the other questions that involve SQL queries. Note that, since the zoning database is small and the queries are not that complicated, we could do all of them in an MS-Access database in place of Postgres to process the queries and views. That is fine and we have built an MS-Access database called 11.521_lab3.mdb containing the zoning variance data. If you wish, you may use this MS-Access database (and the earlier lab #2 database) for doing the homework (in place of using Postgres). However, we suggest that you gain some practice in running SQL queries in Postgres and pulling some query results from Postgres into ArcMap.

Be sure to include your SQL queries when you answer each of the questions. That way, you can get partial credit for getting part way to the answer and we can cut-and-paste them in pgAdmin to test them and offer advice if parts are wrong.

PSet A: Examining Boston Zoning Variances

Question 1: Let's examine the neighborhood pattern of zoning variances. Write a SQL query that counts the number of zoning variances in each of the 16 neighborhoods across all of Boston.

Question 2: Rewrite your query from the previous question to include the name of the neighborhood.

Question 3: Next, write a query that counts the zoning variances that are in each of the sub-neighborhoods of East Boston. Include in your table the names of both the neighborhood and the subarea. Use the 'substring' function, substr(), to list only the first 12 characters of the neighborhood, and the first 25 characters of the subarea. [Hint: to use substring, write the command substr(name_of_column,first_character,last character). For example, substr(nsa_area,1,12) pulls out the first 12 characters of 'nsa_area'. In MS-Access, the equivalent function is MID(nsa_area,1,12) - try it out.]

Question 4: In answering the previous question, you may have discovered that one zoning variance was encoded as being in East Boston based on the neighbrhd field but in another neighborhood based on the subnghbrhd field. What is the case number of this zoning variance and what is its neighborhood and sub area according to the subnghbrhd field?

Question 5: Rewrite your query from question 3 to include only those zoning variances that had a proposed use of 'housing'.

Question 6: Now write a new query that uses the results of your two queries in questions 3 and 5 to compute the fraction of East Boston zoning variances in each subarea that had a proposed use of housing. You may want to create a view for each of your previous queries and then use those views in your new query to compute the fraction that had a proposed use of housing.[hint: do you see why you cannot compute the fraction whose proposed use is housing in a single simple query without generating the intermediate tables (or using sub-queries)?]

Question 7: Do the results suggest any meaningful differences across sub-neighborhoods? If so, what might be a reason for sub-neighborhood differences?

 Home | Syllabus | Lectures | Labs | CRON | MIT