MySQLで学ぶGIS入門第24回

MySQLの「ST_Length()」関数で線の長さを測ってみよう

第24回の今回は、「ST_Length()関数」を使ったLineStringの長さの計測方法と、全国の鉄道路線の総延長ランキングや駅の長さ比較への応用について解説します。

坂井恵 (さかいけい)

6:30

はじめに

前回は「ST_Union()」関数を使って、POLYGONを合体させる方法を紹介しました。今回は、線の長さを計測する「ST_Length()」関数を紹介します。

ST_Length()は、鉄道路線の総延長を計算したり、道路の長さを比較したりと、様々な場面で活用できます。

ST_Length()の基本

ST_Length()は、LineStringの値を与えると、その長さを返す関数です。まずはデカルト座標系でシンプルな例を見てみましょう。

mysql> SET @g1=ST_GeomFromText('LINESTRING(0 0, 3 4)');

mysql> SELECT ST_Length(@g1);
+----------------+
| ST_Length(@g1) |
+----------------+
|              5 |
+----------------+
1 row in set (0.000 sec)

原点(0,0)から点(3,4)への線分の長さが5と返ってきました。(0,0)から(3,4)というのは、各辺の長さが3,4,5の三角形の斜辺に相当するものですから、正しい結果が返ってきたと判断できます（三平方の定理（√(3^2+4^2) = 5））。

ST_Length()関数は LineString の長さを返すものなので、もちろん2つの点による線分の長さだけでなく、3点以上から構成される折れ曲がった線全体の長さを測ることもできます。先ほどの三角形の斜辺ではない部分、つまり上図の赤線部分（(0,0)から(3,0)を経由して(3,4)に至る線）の長さを求めてみましょう。

mysql> SET @g2=ST_GeomFromText('LINESTRING(0 0, 3 0, 3 4)');
mysql> SELECT ST_Length(@g2);
+----------------+
| ST_Length(@g2) |
+----------------+
|              7 |
+----------------+
1 row in set (0.000 sec)

正しく 2本の線分要素の合計となる、3 + 4 = 「7」という結果を得られたことが確認できました。

地理座標系ではメートル単位で結果を得られる

ここまではデカルト座標系（SRIDなし）での例でしたが、ST_Length()では当然のように地理座標系（SRID付き）のデータに対しても長さを求めることができます。これまでの連載の中で登録した鉄道路線データ(railroadテーブル)を例に試してみましょう。

テーブルにはさまざまな路線のデータが入っているので、ここではその中から常磐線のデータをターゲットとして見てみることにします。

まず、路線データの特徴を以下のクエリで確認してみると、常磐線のデータだけで、なんと183行もありました。どうやら、路線データは「路線毎に1本の LineString」というわけではなさそうです。

SELECT * FROM railroad where n02_003='常磐線';

実際にデータの中身を見てみると、ブチブチに切れたLineStringであることが分かります。2点のみからなるLineStringから、何点も経由したLineStringまでさまざまありますね。

今回のテーマである ST_Length()関数を使って、適当な5件の長さを見てみたものが以下の実行結果です。

mysql> SELECT n02_003, n02_004, ST_Length(SHAPE) AS length_meter
    ->   FROM railroad
    ->  WHERE n02_003 = '常磐線'
    ->  LIMIT 5;
+-----------+-----------------------+--------------------+
| n02_003   | n02_004               | length_meter       |
+-----------+-----------------------+--------------------+
| 常磐線    | 東日本旅客鉄道        |  4812.998228976675 |
| 常磐線    | 東日本旅客鉄道        |  118.2854401664412 |
| 常磐線    | 東日本旅客鉄道        |  4028.390181666361 |
| 常磐線    | 東日本旅客鉄道        | 174.78748615680968 |
| 常磐線    | 東日本旅客鉄道        | 4285.9338842737125 |
+-----------+-----------------------+--------------------+
5 rows in set (0.005 sec)

何キロもある長いものから、100メートル程度の短いものまで存在しています。個別データを詳細に確認したところ、どうやら「駅」のエリアで1つのLineStringとなるようにLineStringが設定されており、そのためブチブチになっていることが分かりました。

なお、1つ1つはブチブチですが、全体を通してみると、下図のように日暮里駅から仙台近くの岩沼駅まで1本の線で繋がっていることが分かります。

先ほどのクエリで ST_Length()の結果はメートルで得られましたが、一方のPostGIS実装では地理座標系で何も考えずにST_Length()を使用すると「度」の単位で結果が返ってきます。度で得た結果は場所や向きによって長さが異なるため、長さには直結しません。MySQLの「メートル」を返すこの仕様は、直感的で使いやすいと言えるでしょう。

路線別の総延長ランキング

1つの路線が複数の行に分かれているのを見て、「ST_Length()とSUM()を組み合わせれば、路線ごとの総延長を計算できそうだ」と思いつきますね。実際に全国の鉄道路線データで試してみましょう。

mysql> SELECT n02_003 AS 路線名,
    ->        n02_004 AS 運営会社,
    ->        COUNT(*) AS 区間数,
    ->        ROUND(SUM(ST_Length(SHAPE))/1000, 1) AS 総延長_km
    ->   FROM railroad
    ->  GROUP BY n02_003, n02_004
    ->  ORDER BY SUM(ST_Length(SHAPE)) DESC
    ->  LIMIT 15;
+--------------------+-----------------------+-----------+--------------+
| 路線名             | 運営会社              | 区間数    | 総延長_km    |
+--------------------+-----------------------+-----------+--------------+
| 山陰線             | 西日本旅客鉄道        |       342 |        678.3 |
| 東北新幹線         | 東日本旅客鉄道        |        51 |        675.2 |
| 東北線             | 東日本旅客鉄道        |       400 |        605.5 |
| 山陽新幹線         | 西日本旅客鉄道        |        46 |        555.5 |
| 山陽線             | 西日本旅客鉄道        |       332 |        552.1 |
| 東海道新幹線       | 東海旅客鉄道          |        44 |        518.6 |
| 奥羽線             | 東日本旅客鉄道        |       275 |        508.1 |
| 函館線             | 北海道旅客鉄道        |       224 |        478.5 |
| 日豊線             | 九州旅客鉄道          |       258 |          475 |
| 東海道線           | 東海旅客鉄道          |       231 |        371.5 |
| 根室線             | 北海道旅客鉄道        |       123 |        362.2 |
| 常磐線             | 東日本旅客鉄道        |       183 |        355.8 |
| 予讃線             | 四国旅客鉄道          |       199 |        325.4 |
| 鹿児島線           | 九州旅客鉄道          |       233 |        300.6 |
| 北陸新幹線         | 西日本旅客鉄道        |        23 |        294.2 |
+--------------------+-----------------------+-----------+--------------+
15 rows in set (0.087 sec)

山陰線が約678kmでトップでした。新幹線よりも長い路線なのは驚きですね。

続いて、東北新幹線が約675km、東北線約606kmと長大路線が並びます。新幹線は総延長の割に区間のレコード数が少ない（＝駅が少ないためと考えられる）のも見て取れます。東海道新幹線と山陽新幹線は、利用者目線では「合わせて1本」という感覚ではありますが、データの取り扱い上は別々になっているのですね。合わせると1070kmを越える超長路線です。

私はそれほど鉄道に詳しいわけではないのですが、路線長が上位のものを見ると新幹線以外ではいわゆる「○○本線」と呼ばれている長大路線が並んでいることが分かります。そんな中に突如顔を出す常磐線。全国的に見ても、なかなか長い路線なのです。

駅データの「長さ」

ところで、路線データとして公開されているデータ群の中には、駅も含まれています。筆者は当初、駅の形がポリゴンとして表現されていることを期待していたのですが、内容を見るとLineStringとして定義されていました。これは「路線の中での駅の範囲」を線として表現しているためです。

では、駅のデータからも「長い駅」を探してみましょう。結果のエイリアスを「駅長」としましたが、これは駅の幅ではなく、長さのことです。

mysql> SELECT n02_005 AS 駅名,
    ->        n02_003 AS 路線名,
    ->        ROUND(ST_Length(SHAPE), 1) AS 駅長_m
    ->   FROM station
    ->  WHERE n02_005 IS NOT NULL
    ->  ORDER BY ST_Length(SHAPE) DESC
    ->  LIMIT 10;
+-----------------+----------------------+----------+
| 駅名            | 路線名               | 駅長_m   |
+-----------------+----------------------+----------+
| 新宿三丁目      | 13号線副都心線       |    817.2 |
| 上野            | 東北線               |    726.8 |
| 宇都宮          | 日光線               |    628.0 |
| 宇都宮          | 東北線               |    628.0 |
| 関西空港        | 空港線               |    596.1 |
| 関西空港        | 関西空港線           |    596.1 |
| 徳山            | 山陽新幹線           |    513.3 |
| 三河安城        | 東海道新幹線         |    511.5 |
| 小山            | 東北線               |    478.0 |
| 小山            | 東北新幹線           |    477.1 |
+-----------------+----------------------+----------+
10 rows in set (0.023 sec)

副都心線の新宿三丁目駅が800m以上の長さがあるというのは、意外なものがトップに来ましたね。

筆者は当該駅に関する情報は持ち合わせておらず、検索しても鉛直方向の複雑性に対する記述ばかりで、駅自体の長さに関する情報は見つけられませんでした。かなり長さを感じる駅なのでしょうか。利用されている方の話を聞いてみたいものです。

その他はターミナル駅や新幹線の駅などで、納得の結果とも言えます。

ST_Length()は LineString専用

ST_Length()が線の長さを求めるものであるならば、Polygonを構成する境界線の長さも得られることを期待するかもしれません。しかし、残念ながらST_Length()は LineString専用です。LineString以外の、例えばPolygonのジオメトリを引数として与えた場合はエラーにはならずに NULLが返されます。

mysql> SELECT ST_Length(ST_GeomFromText('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))')) len;
+------+
| len  |
+------+
| NULL |
+------+

Polygonの周長を求める関数としてPostGISには ST_Perimeter()がありますが、MySQLにはこの関数が実装されていません。MySQLの地理情報機能の多くは「Boost.Geometry」という外部ライブラリに頼っているため、呼び出し部分を実装すれば、MySQLで使えるGIS関数を増やすことができそうです。

実際に、筆者もこの ST_Perimeter()関数が欲しくてパッチを書いて提案しましたが、残念ながら提案から半年経過しても、開発陣から反応をもらうに至っていません。

現在は、本体に取り込んでもらうことを諦めて、MySQL Plugin機能による関数実装を進めています。このプラグインについては、いずれ本連載の中で改めて紹介したいと思います。

おわりに

今回は、ST_Length()関数を使って LineStringの長さを計測する方法を紹介しました。MySQLでは地理座標系のデータに対してメートル単位で結果が返るため、換算の手間なく直感的に使えます。

鉄道路線の総延長ランキングや駅の「長さ」比較など、データを眺めているだけでも楽しい発見がありますね。

なお、本稿の鉄道データは「国土交通省国土数値情報ダウンロードサイト」よりダウンロードしたものを使用しています。

【コラム】常磐線に乗ってみた！

　本文でも紹介した長大路線、常磐線。今年６月に仙台で開催されたオープンソースカンファレンスに参加するために、乗ってきました。以前にも2、3回ほど仙台から土浦や我孫子付近までを乗ったことはあるのですが、GPSロガーを持ち歩くようになってからは初めての乗車です。

ご存じの方も多いと思いますが、常磐線は2011年の東日本大震災以後一部区間が運休となり、上野から仙台までを通貫して乗車できない期間がありました。その期間まさに9年間。2020年にようやく全線運転が再開されたのです。

仙台でのイベントへの参加が決まってから、時刻表とにらめっこして現実的な旅程を組むことができたので実施に至りました。旅程は我孫子駅の隣駅から出発して仙台経由、上野経由で我孫子駅に至るといういわゆる1周ルートです。乗車券は、一見すると隣駅に行くだけなのに1万円以上もする、ちょっとトリックのような切符です。