古都の治安を守れい!これがAlteryx、Snowflake、Lookerで作る奈良県犯罪MAPじゃあアァァッッ!

行基像の前でまた会おう
2020.10.14

悪が蔓延るこの地にて 起きる犯罪はEverywhere

己を守る手段とは データの可視化に他ならず

古都の平和を守るため 分析ツールをまといて即、参上!

大阪オフィス 玉井励

やったこと

下記のダッシュボードを作成したでござる!

ドリルダウンして各犯罪の詳細を出すことも可能じゃあ!

環境

使用ツール

  • Alteryx Designer
    • 2020.2
  • Snowflake
    • 4.33.2
  • Looker
    • 7.16.26

使用データ

  • 下記のCSVデータ2年分(2018〜2019)
    • ひったくり
    • 車上ねらい
    • 部品ねらい
    • 自動販売機ねらい
    • 自動車盗
    • オートバイ盗
    • 自転車盗

その他

今回作成したLookMLは下記にアップしているので、全コードが見たいという方はこちらをご覧あれ。

手順

概要

下記の通りに作業するぞい

  1. csvデータをAlteryxで整形する
  2. Snowflakeにロードする
  3. Lookerで上記データをモデリングする
  4. Lookerで可視化する

作業をする前に、どういう形でデータをモデリングするか考える

実際に作業に入る前に、大体のデータの持ち方について考えるぞい

今回のデータは、犯罪手口別にCSVファイルが分かれておる。で、こちらのデータ、犯罪手口によって、カラム名が多少異なっておる。

犯罪が起こった場所に関する情報は、どのファイルでも共通して存在しておるが、「現金以外の主な被害品」みたいな情報は、犯罪手口によってあったりなかったりするのう。この他にも、犯罪手口によって固有の情報(カラム)がある。つまり、全てをUNIONして1つのテーブルにすることはできないということじゃなあ(無理やりやってもいいが、NULLが大量発生するので非常に面倒)。

だから、ひとまず2018年と2019年に分かれているのは併合するとして、犯罪手口別に分かれている件については、そのままテーブルを分けて入れるぞい。

そして、共通のカラムと別のカラムが混在しているようなデータの場合、Lookerであればextendsを使って、無駄な記述を増やすことなくモデリングすることができるので、その方向性で参ろうか。

Alteryxでデータを整形してSnowflakeにロードする

Alteryxのワークフロー

まずはAlteryx Designerでデータを整形しつつSnowflakeにロードじゃあ。今回作成したワークフローは下記の通りじゃ。

このワークフローで主にやっているのは下記の通り。

Snowflakeを確認する

ちゃんとロードできていることを確認するでござる。

Lookerでデータをモデリングする

さあ、今回のメインであるデータモデリングじゃな。

共通viewをつくる

当初の計画通り、まずはextendsを使用して各テーブルに共通して存在するカラム(発生場所の住所とか管轄警察署とか)を1回の記述で済ませるぞい。というわけで、まずはextend_base.viewを作成し、共通カラムを定義するぞい。このview自体はそのまま使わず、継承して別のviewファイルを作成していくので、extension: requiredを記述しておくぞい。

犯罪全般用のviewをつくる

ここからは「どのような分析をしたいか」という目的によって作り方は変わってくるぞい。私はまず手口関係なく「奈良県の犯罪全体に関することを分析したい」と思ったので、全部の犯罪手口を横串で分析できるデータモデルを作ることにしたぞい。

作り方じゃが、犯罪手口固有のカラムはいらないので、基本的には先程の共通viewでいけるはずじゃ。ただし、DWH(Snowflake側)はテーブルが手口毎にできているので、全てUNIONする必要があるぞい。Snowflake側でやってもええが、せっかくなので、Lookerの派生テーブルで定義するぞい(Looker上でUNIONしたい場合は、SQL派生テーブルを定義するしかないのじゃよ)。

犯罪手口個別のviewファイルをつくる

この時点で「犯罪全体のダッシュボードのどれかを選ぶと、その犯罪手口個別のダッシュボードに飛ぶ」という構想が頭の中に出来ておった。というわけで、そのダッシュボードを作るための、犯罪手口個別のviewをつくるぞい。

下記は「ひったくり」のviewじゃ。

犯罪の発生場所などは、extend_base側で書いてあるものを継承しているので、再度記述する必要はないぞい。ひったくりテーブルだけに存在する3つのカラムについて定義するだけでOKじゃ。

これを犯罪手口テーブルの数だけ作成するぞい。

exploreは何もやらない

今回は各view(テーブル)がそれぞれ独立している(リレーショナルがない)ため、exploreは質素じゃあ…。joinは無いぞい。

Lookerでダッシュボードをつくる

必要なデータモデリングが一通り終わったので、可視化に勤しむとするかのう。記事冒頭に乗せているダッシュボードを作るんじゃが、ほとんどは普通の棒グラフなので、ここではMAPと別ダッシュボードへの遷移について解説するぞい。

地図について

Lookerは緯度経度のデータがあれば、それを自動的に地図にプロットしてくれるぞい(そのためにAlteryxで事前ジオコーディングした)。緯度経度のデータは、LookMLで下記のように定義するんじゃ。

これをExploreで可視化してみるぞい。位置情報と、それに合わせて可視化したいmeasureを選ぶのじゃ。

すると、下記のようにmeasureの大小を地図上にプロットしてくれるぞい。

プロットの描画方法は色々選べるぞい。今回はヒートマップを採用じゃあ!。やはり人口の多い市あたりが犯罪が多いのう!

別ダッシュボードへの遷移

冒頭のダッシュボード紹介にあるように、このダッシュボードは、地図のある部分を選択→ドリルダウンが出てくる→犯罪手口を選ぶとそこから犯罪手口固有ダッシュボードに遷移…という流れが仕掛けられておる。

これを実現するためには、先程出たbasic_info.viewにあるdimentionにあるパラメータを追加じゃ。

Lquidで、選択した犯罪手口に応じて飛ばすダッシュボードを変えるようにしておるんじゃな。

せっかくなので色々見てみる

自分が住んでいる場所の犯罪についてパトロール(?)してみるぞい。

葛城市あたりを見てみるぞい。さすが葛城市、犯罪がめちゃくちゃ少ないのう。

詳しく見てみるぞい。やはり自転車はよく盗まれるようじゃな…。1件だけ車の部品がやられている犯罪があるようじゃ。

葛城市の東室という地域に絞った自転車盗ダッシュボードじゃ。意外と若い人がやられておる。あと、鍵はちゃんとしたほうがよさそうじゃな。

やはり駐輪場でやられることが多いようじゃ。出かける時は注意じゃぞ。

おわりに

公式が既に犯罪マップを提供していたというオチじゃあ!!!