Metabaseでユーザの都道府県ごとの住所を集計して地図に表示する

目的

Metabaseでサービスを利用しているユーザの場所を可視化したいという要望があり、地図に表示させました。

概要

Data of Japanの都道府県別日本地図（GeoJSON形式）を参照し、ユーザの属性テーブルの住所項目と紐付けて集計・表示させます。

手順

Mapの追加

1. 管理者アカウントにて、右上の設定メニューからadminを選択します。

2. SettingタブからMapsを選択してAdd a mapを押します。
   

3. 以下の画面にてMapの設定を行います。
   

今回の私の設定は以下の通りです。
identifierを何にするかが重要です。Data of JapanのJSONからは都道府県No.を数値で表したidや英字表記のnamなども選択できるので、データの項目に合わせて紐付けやすいものを選択して下さい。

項目	値
URL	https://raw.githubusercontent.com/dataofjapan/land/master/japan.geojson
identifier	nam_ja
display name	nam_ja

Mapと住所項目の紐付け

私はこのようなSQLを発行しました。
addressはユーザが入力する住所項目のため、locateでこの項目内で都道府県の文字列を検索してnam_jaと紐付けています。

SELECT
	CASE WHEN address IS NOT NULL THEN
		SUBSTRING(address, 1, CASE WHEN 
			locate('県', address_1) <> 0 THEN
				locate('県', address_1)
			WHEN 
			locate('府', address_1) <> 0 THEN
				locate('府', address_1)
			WHEN 
			locate('都', address_1) <> 0 THEN
				locate('都', address_1)
			WHEN 
			locate('道', address_1) <> 0 THEN
				locate('道', address_1)
			END)
	ELSE
		'その他'
	END AS map,
	COUNT(*) AS count
FROM
	profiles
WHERE
	address IS NOT NULL
GROUP BY
	map
ORDER BY count DESC

結果

このような形で表示することが出来ました。

参考にした記事

metabaseで日本の地図を表示する