untitled .engineer

技術系のブログ（仮）

MySQL8.0でGIS機能を試す 地理座標系と投影座標系のパフォーマンス比較

GIS MySQL

目次

eyechatch

まえおき

ポイント

  • 「普通に考えれば地理座標系(球面)よりも投影座標系(平面)の方が当然ながらパフォーマンスはよいはず」という仮説に基づいてそれを検証します。
  • ただし、クエリの中身は「Ingressの機能の一部」を再現しようとしたものなので、Ingressのルールがわからないと「何の意味があるのか」は全く分からないと思いますが、これを説明し始めるとゴールが見えなくなるので今回は省略します。
  • よほど興味のある方以外は結論までサックリと斜め読みしてください。

検証手順

  • 投影座標系は前回同様JGD2000の9系の領域を使うことにします。
  • 比較を行うので、地理座標系のデータもJGD2000の投影座標系9系と同じ領域に絞ることにします。
  • あらかじめテーブルにGEOMETRY型のデータを保持してからクエリを実行します。

データ準備

検証する際はそれぞれ一度テーブルを空にして流し込みなおしてから実施します。

地理座標系のデータ準備

  1. MySQL8.0でGIS機能を試すためのデータ準備 - untitled .engineer のエントリで紹介した元データのCSVファイル（h2905puboffice_utf8.csv）をテキストエディタで開きます。
  2. 9系の領域に絞り込むため以下を消します。
    • jiscode 1000～6461 「北海道」から「福島県」の前まで
    • jiscode 13421 東京都の「小笠原村」
    • jiscode 15000～ 「新潟県」以降
  3. 以下「MySQL8.0でGIS機能を試すためのデータ準備」のエントリで紹介した以下を実施します。
    • local_governments テーブルにインポート
    • portals テーブルに INSERT

SQL文で指定するSRIDは4615に統一します。

投影座標系のデータ準備

前回のエントリで紹介した通り、投影座標系では座標系原点からの相対距離が必要になります。
このデータを作るのにまた国土地理院のサービス 緯度、経度への換算 を利用します。
前回は単一の変換でしたが今回は「一括計算」を利用します。

  1. 上記で9系に絞ったCSVを加工して、「 緯度、経度への換算 」の「一括計算」で利用できる形式に変換します。
  2. ここから先は同じ操作を行います。

SQL文で指定するSRIDは2451に統一します。

計測1 単純な距離

特定の距離の中にある地点を列挙する

最初はシンプルなクエリを実行してみます。

SELECT id, name, ST_Distance(P.geom, (SELECT geom FROM portals WHERE `id` = 13000)) AS dist
FROM gis.portals P
WHERE ST_Distance(P.geom, (SELECT geom FROM portals WHERE `id` = 13000)) < 100000
ORDER BY ST_Distance(P.geom, (SELECT geom FROM portals WHERE `id` = 13000)) ASC;

id=13000は東京都庁を指します。
このクエリでは東京都庁から100km以内にある地点を距離順に列挙します。

地理座標系の計測結果

+-------+-----------------------------+--------------------+
| id    | name                        | dist               |
+-------+-----------------------------+--------------------+
| 13000 | 東京都庁                    |                  0 |
| 13104 | 新宿区役所                  | 1175.4575822377105 |
| 13114 | 中野区役所                  |  3202.983741088291 |
| 13113 | 渋谷区役所                  | 3246.6669533301974 |
| 13116 | 豊島区役所                  |   4639.38431014907 |
| 13115 | 杉並区役所                  |  5119.614103686591 |
 --- 中略 ---
|  9205 | 鹿沼市役所                  |  97493.89987334797 |
|  9201 | 宇都宮市役所                |  97579.87680054086 |
|  9000 | 栃木県庁                    |  98740.28309924473 |
|  8000 | 茨城県庁                    |  99322.45244651193 |
| 10382 | 下仁田町役場                |  99985.99017845675 |
+-------+-----------------------------+--------------------+
316 rows in set (0.10 sec)

投影座標系の計測結果

+-------+-----------------------------+--------------------+
| id    | name                        | dist               |
+-------+-----------------------------+--------------------+
| 13000 | 東京都庁                    |                  0 |
| 13104 | 新宿区役所                  | 1175.4530636319485 |
| 13114 | 中野区役所                  | 3202.4869311549905 |
| 13113 | 渋谷区役所                  |  3246.277019280479 |
| 13116 | 豊島区役所                  |  4638.827432597314 |
| 13115 | 杉並区役所                  |  5118.783688228149 |
 --- 中略 ---
|  9205 | 鹿沼市役所                  |  97481.23444723446 |
|  9201 | 宇都宮市役所                |  97566.89456185118 |
|  9000 | 栃木県庁                    |  98727.27254895044 |
|  8000 | 茨城県庁                    |  99310.14864353726 |
| 10382 | 下仁田町役場                |  99976.36475823968 |
+-------+-----------------------------+--------------------+
316 rows in set (0.10 sec)

（実行時間は最頻値を採用しています。）
距離は当然ながら微妙に差が出ますがタイムは（0.01秒以上の）差が現れません。
モニターで見てもCPU負荷に大きなさは見受けられません。

計測2 複雑な計測

MySQL8.0でGIS機能を試す No.2 - Ingressのリンク可能なリンク先の選別 - untitled .engineer のエントリにあるクエリを比較してみます。

検索条件

  • 1000km以内のポータル
  • 既存のリンクと交差しない
  • 既存のリンクと一致しない

地理座標系の計測結果

+--------------------+--------+
| name               | DIST   |
+--------------------+--------+
| 東京都庁           |   1175 |
| 渋谷区役所         |   3549 |
| 中野区役所         |   3882 |
| 千代田区役所       |   4535 |
| 文京区役所         |   4676 |
 --- 中略 ---
| 館山市役所         |  78838 |
| 御宿町役場         |  80803 |
| 勝浦市役所         |  82186 |
| 八丈町役場         | 286461 |
| 青ヶ島村役場       | 358016 |
+--------------------+--------+
23 rows in set (0.50 sec)

投影座標系の計測結果

+--------------------+--------+
| name               | DIST   |
+--------------------+--------+
| 東京都庁           |   1175 |
| 渋谷区役所         |   3549 |
| 中野区役所         |   3882 |
| 千代田区役所       |   4535 |
| 文京区役所         |   4675 |
 --- 中略 ---
| 館山市役所         |  78827 |
| 御宿町役場         |  80793 |
| 勝浦市役所         |  82175 |
| 八丈町役場         | 286423 |
| 青ヶ島村役場       | 357970 |
+--------------------+--------+
23 rows in set (0.47 sec)

（実行時間は最頻値を採用しています。）
何とか6%の差をたたき出すことに成功しました。

今回の結論

  • 「投影座標系の方がパフォーマンスはよい」という仮説はこのケースでは一応立証できたように思います。
  • が、今回用意しているデータ数や検索条件では劇的な差が出るものではありませんでした。
  • 適当な材料があったらまた挑戦したいと思います。