MySQLで線やポリゴンの距離を測定してみよう

はじめに

前回はMySQLのSpatial機能を使って、点と点のあいだの距離を求める方法を紹介しました。今回は同じ手法が、線(LINESTRING)や面(POLYGON)でも使えることを紹介します。

今回使うデータ

前回と同じく、千葉県内のとある駅周辺の飲食店POINTデータを使います(再掲)。

mysql> SELECT id, name, ST_AsText(pos) FROM restaurant ;
+----+------------------------+-----------------------------+
| id | name                   | ST_AsText(pos)              |
+----+------------------------+-----------------------------+
|  1 | ラーメンT              | POINT(35.871958 140.010395) |
|  2 | ラーメンH              | POINT(35.871402 140.011747) |
|  3 | カレーC                | POINT(35.871836 140.011138) |
|  4 | ファストフードM        | POINT(35.872169 140.012188) |
|  5 | レストランC            | POINT(35.871424 140.010493) |
|  6 | カレーH                | POINT(35.873222 140.011038) |
+----+------------------------+-----------------------------+

加えて、LINESTRINGデータの例として、近くを走る国道のデータ（ごく一部を簡易的に表現したもの）を新たに登録しましょう。

まず道路専用のテーブルを作成し、その後1件のデータを登録します。

CREATE TABLE kokudou (
  id integer primary key auto_increment,
  name varchar(30),
  roadline GEOMETRY SRID 6668
);

INSERT INTO kokudou (name, roadline) VALUES 
 ('356',  ST_GeomFromText('LINESTRING(35.870317 140.014765, 35.870760 140.013982, 35.870854 140.013413, 35.871026 140.008256, 35.871391 140.007593)',6668));

ここで登録した国道データは、下図の青い線で表されるLINESTRINGです。僅か5点で表されていますが、良い感じに道路がトレースされていることが分かります。

 ST_Distance()で距離を計測

それでは、この国道(LINESTRING)データと、各店舗のPOINTデータの距離を計測してみましょう。前回と同様にST_Distance()関数を使います。引数として前回はPOINT型を2つ与えましたが、今回は片方がLINESTRING型、もう一方がPOINT型になります。

mysql> SELECT r.id, r.name, k.name, ST_AsText(r.pos), 
    ->        ST_DISTANCE(k.roadline, r.pos) d 
    ->   FROM restaurant r, kokudou k
    ->  WHERE k.name='356'
    ->   ORDER BY d;
+----+------------------------+------+-----------------------------+--------------------+
| id | name                   | name | ST_AsText(r.pos)            | d                  |
+----+------------------------+------+-----------------------------+--------------------+
|  5 | レストランC            | 356  | POINT(35.871424 140.010493) | 52.392548733349315 |
|  2 | ラーメンH              | 356  | POINT(35.871402 140.011747) | 54.590696364773756 |
|  3 | カレーC                | 356  | POINT(35.871836 140.011138) | 100.45373557786223 |
|  1 | ラーメンT              | 356  | POINT(35.871958 140.010395) | 111.23190762091102 |
|  4 | ファストフードM        | 356  | POINT(35.872169 140.012188) | 141.25490190922957 |
|  6 | カレーH                | 356  | POINT(35.873222 140.011038) | 253.74209784335673 |
+----+------------------------+------+-----------------------------+--------------------+
6 rows in set (0.045 sec)

国道と店舗を一緒の地図にプロットしたのが下図です。上のクエリと見比べてみてください。5番と2番が国道からほぼ同じくらいの距離、次いで3番、1番、4番、と、目視で確認したものとSQLの結果とは一致していることが分かります。

前述したとおり、LINESTRINGデータ自体はたった5つの点で構成されており、今回の店舗に近いあたりは、その「点と点の間」の線分の途中部分となります。このような点と線との最短距離を求めるのは自力で行おうとすると結構大変だと思いますが、MySQLはそれを内部でやってくれて、実際の線上と最近距離を返してくれるのはすごいと思いませんか。

絞り込みもできる

今回の実行例ではテーブルの全件を対象としましたが、もちろん絞り込みもできます。実際の開発要件では(今回の簡易的なテーブルには含まれていません)「ラーメン屋さんだけ」「カレーかファストフードだけ」のようなカテゴリによる絞り込み等により便利な検索機能を提供しますが、MySQLのSpatial関数を使うことで、それらに「距離」による絞り込み等の機能を追加できるようになります。

国道から120m以内の距離にあるお店を洗い出すクエリは以下のとおりです。先ほどのクエリに「AND ST_DISTANCE(k.roadline, r.pos) < 120」という条件を追加しただけです。

mysql> SELECT r.id, r.name, k.name, ST_AsText(r.pos), 
    ->        ST_DISTANCE(k.roadline, r.pos) d 
    ->   FROM restaurant r, kokudou k
    ->  WHERE k.name='356'
    ->    AND ST_DISTANCE(k.roadline, r.pos) < 120
    ->   ORDER BY d;
+----+------------------+------+-----------------------------+--------------------+
| id | name             | name | ST_AsText(r.pos)            | d                  |
+----+------------------+------+-----------------------------+--------------------+
|  5 | レストランC      | 356  | POINT(35.871424 140.010493) | 52.392548733349315 |
|  2 | ラーメンH        | 356  | POINT(35.871402 140.011747) | 54.590696364773756 |
|  3 | カレーC          | 356  | POINT(35.871836 140.011138) | 100.45373557786223 |
|  1 | ラーメンT        | 356  | POINT(35.871958 140.010395) | 111.23190762091102 |
+----+------------------+------+-----------------------------+--------------------+
4 rows in set (0.000 sec)

近いお店どうしのペアを求める

距離を返す関数の活用として、店舗どうしの距離を求める例を紹介します。店舗データのテーブルを自己結合し、それぞれのテーブルのPOINT型データの列を ST_Distance()関数に渡して距離を求めるものです。

以下の例は、互いの距離が100m未満であるものを抽出します。

mysql> SELECT r1.id, r2.id,
    ->        ST_Distance(r1.pos, r2.pos) d
    ->   FROM restaurant r1, restaurant r2
    ->  WHERE ST_Distance(r1.pos, r2.pos) < 100
    ->    AND r1.id < r2.id
    ->  ORDER BY d;
+----+----+-------------------+
| id | id | d                 |
+----+----+-------------------+
|  1 |  5 | 59.90848339559455 |
|  1 |  3 | 68.45154603322156 |
|  2 |  3 |  73.1011145197525 |
|  3 |  5 |  74.0459535998026 |
|  2 |  4 | 93.96213491417447 |
+----+----+-------------------+
5 rows in set (0.000 sec)

WHERE句の条件でr1.id < r2.idと指定しているのは、店舗Aから店舗Bの距離と 店舗Bから店舗Aの距離の両方の結果は不要なので、それを除外するための工夫です。この条件により、自店舗同士の距離(当然ゼロメートルになる)も除外できています。
カテゴリを指定して実施すれば「最も競合から遠いラーメン屋」を知ることができたり、逆に「ラーメン激戦区」のような分析にも応用できるかもしれませんね。

POLYGONでも使える

具体的な実行例は割愛しますが、ST_Distance()関数はPOINTとPOINT、POINTとLINESTRING以外にも、POINT/LINESTRING/POLYGONのすべての組み合わせで実施することができます(下表)。POINTとLINESTRINGの組み合わせで見たのと同様、LINESTRINGやPOLYGONを構成する点どうしに限らず、最も近くなる線上の場所どうしの距離を返してくれるのが特徴です。

表：ST_Distance(g1, g2)に与えることができる地理情報型の組み合わせ

【コラム】FOSS4G 2026 Hiroshima

前回のコラムで紹介した「FOSS4Gイベント」。主に国内の FOSS4Gイベントを中心に紹介しました。
アメリカ・シカゴに本拠地を置くOSGeo財団(Open Source Geospatial Foundation)が開催する世界最大級の地理情報イベントも、年1回開催されています。イベント名は「FOSS4G + 開催地名 + 年」とされるのが慣例で、2025年にはオークランドで開催されたので「FOSS4G Auckland 2025」という名称でした。このワールドワイドのFOSS4Gイベントは、この名称では2004年から開催されています。過去15年間の開催地を表に示しました。世界各地で開催されてきたことが分かります。

年/会議名	国	都市
2025	ニュージーランド	オークランド
2024	ブラジル	ベレン
2023	コソボ	プリズレン
2022	イタリア	フィレンツェ
2021	アルゼンチン	ブエノスアイレス(オンライン)
2020	カナダ	カルガリー
2019	ルーマニア	ブカレスト
2018	タンザニア	ダルエスサラーム
2017	アメリカ	ボストン
2016	ドイツ	ボン
2015	韓国	ソウル
2014	アメリカ	ポートランド(オレゴン州)
2013	イギリス	ノッティンガム
2012	中国	北京
2011	アメリカ	デンバー

そして、このワールドワイドのFOSS4Gイベントが2026年、ついに日本にやってきます！

⚫︎FOSS4G Hiroshima 2026
https://2026.foss4g.org/en/

全7日間の日程が組まれていますが、ワークショップが最初の2日間、コードスプリントが最後の2日間の日程で組まれており、いわゆるセミナー形式のカンファレンスは真ん中の3日間というスケジュールになっています。詳しい講演内容等は4月中下旬ごろに公開予定となっていますが、チケットはすでに販売されているので、ご興味のある方はぜひ足をお運びください。
海外に出ないと参加できなかったような国際会議がいよいよ日本にやってくる、国内で移動するだけで参加できる、というまたとない機会です。筆者もわくわくしています。