MySQL X Dev API から位置情報を検索してみた（リレーショナル編）

このエントリは、[RDBMS-GIS(地理情報・位置情報) Advent Calendar 2023](https://qiita.com/advent-calendar/2023/rdbms_gis) の20日目です。「RDBMS-GIS Advent Calendar」では参加者を絶賛募集中です！軽いネタでもいいので、お気楽に参加ください！

MySQL には、昔ながらの SQL インターフェースの他に、[X Dev API](https://dev.mysql.com/doc/x-devapi-userguide/en/) というプログラム的なインターフェースもあります。これは、主には MySQL でリレーショナルなデータだけではなく、ドキュメントデータ（JSON）も扱えるようにする[ドキュメントストア](https://www.mysql.com/jp/products/enterprise/document_store.html)機能のために設計されたものではありますが、コレクション (リレーショナルでのテーブル相当) - ドキュメント (リレーショナルでのレコード相当)で管理されるドキュメント型コレクションに対してだけではなく、リレーショナル型のテーブルに対しても、全ての機能が扱えるわけではありませんが、CRUD 処理機能を中心に API が提供されています。API が提供されていない機能についても、SQLを実行するメソッドが提供されているので、一応全ての機能は X Dev API 経由で実行することは可能です。現在、JavaScriptやPythonなど各プログラム言語向けのMySQLドライバである[各 Connecters](https://dev.mysql.com/downloads/) の実装も、X Dev APIの上に実装されています。

今回は、どうしても使えない部分は SQL 実行メソッドを使いながらも、できる限りX Dev APIのメソッドを使って、GISデータを検索してみたいと思います。

まず、GISカラムを含むテーブルを作成します。空間インデックスも張っておきます。これ自体もX Dev API経由で実行しても構いませんが、テーブルの作成はSQLを実行するメソッド経由で実行するしかありませんので、面白みもないのでSQLだけ掲載します。

```sql
CREATE TABLE `geo_table` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `geometry` geometry NOT NULL SRID 4326 COMMENT 'Geometry',
  `property` json NOT NULL COMMENT 'Property',
  PRIMARY KEY (`id`),
  SPATIAL KEY `geometry` (`geometry`)
) ENGINE=InnoDB;
```

これに対し、10万件のランダムなPOINT情報を挿入します。ここで、X Dev API（[Connector/Node.JS](https://www.npmjs.com/package/@mysql/xdevapi)経由）を使ってみます。

```javascript
const mysqlx = require('@mysql/xdevapi');
const config = { schema: 'gis_db', table: 'geo_table', user: 'root', server: '10.0.XX.XX', password: 'XXXXXXX' };

const main = async () => {
  const session = await mysqlx.getSession({ host: config.server, port: 33060, user: config.user, password: config.password });
  let schema = await session.getSchema(config.schema);
  if (!(await schema.existsInDatabase())) schema = await session.createSchema(config.schema);

  const table = await schema.getTable(config.table);

  for (let i = 0; i < 100000; i++) {
    const lon = Math.random() * 360 - 180;
    const lat = Math.random() * 170 - 85;
    await table.insert(['geometry', 'property']).values(
      mysqlx.expr(`ST_GeomFromGeoJSON('{ "type": "Point", "coordinates": [${lon}, ${lat}]}',1,4326)`), "{}"
    ).execute(); // <== 挿入処理、X Dev API 経由
  }
};

main();
```
これの挿入部分の処理は、SQL の実行メソッドで置き換えると、次のように変更できます。
```javascript
    await session.sql(`INSERT INTO gis_db.geo_table (geometry, property) VALUES (ST_GeomFromGeoJSON('{ "type": "Point", "coordinates": [${lon}, ${lat}]}',1,4326), '{}');`).execute();
```
このように、よく知られた SQL 実行インターフェースでも、値の挿入ができますが、X Dev API独自のCRUDインターフェースを使うと、前のJavaScriptプログラムのような形になります。簡単に説明しますと、テーブルのインスタンスである`table`を作成し、`insert`メソッドで値を入れるカラムを指定します。その後、メソッドチェーンで繋げて`values`メソッドで挿入する値を指定します。`values`メソッドの指定する値は、リテラルのほかに、SQL関数の評価が必要な場合は、`mysqlx.expr`を使って指定します。そして、さらにメソッドチェーンで`execute`メソッドを実行します。結果は`Promise`で返されるので、`then`や`await`などを使って、非同期で処理を待ちます。

次に、10万件の位置情報に対して、検索を行ってみます。[不可視のインデックス](https://dev.mysql.com/doc/refman/8.0/ja/invisible-indexes.html)機能を使って、空間インデックスを有効化、無効化して比較してみましょう。
```javascript
const mysqlx = require('@mysql/xdevapi');
const config = { schema: 'gis_db', table: 'geo_table', user: 'root', server: '10.0.XX.XX', password: 'XXXXXXX' };

const main = async () => {
  const session = await mysqlx.getSession({ host: config.server, port: 33060, user: config.user, password: config.password });
  let schema = await session.getSchema(config.schema);
  if (!(await schema.existsInDatabase())) schema = await session.createSchema(config.schema);

  const table = await schema.getTable(config.table);

  console.log("With Index");
  let start = Date.now();
  await session.sql('ALTER TABLE gis_db.geo_table ALTER INDEX geometry VISIBLE;').execute();
  let res = await table.select('count(geometry)').where(`ST_Within(geometry, ST_GeomFromText('POLYGON((-3 3, -3 -3, 3 -3, 3 3, -3 3))', 4326))`).execute();
  console.log(`Number: ${await res.fetchOne()}`);
  let end = Date.now();
  console.log(`Seconds: ${end - start}\n`);

  console.log("Without Index");
  start = Date.now();
  await session.sql('ALTER TABLE gis_db.geo_table ALTER INDEX geometry INVISIBLE;').execute();
  res = await table.select('count(geometry)').where(`ST_Within(geometry, ST_GeomFromText('POLYGON((-3 3, -3 -3, 3 -3, 3 3, -3 3))', 4326))`).execute();
  console.log(`Number: ${await res.fetchOne()}`);
  end = Date.now();
  console.log(`Seconds: ${end - start}\n`);

  console.log("Finished");
};

main();
```
テーブルのインスタンスである`table`を作成し、`select`メソッドで得たいカラムや表現を指定します。その後、メソッドチェーンで繋げて`where`メソッドで検索条件を指定します。さらに、`limit`、`offset`などをメソッドチェーンすることもできます。最後に、`execute`で結果の`Promise`を得て、その結果から`fetchOne`あるいは`fetchAll`などを通じて、検索結果データを得ます。

実行結果は以下の通りです。

```shell-session
$ node gis_X_API.js 
With Index
Number: 56
Seconds: 14

Without Index
Number: 56
Seconds: 370

Finished
```
空間インデックスを有効にすると、10万件の中から56件を検索するのに14ミリ秒、無効にすると370ミリ秒で、X Dev APIでも空間インデックスを有効にして、空間DBを検索できることがわかります。