MySQL8.0でGIS機能を試す No.1 - Ingressのリンク可能判定
目次
本エントリの概要
- 位置情報ゲームIngressの機能の一部をMySQL8.0のGIS機能を使って再現できるかを試してみた記録。
- 「リンク元としてリンク可能状態かを判定する」機能を再現してみます。
- 初回なのでIngressの簡単な説明を挿入します。
大前提
- 本エントリはあくまで個人の興味から技術的な実装を「試してみた」だけの記録です。
- 実際のサービスが同等の技術で実装されているかは全く考慮していません。
- 特にパフォーマンスにおいては大きな差があるのは承知しています。
- GIS以外の機能でできることは検証を省きます。
背景
- 3年間ほどIngressにドはまりしてしまい、時間と色々なことを犠牲にしてしまった人生を送った私(ただし得難いものを得ることもありました)ですが、はまっていた当時「この機能はデータベース上でどのように実装しているのだろう」と不思議に思っていました。
- それまでDB界隈でもGISという言葉は聞いたことありましたが、「DBに緯度経度を保存するんでしょ」程度にしか理解していませんでした。今思えば超低レベル。
- MySQL8.0でGISが強化されたという話しを聞き、関連する関数を見ていたら「あの機能、もしかして普通のRDBMSの機能でもできるんじゃ」って思えてきたんで、実際に試してみたくなりました。
- 以下でIngressについて説明しますが、相当端折ります。ご存知の方は読み飛ばしてください。もっと詳しく知りたい方はGoogleで検索してみてください。
Ingressとは
IngressのDB実装
- たぶん内部的な実装は非公開だと思います。(私の検索能力では見つけられせんでした)
- Googleさんが関係してますので、商用やOSSのRDBMSのままではなく独自実装だったりするんじゃないかなって勝手に想像しています。
- アプリ名称のIngressがRDBMSのIngresと関係あるのかどうかもわかりません。
Ingressの基本ルール
- 「ポータル」と呼ばれる地点が世界中に無数にあります。
- ポータルに自陣の共鳴機(レゾネーター)を8本設置するとそこから(へ)別の自陣が占有するポータルへ(から)「リンク」を生成することができます。
- リンク条件はいくつかあるのですがそれは後述。
- ポータルをリンクを結んで三角形を作ると「フィールド」が生成され、その中は自陣の領土となります。四角形以上の多角形はフィールドになりません。
- 敵陣のポータルに設置されているレゾネーターを武器で攻撃し破壊すると、そこから(へ)張られていたリンク/フィールドが破壊されます。
- このフィールドの広さを一定時間ごとのチェックポイントでエリアごとに集計し、競い合います。
検証したいこと「リンク条件の一つ「リンク元ポータルがフィールドに埋もれていないこと」の判別」
検証対象のリンク条件
- ポータルからポータルへリンクを張る
- リンク元のポータルがフィールドに埋もれているとリンクを出すことができない
- フィールドの頂点のポータルは「埋もれて」いない
- リンク先のポータルは埋もれていてもよい
ルール上は他にも「リンクがすでにN本出ていない(NはMOD構成による)」などもあるのですがここでは省略します。
データ設計
ポータルの位置情報として前回のエントリで作ったportalsテーブルを利用します。
MySQL8.0でGIS機能を試すためのデータ準備 - untitled .engineer
実際のポータルはもっと多い(非公式の参考情報では港区だけで2500ポータル以上)のですが、パフォーマンスを考慮しない検証なので。
これを実装するため(ほかの検証項目も考慮)のデータ設計として、portalsテーブルのほかに作成済のフィールド情報を格納するfieldsテーブルを作ります。
CREATE TABLE fields ( `id` int(11) NOT NULL AUTO_INCREMENT, `portal_1_id` int(11) NOT NULL, `portal_2_id` int(11) NOT NULL, `portal_3_id` int(11) NOT NULL, `geom` GEOMETRY DEFAULT NULL, PRIMARY KEY (`id`) ,CONSTRAINT duplicate_check CHECK(`portal_1_id` != `portal_2_id` AND `portal_2_id` != `portal_3_id` AND `portal_1_id` != `portal_3_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
- NULL/NOT NULLの区別に深い意味はありません。
- 遊びでcheck制約をつけています。構文は通りますが、MySQLの暗黒面に吸い込まれてなかったことにされます。
検証実施
検証内容
- 練馬区役所-渋谷区役所(仮庁舎)-台東区役所を頂点とするフィールドがあるとする
- 新宿区役所からリンクが引けるかの判別を行う
- 新宿区役所が他のいずれかのフィールドのエリア内に含まれているかを確認する
- 「含まれる」かどうかはST_Contains()を使ってみる
まずはフィールドなしでSELECT
- id:13104(新宿区役所)がフィールドに埋もれていないかをクエリします。
SELECT COUNT(*) FROM fields F WHERE ST_Contains(F.geom, (SELECT geom FROM portals WHERE `id` =13104)); +----------+ | COUNT(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec)
想定通りです。
フィールド作成
以下のINSERT文でフィールドのポリゴンレコードを作ります。
INSERT INTO fields VALUES ( null, 13113, 13106, 13120, ST_GeomFromText( CONCAT( 'POLYGON((', (SELECT CONCAT(lat,' ',lon,',') FROM portals WHERE `id` = 13113), (SELECT CONCAT(lat,' ',lon,',') FROM portals WHERE `id` = 13106), (SELECT CONCAT(lat,' ',lon,',') FROM portals WHERE `id` = 13120), (SELECT CONCAT(lat,' ',lon,')') FROM portals WHERE `id` = 13113), ')' ) ,4612) );
再度判定
もう一度先のクエリを実行してみます。
+----------+ | COUNT(*) | +----------+ | 1 | +----------+ 1 row in set (0.00 sec)
想定通りです。
念のため
- 「フィールドの頂点のポータルは「埋もれて」いない」が成立しているか確認します。
SELECT COUNT(*) FROM fields F WHERE ST_Contains(F.geom, (SELECT geom FROM portals WHERE `id` =13113)); +----------+ | COUNT(*) | +----------+ | 0 | +----------+ 1 row in set (0.01 sec)
こちらも想定通りです。
今回は比較的簡単に検証できました。
次回はリンク先の選定について検証します。
参考
- MySQLマニュアル 12.15.1 Spatial Function Reference https://dev.mysql.com/doc/refman/8.0/en/spatial-function-reference.html
- MySQL8.0で関数が刷新されたのでうっかり(日本語だからといって)5.7のマニュアルで見ていると間違えてしまうので注意
- CraftMAP 白地図生成サービス http://www.craftmap.box-i.net/ken.php
- 地図画像を使わせてもらいました