256bitのミステリ世界

H3地理空間インデックスをDatabricksで試してみる

最近こちらの記事を見させて頂きました！ 大規模地理データに対して検索するにはのどうしたら良いのか……

akuwano
11〜17分

最近こちらの記事を見させて頂きました！

大規模地理データに対して検索するにはのどうしたら良いのか…

へー、Databricksにはないんかなぁと思ったら、あるやん！ってなったので試してみましたｗ

まずサンプルデータを入れる

SQLで東京周辺のデータを入れていきます。

-- ポイントデータの作成（東京周辺の座標）
CREATE OR REPLACE TEMP VIEW points AS
SELECT 
  point_id,
  lat,
  lon,
  h3_longlatash3string(lon, lat, 9) AS h3_index,
  st_point(lon, lat) AS geometry
FROM VALUES
  (1, 35.6812, 139.7671),  -- 東京駅
  (2, 35.6895, 139.6917),  -- 新宿駅
  (3, 35.6586, 139.7454),  -- 渋谷駅
  (4, 35.7295, 139.7190),  -- 池袋駅
  (5, 35.6284, 139.7387),  -- 品川駅
  (6, 35.7100, 139.8107),  -- 上野駅
  (7, 35.6580, 139.7016),  -- 恵比寿駅
  (8, 35.6938, 139.7036),  -- 代々木駅
  (9, 35.6654, 139.7300),  -- 六本木
  (10, 35.6470, 139.7096)  -- 目黒駅
AS t(point_id, lat, lon);

SELECT * FROM points;

H3インデックスを作成

次にその座標に対してH3インデックスを作成していきます

-- ポリゴンデータの作成（東京周辺のエリア）
CREATE OR REPLACE TEMP VIEW polygons_base AS
SELECT 
  polygon_id,
  wkt,
  st_geomfromtext(wkt) AS geometry
FROM VALUES
  (1, 'POLYGON((139.75 35.67, 139.78 35.67, 139.78 35.69, 139.75 35.69, 139.75 35.67))'),  -- 東京駅周辺
  (2, 'POLYGON((139.68 35.68, 139.70 35.68, 139.70 35.70, 139.68 35.70, 139.68 35.68))'),  -- 新宿駅周辺
  (3, 'POLYGON((139.73 35.65, 139.76 35.65, 139.76 35.67, 139.73 35.67, 139.73 35.65))')   -- 渋谷・六本木周辺
AS t(polygon_id, wkt);

-- H3インデックスを生成（ポリゴンをカバーするH3セルを展開）
CREATE OR REPLACE TEMP VIEW polygons AS
SELECT 
  polygon_id,
  h3_index,
  geometry
FROM (
  SELECT 
    polygon_id,
    h3_polyfillash3string(wkt, 9) AS h3_indexes,
    geometry
  FROM polygons_base
)
LATERAL VIEW explode(h3_indexes) AS h3_index;

SELECT polygon_id, h3_index, geometry FROM polygons LIMIT 20;

polygon_id	h3_index	geometry
1	892f5aade63ffff	POLYGON((139.75 35.67,139.78 35.67,139.78 35.69,139.75 35.69,139.75 35.67))
1	892f5a32da3ffff	POLYGON((139.75 35.67,139.78 35.67,139.78 35.69,139.75 35.69,139.75 35.67))
1	892f5a32d13ffff	POLYGON((139.75 35.67,139.78 35.67,139.78 35.69,139.75 35.69,139.75 35.67))
1	892f5aad30fffff	POLYGON((139.75 35.67,139.78 35.67,139.78 35.69,139.75 35.69,139.75 35.67))
1	892f5aad36bffff	POLYGON((139.75 35.67,139.78 35.67,139.78 35.69,139.75 35.69,139.75 35.67))

こんな感じでドラララってでます

H3とST_CONTAINSを使った空間結合

していきましょう。こんな感じ。

-- H3で候補を絞り込み、ST_CONTAINSで正確な判定
SELECT 
  p.point_id,
  p.lat,
  p.lon,
  poly.polygon_id,
  p.h3_index AS point_h3,
  poly.h3_index AS polygon_h3
FROM points p
JOIN polygons poly
  ON p.h3_index = poly.h3_index  -- H3で候補を絞り込み
WHERE st_contains(poly.geometry, p.geometry)  -- 正確な判定
ORDER BY p.point_id, poly.polygon_id;

point_id	lat	lon	polygon_id	point_h3	polygon_h3
1	35.6812	139.7671	1	892f5a32d97ffff	892f5a32d97ffff
2	35.6895	139.6917	2	892f5a375afffff	892f5a375afffff
3	35.6586	139.7454	3	892f5aadc6fffff	892f5aadc6fffff

こんな感じででます。

画像で表示するとこんな感じです。

以上です（シンプル）

ドキュメントはこちら。

是非H3インデックス！おためしくださいませ！！！

コメントを残す

Trending