How you can use Data Studio to track crimes in Chicago

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

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

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

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

Step 4

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

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Michaël Scherding

Michaël Scherding

Data centric, always learning and trying to have fun with all the projects I run.