Querying Oracle from Client Apps

11.521	Spatial Database Management and Advanced Geographic Information Systems
11.523	Fundamentals of Spatial Database Management

Review of SQL Homework and GIS-RDBMS Integration

Lab #3 + Problem Set-A (lab #3 part due today on Stellar, rest due next Thursday)
In-Lab Exam - next Tuesday (March 9, 3:00 - 5:00 in Room 37-312)
- Practice exam now online (from last year)
Lab #5 (raster interpolation of East Boston land value) - due Tuesday, March 16

Prep for Tuesday's in-lab exam
- Review of Labs 1-4 and SQL examples
- Use of URISA proceedings database to illustrate SQL queries
Demo/discuss parts of Lab #5
- East Boston grid cells and interpolating land value
- You will need to finish the 'dissolve' part of Problem Set A
- Model Builder

Last year's exam is now online (with answers)
- Open-book, open notes (just no conversation or emailing during test)
- Content that is covered: Labs #1-4 (but not the problem set A part with 'updating' of lookup tables and 'dissolving' shapefiles)
Finish discussion of advanced SQL notes from previous lecture
- Null values and use of DISTINCT
- Inner and Outer Joins with group by
- Views, synonyms, data dictionary, insert/delete/update
- Other topics (not on the test): transaction control and data definition language
Explain URISA proceedings database
- Not on this year's exam but could be useful to practice SQL queries
- URISA Schema and sample queries now linked to SQL Notes page
  - 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
  - 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)
    - MATCH table associates papers with keywords
    - MATCH is an extra table needed to handle a many to many relationship between papers and keywords
  - MS-Access URISA database is in class locker K:\data\urisa\11.521_urisa.mdb

Create East Boston grid cells
- Be careful about coordinate system (Mass State Plane NAD83 - meters)
Interpolate housing value by averaging 12 closest sales
- Join Oracle table of sales prices to 1996 East Boston parcel centroids
- Centroids and grid cells must use the same coordinate system
Performance issues
- With a few thousand rows in the tables, joining an Oracle table to a shapefile that resides on a network drive can lead to performance problems
- Be sure to set the 'working drive' to a truly local drive folder (*not* the desktop on WinAthena machines) when doing spatial analyst operations
Set the Mask to cut out everything except the East Boston blocks
Use Model Builder to 'automate' these steps