DanielJWood

Putting Colleges on the Map

MapBox's OSM Sprint comes to a Campus near you

In recent weeks, the MapBox team has participated in a mapping sprint to improve areas of the world that lack coverage in OpenSteetMap. Since starting our mapping sprint, we have traced streets, buildings and parks in highly populated, under-mapped areas of the world like Mexico, Brazil, Bangkok and drought stricken West Africa

Our first major mapping push in the US has been to add building footprints to college campuses in OpenStreetMap. We prioritized building footprints because that's the place where students check in the most on Foursquare, which now uses MapBox Streets. But where to find colleges that need tracing? We figured we should find to schools where students' spend more

DanielJWood

1. Get your DMA data into an sqlite file. This sqlite file must contain the county FIPS, the name of the DMA area, and the unique DMA code for that area.
2. Create a county sqlite file from a county shapefile that contains county FIPS.
3. Join DMA sqlite with a geographic enabled county polygon sqlite file to create a DMA sqlite file that has a geometry column.
4. Load the DMA sqlite file into Qgis
5. Use the dissolve tool to create the larger dma shapes from the smaller counties (Vector>Geoprocessing Tools>Dissolve)

• The Input vector layer is the DMA sqlite file
• The Dissolve Field is whatever unique ID each DMA has. So all the counties surrounding around Mobile Alabama and Pensacola Florida, for instance, would use the dissolve field that containedMobile, AL-Pensacola, FL.

6. View your new DMA polygons, and save as an sqlite file, in whatever projection you need it in.

Troubleshooting:

DanielJWood

Attach Database '/Users/danielwood/Dropbox/geodata-prep/sqlite-databases-new/ACS_06_VAP.sqlite' AS ACS_06_VAP;
SELECT * FROM ASN
join ACS_06_VAP.COUNTY on ASN.geoid = COUNTY.geoid;