Administrative

• Turn in all exercises and homework on our Stellar website: http://stellar.mit.edu/S/course/11/sp10/11.521
• Lab #3 (lab exercise part) - turn in 4 questions (save II-1 for part of Problem set A)
• Lab #4 (mapping zoning variance lab) - due Tuesday, March 2
• In-lab test - still on Tuesday, March 9
• Problem Set-A (rest of lab #3) - postponed due date: now due Thursday, March 11 (after the test)

Today

• Keep practicing relational thinking
  • Subquery example from Lab #2
  • Finding bad matches in EB assessing and land use table
• Lab #4 - Mapping zoning variances
  • Geocoding location
  • Creating pin-map of locations (data conversion, coordinate system, pin-map from data table)
  • Symbolization (by approval status, land use, etc.)
  • Spatial join (point-in-polygon operation - tag zoning variance with census info about neighborhood)
• Lab #3 - Evolution of City Databases
  • History of zoning variance database
  • Personal vs. institutional database integration
  • Using lookup tables to accumulate local knowledge regarding read-only data
• If there is time: Review of Raster modeling (for Lab #5): lecture notes from 11.520 last Fall

Practice thinking relationally

What SQL query answers the following questions:

• Does every Assessing record in BOS05EB have a corresponding 'ground' parcel in ebos_parcels05.shp?
• Does every ground parcel have an Assessing record in BOS05EB?
• Is every property type code in BOS05EB explained in the MASS_LANDUSE lookup table?
• Which land uses in MASS_LANDUSE are not found within East Boston?

In lab#2, when you ran q_eb05new to join your expanded t_noncondo table with MASS_LANDUSE, you may have noticed that q_eb05new produced a table with one fewer record than t_noncondo. Which record dropped out?

SELECT bos05eb.PTYPE 
  FROM bos05eb 
 WHERE bos05eb.ptype not in (select stclass_txt from mass_landuse); 

Or, equivalently:

SELECT bos05eb.PTYPE 
  FROM bos05eb
 WHERE NOT EXISTS(SELECT NULL from mass_landuse m
                   WHERE bos05eb.ptype = m.stclass_txt); 

Let's figure out how to do the same query in MS-Access - by pasting the SQL into MS-Access ...

Lab #4 - Mapping zoning variances

• Geocoding location
  • Already done for you for zoning variances (table LONLAT4)
  • See ArcGIS help as well as Yahoo, Google, etc. of you want to convert addresses to XY locations
  • Check out free geocoding at: http://geocoder.us/ (and think about one-at-a-time vs. batch and how to handle ambiquity and quality of match)
• Creating pin-map of locations (data conversion, coordinate system, pin-map from data table)
  • Bring in X-Y expressed as longitude and lattitude (and worry about negative values and integer truncation)
  • Let ArcGIS convert on the fly to Mass State Plane NAD83 meters
• Symbolization
  • Use different SYMBOL based on attribute value (approval status, land use, etc.)
  • Multiple symbols within one layer or multiple layers with different field definitions
  • Saving as 'layer' vs. saving shapefiles
• Spatial join (point-in-polygon operation)
  • Which 1990 census tract contains each zoning variance - need to join by location
  • Compute based on geometry (spatial join) but save results in attribute table (tag zoning variance with census info about neighborhood)
• Zoning applicant: FNAME stands for First Name and APPLICANT contains Last Name or Institutional/Corporate name
• Google mashup - Be careful about coordinate system when saving KMZ files.
• For SQL*Plus, these window formatting commands are handy - they will run at startup if in 'login.sql' in your working directory

  set linesize 100
  set pagesize 40
  set pause on
  set pause more...
  select * from cat;

Lab #3: Evolution of City Databases

• History of zoning variance database
• Personal vs. institutional database integration - let's review the last two links in this table

Lab #3 lookup example: spelling corrections for Boston parcel ownership

Build lookup table for Boston parcel ownership

• Book Chapter describing 'lookup table' example: http://mit.edu/11.521/papers/techcity_7ferreira.pdf
• Queries to produce results discussed in book chapter: lookup_inclass_01feb22.html

Using lookup tables to merge local data with large, 'read-only' official databases

The book 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. 

In parts of Lab #3 and Problem Set A, we use the methods discussed in the book chapter (i.e. local 'lookup' tables and SQL 'update' queries) to categorize the owners of the properties in the zoning variance database. After making sure we understand these 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 queries that can will run in class and the powerpoint slides that outline the methods and ideas in the book chapter.

A set of notes illustrating these types of queries using the zoning variance database is available within the SQL Notes section of the class web page at this link: Grouping zoning applicants via 'lookup' tables.

More Advanced SQL Query Construction Techniques.

Review: SELECT Statement Syntax

Basic Syntax :

  SELECT expr1, expr2, expr3, ...
    FROM object1, object2, ...
   WHERE conditions
GROUP BY expr4, expr5, expr6, ...
  HAVING conditions
ORDER BY expr7, expr8, expr9, ...

Ordering Rows Returned by a Query

The syntax looks like this:

    SELECT ...
    ORDER BY expr1 [ [ ASC | DESC ] , expr2 [ ASC | DESC ] ... ]

  SELECT parcelid, landuse, sqft
    FROM parcels
ORDER BY landuse ASC, sqft DESC;

  PARCELID LAN       SQFT
---------- --- ----------
        15          34800
        10          10900
         7 A        14000
        14 A        10000
        16 A         9600
        18 A         9500
        11 C       210000
         2 C       100000
        19 C        40000
         8 C        24800
         1 C        20000
         4 CL
         6 CM        2100
        20 E        50000
         5 E        25000
         9 R1        1800
        17 R1        1500
        13 R2
         3 R3        5500
        12 R3        5300

20 rows selected.

Order of Operations with 'AND' and 'OR'

We might (incorrectly) write a query like this:

  SELECT parcelid, landuse, sqft
    FROM parcels
   WHERE sqft = 5300
      OR sqft = 10000
     AND landuse = 'A'
ORDER BY landuse ASC, sqft DESC;

  PARCELID LAN       SQFT
---------- --- ----------
        14 A        10000
        12 R3        5300

  SELECT parcelid, landuse, sqft
    FROM parcels
   WHERE sqft = 5300
      OR (    sqft = 10000
          AND landuse = 'A')
ORDER BY landuse ASC, sqft DESC;

  PARCELID LAN       SQFT
---------- --- ----------
        14 A        10000
        12 R3        5300

  SELECT parcelid, landuse, sqft
    FROM parcels
   WHERE (   SQFT = 10000
          OR SQFT = 5300)
     AND LANDUSE = 'A'
ORDER BY landuse ASC, sqft DESC;

  PARCELID LAN       SQFT
---------- --- ----------
        14 A        10000

SELECT DISTINCT

Normally, a query may return duplicate rows. For example, if we query the FIRES table to list the parcels that had fires, we'll find that the parcels that had more than one fire (parcels 2 and 3) show up multiple times:

  SELECT parcelid
    FROM fires
ORDER BY parcelid;
 
  PARCELID
----------
         2
         2
         3
         3
         7
        20
 
6 rows selected.

  SELECT DISTINCT parcelid
    FROM fires
ORDER BY parcelid;
 
  PARCELID
----------
         2
         3
         7
        20
 
4 rows selected.

  SELECT DISTINCT p.onum, o.oname
    FROM parcels p, owners o
   WHERE p.onum = o.ownernum
ORDER BY p.onum;
 
     ONUM ONAME
---------- -----------------------
         9 PATRICK KING
        10 METHUINON  TRUST
        11 FERNANDO MARTINEZ
        18 JOHN MCCORMACK
        29 FRANK O'BRIEN
        32 GERALD RAPPAPORT
        38 BAY STATE, INC.
        55 THOMAS KELLIHER
        89 JOSEPH NOONAN
       100 MGH, INC.
       200 VANDELAY INDUSTRIES
 
11 rows selected.

  SELECT DISTINCT p.onum, DISTINCT o.oname
    FROM parcels p, owners o
   WHERE p.onum = o.ownernum
ORDER BY p.onum;

  SELECT COUNT(wpb) wpb_ct,
         COUNT(DISTINCT wpb) wpb_ct_distinct,
         COUNT(*) row_ct
    FROM parcels;
 
    WPB_CT WPB_CT_DISTINCT     ROW_CT
---------- --------------- ----------
        20              20         20
 
1 row selected.

Cartesian Products

SELECT p.parcelid, p.onum, o.ownernum, o.oname
  FROM parcels p, owners o;

There are only 20 rows in PARCEL and 10 rows in OWNERS, so what's going on? The problem here is that this query has no WHERE clause that specifies how the PARCEL table relates to the OWNERS table. Without this information, Oracle does not know how to match a row in PARCEL to a corresponding row in OWNERS. What does it do instead? It matches every row in PARCEL to every row in OWNERS. Hence, we end up with:

(20 rows in PARCEL) matched to (10 rows in OWNERS) = 20 x 10 = 200 rows returned

To fix this query, we need to specify how the owner numbers stored in the PARCEL table should be matched to owner numbers in the OWNERS table. In PARCEL, the owner numbers are stored in ONUM, while in OWNERS they are stored in OWNERNUM. Here is the repaired query with the appropriate join condition in the WHERE clause:

SELECT p.parcelid, p.onum, o.ownernum, o.oname
  FROM parcels p, owners o
 WHERE p.onum = o.ownernum;

This query returns 20 rows, which is definitely an improvement.

Note that a Cartesian product can easily be hidden in a query that requires multiple joins. Suppose we want to find all the papers, with associated keywords, written by authors with the last name WALKER. We could try this query:

COLUMN keyword FORMAT A20 TRUNC
COLUMN title   FORMAT A25 TRUNC

  SELECT a.lastname, a.fnamemi, k.keyword, t.title
    FROM keywords k, match m, titles t, authors a
   WHERE m.code = k.code
     AND t.paper = a.paper
     AND a.lastname = 'WALKER'
ORDER BY a.lastname, a.fnamemi, k.keyword, t.title;

This query returns a whopping 6174 rows! What's wrong? All of the tables appear to be involved in a join condition. The problem is that while the MATCH and KEYWORDS tables are tied together, and the TITLES and AUTHORS tables are linked to each other, nothing links these two sets of tables together. Adding another join condition fixes it:

  SELECT a.lastname, a.fnamemi, k.keyword, t.title
    FROM keywords k, match m, titles t, authors a
   WHERE m.code = k.code
     AND t.paper = a.paper
     AND m.paper = t.paper
     AND a.lastname = 'WALKER'
ORDER BY a.lastname, a.fnamemi, k.keyword, t.title;

This query returns a much more reasonable 14 rows. Note that we could have also specified "m.paper = a.paper" and it would have worked too. Why?

A Cartesian product can easily be hidden by a GROUP BY, since it will aggregate all the spurious rows, and you will not see the rows that made up the groups. Here's a variation on the earlier, broken example, now with a GROUP BY:

  SELECT a.lastname, a.fnamemi, count(k.keyword) keywords, count(t.title) titles
    FROM keywords k, match m, titles t, authors a
   WHERE m.code = k.code
     AND t.paper = a.paper
     AND a.lastname = 'WALKER'
GROUP BY a.lastname, a.fnamemi
ORDER BY a.lastname, a.fnamemi;

Because this query returns only counts, it's not obvious that the query is defective--unless you have an idea about what results are reasonable! Always scrutinize your results!

NULL Values

NULL is a special value that means "missing" or "unknown." It can be placed in a column of any type, unless the column has been declared NOT NULL, in which case NULL values are forbidden. A NULL is not the same as zero or any other value.

Special rules apply when NULL values are involved:

• A row containing a NULL value in a column will never match another row in a join, not even another row containing NULL in that same column.
• Remember that in logical conditions (e.g., col1 = col2, col3 > col4, col5 < 0), NULL does not equal NULL; logical expressions containing NULL will always evaluate to UNKNOWN, which is similar (but not identical) to FALSE.
• Never use NULL with one of the boolean operators (col1 = NULL, col2 <> NULL, col3 < NULL, col4 <= NULL, col5 > NULL, col6 >= NULL). All of these will probably not perform as intended. Always use the IS operator when testing for NULL (col7 IS NULL, col8 IS NOT NULL).
• Group functions on a column ignore NULL values.

For more information on NULL values and how Oracle treats them, consult the Oracle documentation. With luck, their link to the Oracle 8i documentation hasn't changed much and you can find the SQL*Plus documentation here: http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/index.htm (Note also the link to an explanation of oracle error messages).

Outer Joins

Take a look at this query:

  SELECT p.parcelid, f.fdate
    FROM parcels p, fires f
   WHERE p.parcelid = f.parcelid
ORDER BY p.parcelid, f.fdate;

The query above returned 6 rows, but there are 20 parcels. Fourteen parcels seem to be missing. Where did they go? The answer is that Oracle will only list the parcels that occur in both tables, PARCELS and FIRES.

How do we get around this problem if we want to see all 20 rows in the parcel table, whether they match a record in FIRES or not? The answer is an outer join. The standard join is also known as an "inner join," meaning that the default behavior of not matching NULL values occurs. In an outer join, we explicitly tell Oracle that we want it to display NULL values that would otherwise be excluded by adding the (+) outer join operator to each column in the WHERE clause where the additional NULLs should appear.

    SELECT p.parcelid, f.fdate
    FROM parcels p, fires f
   WHERE p.parcelid = f.parcelid (+)
ORDER BY p.parcelid, f.fdate;

    SELECT p.parcelid, f.fdate
    FROM parcels p, fires f
   WHERE p.parcelid (+) = f.parcelid
ORDER BY p.parcelid, f.fdate;

Inner vs. Outer Joins with GROUP BY

  SELECT p.parcelid, p.add1, p.add2, f.fdate
    FROM parcels p, fires f
   WHERE p.parcelid = f.parcelid
ORDER BY p.parcelid, f.fdate;

  SELECT p.parcelid, p.add1, p.add2, f.fdate
    FROM parcels p, fires f
   WHERE p.parcelid = f.parcelid (+)
ORDER BY p.parcelid, f.fdate;

  SELECT p.parcelid, COUNT(fdate) fire_count
    FROM parcels p, fires f
   WHERE p.parcelid = f.parcelid
GROUP BY P.PARCELID
ORDER BY COUNT(fdate) DESC, p.parcelid;

List the count of fires for all parcels, whether it experienced a fire or not (this requires an outer join with grouping):

  SELECT p.parcelid, COUNT(fdate) fire_count
    FROM parcels p, fires f
   WHERE p.parcelid = f.parcelid (+)
GROUP BY p.parcelid
ORDER BY COUNT(fdate) DESC, p.parcelid;

The query below runs but returns the wrong result--some parcels that had no fires show up with one fire in the count. Why?

  SELECT p.parcelid, COUNT(*) bogus_fire_count
    FROM parcels p, fires f
   WHERE p.parcelid = f.parcelid (+)
GROUP BY p.parcelid
ORDER BY COUNT(*) DESC, p.parcelid;

Views and Other Table-Like Database Objects

• Synonyms

  A synonym is simply a second name for an existing object. These are particularly convenient when a table is owned by another user (or, stated differently, stored in a different schema). You have been using synonyms all along for the objects in the PARCELS, URISA, and ZONING databases. To see the synonyms, use this query:

    SELECT synonym_name, table_owner, table_name
      FROM user_synonyms
  ORDER BY synonym_name;

• Views

CREATE VIEW parcel_owners
  AS SELECT p.parcelid, o.oname, P.ADD1, P.ADD2, P.ZIP
       FROM parcels p, owners o
      WHERE p.onum = o.ownernum(+);

CREATE VIEW owner_sqft
  AS SELECT o.oname, SUM(SQFT) TOTAL_SQFT
       FROM parcels p, owners o
      WHERE p.onum = o.ownernum
   GROUP BY o.oname;

Once the view is created, it can be treated for (almost) all intents and purposes as a true table. You can describe them to see their structure:

DESCRIBE parcel_owners

DESCRIBE owner_sqft

The Data Dictionary

SELECT * FROM cat;

SQL> DESCRIBE user_views

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 VIEW_NAME                                 NOT NULL VARCHAR2(30)
 TEXT_LENGTH                                        NUMBER
 TEXT                                               LONG
 TYPE_TEXT_LENGTH                                   NUMBER
 TYPE_TEXT                                          VARCHAR2(4000)
 OID_TEXT_LENGTH                                    NUMBER
 OID_TEXT                                           VARCHAR2(4000)
 VIEW_TYPE_OWNER                                    VARCHAR2(30)
 VIEW_TYPE                                          VARCHAR2(30)

SET LONG 5000
SELECT view_name, text
  FROM user_views
 WHERE view_name IN ('PARCEL_OWNERS', 'OWNER_SQFT');

Data Manipulation Language

• INSERT

• DELETE

• UPDATE

INSERT: Add Rows to a Table

General syntax:

INSERT INTO table1 (col1, col2, ...)
  VALUES (value1, value2, ...)

or

INSERT INTO table1 (col1, col2, ...)
  SELECT ...

Example: Add a row to the FIRES table

INSERT
  INTO fires (parcelid, fdate, ignfactor, estloss)
VALUES (12, '17-JAN-96', 2, 35000)

DELETE: Delete Rows from a Table

DELETE
  FROM table1
 WHERE conditions;  

DELETE
  FROM fires
 WHERE estloss < 50000;

UPDATE: Modify Data in a Table

General syntax:

UPDATE table1
   SET col1 = value1, col2 = value2, ...
 WHERE conditions;

or

UPDATE table1
   SET col1 =
       (SELECT ...)
 WHERE conditions;

Example: Change the building value of a particular parcel

UPDATE tax
   SET bldval = 200000
 WHERE parcelid = 11;

Transaction Control

• COMMIT: makes permanent all changes since the start of the session or the previous COMMIT
• ROLLBACK: reverses pending changes to the database
• SAVEPOINT: allows more refined control over COMMITs and ROLLBACKs
• SET TRANSACTION: allows more refined control over transaction progress

Normally, we are not particularly concerned about transaction control since we are focused on analyzing and cross-referencing existing databases to which we have read-only access and we are generating new data only in temporary tables or personal lookup tables that do not need to be shared. However, we will need to concern ourselves with COMMIT and ROLLBACK for exercises involving the use of data manipulation language terms such as UPDATE. If we run an UPDATE query that fails due to a SQL error, then any pending changes to the database tables that have occurred since the last COMMIT will be rolled back.

Data Definition Language

Create and Drop Objects

Create new objects

CREATE TABLE table1 ... ;

CREATE VIEW view1 ... ;

CREATE INDEX index1 ON table1 (col1, ... );

Drop objects permanently

DROP TABLE table1;

DROP VIEW view1;

DROP INDEX index1;

Modify existing objects

ALTER TABLE table1;

ALTER VIEW view1;

ALTER INDEX index1;
 

Access Privileges for Objects

Specific privileges on tables can be given to individual users

GRANT SELECT
   ON table1
   TO user1;

GRANT SELECT, INSERT, UPDATE (col1)
   ON table1
   TO user1
 WITH GRANT OPTION;

Privileges can be revoked

REVOKE ALL
    ON table1
  FROM user1;

More examples of advanced query construction

Examining Views and other system tables: Suppose we have taken advantage of Oracle to save complex queries as views that can be imported into ArcMap. How can we find the definition of a view?

• Info about VIEWS is in the system table ALL_VIEWS

describe all_views
set linesize 100 
column text format A40
      

select substr(view_name,1,12) as name, text_length, text from ALL_VIEWS where owner = 'JF';

• Set environment to read long text strings and list MADENSITY view:
  

  set long 685
  set linesize 180
  select view_name, text 
    from all_views 
   where owner = 'JF'
     and view_name = 'MADENSITY';

• This query computes the total area of each Mass town and then calculates population density

   select town_id, town, count(*) as parts, avg(pop90) as pop90, sum(area) as area,
          10000*(avg(pop90)/sum(area)) as density
     from matown2000
    group by town_id, town
    order by town;

• Now allow anyone to select from this view as v_madensity (instead of jf.madensity)

  grant select on madensity to public;
  create public synonym v_madensity for madensity

Computing the percentage of East Boston parcels that are triple-deckers (LU=R3)

• Numerator: create view v_num as (select count(LU) as R3_count from bos05eb where LU = 'R3');
• Denominator: create view v_denom as (select count(LU) as all_lu from bos05eb);
• Ratio: select 100 * r3_count / all_lu from v_num, v_denom;

Compute same percentage in a single SQL query:

select 100 * n.r3_count / d.all_lu 
  from (select count(LU) R3_count from bos05eb where LU = 'R3') n,
       (select count(LU) all_lu from bos05eb) d; 

 

Written February, 2001-2006 (Tom Grayson, Joe Ferreira), Last Modified 26 February 2009 (jf)