April, 2017

Good Schools and Affordable Homes

This month’s map of the month was inspired by a recent article published by in the New York Times. The original article is linked below. Additionally, a link to Asana is provided to track the project’s status.

NYT Original Article

Asana Project

Map of the Month Collateral

All Map of the Month products, including illustrator, image, presentation, and write up files can be found in the MOTM_Uploads - 2017_04 directory on Box.

Data Sources

San Francisco Metro Area chart (NYT)

Input

• Grade, Commute, Home Price Data - from the HTML source of the NYT article above.

• Stanford School District Data. The same data also in this FileGDB as stanford_schools_data.

Note that we used the Stanford School geometries rather than ESRI geometries because there were at least 2x more matches with the data. Apparently the Stanford School data district_nces code as used in NYT does not link automagically with the ESRI geometries. Stanford seems to have processed the geometries into more detailed subsets.

Output

• Above inputs are joined together and named nyt_join_2013_unified in the FileGDB here NOTE-THE EPSG of that data is 3857.

headers:

• gsmean_poo = e.g. 1 grade ahead
• district_n = school district
• mean_commu = mean commute time

These are basically all the variables of interest.

NYT Primary Data Sources

Stanford Schools Project

Input

• Stanford School District Data. The same data also in this FileGDB as stanford_schools_data.

• schools_ca.csv From this csv from the Stanford Schools Data Archive. See this link for the metadata.

Output

• AGOL Feature Class

Also copied in this GDB as stanford_schools_data

Processing Script

Redfin/Places

Input

• Census Places geometries (ESRI) on AGOL here The same data are also subset in this FileGDB as esri_places_geometries_bay_area]

• Redfin Data Export as subset in this CSV to Places in california in February, 2017. The source for this subset is this full CSV here as exported from the Redfin Data Center TDE File.

Output

• AGOL Feature Class

Processing Script

Commute Data

Input

• Vital Signs API

Should be joined to:

• Census Places geometries (ESRI) on AGOL here. The same data are also subset in this FileGDB as esri_places_geometries_bay_area]

Output

Analysis

The output table nyt_join_2013_unified referenced above in the data sources section was analyzed using tsql.

School districts with the following characteristics were selected:

• Median per sf home price below Bay Area Average
• School district performance above Bay Area Average
• Mean commute less than 30 minutes

Script:

2017_04_motm_analysis.sql