Google map overlays and the mortgage crisis

The meltdown on Wall Street, which has become issue No. 1 in the race for the White House, was fueled in part by lots of risky lending in Arizona.

I helped a colleague of mine examine millions of loan applications, data which came from the Federal Financial Institutions Examination Council. We took a look at three years’ worth of data, and found that many Census tracts in the Tucson and Phoenix metro areas had high rates of subprime mortgages.

One way I thought we could explain this to readers was to show them how widespread lending was in their neighborhood or different parts of the Tucson area. Using Google Maps, I made a searchable tool for people to do just that.

The process was fairly straightforward. Here’s how it came together:

  • In the mortgage data, the smallest unit of geographic specificity is the Census tract. By doing GROUP BY and sum() queries in SQL Server, I was able to pull out the dollar value of risky loans — those with a rate spread greater than 3 or 5 percent, depending on the loan — and total loans in each tract. 
  • I exported that data as DBF and then imported it into ArcView. Since I had a shapefile of Arizona’s Census tracts, I joined the DBF to those tracts. I also calculated the percent of risky to total loan value.
  • I exported that layer’s shapefile to a folder on my desktop, and imported it into Shp2kml, a free program that converts shapefiles to KML files, the latter that can be read by Google Earth and Google Maps.
  • I uploaded the newly created KML file to a Web server, and created a Google Maps API in a new, blank Web page. I then told the map to reference that KML file. Here’s an excerpt of the code:
       var gx = new GGeoXml("http://site-url/subprime.kml");

All that was left was to put the latitude/longitude of Tucson in to center the map on the city. 

The map eventually went with a larger story on lending. And those shapefiles created for the Google map also were used to create a graphic for the paper.