Databurgh

The Pennsylvania Department of Transportation (PennDOT) continually collects data on road crashes from local police departments, compiling a yearly database with 12(!) tables. Just look at this bad boy:

Since each year was broken out into a separate Access database -- and I didn’t feel like linking THAT many tables!  -- I exported and joined together a smaller dataset, listing location information, who was at fault, what the weather was like and so on.

Getting the intersections

The Pennsylvania Geospatial Data Clearinghouse provides a ton of good Allegheny County data. That’s where I got the shapefile of Allegheny County street centerlines, which I opened in QGIS.

After filtering the road system down to just Pittsburgh, I ran a line intersection query, pulling up every point where two roads crossed. (Later went through it manually to remove some false intersections -- crossing of elevated highways, etc.)

Finally, I imported the accident CSV and saved it as another shapefile, adding one more QGIS layer.

On to PostGIS

To figure out how which accidents coorelated to which intersections, I turned to PostGIS, a powerful extension to PostgreSQL. Using PostGIS, I could import my shapefiles as database objects and run spatial queries against them.

Most Windows builds of postgres/PostGIS come with a handy SHP exporter, so I ran both the intersection and accident shapefiles through and uploaded them as geography objects to my local database.

I then used postGIS’s ST_DWithin function to group accidents that were within 15 meters of the same intersection.

SELECT intersections.x,

intersections.y,

COUNT(accidents.*) as count

FROM accidents, intersections

WHERE ST_DWithin(accidents.geog, intersections.geog, 15)

GROUP BY intersections.gid

ORDER BY count DESC

The result:

As reported, 18th and Carson showed up as No. 1, with 13 crashes.

Reporting the story

Armed with these figures, reporters visited the city’s most dangerous intersections and walked the beat, talking with locals and business owners.

They followed up with city hall, questioning both Pittsburgh’s public works department and the Bureau of Police. Then there was PennDOT, the third arm in the triumvirate protecting Pittsburgh pedestrians … you can read their response in the story.

Yep.

Using latitude/longitude data and Excel’s pivot tables, you can build a passable heatmap without muddling around with SRIDs and SHPs. The top of this post shows a map I made showing car crashes in Pittsburgh in 2013 (with help from here and here).

Zoomed in:

You can clearly see some of the city's main thoroughfares, as well as a few bridges.

How I built it:

Start with a spreadsheet of latitude/longitude data. I generated mine in Access using PennDOT data of vehicle accidents statewide, filtering just to Pittsburgh. (Used the same dataset for this story on pedestrian crashes, which I’ll be posting about soon.) Note: To get things oriented correctly, you may have to assign negative values to latitudes. Otherwise the map will come out upside down.

Select the data and click Insert → PivotTable. I placed the table in a new worksheet.

Drag your latitude field to “Row Labels” and your longitude field to “Column Labels”. If you have a third column -- say, “fatalities” -- drag that to “Values”, but otherwise just drag your latitude column.

Click the drop-down arrow next to the field you put in “Values” and click “Value Field Setting.” Unless you have a numerical field you’re summing (like fatalities), change the “Summarize value field by” selection to “Count.” It should look something like this:

This will probably generate a map with ridiculous precision, especially if you’re using latitude-longitude pairs with a lot of decimal places. Let’s aggregate things a bit. Right-click on any cell in your latitude column and click “Group,” editing the “By” field to be “.01” or “.001”.

Do the same for your longitude row.

Select the entire workbook and drag in your row and column headers to make each cell a square. I’d also make the font smaller. Now we’re getting somewhere!

Let’s add coloring. Select all cells and click “Conditional formatting.” First, we’ll want to format all cells based on their values on a two-color scale. Leave “Type” on “Minimum” alone, but change “Highest Value” under “Maximum” to “Percentile.” I usually let the scale top out at 80%.

Unless you like the default colors, change them to something ominous -- like pretty pink to dark red. Click OK. 

Click “Conditional formatting” again to add a new rule. This time, select “Format only cells that contain,” changing “Cell Value” to “Blanks.” Then click “Format” to add styling; I made my empty cells black.

Voila! You have a heat map. Throw in some additional formatting to center the number within their cells, if you’d like.

Here’s the raw data and my map in one spreadsheet.

Note: You can also do this on Google Sheets, though you’ll have to round your lat-lng pairs first (Google’s pivot table function doesn’t have a “group” feature) and zoom your browser out realllllly far (Google Sheets doesn’t have a document zoom like Google Docs).

A few other maps I made:

Airport density in the U.S.A.

Marcellus Shale wells drilled in Pa. (notice the Allegheny County-shaped hole?)

Brian used foreign-born population tables to map Irish, German and Italian Immigrants living in Pittsburgh. (Irish are pink, German are dark green, and Italian are blue.) Full map here.

Working with my colleagues Mark Dent and Sam Werner, we decided to focus our analysis on teams ranked in The Associated Press’ Top 25 end-of-year rankings in the 2013-14 academic year. Not only were these schools good representatives of the entire Division I landscape, they were the ones who were clearly the most successful in the sporting world. We also included the University of Pittsburgh and Penn State University because of their geographic proximity to our readership.

We divvied up the list evenly and reviewed team media guides and player biographies on the team’s official website to find academic information about these students. Schools are not required to disclose the majors of its college athletes, but most do.

We shared our data on a Google spreadsheet, and we identified the schools that “cluster” athletes in majors by determining which majors accounted for 25 percent – or more – of a team’s roster. We found that 13 of the 22 Top 25 football schools that listed majors had athlete clusters, and 16 of the 20 Top 25 basketball schools that listed majors had athlete clusters.

The methodology was rather simple. I used a pen and a notepad to keep tally of the majors, inserted the data into the spreadsheet and used Google’s default sum formula to get a total count of athletes. From there, it was easy to determine which majors accounted for 25 percent of a team’s roster. Later, we condensed the majors into disciplines for better visual representation to run with the story.

Requirements

QGIS, an open-source GIS manager similar to ArcGIS (but free!)

Tilemill

A Mapbox account (unless you're hosting the map tiles yourself, in which case you probably know how to do this already).

(Optional) Leaflet.js and some coding know-how.

Getting the data

Let's start by downloading the ACS counts of foreign-born persons, the key dataset we need to power this project. Head over to the American Factfinder.

I usually start by selecting my geography. In this case, it was "Census Tract - 140," with all census tracts within Allegheny County, Pennsylvania selected. Add that to your selections.

Narrow down that big list of results by typing "B05006" into the "Topic or table name" field of the "Refine your search" box. Then click "2012 ACS 5-year estimates" version -- might as well be current! 

Once on the data screen -- it should show a long list of foreign counties with columns separating Census tracts -- click "Download." I prefer to download my data with descriptive data element names (who has time to figure out header codes?) and with annotations in a single file. Don't bother downloading it as a .xls file -- CSV is cleaner and is read just as easily by Excel.

Pop open the downloaded zip file, open the spreadsheet and make sure you've got what you need. I took the extra step of filtering the file down to only geographic sub-regions (like Northern Europe, Middle Africa, etc.), deleting country-specific or continent-wide rows.

OK! Time to get the shapes we'll need to build this map.

Download shapefiles

Bless the Census's heart. Not only does it provide numerical data for us, it also ships out geographic shapes through the TIGER Products system. Let's grab Allegheny County's 2012 Census tract outlines, starting at this page. 

Open up QGIS Desktop. Click "Add Vector Layer" from the left-hand toolbar.

Click "Browse" and select the .shp file from the extracted shapefile folder you downloaded from the Census. (Mine is called "tl_2013_42_tract.shp.") Click "Open" to add the shapefile. 

Bonus step: If you don't want your randomly scattered dots to show people floating in a river, download a countywide water features shapefile from the Tiger Lines site and subtract it from your county tract shapefile. That will eliminate bodies of water -- lakes, rivers, creeks, etc. -- from your census tracts. (Beyond the scope of this post, I'm afraid.)

Now to add the data. Click "Add Text Deliminated Layer" from the left-hand toolbar. Select the population estimates CSV you cleaned up. Make sure to click "No geometry" in the "Geometry definition" box. 

Right-click the tract layer in your Layer bin and click "Properties." From here, click "Joins." 

Click the green "+" button at the bottom of the page. You'll then have to choose the field that links the two datasets together. In this case, it's TractID in the CSV file and TRACTCE in the shapefile.

OK! You've downloaded a shapefile, formatted it and associated data with each feature.

Scatter the dots

First, a bit of weirdness. There's a bug in QGIS that deactivates the scattered-dot function unless you pet it softly and hand-feed it Fancy Feast. So! 

Right-click the tract layer in the layer bin and click "Save As." Save the shapefile as a GeoJSON file. Then, re-import the new file as a new vector layer. Delete the old layer. Trust me, that was completely necessary.

Open up the "Random points" box through Vector --> Research Tools. Select the population sector you want under "Use value from input field." Make sure you select a destination for the shapefile that has a meaningful name, like "immigrants-australia." Hit OK and let QGIS add a new layer of dots to your workspace. 

Rinse and repeat, selecting a new region under "Use value from input field" and saving each collection of dots to a new shapefile.

Make map tiles

Open TileMill and start a new project. Uncheck "Include world layer and styles" -- they'll just get in your way. 

Let's make a basic style for all the population dots. In the style.mss pane, make a class that looks something like the following:  .immigrants { marker-width: 6; marker-fill:#f45; marker-line-opacity:0; marker-allow-overlap:true; }

From the Layers screen, click "Add layer." in the following screen, select one of your random dot shapefiles as the datasource. Make sure to give the layer the class you designated on your stylesheet -- again, I used "immigrants."

Click "Save" (not "Save & Style"!) Voila! 

Repeat steps 2 and 3 for all the random-dot shapefiles you generated earlier. 

OK, now you should have a whole mess of dots on your screen. Time to style them! Each layer (one for each shapefile you uploaded) should have a unique ID, something like “#westernafrica.” On your stylesheet, make a style for each ID that changes the marker-fill property. For example. #canada { marker-fill: #7EA7D8 } That will give you something like this: 

Click Export --> Upload. You may have to sign into your Mapbox account. 

Give your project a nice name and description, and set the center and bounds of the visualization. You'll also want to modify your zoom levels so you're not exporting billions of blank tiles; I selected 11 as my lowest zoom and 16 as my highest.

Click upload!

Final stop: Mapbox

Log in to your online Mapbox account and hop over to "Data." If your tiles have finished uploading, they should appear there. 

Click your uploaded layer and hit "Create project." That'll take you to what looks like a blank screen, but never fear.

Click the magnifying glass and enter the geography you're mapping. For me, it's "Pittsburgh." That'll re-center the map map over your dots. 

So the dots are there, but where are the streets? Click "Style" and hit "Discard styles" under the "Color" tab. Boom! Streets reappear. 

Save your project (you'll have to re-enter your password) and head back to your main profile page. It should show up there (albeit as "Untitled project" ... better fix that!). 

You're done! Click the "Share project" button to grab a permalink you can send everywhere.

Other things to do

Right now, your map is just a mess of dots. I found it helpful to generate a separate Mapbox layer for every cluster of interest -- say, all European regions, or all Asian. That way ...

... I could stack all these layers onto a Leaflet map and make them toggle-able! This is the route I ended up taking, instead of relying on Mapbox's basic map. Leaflet has a helper function that makes adding Mapbox layers a snap ... but you have to know some code.

You don't have to use QGIS to generate your point data. Englewood is a great option for code-inclined folks.

I've used Google Fusion Tables for more than a year for all mapping needs, but I knew it wasn't going to cut it here. Andrew McGill suggested Google MapMaker, but this error message popped up every time. (Full size here.)

After six attempts at this and much searching of the error message, this was me.

See ya, Google.

Enter Mapbox. I attended Lisa Wiliams' ONA session last fall about Tile Mill, where Mapbox was referenced, but hadn't tried to use it until last week. Big mistake, as it's a great tool.

It's a very easy interface to use, particularly for placing lines over roads. This project unfortunately required plenty of hand drawing, but Mapbox's straightforward tools kept me sane over the course of 120-plus lines.

Here's an example — on a stretch of North Side street that won't be paved but should be — and what the drawing tool looks like.

Two financial caveats about Mapbox:

1. They charge at least $5/month for an account that allows you to place more than 20 points/lines/polygons on a map. So we're $5 poorer at the Post-Gazette.

2. If your map goes mildly viral like this one did, you get charged when the map is loaded more than 10,000 times. The rate is apparently 50 cents for every additional 1,000 views. That could add up.