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

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

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

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

まず、GISカラムを含むテーブルを作成します。空間インデックスも張っておきます。これ自体もX Dev API経由で実行しても構いませんが、テーブルの作成は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経由）を使ってみます。

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 の実行メソッドで置き換えると、次のように変更できます。

    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万件の位置情報に対して、検索を行ってみます。不可視のインデックス機能を使って、空間インデックスを有効化、無効化して比較してみましょう。

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などを通じて、検索結果データを得ます。

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

$ 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を検索できることがわかります。