GTFS Advent Calendar 2日目の記事です。
今日はGTFSデータをデータベース(RDBMS)に格納するための手順を紹介しようと思います。

GTFSデータとRDBMS

GTFS(General Transit Feed Specification)は零細事業者の利用を視野に入れており、テキストエディタや表計算ソフトでの閲覧が容易なCSV形式を採用しています。具体的には経路情報(routes.txt)や便情報(trips.txt)のような情報の単位でCSVファイルが分かれています。

ホーム>政策・仕事>総合政策>公共交通政策>技術資料（仕様書、ガイドライン）（2019年３月）(国土交通省)

CSVファイル内のフィールドには他のCSVファイルのフィールドと関連付く項目があり、RDBMSにおける表結合の操作と相性が良さそうです。

というワケで、実際にGTFSデータをRDBMSに格納してSQLを介してGTFSデータを処理してみましょう。

RDBMS環境の準備

まずはRDBMS環境の準備です。Dockerを使用して手早く用意しましょう。
Dockerイメージ作成用のDockerfileは以下のリポジトリに用意してあります。

ValLaboratory/advcal/advcal/2019/gw/docker_env/

$ git clone https://github.com/ValLaboratory/advcal.git
$ cd advcal/2019/gw/docker_env/
$ docker-compose build gtfs_db

git cloneしたのち docker-compose build を実行します。

$ docker images
REPOSITORY  TAG     IMAGE ID      CREATED         SIZE
gtfs_db     latest  eab624c5d7f2  38 minutes ago  449MB

Dockerイメージが作成されるので、イメージからコンテナを起動します。

$ docker run --name mysql01 -e MYSQL_ROOT_PASSWORD=mysql -d -p 3306:3306 gtfs_db
$ docker exec -ti mysql01 mysql -uroot -p

上記の docker exec を実行すると、MySQLのコンソールにログインできます。
コンソール上から以下の手順を実行し、必要なデータベーススキーマとテーブルの作成、GTFSデータの投入を行います。
(必要なSQLファイルはDockerイメージ作成時にコンテナにコピーしています)

mysql> -- データベーススキーマを作成する。
mysql> source /tmp/setup_gtfs_db.sql
mysql>
mysql> -- ER図作成用のデータベースを作成し、テーブルを作成する。
mysql> use gtfs_db_reference
mysql> source /tmp/gtfs_reference.sql
mysql>
mysql> -- GTFSデータ格納用のデータベースを作成し、テーブルを作成する。
mysql> use gtfs_db
mysql> source /tmp/gtfs.sql
mysql> source /tmp/kitaena_gtfs_data.sql

これでRDBMSの準備は完了です。ER図作成用のデータベースは後のAdvent Calendarで解説します。
まずはGTFSデータ格納用のデータベースを触ってみます。

$ docker exec -ti mysql01 mysql -uroot -p
Enter password:
...中略...
mysql>
mysql> -- GTFSデータ格納用のデータベースに切り替えます。
mysql> use gtfs_db ;
mysql>
mysql> -- 適当なテーブルの定義を表示させてみます。
mysql> desc feed_info ;
+---------------------+-------------+------+-----+---------+-------+
| Field               | Type        | Null | Key | Default | Extra |
+---------------------+-------------+------+-----+---------+-------+
| feed_publisher_name | text        | NO   |     | NULL    |       |
| feed_publisher_url  | text        | NO   |     | NULL    |       |
| feed_lang           | varchar(16) | NO   |     | NULL    |       |
| feed_start_date     | varchar(12) | YES  |     | NULL    |       |
| feed_end_date       | varchar(12) | YES  |     | NULL    |       |
| feed_version        | text        | YES  |     | NULL    |       |
+---------------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql>
mysql> -- データを見てみる。
mysql> SELECT * FROM feed_info ;
+-----------------------------+---------------------------+-----------+-----------------+---------------+-------------------------------------------------+
| feed_publisher_name         | feed_publisher_url        | feed_lang | feed_start_date | feed_end_date | feed_version                                    |
+-----------------------------+---------------------------+-----------+-----------------+---------------+-------------------------------------------------+
| 北恵那交通株式会社          | http://www.kitaena.co.jp/ | ja        | 20190401        | 20200331      | 20190401_2019年04月01日（北恵那バス）           |
+-----------------------------+---------------------------+-----------+-----------------+---------------+-------------------------------------------------+
1 row in set (0.00 sec)

GTFSの各CSVファイル名をテーブル名、CSV内の各項目をテーブルのフィールドに対応させる形のテーブル定義となっています。そして、GTFSのデータとして北恵那交通株式会社のGTFSデータを格納しています。

これでGTFSを格納したDBが用意できました。

まとめ

CSVの形で提供されているGTFSデータをRDBMSに格納するための環境作成を行いました。
次回以降のAdvent Calendarでは、このDBを使ってGTFSデータの処理をいろいろ試してみようと思います。