---

1. Dissertation: Mi Diao (2010), "Sustainable Metropolitan Growth Strategies:Exploring the Role of the Built Environment" in MIT's DSpace: https://dspace.mit.edu/handle/1721.1/62125, pages 1-55.
• Use Thesis Defense slides (1-26) to present and discuss Chapters 2 and 3:
• diaomi_dissertation_defense_v6.pdf
• Chapter 2: Measuring built environment (BE)
• Chapter 3: Modeling VMT = F(BE,VMT)


2. Use SQL, R, and QGIS to examine data and construct indicators
• Database tables
• Browser and pgAdmin-4 access to Postgres
• Database / Schema / Table hierarchy
• Logins, Roles, Privileges to access tables
• SQL queries
• Pull BE factors from one table and VMT variables from another
• Join built_environment_250m and vmt_250m tables
• Include constraint!!  ...where b.g250m_id = v.g250m_id
• 6 rows in output in 30 milliseconds
• without constraint, 117196 x 60895 = 7.1 Billion rows and forever to finish!!
• ...Close session and hope Postgres gives up and cleans up...

select b.g250m_id, b.fac1_nw, b.fac2_connectivity,
             b.fac3_transit, b.fac4_auto, b.fac5_walk,
             v.hshld_2000, v.no_vin, v.vmt_vin, v.vmt_pop, v.vmt_hh
        from vmtdata.built_environment_250m b, vmtdata.vmt_250m v
       where b.g250m_id=v.g250m_id
         and v.no_vin >1000;

• Save query as View to facilitate reuse (by R and QGIS) without needing to create a new table:

create view public.v_jf_be_thin as (select b.250m_id, b.fac1_nw, ...);

3. Use R to generate summary statistics for our new view
• Use Rstudio to explore Postgres datasets and rebuild Mi Diao's indicators and models
• Pull Postgres tables into Rstudio
• Connect R to Postgres using RPostgreSQL library (in lab #1 next week)

UAchannel <- dbConnect("PostgreSQL", dbname = "class946",
host = "cronpgsql.mit.edu", port = 5432, user = "myUserName",
password = "myPassword")

• Grab rows from our view v_jf_be_thin

bedata <- dbGetQuery(UAchannel, "SELECT * FROM v_jf_be_thin")

• Examine summary statistics for columns in this view

summary(bedata)

Before Lab #1 on Tuesday (in 9-554 computing lab)

1. Read rest of Chapter 3 in Prof. Mi Diao's dissertation (through page 55)
2. Skim additional readings (below) on VMT and BE
3. Begin getting comfortable with datasets and software
• Do 'warmup' Lab: http://web.mit.edu/11.s946/www/lab_warmup/
• Login to Postgres on cronpgsql.mit.edu and change temporary password to a new strong password without spaces or special characters
• via phppgadmin: click on 'PostgreSQL' in table of contents, then 'Account' tab to view your account and see 'change password' option
• via pgAdmin4: open 'Login/Group Roles' in table of contents, then right-click your account and choose 'Properties', and finally click 'Definition' Tab to see 'password' box in which to enter your new password
• Try using 'startup' QGIS project in class locker, but DO NOT save name/password in project file
4. Use Friday's lab (from 2:30 - 4:30 in 9-554) to finish getting comfortable
5. When connecting to the Postgres server from off campus, you need a VPN (virtual private network) connection.

---

Readings

These additional readings are available on Stellar (http://stellar.mit.edu):

1. PDF of slides from Mi Diao's dissertation defense (Aug. 2010) - diaomi_dissertation_defense_v6.pdf
2. Cervero & Kockelman paper on 3Ds: "Travel Demand and the 3Ds: Density, Diversity, and Design," - Cervero_1997.pdf
3. Link to review of VMT/Land-use/Transportation relationships (2012) - VMT_readings.txt
4. Journal article on Chapter 3: M. Diao and J. Ferreira, "Vehicle miles traveled and the built environment:
   evidence from vehicle safety inspection data," EPA, v.46 (2014), doi:10.1068/a140039p.

---

Created by Joe Ferreira (2017); Last modified: October 18, 2017 [jf]

Back to DUSP | CRON | MIT | MiniClass Homepage