はじめに
前回の記事ではGeometry型を使ったテーブルを作った後、データの登録と簡単な検索をした所で終わっていました。
今回はGeometry型を活用して、地点間の距離を求めたいと思います。
使用した環境
前回の記事と同一の環境となります。
- OS
- CentOS7.7（1908）
- RDBMS
- MySQL8.0.19
使用したデータ
指定した地点間の距離を求めるSQL
- 以下のSQLでは、
ST_Distance関数を使って2点間の距離を求めています。 -
ST_Distance関数とよく似た名称のST_Distance_Sphere関数がありますが、リファレンスマニュアルには**「一般的な用途の距離計算にはST_Distance関数を参照してください」**と書いてあるので、一般的な用途ではST_Distanceを使えば良さそうです。
SQL
SELECT
CONCAT(st1.name, '-', st2.name) AS 区間,
ST_Distance(st1.location, st2.location) AS '距離(m)'
FROM
station AS st1,
station AS st2
WHERE
st1.name = '東京'
AND st2.name = '新宿';
実行結果
+---------------+-------------------+
| 区間 | 距離(m) |
+---------------+-------------------+
| 東京-新宿 | 6053.612945828571 |
+---------------+-------------------+
指定した地点から距離の近い順に並べるSQL
- 以下のSQLでは、東京駅から近い順に駅を並べて取得しています。
- 抽出した列に日本語で別名を付けているため、並べ替えをする際に指定する列名にバッククォート（`）を付けています。
- 実はこれが分からなくて一番苦労しました...
SQL
SELECT
CONCAT(st1.name, '-', st2.name) AS 区間,
ST_Distance(st1.location, st2.location) AS '距離(m)'
FROM
station AS st1,
station AS st2
WHERE
st1.name = '東京'
AND st1.id != st2.id
ORDER BY
`距離(m)` ASC;
実行結果
+------------------+--------------------+
| 区間 | 距離(m) |
+------------------+--------------------+
| 東京-新橋 | 1828.3847670426867 |
| 東京-四ツ谷 | 3368.720839511702 |
| 東京-上野 | 3708.4275968908173 |
| 東京-日暮里 | 5208.928929258798 |
| 東京-新宿 | 6053.612945828571 |
| 東京-品川 | 6350.878230165294 |
| 東京-渋谷 | 6455.104217184891 |
| 東京-池袋 | 7424.298478701934 |
| 東京-大崎 | 7733.106194261948 |
| 東京-北千住 | 8319.038187430975 |
| 東京-赤羽 | 11484.835519176353 |
| 東京-蒲田 | 13975.635963846811 |
| 東京-三鷹 | 18813.40814197637 |
| 東京-立川 | 32051.382378005477 |
| 東京-八王子 | 38789.35456665789 |
+------------------+--------------------+
指定した地点の最近隣の地点だけを取得するSQL
- 以下のSQLでは、東京駅から最も近い駅を取得しています。
- 上記の「指定した地点から距離の近い順に並べるSQL」とほぼ同じですが、
LIMIT句を使って抽出結果を上位1件だけに絞り込んでいます。
SQL
SELECT
CONCAT(st1.name, '-', st2.name) AS 区間,
ST_Distance(st1.location, st2.location) AS '距離(m)'
FROM
station AS st1,
station AS st2
WHERE
st1.name = '東京'
AND st1.id != st2.id
ORDER BY
`距離(m)` ASC
LIMIT 0,1;
実行結果
+---------------+--------------------+
| 区間 | 距離(m) |
+---------------+--------------------+
| 東京-新橋 | 1828.3847670426867 |
+---------------+--------------------+