R-tree と MySQL と PostGIS id:tanishiking24 2017/06/08 1

概要 • R-tree は空間情報を効率よく検索するためのデータ構造 • R-tree の仕組みをざっくり説明 • MySQL や PostGIS に OpenStreetMap のデータを突っ込んで遊んでみる 2

参考資料 • Guttman, Antonin. R-trees: a dynamic index structure for spatial searching. Vol. 14. No. 2. ACM, 1984. • MySQL 5.7 Reference Manual - Creating Spatial Indexes • PostGIS 2.3.3dev Manual • Boundless : Introduction to PostGIS (Japanese) • 位置情報サービスのはじめ方 ( 栗栖さんの連載！ • 空間アクセス法- 京都大学 田中克己 角谷和俊 3

自己紹介 • id:tanishiking24 • 2017 年 4 月新卒入社 • はてなブックマークチーム • Scala とか TypeScript をよく書いてる • 大学学部のときはプログラミング言語っぽい研究室 4

ナイーブな例 x 座標と y 座標をもつデータを格納するテーブルがあったとする。 これらのデータから近くのレコードを取得する処理を考えてみる CREATE TABLE geometries ( name VARCHAR(100) NOT NULL, loc_x DOUBLE NOT NULL, loc_y DOUBLE NOT NULL ); SELECT name, SQRT(POWER($x - loc_x) + POWER($y - loc_y)) AS dist FROM geometries WHERE dist < 100.0; 毎回全レコードに対して距離の計算を走らせることになる。なんとか計 算するデータを絞り込めないものか 5

ナイーブな例 - 区間毎にインデックスで絞り込んだ後にソート region というカラムを追加してインデックスを貼る。 CREATE TABLE geometries ( name VARCHAR(100) NOT NULL, loc_x DOUBLE NOT NULL, loc_y DOUBLE NOT NULL, region VARCHAR(100) NOT NULL, KEY idx_region ( region ) ); 距離の計算が必要になるのは region で絞り込まれたレコードのみとなる。 SELECT name, SQRT(POWER($x - loc_x) + POWER($y - loc_y)) AS dist FROM geometries WHERE region = ”some_region” AND dist < 100.0; region を跨っての検索ができなかったり何かと不便 6

R-tree R-tree は階層的に入れ子になった重なり合う最小外接矩形 (Minimum Bounding Rectangle: MBR) により空間を分割する。 7

R-tree のノード 内部ノード/根ノードのエントリ • 子ノードへのポインタ • 子ノードの全エントリを包含する MBR 葉ノードのエントリ • データオブジェクトへのポインタ • データオブジェクトを表す MBR 8

各ノードの要素数 以下の制限を満たしつつ挿入削除を行うことで木のバランスを保つ • 各ノードが持つ最大エントリ数を M • 最小エントリ数を m (m <= M/2) とする • 各ノードは m 個以上 M 個以下のエントリを持つ • 根ノードは (それが葉ノードでない限り) 最低 2 つの要素を持つ 9

CRUD Read 子ノードに対応する MBR を探索すべきかを判断することで、探索範囲を 減らす Create 近接する葉ノードに追加、エントリ数が M より多くなる場合はノードを 分割 Delete 削除し、ノードのエントリ数が m 未満になった場合は木の再構築を行う Update 削除して挿入するだけ 10

Read - 点検索 1. 根ノードから出発 2. 葉ノードでない場合、検索地点を含む子ノードに対して再帰的に 2 を実行 3. 葉ノードな場合、検索地点を含むデータオブジェクトを取得 11

Read - 範囲検索 答えは R7, R8, R9, R10 12

Read - 範囲検索 1. 根ノードからスタート 2. 葉ノードでない場合、検索範囲と重なる子ノードに対して 2 を実行 3. 葉ノードな場合、検索範囲と重なるデータオブジェクトを取得 13

Create - 概要 新しく追加するデータオブジェクトに対応するキーを E とする 1. E が所属すべき近接する葉ノードを探索 2. 葉ノードに空きがあれば挿入し MBR を拡大。親ノードの MBR も拡 大していく 3. 空きがなければノードを分割 14

Create - 例 近接している葉ノードは R5 だが、R5 には既に 3 つのエントリが含まれ ているため、ノードを分割する必要がある。 15

Create - 例 16

Create - ノードの分割 木の平行さを保ち、MBR ができるだけ小さくなるように分割する Exhaustive Algorithm 全探索により分割結果の MBR が一番効率の良いものを選ぶ。当然遅いの で挿入効率が悪くなる。 Quadratic Cost Algorithm はじめだけ全探索で要素数 2 のノードを 2 つ作り、全ての要素がどちら かのノードに属するようにノードを少しずつ広げていく (MBR が最小に なるように) Linear Cost Algorithm Quadratic Cost Algorithm の改良版、はじめに要素数 2 のノードを 2 つ作る 際に全探索ではなくノード間の (正規化された) 距離を用いてグルーピン グする。のこりの要素のグルーピングはランダムに行うことでパフォー マンスを確保 17

Delete 削除のアルゴリズム • 葉ノードからエントリを削除する • 葉ノードのエントリ数が少なすぎないよう木の再構築 • 再構築後に根ノードの要素が一つしかない場合はその子ノードを根 ノード 木の再構築 • 1. エントリ数が少なくなりすぎた節点 (及びエントリ) を削除 • 2.MBR の形を縮小 • 3. 親ノードもについても 1 を適用 • 4. 最後に削除した全てのノードを再び挿入 18

Delete - 木の再構築の理由 B+-tree などでは、エントリ数が少なくなったノードは近隣兄弟ノードと マージする処理が行われるが、R-tree では削除して際挿入というヘビー な処理が実行される。 • 実装が簡単 • 下手に近隣ノードとマージすると効率の良くない MBR が構築され てしまうことが多い 19

遊んでみる - Well Known Text Well Known Text は点や線分などのデータ構造を表現するフォーマット INSERT INTO geometries VALUES /* (0, 0) の点 */ ('Point', 'POINT(0 0)'), /* (0, 0) と (1, 1) を結ぶ線分 */ ('Linestring', 'LINESTRING(0 0, 1 1)'), /* (0, 0) と (1, 0) と (1, 1) を頂点とする領域 */ ('Polygon', 'POLYGON((0 0, 1 0, 1 1))'); 20

遊んでみる - データの準備 OpenStreetMap の OverPass API から京都府周辺のコンビニを全て取得 curl 'http://overpass-api.de/api/interpreter' -d @./query.xml (国土地理院の京都府東西南北端の座標を利用) 21

遊んでみる - MySQL CREATE TABLE convenience ( name VARCHAR(100) NOT NULL, geom GEOMETRY NOT NULL, SPATIAL KEY geometry ( geom ) ) 22

MySQL - 実験データの準備 データを CSV に整形して DB にいれる mysql> LOAD DATA INFILE '/var/lib/mysql-files/convenience.csv' -> INTO TABLE convenience FIELDS TERMINATED BY ',' -> (@name, @lon, @lat) -> SET name = @name, -> geom = ST_GeomFromText(CONCAT('POINT(', @lon, ' ', @lat, ')')); Query OK, 3311 rows affected (0.01 sec) Records: 3311 Deleted: 0 Skipped: 0 Warnings: 0 23

MySQL - 実験データの準備 mysql> select name, ST_AsText(geom) from convenience limit 3; +--------------------+-------------------------------+ | name | ST_AsText(geom) | +--------------------+-------------------------------+ | ファミリーマート | POINT(135.7726379 35.028738) | | サンクス | POINT(134.7346876 34.8212667) | | ファミリーマート | POINT(135.5554523 34.502714) | +--------------------+-------------------------------+ 3 rows in set (0.00 sec) 24

MySQL - 範囲検索 烏丸通りと河原町通り間かつ、御池通りと四条通りの間のコンビニ mysql> SET @karasuma_oike = POINT(135.7510319, 35.0125248); mysql> SET @shijo_kawaramachi = POINT(135.7670933, 35.003777); mysql> SET @envelope = ST_MakeEnvelope(@karasuma_oike, @shijo_kawaram mysql> SELECT name, ST_AsText(geom) FROM convenience -> WHERE ST_Within(geom, @envelope); +-------------------+-------------------------------+ | name | ST_AsText(geom) | +-------------------+-------------------------------+ | ファミリーマート | POINT(135.7670903 35.0058163) | | サークル K | POINT(135.7623839 35.0059873) | | 7-Eleven | POINT(135.7618849 35.0110656) | ... | 7 Eleven | POINT(135.7565414 35.0038313) | +-------------------+-------------------------------+ 21 rows in set (0.00 sec) 25

MySQL - 範囲検索 よくわからなかったので Google reverse geocoding API で住所調べてみた $ python convenience.py 日本, 〒 604-8046 京都府京都市中京区東側町５０８ 棲家新京極 日本, 〒 604-8141 京都府京都市中京区泉正寺町３３４ 日昇ビル 日本, 〒 604-0835 京都府京都市中京区高宮町（御池通）２０６ 御池 ビル ... 日本, 〒 604-8223 京都府京都市中京区小結棚町４４４ ニッセン四条新 町ビル 26

MySQL と Spatial index MySQL5.7 から InnoDB でも空間インデックスがサポートされたので使って みた • MySQL5.7 時点では geometry 型 (ユークリッド座標系) のみサポート • geography 型 (球座標系) はない • geometry 型上での距離計算は単位に関わらずユークリッド座標系で の距離になる • ST_DistanceSphere() のような球体を考慮した距離の算出もでき るっちゃできる • 半径 x(m) 以内みたいな関数がない (postgis での ST_DWithin) 27

PostGIS 半径 x(m) 以内みたいなクエリが投げたすぎるので postgis も試してみる test=# CREATE EXTENSION postgis; CREATE EXTENSION test=# \d convenience; Table ”public.convenience” Column | Type | Modifiers --------+-----------------------+----------- name | character(100) | not null geo | geography(Point,4326) | not null Indexes: ”idx_geo” gist (geo) 4326 は SRID、測地系は WGS84、座標系は地球座標系 (GPS でよく使われる やつらしい) 28

PostGIS こんな感じのデータに整形して ファミリーマート,SRID=4326;POINT(135.7726379 35.028738) サンクス,SRID=4326;POINT(134.7346876 34.8212667) ファミリーマート,SRID=4326;POINT(135.5554523 34.502714) test=# COPY convenience (name, geo) test=# FROM '/var/lib/postgresql/convenience_ewkt.csv' WITH CSV; COPY 3311 29

PostGIS はてな京都オフィスから 500m 以内のコンビニを取得 test=# SELECT name, ST_Distance(geo, ST_GeoGraphyFromText( test=# 'POINT(135.761901 35.011143)')) as distance test=# FROM convenience test=# WHERE ST_DWithin(geo, test=# ST_GeoGraphyFromText('POINT(135.761901 35.011143)'), 500.0) test=# ORDER BY distance; name | distance -----------------+--------------- 7-Eleven | 8.711656739 ローソン | 94.025915279 ファミリーマート| 146.467619235 サークル K | 206.235846632 7-Eleven | 249.307981544 Lawson | 308.894119598 ampm | 480.195261865 (7 rows) 30

PostGIS - ST_DWithin • Q: R-tree とはいえ半径 x(m) 以内っていうクエリだとインデックス使 いにくいんじゃ…? • A: ST_DWithin は検索範囲の円を含む矩形でインデックス付きの検索 を行なった後に、一定距離以内のデータを検索するという風になっ てるらしい。 ST_DWithin(geometry, geometry, distance) 関数は、インデクス付きの 距離検索を実行する手軽な方法です。この関数は、距離半径を 十分に含む大きさの検索矩形を作成して、インデクス付きの結 果サブセットに対して確実な距離検索を行います。 http://cse.naro.affrc.go.jp/yellow/pgisman/2.3.0/PostGIS_FAQ.html 31

PostGIS • MySQL と比べて圧倒的に多機能で便利 32