11.521/523 - Lab #2 In-Class Notes:
----------------------------------

TODAY WebEx link to share my screen: (optional) <br>https://mit.webex.com/join/jf<br>
Overview of all 6 Labs before Spring Break:
-------------------------------------------
#1 - [Last week] Intro to SQL and data exchange
with PostgreSQL, MS-Access, Excel, ArcGIS

#2 - Exploring Land Use Patterns in East Boston
Relational Queries and Map-Database-Spreadsheet Interaction
Also, practice MS-Access and phpPgAdmin query construction 
(and test SQL compatibility between PostgreSQL and MS-Access)

#3 - Accumulating and Sharing Local Knowledge
Relational lookup tables to correct mis-spellings and share
changes to official 'read only' datasets.

#4 - Mapping Boston Zoning Variances and Property Sales
Linking ArcMap with PostgreSQL and MS-Access query results

#5 - Exploring Land Value Patterns in East Boston
More complex queries and raster analyses using Model Builder

#6 - Modeling Metro Development using ArcGIS plug-ins (Community Viz)
and map publishing using ArcOnline and OpenLayers map publishing tools. 

-------------------------------------
LAB #2: Today's lab exercise (due on Tue. Feb. 20)
(but Problem Set A will also be available later this week)

 PART 1: Practice SQL queries with MS-Access and PostgreSQL
-- All 7 'toy' parcel tables are in PostgreSQL with same name
as in MS-Access
-- For PostgreSQL access to the 'toyparcels' data, use the 'sqlguest' account or your individual account
-- Start with MS-Access (lab-1 examples) or PostgreSQL (SQL examples 
in the 'SQL help' section of the class SQLnotes) and switch 
back and forth<br>-- SOFTWARE SETUP: text editor + MS-Access + lab-in-browser + pgAdminIII (optional)
-- Be sure to understand the syntax for <br>---- JOINING two (or more)tables <br>---- aggregating rows based on values in one (or more) columns <br>(via a 'GROUP BY' expression in Postgres or a TOTALS row in MS-Access).
-- When constructing SQL queries, pay attention to how missing 
values are handled and avoid dividing by zero.

-- If you are *not* familiar with MS-Access or SQL, Part I can
take a while.  If so, start the mapping in Part II before 
finishing all the queries in Part I.

PART I LAB DEMO:
---------------
-- Postgres tricks and tips: editing
-- MS-Access: SQL view and PostgreSQL comparison
-- JOIN parcels and tax tables
-- GROUP BY to get counts and sums
-- saving queries vs. making new tables

How many parcels have paid taxes?
How many commercial properties have paid taxes?
How many different owners have paid taxes?
How much tax has each owner paid?

[Beware: in the 'real world' tax is a transaction generally
paid every year.  But, our toy database only considers
one-time tax payments.]

PART 2: Mapping East Boston Land Use 
------------------------------------
-- You need to manipulate the data in MS-Access first
-- This part uses only ArcMap and MS-Access
-- Handling complexities - UNDERSTAND THE 'GOTCHAS'
(1) one-to-many issues with condos vs. homes and apartments
(2) dBase filename constraints
(3) Data type issues when matching land use categories and shadings 
(4) Spaces buried in column names

PART 3: 3D Mapping of East Boston using ArcScene <br>-----------------------------------
-- We did most of this in Lab #1
-- no need to turn in anything else
-- we will have more time for 3D visualizing later
--- Get familiar with ArcScene 3D interface 
--- and copy/pasting shapefiles from ArcMap

PART 4: Excel exploration of East Boston parcel data <br>--------------------------
-- OPTIONAL - plot a scattergram of land value per square
foot by lotsize for parcels with &amp;lt;= 12000 square feet (a quarter acre).
-- We will do more with Excel/ArcGIS/RDBMS later

-------------------------------------
SQL Help, tools, and tutorials

There are many useful SQL aids on the Web.  
(1) Google searches can assist with SQL syntax, PostgreSQL error messages, etc. 
(2) SQLzoo.net is an example of simple interactive SQL tutorials:
http://sqlzoo.net/  Try the 'quick reference' SQL examples on the left
but beware of slight variations in SQL syntax (e.g, how tables are joined)
(3) SQuirreL SQL is a popular open source client that provides a graphical
interface alternative to the command-line in Postgres.
http://squirrel-sql.sourceforge.net

&lt;/pre&gt;
  &lt;/body&gt;
&lt;/html&gt;
</pre>
  </body>
</html>