BigQueryで位置情報を利用したアクセス元の可視化

BigQueryを活用することで地理情報解析が行えるようでしたので、その方法をまとめました。

最終的にはどの地域からアクセスが多いかマップ上で可視化できます。

Contents

IPアドレスでどの地域からアクセスされているか可視化する
位置情報の活用事例
まとめ

IPアドレスでどの地域からアクセスされているか可視化する

利用するデータ

BigQuery一般データセットで一般公開されているWikipediaの改訂履歴データを利用します。

このテーブルには、2010年4月までの Wikipedia の全記事に関する改訂履歴が含まれています。改訂履歴には編集者のIPアドレスが含まれています。今回はこのIPアドレスを利用します。

IPアドレスから緯度経度をもとめる

GeoLite2から経度緯度を取得します。MAXMIND社が無償で公開しており、これもBigQueryに公開されています。

BigQuery GIS でアクセス元を可視化

BigQuery GIS を利用します。地理情報システム（GIS：Geographic Information System）を利用した位置情報を扱うことのできるBigQueryの機能で、地理空間データを分析・可視化できるようになります。BigQueryでは地理関数として提供されており、ST_GeogPointがそれに当たります

IPアドレスからアクセス元のアクセス数を取得するクエリ

クエリ

WITH wiki_ips AS (
 -- wikipediaの編集者IPアドレスを取得
 SELECT
   NET.SAFE_IP_FROM_STRING(contributor_ip) AS ip,
   LENGTH(NET.SAFE_IP_FROM_STRING(contributor_ip)) AS len
 FROM
   `publicdata.samples.wikipedia`  -- wikipedia改訂履歴
 WHERE
   contributor_ip IS NOT NULL
   AND LENGTH(NET.SAFE_IP_FROM_STRING(contributor_ip)) = 4
),
area as (
 -- wikipediaの編集者IPアドレスから経度・緯度情報を紐付け
 SELECT
   country_name,  -- 国
   city_name,     -- 都市
   latitude,      -- 経度
   longitude      -- 緯度
 FROM
   -- geolite2 経度緯度
   `fh-bigquery.geocode.201806_geolite2_city_ipv4_locs` l
 INNER JOIN
   -- IP（class B）で地域紐付け
   wiki_ips wiki ON l.network_bin = wiki.ip & NET.IP_NET_MASK(4, 24)
),

address AS (
 SELECT
   MAX(longitude) AS longitude,
   MAX(latitude) AS latitude,
   city_name,
   COUNT(*) AS count             -- アクセス数
 FROM
   area
 GROUP BY
   city_name
 HAVING
   MAX(country_name) = 'Japan'   -- 日本に限定
)

SELECT
 ST_GeogPoint(SAFE_CAST(longitude AS FLOAT64),  -- 地理関数
 SAFE_CAST(latitude AS FLOAT64)) AS wkt,
 count
FROM
 address
ORDER BY
 count DESC;

このクエリを元に地図上にプロットしていきます。

地図上にプロットする

BigQuery Geo Vizを利用します。

BigQuery Geo Viz は、Google Maps API を使用して BigQuery の地理空間データを可視化するためのウェブツールで、SQLクエリを実行しインタラクティブな地図に結果を表示できます。

前提

Googleアカウントを持っていること
上記アカウントでGCPアカウントをお持ちであること
GCPアカウントでプロジェクトを作成していること

手順

1. BigQuery Geo Vizにアクセスする

2. 「Authorize」ボタンをクリックし、ご自身のGoogleアカウントで認証する

３. 「Query」に「IPアドレスからアクセス元のアクセス数を取得するクエリ」を入力する
Project IDはご自身のGCPアカウントから作成したものをご利用ください

４. 「Geometry column」カラムに「wkt」を選択する

５. 「Style」を入力する。

# 入力する値
fillColor: ff0000
fillOpacity: 0.5
strokeOpacity: 0.5
circleRadius:
    Function: linear
    Field: count
    Domain: 1000 - 13095
    Range: 6000 - 40000

６. 完成