この記事は最終更新から6年以上経過しています。内容が古くなっている可能性があります。
はじめに
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の公式リポジトリを使って手軽にインストールします。
|
1 2 3 4 5 6 7 8 |
$ sudo yum install -y https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm $ sudo yum -y install mysql-community-server $ rpm -qa | grep -i mysql mysql-community-common-8.0.11-1.el7.x86_64 mysql-community-client-8.0.11-1.el7.x86_64 mysql80-community-release-el7-1.noarch mysql-community-libs-8.0.11-1.el7.x86_64 mysql-community-server-8.0.11-1.el7.x86_64 |
なお、GISの利用に関して特別な設定は必要ないため、/etc/my.cnfはデフォルトで構いません。
Step.2 地理情報データのロード
MySQLでは、GIS情報を「ジオハッシュ（GeoHash）」という値で扱うのが分かりやすいです。これは、緯度・経度の情報をハッシュ値にしたもので、文字列データとなるためインデックスも使用できます。
今回の7つの城のジオハッシュに関して、以下のような手順で取得しました。
- Google Mapで地点検索
- URL（https://www.google.co.jp/maps/place/姫路城/… ） に表示された「@」以降の数字をコピー（緯度・経度を示します）
- geohash.orgの「Show coordinates in the map」に上記数値をペーストし、実行
- ページ上部に出力される値を取得
※ 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にインポートします。
|
1 2 3 4 5 |
$ sudo systemctl start mysqld $ sudo cat /var/log/mysqld.log | grep temp 2018-06-20T04:51:26.992315Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: geYdzCrhr1>s $ mysql -u root -p Enter password: |
|
1 2 3 4 5 6 7 |
mysql> SET PASSWORD = "MySQL8.0"; mysql> CREATE DATABASE gis_test; mysql> use gis_test; mysql> CREATE TABLE my_castles (Id tinyint, Name varchar(100), Lat_Lng text, Geohash varchar(30), PRIMARY KEY(Id)); mysql> INSERT INTO my_castles VALUES (1, "姫路城 (Himeji-jo)", "34.839449,134.6939047", "wypy1n34e2p0"), (2, "熊本城 (Kumamoto-jo)", "32.8061859,130.7036448", "wvufwhpmzsmd"), (3, "松本城 (Matsumoto-jo)", "36.238652,137.9667664", "xn6n5t036wm5"), (4, "彦根城 (Hikone-jo)", "35.276452,136.2496573", "xn2bsmvupp7b"), (5, "広島城 (Hiroshima-jo)", "34.4014949,132.4574179", "wyn5wnse1x3r"), (6, "久保田城 (Kubota-jo)", "39.7224843,140.1217534", "xp59ux381rxy"), (7, "八王子城 (Hachiōji-jo)", "35.6527607,139.2541586", "xn74bf3u3h6g"); |
Step.3 GeoHashから緯度・経度の値を求める
MySQL8.0には、GeoHashの値から緯度・経度を求めるST_LongFromGeoHash()関数 / ST_LatFromGeoHash()関数 が実装されています。
既に分かっている値ではありますが、これら関数を使って具体的な緯度・経度を求めてみましょう。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> SELECT ST_LatFromGeoHash(Geohash) FROM my_castles WHERE Id = 1; +----------------------------+ | ST_LatFromGeoHash(Geohash) | +----------------------------+ | 34.839449 | +----------------------------+ mysql> SELECT ST_LongFromGeoHash(Geohash) FROM my_castles WHERE Id = 2; +-----------------------------+ | ST_LongFromGeoHash(Geohash) | +-----------------------------+ | 130.703645 | +-----------------------------+ |
テーブルに緯度・経度の個別カラムを追加し、それぞれ値を入れてみましょう。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
mysql> ALTER TABLE my_castles ADD COLUMN Lat_v decimal(9,6) AFTER GeoHash; mysql> ALTER TABLE my_castles ADD COLUMN Lng_v decimal(9,6) AFTER Lat_v; mysql> UPDATE my_castles SET Lat_v = ST_LatFromGeoHash(Geohash); mysql> UPDATE my_castles SET Lng_v = ST_LongFromGeoHash(Geohash); mysql> SELECT * FROM my_castles; +------+-----------------------------+------------------------+--------------+-----------+------------+ | Id | Name | Lat_Lng | Geohash | Lat_v | Lng_v | +------+-----------------------------+------------------------+--------------+-----------+------------+ | 1 | 姫路城 (Himeji-jo) | 34.839449,134.6939047 | wypy1n34e2p0 | 34.839449 | 134.693905 | | 2 | 熊本城 (Kumamoto-jo) | 32.8061859,130.7036448 | wvufwhpmzsmd | 32.806186 | 130.703645 | | 3 | 松本城 (Matsumoto-jo) | 36.238652,137.9667664 | xn6n5t036wm5 | 36.238652 | 137.966766 | | 4 | 彦根城 (Hikone-jo) | 35.276452,136.2496573 | xn2bsmvupp7b | 35.276452 | 136.249657 | | 5 | 広島城 (Hiroshima-jo) | 34.4014949,132.4574179 | wyn5wnse1x3r | 34.401495 | 132.457418 | | 6 | 久保田城 (Kubota-jo) | 39.7224843,140.1217534 | xp59ux381rxy | 39.722484 | 140.121753 | | 7 | 八王子城 (Hachiōji-jo) | 35.6527607,139.2541586 | xn74bf3u3h6g | 35.652761 | 139.254159 | +------+-----------------------------+------------------------+--------------+-----------+------------+ |
Step.4 各地点間の距離を求める
MySQL8.0には、特定の地理座標間の距離を求めるためのST_Distance()関数が存在します。これを使って、7地点間の距離をそれぞれ求めてみましょう。
まず、7地点から2つの地点を選ぶ場合の組み合わせは、7C2 = (7*6) / 2 = 7*3 = 21 通りあります。各パターンをテーブルに格納しましょう。
※ 『SQLで数学パズルを解く』の以下のSQLを使わせて頂きました
> 1-4．組み合わせ（Combination without repetition）
|
1 2 |
mysql> CREATE TABLE castle_distance (From_id tinyint, To_id tinyint, From_Lat decimal(9,6), From_Lng decimal(9,6), To_Lat decimal(9,6), To_Lng decimal(9,6), Distance_km float, PRIMARY KEY(From_id, To_id)); mysql> INSERT INTO castle_distance(From_id, To_id, From_Lat, From_Lng, To_Lat, To_Lng) SELECT t1.Id, t2.Id, t1.Lat_v, t1.Lng_v, t2.Lat_v, t2.Lng_v FROM my_castles t1, my_castles t2 WHERE t1.Id < t2.Id; |
それでは地点間の距離を求めてみましょう。例えば、姫路城（Id=1）と熊本城（Id=2）の距離は以下のように求めることができます。
|
1 2 3 4 5 6 7 |
mysql> SELECT ST_Distance(ST_GeomFromText(CONCAT('POINT(',From_Lat,' ',From_Lng,')'), 4326), ST_GeomFromText(CONCAT('POINT(',To_Lat,' ',To_Lng,')'), 4326)) AS `distance(m)` FROM castle_distance WHERE From_Id = 1 AND To_Id=2; +--------------------+ | distance(m) | +--------------------+ | 432747.72976621793 | +--------------------+ 1 row in set (0.00 sec) |
注意点は、ST_GeomFromText()関数の引数は文字列でないといけないため、「ST_GeomFromText(‘POINT(From_Lat From_Lng)’, 4326)」のような形式は使えません。そのため、CONCAT関数を使って無理やり **_Lat / **_Lng カラムの値を渡しています。
これを使って、21パターンの距離をテーブルに格納しましょう。また、値はメートルとして出力されるため、併せてキロメートルに変換します（1000で除算）。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
mysql> UPDATE castle_distance SET Distance_km = ST_Distance(ST_GeomFromText(CONCAT('POINT(',From_Lat,' ',From_Lng,')'), 4326), ST_GeomFromText(CONCAT('POINT(',To_Lat,' ',To_Lng,')'), 4326)) / 1000; mysql> SELECT * FROM castle_distance; +---------+-------+-----------+------------+-----------+------------+-------------+ | From_id | To_id | From_Lat | From_Lng | To_Lat | To_Lng | Distance_km | +---------+-------+-----------+------------+-----------+------------+-------------+ | 1 | 2 | 34.839449 | 134.693905 | 32.806186 | 130.703645 | 432.748 | | 1 | 3 | 34.839449 | 134.693905 | 36.238652 | 137.966766 | 334.921 | | 2 | 3 | 32.806186 | 130.703645 | 36.238652 | 137.966766 | 767.582 | | 1 | 4 | 34.839449 | 134.693905 | 35.276452 | 136.249657 | 149.972 | | 2 | 4 | 32.806186 | 130.703645 | 35.276452 | 136.249657 | 580.678 | | 3 | 4 | 36.238652 | 137.966766 | 35.276452 | 136.249657 | 188.446 | | 1 | 5 | 34.839449 | 134.693905 | 34.401495 | 132.457418 | 210.773 | | 2 | 5 | 32.806186 | 130.703645 | 34.401495 | 132.457418 | 240.414 | | 3 | 5 | 36.238652 | 137.966766 | 34.401495 | 132.457418 | 540.74 | | 4 | 5 | 35.276452 | 136.249657 | 34.401495 | 132.457418 | 360.161 | | 1 | 6 | 34.839449 | 134.693905 | 39.722484 | 140.121753 | 724.536 | | 2 | 6 | 32.806186 | 130.703645 | 39.722484 | 140.121753 | 1141.24 | | 3 | 6 | 36.238652 | 137.966766 | 39.722484 | 140.121753 | 430.517 | | 4 | 6 | 35.276452 | 136.249657 | 39.722484 | 140.121753 | 600.46 | | 5 | 6 | 34.401495 | 132.457418 | 39.722484 | 140.121753 | 901.231 | | 1 | 7 | 34.839449 | 134.693905 | 35.652761 | 139.254159 | 424.698 | | 2 | 7 | 32.806186 | 130.703645 | 35.652761 | 139.254159 | 848.336 | | 3 | 7 | 36.238652 | 137.966766 | 35.652761 | 139.254159 | 133.108 | | 4 | 7 | 35.276452 | 136.249657 | 35.652761 | 139.254159 | 275.882 | | 5 | 7 | 34.401495 | 132.457418 | 35.652761 | 139.254159 | 635.455 | | 6 | 7 | 39.722484 | 140.121753 | 35.652761 | 139.254159 | 458.134 | +---------+-------+-----------+------------+-----------+------------+-------------+ 21 rows in set (0.01 sec) |
※ “4326” は空間参照系識別コード（SRID）と言われるコードの一種で、日本では「GPSで得られる位置」を得るために用いられるようです
Step.5 全地点を巡るルートの合計距離を求める
最後にスタート地点である姫路城から、その他6つの城全てを巡るルートの合計距離を求めます。この時のルートは、6! = 6*5*4*3*2*1 = 720 通りあります。
今回は、上記720パターン全てを網羅的に求める地道な方法で試してみましょう。
|
1 2 3 4 5 |
mysql> CREATE TABLE total_distance (total_id int auto_increment, 1st_id tinyint, 2nd_id tinyint, 3rd_id tinyint, 4th_id tinyint, 5th_id tinyint, 6th_id tinyint, 7th_id tinyint, Total_distance_km float, PRIMARY KEY(total_id)); mysql> INSERT INTO total_distance(2nd_id, 3rd_id, 4th_id, 5th_id, 6th_id, 7th_id) SELECT t1.Id, t2.Id, t3.Id, t4.Id, t5.Id, t6.Id FROM my_castles t1, my_castles t2, my_castles t3, my_castles t4, my_castles t5, my_castles t6 WHERE t1.Id <> 1 AND t2.Id NOT IN (1, t1.Id) AND t3.Id NOT IN (1, t1.Id, t2.Id) AND t4.Id NOT IN (1, t1.Id, t2.Id, t3.Id) AND t5.Id NOT IN (1, t1.Id, t2.Id, t3.Id, t4.Id) AND t6.Id NOT IN (1, t1.Id, t2.Id, t3.Id, t4.Id, t5.Id); mysql> UPDATE total_distance SET 1st_id = 1; |
合計距離は以下のようなストアドプロシージャ / ファンクションを作成し、算出してみます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
mysql> DELIMITER // mysql> CREATE FUNCTION func_get_distance(id1 tinyint, id2 tinyint) RETURNS float DETERMINISTIC READS SQL DATA BEGIN DECLARE dist float; SELECT Distance_km INTO dist FROM castle_distance WHERE (From_id = id1 AND To_id = id2) OR (From_id = id2 AND To_id = id1); RETURN dist; END; // mysql> CREATE PROCEDURE proc_estimate_total_distance() BEGIN DECLARE v1 tinyint; DECLARE v2 tinyint; DECLARE v3 tinyint; DECLARE v4 tinyint; DECLARE v5 tinyint; DECLARE v6 tinyint; DECLARE v7 tinyint; DECLARE cur1 CURSOR FOR SELECT 1st_id, 2nd_id, 3rd_id, 4th_id, 5th_id, 6th_id, 7th_id FROM total_distance; SET @no = 0; SELECT COUNT(*) INTO @total_no FROM total_distance; OPEN cur1; WHILE @total_no > @no DO FETCH cur1 INTO v1, v2, v3, v4, v5, v6, v7; UPDATE total_distance SET Total_distance_km = func_get_distance(v1,v2)+func_get_distance(v2,v3)+func_get_distance(v3,v4)+func_get_distance(v4,v5)+func_get_distance(v5,v6)+func_get_distance(v6,v7) WHERE 1st_id = v1 AND 2nd_id=v2 AND 3rd_id=v3 AND 4th_id=v4 AND 5th_id=v5 AND 6th_id=v6 AND 7th_id =v7; SET @no = @no +1; END WHILE; CLOSE cur1; END; // mysql> DELIMITER ; |
以下のようになっていれば、処理は正常に行えていると考えられます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
mysql> CALL proc_estimate_total_distance(); Query OK, 0 rows affected (2.95 sec) mysql> SELECT * FROM total_distance LIMIT 5; +--------+--------+--------+--------+--------+--------+--------+-------------------+ | 1st_id | 2nd_id | 3rd_id | 4th_id | 5th_id | 6th_id | 7th_id | Total_distance_km | +--------+--------+--------+--------+--------+--------+--------+-------------------+ | 1 | 7 | 6 | 5 | 4 | 3 | 2 | 3100.25 | | 1 | 6 | 7 | 5 | 4 | 3 | 2 | 3134.31 | | 1 | 7 | 5 | 6 | 4 | 3 | 2 | 3517.87 | | 1 | 5 | 7 | 6 | 4 | 3 | 2 | 2860.85 | | 1 | 6 | 5 | 7 | 4 | 3 | 2 | 3493.13 | +--------+--------+--------+--------+--------+--------+--------+-------------------+ 5 rows in set (0.00 sec) |
Step.6 順位を確認
ここまでできれば、あとは Total_distance_km カラムをORDER BYで並び替えれば、求めていた答えが得られます。短い順なので、昇順に並び替えましょう。
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> SELECT * FROM total_distance ORDER BY Total_distance_km ASC LIMIT 5; +--------+--------+--------+--------+--------+--------+--------+-------------------+ | 1st_id | 2nd_id | 3rd_id | 4th_id | 5th_id | 6th_id | 7th_id | Total_distance_km | +--------+--------+--------+--------+--------+--------+--------+-------------------+ | 1 | 5 | 2 | 4 | 3 | 7 | 6 | 1811.55 | | 1 | 2 | 5 | 4 | 3 | 7 | 6 | 1813.01 | | 1 | 5 | 2 | 4 | 7 | 3 | 6 | 1871.37 | | 1 | 2 | 5 | 4 | 7 | 3 | 6 | 1872.83 | | 1 | 4 | 3 | 7 | 6 | 5 | 2 | 2071.3 | +--------+--------+--------+--------+--------+--------+--------+-------------------+ 5 rows in set (0.00 sec) |
上記の結果から、最も短い距離で7地点を巡るルートは、「1.姫路城 → 5.広島城 → 2.熊本城 → 4.彦根城 → 3.松本城 → 7.八王子城 → 6.久保田城」で、合計距離は約 1811.55 kmということが分かります。
※ 上記はあくまで「距離」なので、実際に車なりで行くとなるともっと大変です
手順B : CTEを利用する
ここまで地道な方法で進めてきましたが、MySQL8.0で実装されたCTE（共通テーブル式）を利用すれば、もっと簡単に結果を取得することができます。
早速SQLを見てみましょう。例えば、このようなSQLが考えられます。
※ テーブルデータは「Step.3」完了時点のもので構いません
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 |
WITH RECURSIVE 1st_point AS( SELECT Id AS 1st_id, Name AS 1st_name, Lat_v, Lng_v FROM my_castles ), 2nd_point AS( SELECT Id AS 2nd_id, Name AS 2nd_name, Lat_v, Lng_v FROM my_castles ), 3rd_point AS( SELECT Id AS 3rd_id, Name AS 3rd_name, Lat_v, Lng_v FROM my_castles ), 4th_point AS( SELECT Id AS 4th_id, Name AS 4th_name, Lat_v, Lng_v FROM my_castles ), 5th_point AS( SELECT Id AS 5th_id, Name AS 5th_name, Lat_v, Lng_v FROM my_castles ), 6th_point AS( SELECT Id AS 6th_id, Name AS 6th_name, Lat_v, Lng_v FROM my_castles ), 7th_point AS( SELECT Id AS 7th_id, Name AS 7th_name, Lat_v, Lng_v FROM my_castles ) SELECT JSON_ARRAY(t1.1st_id, t2.2nd_id, t3.3rd_id, t4.4th_id, t5.5th_id, t6.6th_id, t7.7th_id) AS routes_path, ST_Distance(ST_GeomFromText(CONCAT('POINT(', t1.Lat_v, ' ', t1.Lng_v, ')'), 4326), ST_GeomFromText(CONCAT('POINT(', t2.Lat_v, ' ', t2.Lng_v, ')'), 4326)) / 1000 + ST_Distance(ST_GeomFromText(CONCAT('POINT(', t2.Lat_v, ' ', t2.Lng_v, ')'), 4326), ST_GeomFromText(CONCAT('POINT(', t3.Lat_v, ' ', t3.Lng_v, ')'), 4326)) / 1000 + ST_Distance(ST_GeomFromText(CONCAT('POINT(', t3.Lat_v, ' ', t3.Lng_v, ')'), 4326), ST_GeomFromText(CONCAT('POINT(', t4.Lat_v, ' ', t4.Lng_v, ')'), 4326)) / 1000 + ST_Distance(ST_GeomFromText(CONCAT('POINT(', t4.Lat_v, ' ', t4.Lng_v, ')'), 4326), ST_GeomFromText(CONCAT('POINT(', t5.Lat_v, ' ', t5.Lng_v, ')'), 4326)) / 1000 + ST_Distance(ST_GeomFromText(CONCAT('POINT(', t5.Lat_v, ' ', t5.Lng_v, ')'), 4326), ST_GeomFromText(CONCAT('POINT(', t6.Lat_v, ' ', t6.Lng_v, ')'), 4326)) / 1000 + ST_Distance(ST_GeomFromText(CONCAT('POINT(', t6.Lat_v, ' ', t6.Lng_v, ')'), 4326), ST_GeomFromText(CONCAT('POINT(', t7.Lat_v, ' ', t7.Lng_v, ')'), 4326)) / 1000 AS total_distance, t1.1st_name, t2.2nd_name, t3.3rd_name, t4.4th_name, t5.5th_name, t6.6th_name, t7.7th_name FROM 1st_point t1, 2nd_point t2, 3rd_point t3, 4th_point t4, 5th_point t5, 6th_point t6, 7th_point t7 WHERE t1.1st_id = 1 AND t2.2nd_id NOT IN(1) AND t3.3rd_id NOT IN(1, t2.2nd_id) AND t4.4th_id NOT IN(1, t2.2nd_id, t3.3rd_id) AND t5.5th_id NOT IN(1, t2.2nd_id, t3.3rd_id, t4.4th_id) AND t6.6th_id NOT IN(1, t2.2nd_id, t3.3rd_id, t4.4th_id, t5.5th_id) AND t7.7th_id NOT IN(1, t2.2nd_id, t3.3rd_id, t4.4th_id, t5.5th_id, t6.6th_id) ORDER BY total_distance ASC LIMIT 5 \G *************************** 1. row *************************** routes_path: [1, 5, 2, 4, 3, 7, 6] total_distance: 1811.552840317179 1st_name: 姫路城 (Himeji-jo) 2nd_name: 広島城 (Hiroshima-jo) 3rd_name: 熊本城 (Kumamoto-jo) 4th_name: 彦根城 (Hikone-jo) 5th_name: 松本城 (Matsumoto-jo) 6th_name: 八王子城 (Hachiōji-jo) 7th_name: 久保田城 (Kubota-jo) *************************** 2. row *************************** routes_path: [1, 2, 5, 4, 3, 7, 6] total_distance: 1813.0104817994106 1st_name: 姫路城 (Himeji-jo) 2nd_name: 熊本城 (Kumamoto-jo) 3rd_name: 広島城 (Hiroshima-jo) 4th_name: 彦根城 (Hikone-jo) 5th_name: 松本城 (Matsumoto-jo) 6th_name: 八王子城 (Hachiōji-jo) 7th_name: 久保田城 (Kubota-jo) *************************** 3. row *************************** routes_path: [1, 5, 2, 4, 7, 3, 6] total_distance: 1871.3722479541084 1st_name: 姫路城 (Himeji-jo) 2nd_name: 広島城 (Hiroshima-jo) 3rd_name: 熊本城 (Kumamoto-jo) 4th_name: 彦根城 (Hikone-jo) 5th_name: 八王子城 (Hachiōji-jo) 6th_name: 松本城 (Matsumoto-jo) 7th_name: 久保田城 (Kubota-jo) *************************** 4. row *************************** routes_path: [1, 2, 5, 4, 7, 3, 6] total_distance: 1872.82988943634 1st_name: 姫路城 (Himeji-jo) 2nd_name: 熊本城 (Kumamoto-jo) 3rd_name: 広島城 (Hiroshima-jo) 4th_name: 彦根城 (Hikone-jo) 5th_name: 八王子城 (Hachiōji-jo) 6th_name: 松本城 (Matsumoto-jo) 7th_name: 久保田城 (Kubota-jo) *************************** 5. row *************************** routes_path: [1, 4, 3, 7, 6, 5, 2] total_distance: 2071.3043318162377 1st_name: 姫路城 (Himeji-jo) 2nd_name: 彦根城 (Hikone-jo) 3rd_name: 松本城 (Matsumoto-jo) 4th_name: 八王子城 (Hachiōji-jo) 5th_name: 久保田城 (Kubota-jo) 6th_name: 広島城 (Hiroshima-jo) 7th_name: 熊本城 (Kumamoto-jo) 5 rows in set (0.13 sec) |
【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が間延びしております。本来は、こちらもストアドファンクションなどにまとめてしまうことを推奨します。
|
1 |
ST_Distance(ST_GeomFromText(CONCAT('POINT(', t1.Lat_v, ' ', t1.Lng_v, ')'), 4326), ST_GeomFromText(CONCAT('POINT(', t2.Lat_v, ' ', t2.Lng_v, ')'), 4326)) / 1000 |
次に各地点の城名を出しています。これも見やすくするためのものなので、必須ではありません。
また、WHEREで指定している条件に関しては、まずスタート地点は姫路城で固定なので「t1.1st_id = 1」を指定します。その後の条件は、「その前に行ったことがある地点」を除外するためのものです。つまり、同じ地点を2回以上経由するパターンを除外しています。これで全ての拠点を一筆書きで経由するという要件を満たすことができます。
最後に、total_distanceカラムの値で取得結果を昇順で並び替えれば、一番短いルートが分かります。
この方法であれば、例えば立ち寄りたい城跡が増えたとしても、シンプルにCTEを追加していけばよいだけなので、簡単に対応することができます。
まとめ
ここまで簡単にではありますが、GISの使用例を紹介しました。
慣れてきたら、既存のアプリケーションやAPIと組み合わせて簡単にお手製地図アプリなどを作ることができそうです。特に GeoHash 値の取得まわりでは、まだまだ改善の余地はあります。
これからのGIS機能の更なる発展に期待ですね！
