ケプラーを利用すると緯度経度のデータからグラフィカルなマップグラフを作成できるとを以前記事に書きました（
【kepler.gl】コロナ対策による人流の変化も地図上に可視化（各種メディアで報道））。今回、ケプラーのH3というグラフ形式を使って、東京都内各地点内の異なる期間のUUの比率をマップグラフで描画するため必要なデータを集計すためのクエリを紹介します。また、集計データのケプラーでの描画については、下記のサイトが分かりやすいのでご参考にして下さい。
◇クエリ例
WITH
t1 AS (
SELECT
UDF.geoToH3(latitude,
longitude,
8) AS geoH3_1,
COUNT(DISTINCT uid) AS CntUU_1,
FROM
data
WHERE
AND TIMESTAMP(DATETIME(TIMESTAMP(detected_time),
'Asia/Tokyo')) >= TIMESTAMP('2020-02-02 00:00:00')
AND TIMESTAMP(DATETIME(TIMESTAMP(detected_time),
'Asia/Tokyo')) < TIMESTAMP('2020-02-03 00:00:00')
AND latitude BETWEEN 23
AND 46
AND longitude BETWEEN 123
AND 148
--
--東京都内
AND ST_COVERS(ST_GEOGFROMTEXT('POLYGON((139.868043 35.628718,139.887956 35.653272,139.885209 35.680048,139.911302 35.685068,139.918855 35.70347,139.896195 35.728001,139.899629 35.744164,139.879716 35.761996,139.878343 35.77481,139.86255 35.79542,139.771226 35.810456,139.732774 35.778152,139.650376 35.799875,139.616731 35.763667,139.54326 35.752523,139.421723 35.765896,139.371598 35.764782,139.277528 35.834953,139.170411 35.843153,139.016603 35.891563,138.955491 35.853728,139.044755 35.717259,139.219163 35.645868,139.411424 35.582232,139.454015 35.541781,139.471867 35.519988,139.482167 35.534517,139.459508 35.571946,139.476674 35.595958,139.443028 35.606008,139.453328 35.6328,139.492467 35.619963,139.528859 35.652889,139.658635 35.598192,139.715627 35.553513,139.750645 35.548765,139.796651 35.524738,139.809354 35.51384,139.868043 35.628718))'),
ST_GEOGPOINT(longitude,
latitude))
GROUP BY
geoH3_1 ),
t2 AS (
SELECT
UDF.geoToH3(latitude,
longitude,
8) AS geoH3_2,
COUNT(DISTINCT uid) AS CntUU_2,
FROM
data
WHERE
AND TIMESTAMP(DATETIME(TIMESTAMP(detected_time),
'Asia/Tokyo')) >= TIMESTAMP('2020-04-19 00:00:00')
AND TIMESTAMP(DATETIME(TIMESTAMP(detected_time),
'Asia/Tokyo')) < TIMESTAMP('2020-04-20 00:00:00')
AND latitude BETWEEN 23
AND 46
AND longitude BETWEEN 123
AND 148
--
--東京都内
AND ST_COVERS(ST_GEOGFROMTEXT('POLYGON((139.868043 35.628718,139.887956 35.653272,139.885209 35.680048,139.911302 35.685068,139.918855 35.70347,139.896195 35.728001,139.899629 35.744164,139.879716 35.761996,139.878343 35.77481,139.86255 35.79542,139.771226 35.810456,139.732774 35.778152,139.650376 35.799875,139.616731 35.763667,139.54326 35.752523,139.421723 35.765896,139.371598 35.764782,139.277528 35.834953,139.170411 35.843153,139.016603 35.891563,138.955491 35.853728,139.044755 35.717259,139.219163 35.645868,139.411424 35.582232,139.454015 35.541781,139.471867 35.519988,139.482167 35.534517,139.459508 35.571946,139.476674 35.595958,139.443028 35.606008,139.453328 35.6328,139.492467 35.619963,139.528859 35.652889,139.658635 35.598192,139.715627 35.553513,139.750645 35.548765,139.796651 35.524738,139.809354 35.51384,139.868043 35.628718))'),
ST_GEOGPOINT(longitude,
latitude))
GROUP BY
geoH3_2 ),
join_log AS (
SELECT
geoH3_1,
MAX(CntUU_1) AS CntUU_1,
MAX(CntUU_2) AS CntUU_2,
FROM
t1
LEFT JOIN
t2
ON
t1.geoH3_1 =t2.geoH3_2
WHERE
geoH3_1 IS NOT NULL
GROUP BY
geoH3_1 )
SELECT
*,
CntUU_2 / CntUU_1 AS rate
FROM
join_log
ORDER BY
CntUU_1 DESC
◇参考サイト
