ペルソナ設定、取り出すデータを絞る（Persona setting, limiting data）

Chicago_crime公開データを地図に落とし込もうとしたら、犯罪数が多すぎて塩梅がつかめなかった。せっかく再考するならペルソナをたてよう。では、仮に自分がシカゴに住む親で、子供がいたら。10年間の犯罪動向で知りたいもの三つ選ぶ。

結論：それでも多かった。次回、さらに絞る。

• 青「OFFENSE INVOLVING CHILDEREN」子供を巻き込んだ犯罪
• 緑「KIDNAPPING」児童誘拐
• 赤「PROSTITUTION」売春

©OpenStreetMap

【SQL】データ総数：41,853件

SELECT
year, date, primary_type, latitude, longitude, block
FROM　`angular-radar-411310.chicago_crime_data.crime`
WHERE
date BETWEEN "2010-01-01" AND "2020-12-31"
AND date IS NOT NULL
AND primary_type IN ('OFFENSE INVOLVING CHILDREN','PROSTITUTION','KIDNAPPING')

AND latitude IS NOT NULL

ORDER BY date

３タイプの年別推移

【SQL】

SELECT
year, primary_type, COUNT(primary_type) AS sum_year
FROM　`angular-radar-411310.chicago_crime_data.crime`
WHERE
date BETWEEN "2010-01-01" AND "2020-12-31"
AND date IS NOT NULL
AND primary_type IN ('OFFENSE INVOLVING CHILDREN','PROSTITUTION','KIDNAPPING')

AND latitude IS NOT NULL

GROUP BY year, primary_type

ORDER BY primary_type, year

Looker Studio

Google Big QueryはLookerに接続されているので、即可視化できます。最初はディメンションや指標に慣れてなくて戸惑ったけども。簡易にみる分はこれで十分です。「PROSTITUTION（売春）」が目に見えて減ってますね。

しかし今回は位置情報が欲しいのでこれは予備情報でした。

QJISに落とし込む

©OpenStreetMap

それでも多い。つぶれている…地図の縮尺と件数が見合ってない。時系列アニメーションかヒートマップつくりたいんだけど…期間とタイプをもっと絞らねば。

********

Chicago_crime When we tried to map the public data, there were too many crimes to get a complete picture. If we're going to rethink this, let's create a persona.

So let's say I'm a parent living in Chicago and I have a child, and I pick three things I want to know about crime trends over a 10-year period.

Conclusion: still too many. Next time I'll narrow it down even more.

• Blue「OFFENSE INVOLVING CHILDEREN」
• Green「KIDNAPPING」
• Red「PROSTITUTION」

©OpenStreetMap

【SQL】 Total number of data: 41,853.

SELECT
year, date, primary_type, latitude, longitude, block
FROM　`angular-radar-411310.chicago_crime_data.crime`
WHERE
date BETWEEN "2010-01-01" AND "2020-12-31"
AND date IS NOT NULL
AND primary_type IN ('OFFENSE INVOLVING CHILDREN','PROSTITUTION','KIDNAPPING')

AND latitude IS NOT NULL

ORDER BY date

Yearly changes in the three types

【SQL】

SELECT
year, primary_type, COUNT(primary_type) AS sum_year
FROM　`angular-radar-411310.chicago_crime_data.crime`
WHERE
date BETWEEN "2010-01-01" AND "2020-12-31"
AND date IS NOT NULL
AND primary_type IN ('OFFENSE INVOLVING CHILDREN','PROSTITUTION','KIDNAPPING')

AND latitude IS NOT NULL

GROUP BY year, primary_type

ORDER BY primary_type, year

Looker Studio

Google Big Query is connected to Looker so you can visualise it immediately. At first I was confused because I wasn't familiar with dimensions and indicators. For simplicity, this is enough. The number of 'PROSTITUTION' is visibly decreasing.

But this time I wanted location information, so this was preliminary information.

Drop into QJIS

©OpenStreetMap

Still too many. It's crushing... the scale of the map doesn't fit the number of cases.  I want to do a time series animation or heat map... but I need to narrow down the time period and type more.