MySQL (InnoDB) で空間検索 (st_within) #MySQL

こんにちは。
MySQL (5.7.12) の InnoDB テーブルに地点データを読み込み、多角形領域内の空間検索 st_within を行ってみました¹。spatial index が有効のようです。ただしデータ読み込みには極めて時間が掛かりました（6 min 42 sec、データ数約530万)。

用いた地点データは、

$ head -n 2 points.jsonl
{"type":"Point","coordinates":[135.7182012,34.944685]}
{"type":"Point","coordinates":[135.718176,34.9444206]}
$

データ読み込みは、

mysql> CREATE TABLE `table_with_a_point` (
    ->   `point` geometry NOT NULL,
    ->   SPATIAL INDEX (`point`)
    -> ) ENGINE=InnoDB;

mysql> LOAD DATA LOCAL INFILE 'points.jsonl'
    -> INTO TABLE table_with_a_point
    -> LINES TERMINATED BY '\n'
    -> (@json)
    -> set point = ST_GeomFromGeoJson(@json);
Query OK, 5313284 rows affected (6 min 42.18 sec)
Records: 5313284  Deleted: 0  Skipped: 0  Warnings: 0

検索は、ST_Buffer() ² ³を使って楕円形の多角形領域 @poly を作り（これの経線方向の長半径は @radius、緯線方向の短半径は @radius * cos(lat)）、その内部を検索しました。

set @radius = 200;
set @center = ST_GeomFromText('POINT(135.7182012 34.944685)', 4326);
set @M2DEG = 180.0 / 3.141592653589793 / 6378137.0;

set @poly = ST_GeomFromText(ST_astext(ST_Buffer(ST_GeomFromText(ST_astext(@center)),
  @radius * @M2DEG)), 4326);

SELECT count(*) FROM table_with_a_point WHERE st_within(point, @poly);
+------------+
|   count(*) |
|------------|
|         25 |
+------------+
1 row in set (0.007 sec)

EXPLAIN SELECT count(*) FROM table_with_a_point WHERE st_within(point,@poly)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: table_with_a_point
   partitions: NULL
         type: range
possible_keys: point
          key: point
      key_len: 34
          ref: NULL
         rows: 59473
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

近傍地点検索

指定点近傍の地点検索を MySQL で行う方法として、その点（@center）からの距離計算（st_distance_sphere は球体近似距離）と上記とを組み合わせる方法があります。その際、多角形領域 @poly は楕円形よりも矩形⁴もしくは円形を作った方が本当は良いでしょう。

SELECT st_astext(point) as point, round(st_distance_sphere(point, @center), 3) as dist
 FROM table_with_a_point WHERE st_within(point, @poly)
 ORDER BY dist asc LIMIT 10;

+-------------------------------+---------+
| point                         |    dist |
|-------------------------------+---------|
| POINT(135.7182012 34.944685)  |   0     |
| POINT(135.7182012 34.944685)  |   0     |
| POINT(135.7182113 34.9447357) |   5.712 |
| POINT(135.7182113 34.9447357) |   5.712 |
| POINT(135.718176 34.9444206)  |  29.489 |
| POINT(135.718176 34.9444206)  |  29.489 |
| POINT(135.7183011 34.9437886) | 100.09  |
| POINT(135.7183011 34.9437886) | 100.09  |
| POINT(135.7183011 34.9437886) | 100.09  |
| POINT(135.7181462 34.9437805) | 100.7   |
+-------------------------------+---------+
10 rows in set (0.006 sec)

なお、多角形領域内検索 WHERE st_within(...) を省略しても、同等の検索結果を得ることができます。しかし、spatial index が使われずに全レコードが処理されるので、数秒（MyISAM の場合）から10秒以上（InnoDB の場合）かかり実用的ではありませんでした⁵ 。

「ArangoDB で近傍地点検索」で今回と同等なことを行っています。今回の MySQL の近傍検索の方がおよそ２倍の速さです。 ↩
現状の MySQL の ST_Buffer() は地表上の円形は未提供です。地表上の正多角形と言えるものを欲しい場合にも自力で計算が必要です（計算式は例えば「回転楕円体面上（地球表面上）の正多角形」）。 ↩
現状の MySQL 8.0.x の ST_Buffer() を調べますと、いまだに "As of 8.0.11 there are a few spatial functions that don’t yet support geography" (MySQL Server Blog) とのことです（"Detecting Incompatible Use of Spatial Functions before Upgrading to MySQL 8.0" より）。 ↩
参考「mysql空間テーブルの作り方」 ↩
MemSQL では、GEOGRAPHY_WITHIN_DISTANCE(geo1, geo2, dist) → Boolean という関数が用意され spatial index も有効なので、このような近傍地点検索は容易です。 ↩