RDBMSに格納したGTFSデータから時刻表を作成してみる

GTFS Advent Calendar 7日目の記事です。
今日はRDBMSに格納したGTFSデータに対してSQLクエリを実行し、時刻表を作成する手順を紹介しようと思います。

6日目の記事で作成したER図を眺めてみると、どうやら以下の4つのテーブルの情報を組み合わせると時刻表が作成できそうです。

さっそくSQLクエリを作成してみましょう。経路ID( route_id )と便ID( trip_id )、そして標柱ID( stop_id )をそれぞれ組み合わせることになります。「ある経路を走っている便と、その便の停留所(標柱)をまとめたものが時刻表だよね」という話になります。

SELECT
   r.route_short_name AS 経路略称,
   r.route_long_name AS 経路名,
   t.trip_id AS 便ID,
   t.trip_short_name AS 便名称,
   t.trip_headsign AS 便行先,
   s.stop_id as 停留所・標柱ID,
   s.stop_name as 停留所・標柱名称,
   st.arrival_time as 到着時刻,
   st.departure_time as 出発時刻
  FROM
    stop_times AS st,
    stops AS s,
    trips AS t,
    routes AS r
  WHERE
    r.route_id = t.route_id
    AND t.trip_id = st.trip_id
    AND st.stop_id = s.stop_id
  ORDER BY
    st.trip_id,
    st.arrival_time
  ;

このSQLクエリを実行すると、以下の結果が得られます。何となくそれっぽい結果ですが、ホントに時刻表の形になっているのでしょうか？

北恵那交通株式会社のGTFSデータを用いた探索はGoogleマップからも行えるので、その探索結果を使って答え合わせ(？)してみます。

9:30 に中津川駅前を出発して中京大学口に行くバスをGoogleマップ上で探索し、その結果と先のSQLクエリの結果を照らし合わせると良さそうです。

SQLクエリの結果と一致しています。ちゃんと時刻表が作成できていますね！

まとめ

RDBMSに格納したGTFSデータに対してSQLクエリを実行し、時刻表を作成する方法を紹介しました。
GTFSデータは経路情報や便情報、通過時刻情報といった単位でファイル(CSV)が分けられていますが、IDをキーにしてデータをまとめる(RDBMS的には表結合する)ことで、比較的理解しやすい形でデータを処理できそうです。