---

Part A: Accessing Google APIs from R

• Accessing Google APIs from R: ./GoogleAPIs.html
• Zip-file with MBTA example: http://mit.edu/11.s946/www/advanced1/MBTA_NODE.zip

---

Part B-1: PostGIS extensions to PostgreSQL

PostGIS adds user-defined objects to PostgreSQL that facilitate geospatial processing

• Bundled with PostgreSQL when downloaded from postgres.org
• Enable PostGIS within any database by adding it as a new 'Extension'
• Can also download separate PostGIS package  from postgis.net
• PostGIS adds functions, tables and views to Postgres
• Look at 1000+ functions: st_distance, st_intersect, st_within, st_buffer, etc.
• View table that catalogs coordinate systems: public.spatial_ref_system
• Geometry information is saved in a view: public.geometry_columns
• What can you do with PostGIS
• Compute spatial indices to speed up processing by 'sorting' in 2D and 3D
• Include spatial operators in the 'where' clause of SQL select statements

• Example - which grid cells are in TAZ labeled as 'ready for more development' (codas=1)
• Suppose we did not have ct_grid table that links grid cell, town, TAZ, etc.
• Use PostGIS spatial operators to find the TAZ that each grid cell is within
• First, pick a few grid cells to test out our reasoning and syntax:

select * from vmtdata.v_vmt_join4
 where no_vin > 500;
-- 19 rows

• Next, use st_within() function to tag grid cells with the TAZ that contains them

select t.taz, t.codas, t.community_, t.town_name, 
       g.g250m_id, no_vin, vmt_vin, vmt_hh, vmt_pop
  from vmtdata.g_bos_vmt_grid g, vmtdata.g_taz2727_codas t
 where g.no_vin >= 500
   and st_within(g.geom, t.geom);
-- works but only 4 rows in output!! 

• Aha, some grid cells may not be entirely within a single TAZ
• Redo query using the centroid of the grid cells
• Also, add geometry column (g.geom) to facilitate mapping later

create table jf_grid19_taz as
select t.taz, t.codas, t.community_, t.town_name, 
       g.g250m_id, no_vin, vmt_vin, vmt_hh, vmt_pop, g.geom
  from vmtdata.g_bos_vmt_grid g, vmtdata.g_taz2727_codas t
 where g.no_vin >= 500
   and st_within(st_centroid(g.geom), t.geom);
-- okay, get all 19 rows 

• Use QGIS to read this table and compare with TAZ and grid cell geometries

---

Part B-2: Auto Insurance Per-Mile Risk Analysis

Does auto accident risk vary in proportion to miles driven?

• Auto insurance cost varies by age, territory, etc. but not so much by mileage
• but, if you drive twice the mileage should not the risk be twice as high?
• age/residence are relatively fixed, but a per-mile charge could have more incentive

• VMT data tags each vehicle VIN with mileage
• Need to cross-reference with insurance claims to get risk
• Tricky since
• Inspection year and policy year differ for each car
• Vehicles change owners, territory, etc.
  
• Millions of vehicle and hundreds of millions of records

• Analysis by MIT and Conservation Law Foundation (CLF) studies
• J. Ferreira and E. Minikel, "Measuring Per Mile Risk for Pay-As-You-Drive Automobile Insurance,"
  Transportation Research Record, No. 2297, (2012), pp. 97–103,
  https://DOI.org/10.3141/2297-12
• J. Ferreira and E. Minikel, "Pay-as-you-drive Auto Insurance in Massachusetts: a Risk Assessment and Report on Consumer, Industry, and Environmental Benefits," Conservation Law Foundation, Nov. 2010,
  http://mit.edu/jf/www/payd/PAYD_CLF_Study_Nov2010.pdf
• Public release of analytic dataset: http://mit.edu/jf/www/payd

• Examine key figures and charts:
• Entity-relationship Diagram for PAYD Analytic Dataset with 700k claims and 4 million vehicle records

• Histogram of annual mileage for vehicles in the study

• Poisson regression of 'pure premium' by mileage - all vehicles

• Variation in annual mileage by driver class and territory

• Poisson regression of 'pure premium' by mileage - adults in territory 3

• Conclusions
• Strong per-mile effect on risk
• Some diminishing returns for higher mileage
• Stratifying by driver class and territory enhances the effectiveness of mileage
• Equity and environmental impacts
• Little differential impact by income and territory (shift in cost is from low mileage to high mileage vehicles)
• 5-9% reduction in GHG emissions if insurance were priced per-mile