スマートスタイル TECH BLOG

データベース&クラウド技術情報

MySQL8.0のGISを使ってみる(お城編)

はじめに

MySQL5.7で本格的に実装された「GIS」機能ですが、MySQL8.0で大幅に強化されました。
本記事では、実際にGIS機能を使ってその挙動を確認してみたいと思います。

What’s New in MySQL 8.0? (Generally Available)

GIS Geography support. Spatial Reference Systems (SRS), as well as SRS aware spatial datatypes, spatial indexes, and spatial functions.

GISの概要

GIS(地理情報システム)は、MySQL5.7で初めて実装されました。しかし、この時点ではX軸・Y軸から構成される座標的なデータしか扱うことができず、情報の精度に課題がありました。

そこでMySQL8.0では、平面地図(メルカトル図法)的なデータと、より正確な地球上の地理データ(正距方位図法)が扱えるようになりました。

やりたいこと

数学などでよく使われる「最短経路問題(巡回セールスマン問題)」のようなことに挑戦したいと思います。

  • 日本の地図上から特定の地点をいくつかピックアップする
  • 上記地点のGIS情報を取得して、なおかつ各地点間の距離を算出
  • スタート地点から全地点を巡って帰ってくるまでの最短経路を求める

「特定の地点」については、私が実際に行ったことがある以下の城跡にしたいと思います。なぜなら私が城好きだからです。なお、スタート地点は姫路城で固定とします。

  • 姫路城(兵庫県)
  • 熊本城(熊本県)
  • 松本城(長野県)
  • 彦根城(滋賀県)
  • 広島城(広島県)
  • 久保田城(秋田県)
  • 八王子城(東京都)

手順A : 自分でデータを作ってみる

Step.1 MySQL8.0のインストール

まずはMySQL8.0を準備します。今回はMySQLの公式リポジトリを使って手軽にインストールします。

なお、GISの利用に関して特別な設定は必要ないため、/etc/my.cnfはデフォルトで構いません。

Step.2 地理情報データのロード

MySQLでは、GIS情報を「ジオハッシュ(GeoHash)」という値で扱うのが分かりやすいです。これは、緯度・経度の情報をハッシュ値にしたもので、文字列データとなるためインデックスも使用できます。

今回の7つの城のジオハッシュに関して、以下のような手順で取得しました。

  1. Google Mapで地点検索
  2. URL(https://www.google.co.jp/maps/place/姫路城/… ) に表示された「@」以降の数字をコピー(緯度・経度を示します)
  3. geohash.orgの「Show coordinates in the map」に上記数値をペーストし、実行
  4. ページ上部に出力される値を取得

※ GeoHash値は ST_Geohash() 関数でも取得できます 
例:SELECT ST_GeoHash(134.6939047, 34.839449, 12);

★各地点のGeoHash

城名 緯度・経度 GeoHash値
姫路城 (Himeji-jo) 34.839449,134.6939047 wypy1n34e2p0
熊本城 (Kumamoto-jo) 32.8061859,130.7036448 wvufwhpmzsmd
松本城 (Matsumoto-jo) 36.238652,137.9667664 xn6n5t036wm5
彦根城 (Hikone-jo) 35.276452,136.2496573 xn2bsmvupp7b
広島城 (Hiroshima-jo) 34.4014949,132.4574179 wyn5wnse1x3r
久保田城 (Kubota-jo) 39.7224843,140.1217534 xp59ux381rxy
八王子城 (Hachiōji-jo) 35.6527607,139.2541586 xn74bf3u3h6g

このデータをMySQLにインポートします。

Step.3 GeoHashから緯度・経度の値を求める

MySQL8.0には、GeoHashの値から緯度・経度を求めるST_LongFromGeoHash()関数 / ST_LatFromGeoHash()関数 が実装されています。
既に分かっている値ではありますが、これら関数を使って具体的な緯度・経度を求めてみましょう。

テーブルに緯度・経度の個別カラムを追加し、それぞれ値を入れてみましょう。

Step.4 各地点間の距離を求める

MySQL8.0には、特定の地理座標間の距離を求めるためのST_Distance()関数が存在します。これを使って、7地点間の距離をそれぞれ求めてみましょう。

まず、7地点から2つの地点を選ぶ場合の組み合わせは、7C2 = (7*6) / 2 = 7*3 = 21 通りあります。各パターンをテーブルに格納しましょう。

※ 『SQLで数学パズルを解く』の以下のSQLを使わせて頂きました
> 1-4.組み合わせ(Combination without repetition)

それでは地点間の距離を求めてみましょう。例えば、姫路城(Id=1)と熊本城(Id=2)の距離は以下のように求めることができます。

注意点は、ST_GeomFromText()関数の引数は文字列でないといけないため、「ST_GeomFromText(‘POINT(From_Lat From_Lng)’, 4326)」のような形式は使えません。そのため、CONCAT関数を使って無理やり **_Lat / **_Lng カラムの値を渡しています。

これを使って、21パターンの距離をテーブルに格納しましょう。また、値はメートルとして出力されるため、併せてキロメートルに変換します(1000で除算)。

※ “4326” は空間参照系識別コード(SRID)と言われるコードの一種で、日本では「GPSで得られる位置」を得るために用いられるようです

Step.5 全地点を巡るルートの合計距離を求める

最後にスタート地点である姫路城から、その他6つの城全てを巡るルートの合計距離を求めます。この時のルートは、6! = 6*5*4*3*2*1 = 720 通りあります。

今回は、上記720パターン全てを網羅的に求める地道な方法で試してみましょう。

合計距離は以下のようなストアドプロシージャ / ファンクションを作成し、算出してみます。

以下のようになっていれば、処理は正常に行えていると考えられます。

Step.6 順位を確認

ここまでできれば、あとは Total_distance_km カラムをORDER BYで並び替えれば、求めていた答えが得られます。短い順なので、昇順に並び替えましょう。

上記の結果から、最も短い距離で7地点を巡るルートは、「1.姫路城 → 5.広島城 → 2.熊本城 → 4.彦根城 → 3.松本城 → 7.八王子城 → 6.久保田城」で、合計距離は約 1811.55 kmということが分かります。

※ 上記はあくまで「距離」なので、実際に車なりで行くとなるともっと大変です

手順B : CTEを利用する

ここまで地道な方法で進めてきましたが、MySQL8.0で実装されたCTE(共通テーブル式)を利用すれば、もっと簡単に結果を取得することができます。

早速SQLを見てみましょう。例えば、このようなSQLが考えられます。

※ テーブルデータは「Step.3」完了時点のもので構いません

【SQLの解説】

共通テーブル式(CTE)については、Microsoftの以下の説明が分かりやすいです。

共通テーブル式の使用

単一の SELECT、INSERT、UPDATE、DELETE、CREATE VIEW の各ステートメントの実行スコープ内で定義される一時結果セットと考えることができます。CTE は、オブジェクトとして格納されず、クエリが実行されている間しか保持されない点で、派生テーブルに似ています。派生テーブルと異なるのは、CTE では自己参照が可能であり、同じクエリ内で複数回参照が可能なことです。

今回のケースでは、まず「1st_point」~「7th_point」までの7つのWITH句で、my_castlesテーブルから必要な情報(id, 城の名前、緯度、経度)のみを取得しています。この時、1st_pointのIdは”1st_id”というように、区別できるようエイリアスを設定します(必須ではありません)。これをCTEの本体となるSELECTでJOINすることで、ここまでの処理で7つの一時テーブルが合体した、大きな一時テーブルが作成されるようなイメージになります。

次に、経路の一覧を配列の形で確認するため、JSON_ARRAY型を使用してroutes_path カラムを規定します。ここには、上記のWITH句で取り出した 1st_id ~ 7th_id の値を格納します。
次の部分は非常に見づらいのですが、以下のような2拠点間の式の距離(ST_Distance関数については前述)の合計になっています。これが、「t1~t2」,「t2~t3」, … と6つの式を足し算しているため、SQLが間延びしております。本来は、こちらもストアドファンクションなどにまとめてしまうことを推奨します。

次に各地点の城名を出しています。これも見やすくするためのものなので、必須ではありません。

また、WHEREで指定している条件に関しては、まずスタート地点は姫路城で固定なので「t1.1st_id = 1」を指定します。その後の条件は、「その前に行ったことがある地点」を除外するためのものです。つまり、同じ地点を2回以上経由するパターンを除外しています。これで全ての拠点を一筆書きで経由するという要件を満たすことができます。

最後に、total_distanceカラムの値で取得結果を昇順で並び替えれば、一番短いルートが分かります。
この方法であれば、例えば立ち寄りたい城跡が増えたとしても、シンプルにCTEを追加していけばよいだけなので、簡単に対応することができます。

まとめ

ここまで簡単にではありますが、GISの使用例を紹介しました。

慣れてきたら、既存のアプリケーションやAPIと組み合わせて簡単にお手製地図アプリなどを作ることができそうです。特に GeoHash 値の取得まわりでは、まだまだ改善の余地はあります。
これからのGIS機能の更なる発展に期待ですね!


MySQL

 

Return Top