Administrative

• Due dates for current assignments
  • Problem Set A: (SQL) due today
  • Problem Set B (lookup table grouping) due Thursday, March 15
  • Lab #5: Part I (raster analysis) due next Tuesday, March 13); Part II (model builder) due Tuesday, April 3
  • In-lab test: Tuesday, March 20
  • Problem Set B (database design): due Friday, March 23 (last homework for 11.523)
• SQL review and extra Lab sessions
  
  • Friday, 12:15 - 2:15 in Room 9-554 this week and next week
  • SQL queries from lab #1 and #2 (comments and tips) are in solutions posted to Stellar
  • New set of SQL examples (URISA proceedings database) will be discussed today
• Exams from the last two year (with answers) are online
  • Save one exam to take later as a practice test
  • Don't look at answers until you've worked it out on your own!

Today:

• Continue discussion of advanced SQL notes and prep for in-lab exam
  • Advanced SQL notes from last lecture
  • Review of SQL examples
  • MAIN TOPIC: Use of URISA proceedings database to illustrate SQL queries and relational thinking
• Demo/discuss parts of Labs #5
  • East Boston grid cells and interpolating land value
  • If we have time,
  • Illustrate use of 'dissolve' to build East Boston block shapefile
  • Model Builder

Tips: ArcGIS usability and Model Builder

• ArcToolbox and Model Builder
  • Where is my customized ArcToolbox?
    • You have a default Toolbox.tbx on your H: drive here: (shown for userid=jf)
      \\WIN.MIT.EDU\dfs\profiles\j\jf\WinData\Documents\ArcGIS (on the H: drive) which shows up as Documents\ArcGIS on the WinAthena machines. 
    • The 'workspace' and 'scratch workspace' settings in the environment point to a default geodatabase that is also on drive H:.  But, you may have already reset these to be C:\temp\scratch.gdb as explained in Lab #1
  • Specifying new toolboxes and model-builder locations
    • Your MB (modelbuilder) models can be saved elsewhere, but it is awkard, because there is no save or ‘save as’ menu.  Instead, you can go to ArcCatalog (or the mini-ArcCatalog pane within ArcMap), and right-click on a folder or on a geodatabase to create a new toolbox, and then click again on that entry to create a new model.  That way, the models can live and travel within a folder (or a GDB) and you can reference those models with local/writable paths.
    • You can use either a folder or a geodatabase for workspace and scratch locations.  If you use a folder, then you get shape files and grids by default, and the file naming restrictions and database column name restrictions of db4 [i.e., 8-character file names and 10-character column names, etc.).  If you use a file geodatabase, you get a slight performance increase and naming convenience – but more risk of false record locking messages. Setting the default workspace and scratch spaces to local drives is a big deal performance-wise on slow-network connections. 
    • Some screen shots (using ArcGIS ver. 10) illustrating the creation of a custom Toolbox on CRON machines: http://mit.edu/11.188/www/recitation/model_builder_notes.pdf
    
    
• Another performance boost comes from eliminating unneeded ArcCatalog folder mounts - especially connections to network locations.   Every time you open a file dialog window, ArcMap scans all of the top-level folders.  When they are remote or big, the lag can be painful.  Supposedly, you can also speed things up by limiting the file types that are scanned (if you have no need to scan SDE database engines or other specific file types).

Finish discussion of advanced SQL notes and prep for in-lab exam

• Prep for in-lab exam on Tuesday, March 20
  
  • Exams from the last two years are now online (with answers)
• Exam is open-book, open notes (just no conversation or emailing/texting/... during test)
• Content that is covered: Labs #1-4 (but *not* problem set B with 'updating' of lookup tables and not model builder) and not database design (Problem set C)
• Review discussion of advanced SQL notes from previous lecture
  • Null values and use of DISTINCT
  • Inner and Outer Joins with group by
  • Views, data dictionary, insert/delete/update commands
  • Other topics (not on the test): transaction control and data definition language
• Thinking relationally: URISA proceedings database of papers, authors, and keywords
  • Design relational tables to store proceedings information about papers, authors, and keywords
    • Database design (problem set C) is not on this year's exam but the URISA database is useful to practice SQL queries and improve our 'relational thinking'

    
    
• How do we handle these issues
• Authors can write more than one paper (one to many relation)
• Papers can have multiple authors (one to many relation)
• Papers can be tagged with up to 5 keywords (many to many relation)

 

• Tables: TITLES, AUTHORS, KEYWORDS, MATCH
• Papers are in TITLES table with primary key = PAPER = yyvppp (year/volume/page of proceedings)
• Keywords are in KEYWORDS table with primary key = CODE
• Authors are in AUTHORS with primary key = LASTNAME || FNAMEMI
• MATCH table associates papers with keywords
• MATCH is an extra table needed to handle a many to many relationship between papers and keywords



• URISA Schema and sample queries now linked to SQL Notes page
• MS-Access URISA database is in class locker K:\data\urisa\11.521_urisa.mdb
• Postgres tables are in 'class521' within the schema 'urisa'
• Review some of the URISA  queries to hone our SQL skill

Demo/discuss parts of Lab #5

• Create East Boston grid cells
  • Be careful about coordinate system (Mass State Plane NAD83 - meters)
• Interpolate housing value by averaging 12 closest sales
  • Join Postgres table of sales prices to 1996 East Boston parcel centroids
  • Centroids and grid cells must use the same coordinate system
  • Do interpolation only for parcels that had at least one sale (but interpolate for all grid cells)
    • otherwise, you will include 'zero' prices in the averaging computation
    • you could restrict computation only to triple-deckers to avoid housing type bias
    • then you could compute value per-sq-ft (of land or usable living space??) and tag average for blocks (or block face)
• Performance issues
  • With a few thousand rows in the tables, joining a Postgres table to a shapefile that resides on a network drive can lead to performance problems
  • Once you are satisfied with the table you pull from Postgres, you may want to save the joined shapefile locally and work with that version
  • Performance can also be helped by setting the 'working drive' and 'scratch drive' to a truly local drive folder (*not* the desktop on WinAthena machines) when doing spatial analyst operations (that require scratch space for processing)
• You can use the East Boston part of the 'bosblocks05 or bosblocks96 shapefiles in the data locker to mask off everything except the East Boston blocks but the voids in the street can result in having 'holes' in your estimation when a grid cell has its center in the street 'void'. Alternatively, use the East Boston planning district boundary as a better mask that will not have any 'holes' within East Boston.
• Model Builder could 'automate' all these steps to compute a Boston housing value surface - but think of how large the model builder diagram would be.