How the Sun-Sentinel busted speeding cops using databases and grade-school arithmetic

Resources and articles about how to request files from the Federal Bureau of Investigation via FOIA.

These will be incorporated into more readable pieces. If you have questions where I got the data just ask and I can send you along.

By now, revenues from the wildly successful ALS Icebucket Challenge are reflected in the latest 990s. What can we find out about how the money was used?

How do we make data relevant to what seems like a non-binary, non-enumerable story? We learn from the famous doctor who saved countless newborn babies by thinking of them as numbers. Even something as valuable as a baby’s life can be reduced to a number from 0 to 10.

A reporter is curious about how Trump the presidential candidate compares to Trump the private billionaire when it comes to charity. By systematically double-checking Trump’s claims, he found an even better story.

How do we turn a narrow topic on a local story into a year-long project? Take that narrow topic on a state-by-state tour and compare how laws and data and history and stories differ in 50 comparable ways. This page is a set of real-world examples of how to find focus in a larger issue, and make a big project by looking at the U.S. state-by-state picture.

Not all of the news and journalism made it to the web. Lexis-Nexis Academic gives us access to well-organized, news archives.

Logging in, creating a dataset, and making a map with Carto Builder and USGS Earthquake data.

California’s state and city agencies publish a lot of data. But a law requiring them to publish catalogs of everything they have makes it much easier to see what’s not yet published.

Homework/topics pushed back a day.

The lab computers have this installed, but you should install this SQLite client on your own computer: http://sqlitebrowser.org/

A brief description of the SQLite database software, specifically, how to navigate a SQLite database file using a SQLite client.

This is a collection of public datasets conveniently packaged as SQLite databases to practice on. You don’t have to worry about the data cleaning/import process, just download the SQLite database files and query them from your favorite SQLite client.

Ignore the current homepage

The U.S. Census records such an incredible, intimidating volume of data points about American life that is is hard to know where to begin. This guide aims to explain how Census data is organized, how to find important and interesting data points, and how the data is distilled and displayed by journalism outlets.

This this first unit of SQL lessons, we cover the syntax and concepts to fetch, sort, and – as we’ve done using spreadsheet functions – transform data values.

How the SQLite database is used in billions of real-world applications today is of little relevance to us in this class. But the web browser is a easy-to-understand scenario of how a database gets created and filled.

A lot of powerful journalism is simply looking at one list and seeing which of its names are on another list. The JOIN clause is the clearest way to express that concept, and to execute it in a blink of an eye. It is the main reason why we learn SQL instead of trying to hack around the usually versatile spreadsheet.

A set of SQL programming lessons using a tiny dataset of “simple folks” so that we can focus on the just the SQL syntax and concepts.

Election Day is near, so what better SQL practice is there than the FEC datasets, which are not only comprehensive and fairly well-documented, but large enough that knowing SQL becomes a huge advantage over the limitations of spreadsheets.

Quiz covering chapters 3, 4, 5 of “Homicide” and “Art of Access”. And some SQL.

THR released a list of Most Powerful People in Entertainment. How can we analyze that as data?

Shapiro is a PhD candidate at MIT and a prolific user of FOIA laws. He’ll talk about how FOIA became relevant to his research and activism, how he got better at making requests, and how to argue with the FBI.

Oh boy…

Today is Election Day. Learn new SQL statement, compete with your classmates, and win the prediction pool by copying Nate Silver.

The data for the midterm in database form.

Phillip Reese, investigative data journalist, Pulitzer Finalist, and my former colleague, will talk about what it’s like to find stories and make impact with data.

(Reviewing) An in-class midterm evaluating your understanding and proficiency of Structured Query Language and relational database concepts. If you’ve already taken the midterm, here are the answers.

Covering more challenges of real-world data. Data without structure is just noise, after all.

If we haven’t already, we’ll look at the very nebulous and political nature of crime stats. Or, watch Season 3 of The Wire.

What are ways that data journalism can go very wrong?

Anecdote, spreadsheet, database, FOIA – it doesn’t matter what you start with but that you start somewhere. So here are some random bits of advice and examples.

Alone, or with a partner, find a significant dataset or (several) that you want to dig into deeper. Use spreadsheets, SQL, Carto – whatever gets the job done.

Get ready for a New Year and a new President with 10 public records requests (FOIA, state, and local)

A non-comprehensive list of examples of powerful data storytelling in the wild that (likely) originated with a spreadsheet.

Using Carto and SQL Joins to make a choropleth of Starbucks and gunshops by zip code