Administrative

• Lab #2 exercise due today (via Stellar)
• Lab #3 available today - using Boston Zoning Variance database (from 1980s) as well as the same East Boston data in Lab #2
  • Lab #3 - Five questions are due Thursday, Feb. 26
  • Problem Set-A - rest of exercise is called Problem Set A and is due Thursday, March 5

Today

• Tips and Tricks working with East Boston parcel data
  • MS-Access query building
  • Pulling data into MS-Excel
• Part I: Introduce 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.
• Problems and strategies for sharing data across agencies and organizations
• Solving the 'spelling error' problems for Boston's parcel database
• Ways of accumulating sharable knowledge over time

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 'CRL' database and login that you have used to connect to Oracle from MS-Access and ArcGIS
  • 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
  • Illustrate with SQL query from end of last weeks's lecture notes

    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 vocabular 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 Oracle. 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 for general questions
  • We can use the class Stellar websitet 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 was 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
• My office hours are Monday 2:30 - 4 and Thursday 10:30 - noon, but my door is often open for a quick question

Part I: Review of '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.  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 the 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 Oracle and are also in MS-Access database saved in class data locker: 11.521_lab3.mdb

In next week's Lab #4, we will map the zoning variances. Today, we will focus on understanding and running basic queries of the zoning databases plus ways of using the ideas in the book chapter that we read 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 sourrounds 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.

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 often it is 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 ovnership 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 parts of Lab #3 and Problem Set A, you will use similar methods to categorize the owners of the properties in the zoning variance database.  A set of notes about these queries is available in the SQL Notes section of the class web page as this link: Grouping zoning applicants via 'lookup' tables.