GTFS Advent Calendar 6日目の記事です。
今日はRDBMSでER図を作成する機能を応用し、GTFSデータの構造をちょっとだけ見やすくする方法を紹介しようと思います。
GTFS仕様におけるデータの関連
GTFSの仕様書では、各CSV内のフィールド間の関連を示す図が示されています(下記PDFファイルの5ページ目)。
例えば便情報(trips.txt)であれば、 route_id や service_id 、 shape_id といったフィールドが他のCSVファイル内のフィールドと紐づいていることが見て取れます。
この紐づき情報を活用し、RDBMSでのテーブル定義時に外部キーとしてフィールド定義することで、ER図等で各GTFSデータ間の関連を見やすくしてみようと思います。
GTFSのテーブル定義とER図作成
まずはテーブル定義です。テーブルと(完全ではありませんが)必要な外部キー定義を記載したSQLファイルを以下に用意しました。といっても、2日目の記事での環境構築時にこのSQLを実行しているので、すでにDockerイメージ内のMySQLには定義が入った状態になっています。
つぎはこのテーブル定義からER図を作成してみます。MySQLにはMySQL WorkbenchというGUIツールがあり、このツールが提供する機能の一つにテーブル定義からER図を作成するというものがあります。
さっそくER図の作成機能を使ってみましょう。
MySQL WorkbenchからGTFSデータが格納されたDBに接続した状態で以下の手順を実行します。
ホーム画面から、"Models"-"Create EER Model from Database"を選択します。
ER図の作成対象とするデータベースは「gtfs_db_reference」を選択します。
(gtfs_reference.sqlでテーブル定義を作成したDBになります)
あとはダイアログの右下にある"Next"ボタンを押してゆくだけでOKです。
すると以下のような感じでER図が生成されます。テーブル定義にマウスカーソルを合わせると、主キー・外部キーになっているフィールドがそれぞれ青色と緑色で表示され、どのテーブルと関連しているかが把握しやすくなります。
実際にこれらの外部キー設定がある状態でGTFSデータをDBに投入しようとすると、制約が厳しすぎてエラーになってしまう場合があります(そもそも元のデータの段階でIDとなるレコードが重複していることもある)。そのため、まずは制約を付けない状態のテーブルを定義して利用するのが良さそうです。
(2日目の記事で紹介したRDBMS環境で gtfs_db と gtfs_db_reference という2つのDBを用意しているのはこのためです)
まとめ
GTFSのテーブル定義に外部キー設定を加え、RDBMSのツールと連携させることでER図を生成してみました。
あらかじめ外部キーという形でテーブル間の関連を定義しておく必要があり、それがちょっと面倒ではありますが、GTFSについては最初にある程度テーブル定義と外部キーの関連を記載したSQLを用意しておき、仕様の追加・変更に応じてリファインメントするようにしておくと、後からGTFSの仕様を理解しようとする人の助けになりそうな気がします。