Administrative

• Lab #1 will be graded and uploaded to Stellar by next Tuesday and solutions will be posted this weekend.
• Lab #2 exercise due today (via Stellar)
• Remember: Problem Set-A is available online
• It is due on Thursday, March 8
• Start it this week since problem set B will be due March 15
• Problem Set B is also available online
• Lab #4 in lab next Tuesday and due Tuesday, March 6 - using Boston Zoning Variance database (from 1980s)
• Optional extra lab time tomorrow, Friday, Feb. 23, in 9-554 from 12:15-2:15 pm. (Shifted a little earlier since Rounaq has a class at 2:30.)  If there is enough interest, we can illustrate queries and ArcGIS-Access-Postgres connectivity of the type that is needed for Problem Set A and for Lab #4 (next week's lab using the Boston zoning data).

Today

• Part I: Evolution of Boston Zoning Variance database
  • Understanding the schema and rationale for the Boston zoning variance database.
  • Use history of zoning database to understand how real databases evolve over time
• Part II: Distributed Access to Parcel-Level City Databases
  • Review Book Chapter reading for today - http://mit.edu/11.521/papers/techcity_7ferreira.pdf.
  • Be sure to read this chapter since the ideas will be important for Problem Set B
• Solving the 'spelling error' problems for Boston's parcel database
• Problems and strategies for sharing data across agencies and organizations
• Ways of accumulating sharable knowledge over time
• Tips and Tricks working with East Boston parcel data and RDBMS tools
  • MS-Access query building
    • PROBLEM: you must rename [fy2005_ land] to remove the space in the name - otherwise, the 'join' in ArcMap will not work but there will be no error message (and you need to click 'validate join' in order to see that there is a problem).
  • The space in 'fy_2005_ land' is already removed in the Postgres table

Part I: Analyzing the 'Zoning Variance' data base

During the next few weeks, you'll be analyzing, cross-referencing, and mapping a dataset created by Clark Broida (MCP, 1987) containing some 1800 zoning variances that were requested for Boston properties during a two-year boom-period in the mid 1980s.  We will use these data in next Tuesdays Lab #4 and in Problem Set A. The SQL Notes section of the class web page contains more information about all the databases used in the class. 
• Here is the Zoning Variance data dictionary
• Here are SQL examples using zoning variances. 
• Note that fname + applicant = first name + last name ---> when owner is an individual
• But, when owner is an institution, 'applicant' is missing (blank) and fname = institution name
• All zoning variance database tables are accessible from your individual accounts in Postgres and are also in MS-Access database saved in class data locker as: 11.521_lab3.mdb

In Tuesday's Lab #4, we will map these zoning variances. Today, we will focus on (a) understanding and running basic queries of the zoning database tables, (b) pulling mapable data from MS-Access and Postgres into ArcGIS, and (c) using the ideas in the book chapter (about spelling 'errors' in the Boston parcel database) to accumulate and utilize knowledge about how to interpret ownership types among the owners of property seeking a zoning variance.

Often, the data that we wish to study cannot be linked to mappable objects in a straightforward (one-to-one) manner.  For example, we may wish to study those zoning variances involving vacant land under city control that are proposed for use as housing.  Figuring out which variances are controlled by the city is a database manipulation task in and of itself and understanding the neighborhoods proximate to the proposed site might require a side effort - using GIS - to tag each zoning variance with, say, the census tract that surrounds its location.   We want to help you understand - and gain some hands-on experience - with relational and spatial database management techniques that can facilitate such multi-stage analyses.  Moreover, we want to help you understand how the architecture of the city's information infrastructure can affect the types of analyses that are possible and the extent to which decentralized access to city data can be an effective empowerment tool.

Querying and mapping zoning variance data: (we will do this in next week's lab)

1. Use database tools to prep data, then pull into ArcGIS
   -- Pull tables from Postgres (or Access) into ArcGIS
   -- Convert XY coordinates in a table to a pin map
   -- Use Mass towns as a background - see matowns00.shp shapefile in class data locker
   -- Spatial joins of zoning variance points with polygon layers (1990 census tracts, msa5_tr90.shp)
   -- Use mass_water.shp to shade water bodies blue and K:\data\bosparcels05.shp for Boston parcels
   -- Be aware that all layers are in Mass State Plane NAD83 meters except Boston parcels (Parcels05) which are in Mass State Plane NAD83 feet.
   -- Google Earth mashups with KML output from ArcGIS
2. Preparing ZONING data for mapping
   -- getting -X,Y (longitude/latitude) values from Postgres tables.
   -- In ArcGIS, use Tools/Add-XY-data to convert lat/lon to points
3. Analyzing zoning variance patterns - join/query tables from Postgres, MS-Access, or ArcGIS sides (depending on size, famililarity, complexity)
4. Google Earth mashup - coordinate system issues and KMZ files

Part II: Distributed Access to Parcel-Level City Databases (Using lookup tables to merge local data with large, 'read-only' official databases).

Most of today's lecture focuses on the Book Chapter reading for today - http://mit.edu/11.521/papers/techcity_7ferreira.pdf which is Chapter 7 of "High Technology and Low-Income Communities:  Prospects for the Positive Use of Advanced Information Technology," edited by Donald A. Schön, Bish Sanyal, and William J. Mitchell, MIT Press, 1997

This chapter explains some of the complexities and technical strategies involved in trying to empower communities by providing local access to parcel-level city databases. We would like to facilitate distributed access to centrally maintained databases - while allowing some capacity on the part of end-users to make changes in the 'official' data. Our example uses parcel-level databases of land use and ownership - a very detailed data layer that has recently become much more standardized and accessible.  To emphasize how it is often necessary to do extensive data processing before such data can be utilized effectively, we consider the problem of addressing  the 'spelling errors' in owner names that complicate efforts to determine ownership patterns from typical parcel databases.  For example, there are at least 17 different spellings of the Boston Redevelopment Authority in the 'owner' field of the official parcel database for Boston. 

Much of the lecture explains how one can use local 'lookup' tables and SQL 'update' queries to correct the spelling errors (and construct other ownership groupings).  After illustrating the techniques, we discus how these methods can provide a useful alternative to 'top-down' and 'bottom up' strategies for fixing the spelling errors.  In particular, they provide a 'middle-out' alternative that allows decentralized accumulation of useful knowledge that can be readily linked to official datasets outside the user's control.  Here are some of the queries that we will run in class and the powerpoint slides that outline the methods and ideas in the book chapter.  

In Problem Set B, you will use similar methods to categorize the owners of the East Boston properties.  A set of notes is available that uses the Boston zoning variance database to illustrate the ideas and relevant queries. It is available in the SQL Notes section of the class web page as this link: Grouping zoning applicants via 'lookup' tables. In addition, the Boston parcel database (for 1996) is available in the class data locker as a 67 MB file formatted for MS-Access: boston_parcels96.mdb.

 

Tips and Tricks working with East Boston parcel data

• MS-Access query building
  • Examine 'q_condosum' query
    • Choose "View/Totals" to toggle 'group by' row in 'Design' mode
    • Use of aggregate functions or 'expression' or 'where' to summarize/prune groups
  • Data type issues when matching/transferring numbers and strings
  • Use of 'Expression Builder' and SQL-view for complex queries
• Pulling data into MS-Excel
  • For older versions of MS-Excel (earlier than the 2007 version)
    • Use Data/Import-External-Data/New-Database -Query
    • Must have MS-Query installed within Excel (not done by default)
    • MS-Query go-between remains hidden if all goes well but has limitations dBase (dbf) types of limitations with data types, name length, etc.
    • Wizard can help with data transformation, filtering, etc.
  • For recent versions of MS-Excel (ver. 2007+ in the computing labs)
    • From the 'get external data' portion of the 'Data' menu, choose 'from other sources' and then pick 'from data connection wizzard'
    • Next choose the same Postgres database connection and login that you have used to connect to Postgres from MS-Access
  • Be careful about saving your account/password in an Excel (xls or xlsx) document - visible as plain text within some saved documents and ODBC configuration files
    • No need to save account/password since you will be prompted for them at run time
  • Illustrate with SQL query: create this view in Postgres and pull into Excel

    create view v_ebosvalue as  
    select pid, 1.0*to_number(lotsize) as lotsize,
           1.0*to_number(living_area) as living_area,
           1.0*to_number(fy2005_land) as fy2005_land
      from bos05eb
     where lotsize > 0 and living_area > 0   
       and fy2005_land > 0;

• Learning curve

• Don't spend hours on mechanical stumbling blocks
  • After getting a question answered, reflect on why you needed help, what vocabulary was needed to access help, and what the answer tells you about design and framework built into the software
  • There are multiple ways to answer most of our questions. You can build queries in ArcGIS, MS-Access (with the design or SQL interface), or Postgres. Since queries in ArcGIS are hard to document and save, we prefer the other two - but still multiple paths
  • Start excercises and homework early enough to leave room for question-and-answer dialog
  • Send email to 11.521staff@mit.edu (or just jf@mit.edu)
  • Send email to 11.521@mit.edu (the whole class) for general questions
  • We can use the class Stellar website for a Wiki discussion forum if you find that to be useful
• Work through the learning curve using the smaller datasets and simpler queries
  • 'Toy' parcel dataset is visible on one screen - easy to see what the answer should be
• Zoning database is bigger but still quite manageable - 1800 rows and dozens of columns with lookup tables
• East Boston data has thousands of rows and two key tables with a few complexities
• Project in second half of semester will involve mixing, matching, and sharing larger datasets for metro Boston & Singapore
• My office hours are Tuesday 2:00 - 3 and Thursday 10:30 - noon, but my door is often open for a quick question
• Extra lab time: Friday, 12:15-2:15 in 9-554