How you can use Data Studio to track crimes in Chicago

Hi everyone!

Hope you’re all doing great. Today we will use BigQuery to retrieve GPS points of crimes in Chicago. Then we will visualize those crimes in Data Studio.

Context

As a reminder you can use BQ to analyse public datasets, in the greatest way to level up your skills on it. In this topic we will use the following table:

bigquery-public-data.chicago_crime.crime

Here is a summary of the table

“This dataset reflects reported incidents of crime (with the exception of murders where data exists for each victim) that occurred in the City of Chicago from 2001 to present, minus the most recent seven days. Data is extracted from the Chicago Police Department’s CLEAR (Citizen Law Enforcement Analysis and Reporting) system. In order to protect the privacy of crime victims, addresses are shown at the block level only and specific locations are not identified.”

Step 1

Let’s play a little bit with the table with a really simple query:

SELECT *
FROM `bigquery-public-data.chicago_crime.crime`
LIMIT 10

We want to visualize all the fields of the table and only the 10 first results. The result will looks like:

We have a lot of informations now with crime type, description, residency and so on…

Step 2

Now we want to have the number of crimes per GPS marker, we will use the following query:

SELECT location AS marker,COUNT( * ) AS number
FROM `bigquery-public-data.chicago_crime.crime`
WHERE location IS NOT NULL
GROUP BY location
ORDER BY number DESC

The result will looks like:

For example on the first line we have 13 602 crimes on the same place…

Step 3

On data studio it was a little tricky to visualize GPS tracking point because of the parenthesis in the table. In order to fix the problem I used the following query:

SELECT  SUBSTR(location, 2, CHAR_LENGTH(location) - 2) AS marker,COUNT( * ) AS number
FROM `bigquery-public-data.chicago_crime.crime`
WHERE location IS NOT NULL
GROUP BY location
ORDER BY number DESC

With this little fix you will have the following result:

Step 4

We can now setup Data Studio, I will use the Bubble map for this topic:

Then you will have to setup the graph with:

Nothing really complex, after all of that you will have the map below:

It’s great but we can do more, now I want to have a bigger bubble when the zone is really dangerous. In Data Studio you can setup bubble in a different way with “size bubble”:

And that’s it, for information the most ‘’dangerous’’ place is near the Chicago airport, so if one day you want to travel in Chicago please be careful near the Airport ^^.

Have fun and see you soon.