MaryaDB(MySQL) - geometry 型を利用して2地点間の距離を計算！

[ サーバ構築 ] [ GIS, MariaDB, MySQL, SQL ]

こんにちは。

MariaDB(MySQL) の geometry 型を利用して2地点間の距離を計算する方法についての記録です。

0. 前提条件

1. SQL 作成例・その１

次の SQL は距離をそのまま（度単位で）出力する例。

1
2
3
4
5
6
7
8
9
10
11

SELECT pref_code, pref_name,
       city_code, city_name,
       town_code, town_name,
       X(loc) AS lon, Y(loc) AS lat,
       GLength(GeomFromText(
           CONCAT('LineString(133.048611 35.468056, ',
                  X(loc), ' ', Y(loc), ')')
       )) AS d
  FROM towns
 ORDER BY d
 LIMIT 10;
  • GLength の単位は「度」（地球外周の 1/360）
  • 緯度・経度は、世界測地系（日本測地系2000）
  • 世界測地系で使用される楕円体は GRS-80
    • 長半径（赤道半径）：6,378,137.000000m
    • 短半径（極半径）：6,356,752.314140m

以下、実行結果。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

+-----------+-----------+-----------+-----------+--------------+--------------+------------+-----------+-----------------------+
| pref_code | pref_name | city_code | city_name | town_code    | town_name    | lon        | lat       | d                     |
+-----------+-----------+-----------+-----------+--------------+--------------+------------+-----------+-----------------------+
| 32        | 島根県    | 32201     | 松江市    | 322010025000 | 苧町         | 133.048634 | 35.469214 | 0.0011582283885351232 |
| 32        | 島根県    | 32201     | 松江市    | 322010083000 | 末次町       | 133.049814 | 35.467467 | 0.0013394513802325288 |
| 32        | 島根県    | 32201     | 松江市    | 322010115000 | 西茶町       | 133.050552 | 35.469118 | 0.0022125381352811426 |
| 32        | 島根県    | 32201     | 松江市    | 322010041000 | 片原町       | 133.050215 | 35.469983 | 0.0025072185784359856 |
| 32        | 島根県    | 32201     | 松江市    | 322010106000 | 中原町       | 133.045517 |  35.46835 | 0.0031079369363018416 |
| 32        | 島根県    | 32201     | 松江市    | 322010100000 | 千鳥町       | 133.045139 |   35.4661 |  0.003985062107410069 |
| 32        | 島根県    | 32201     | 松江市    | 322010145000 | 東茶町       | 133.052529 | 35.468845 |  0.003996654225723998 |
| 32        | 島根県    | 32201     | 松江市    | 322010010000 | 魚町         | 133.053773 | 35.465337 |  0.005834312727316434 |
| 32        | 島根県    | 32201     | 松江市    | 322010084000 | 末次本町     | 133.054498 | 35.468929 |  0.005951377823666156 |
| 32        | 島根県    | 32201     | 松江市    | 322010013000 | 内中原町     | 133.047242 | 35.474144 |  0.006240024439056187 |
+-----------+-----------+-----------+-----------+--------------+--------------+------------+-----------+-----------------------+
10 rows in set (0.977 sec)

2. SQL 作成例・その２

次の SQL は距離を m 換算したものも出力する例。（別名を利用して再計算するためにサブクエリ化）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

SELECT pref_code, pref_name,
       city_code, city_name,
       town_code, town_name,
       lon, lat, d, d * 110946.26 AS d_m
  FROM (SELECT pref_code, pref_name,
               city_code, city_name,
               town_code, town_name,
               X(loc) AS lon, Y(loc) AS lat,
               GLength(GeomFromText(
                   CONCAT('LineString(133.048611 35.468056, ',
                          X(loc), ' ', Y(loc), ')')
               )) AS d
          FROM towns) AS a
 ORDER BY d
 LIMIT 10;
  • 上記の距離換算（度→m）では、地球楕円体単半径を基準している。（110,946.26m/度）
    （長半径を基準にするなら、 111,319.49m/度を乗じる）

以下、実行結果。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

+-----------+-----------+-----------+-----------+--------------+--------------+------------+-----------+-----------------------+--------------------+
| pref_code | pref_name | city_code | city_name | town_code    | town_name    | lon        | lat       | d                     | d_m                |
+-----------+-----------+-----------+-----------+--------------+--------------+------------+-----------+-----------------------+--------------------+
| 32        | 島根県    | 32201     | 松江市    | 322010025000 | 苧町         | 133.048634 | 35.469214 | 0.0011582283885351232 |  128.5011079337988 |
| 32        | 島根県    | 32201     | 松江市    | 322010083000 | 末次町       | 133.049814 | 35.467467 | 0.0013394513802325288 |   148.607121088637 |
| 32        | 島根県    | 32201     | 松江市    | 322010115000 | 西茶町       | 133.050552 | 35.469118 | 0.0022125381352811426 | 245.47283121681681 |
| 32        | 島根県    | 32201     | 松江市    | 322010041000 | 片原町       | 133.050215 | 35.469983 | 0.0025072185784359856 | 278.16652427998923 |
| 32        | 島根県    | 32201     | 松江市    | 322010106000 | 中原町       | 133.045517 |  35.46835 | 0.0031079369363018416 | 344.81397939854753 |
| 32        | 島根県    | 32201     | 松江市    | 322010100000 | 千鳥町       | 133.045139 |   35.4661 |  0.003985062107410069 |  442.1277366848654 |
| 32        | 島根県    | 32201     | 松江市    | 322010145000 | 東茶町       | 133.052529 | 35.468845 |  0.003996654225723998 |  443.4138388572734 |
| 32        | 島根県    | 32201     | 松江市    | 322010010000 | 魚町         | 133.053773 | 35.465337 |  0.005834312727316434 |  647.2951767661582 |
| 32        | 島根県    | 32201     | 松江市    | 322010084000 | 末次本町     | 133.054498 | 35.468929 |  0.005951377823666156 |  660.2831113826994 |
| 32        | 島根県    | 32201     | 松江市    | 322010013000 | 内中原町     | 133.047242 | 35.474144 |  0.006240024439056187 |  692.3073738218818 |
+-----------+-----------+-----------+-----------+--------------+--------------+------------+-----------+-----------------------+--------------------+
10 rows in set (1.957 sec)

別名 d を使用して再計算しているとは言っても、 GLength ... AS d 部分の処理が2倍行われることになるので、結果として、2倍程度時間がかかる。

以上。