GTFS Advent Calendar 3日目の記事です。
今日はGTFSデータをSQL文に変換する方法を紹介しようと思います。

2日目の記事では、GTFSデータをRDBMSに格納する手順を紹介しました。その際、あらかじめGTFSデータをSQLに変換しておき、それをMySQLに流し込むという方法でデータを用意していました。
具体的なSQLファイルは以下になります。

ValLaboratory/advcal/2019/gw/docker_env/docker/gtfs_db/script/kitaena_gtfs_data.sql

用意したRDBMS環境では、GTFSのファイル(CSVファイル)名をDBのテーブル名にし、CSVのフィールドをテーブルのフィールド名に対応付けているので、比較的簡単にGTFSデータをSQLに変換できています。
変換用のスクリプトは以下の場所に用意してあります。

ValLaboratory/advcal/2019/gw/docker_env/script/gtfs_csv2sql.rb

GTFSデータを展開したディレクトリで gtfs_csv2sql.rb を実行するとSQLが生成されます。

$ git clone https://github.com/ValLaboratory/advcal.git
$ cd advcal/2019/gw/docker_env/script
$
$ # 北恵那交通株式会社(http://www.kitaena.co.jp)のGTFSデータ。
$ curl -s -o kitaena.zip 'http://www.kitaena.co.jp/info/GTFS%282019-03-13_1606%29.zip'
$ unzip kitaena.zip
Archive:  kitaena.zip
 extracting: agency.txt
 extracting: agency_jp.txt
 extracting: calendar.txt
 extracting: calendar_dates.txt
 extracting: fare_attributes.txt
 extracting: fare_rules.txt
 extracting: feed_info.txt
 extracting: routes.txt
 extracting: shapes.txt
 extracting: stops.txt
 extracting: stop_times.txt
 extracting: translations.txt
 extracting: trips.txt
$
$ # SQLを生成します。
$ ruby gtfs_csv2sql.rb
  INSERT INTO routes(route_id,agency_id,route_short_name,route_long_name,route_desc,route_type,route_color,route_text_color) VALUES ('4033','3200001023316','','馬籠線[上り・中切経由]','','3','FF0080','FFFFFF') ;
  INSERT INTO routes(route_id,agency_id,route_short_name,route_long_name,route_desc,route_type,route_color,route_text_color) VALUES ('2006','3200001023316','','加子母線[下り]','','3','FF0080','FFFFFF') ;

適当なファイルに出力して...。

$ ruby gtfs_csv2sql.rb > kitaena.sql

MySQLからSQLを読み込ませれば完了です。

$ docker cp kitaena.sql mysql01:/tmp
$ docker exec -ti mysql01 mysql -uroot -p
mysql>
mysql> -- SQLファイルを読み込ませる。
mysql> source /tmp/kitaena.sql
...中略...
mysql> -- 投入されたデータを確認する。
mysql> SELECT * FROM fare_attributes LIMIT 10 ;
+---------+-------+---------------+----------------+-----------+-------------------+
| fare_id | price | currency_type | payment_method | transfers | transfer_duration |
+---------+-------+---------------+----------------+-----------+-------------------+
| 170_00  |   170 | JPY           | 0              | 0         |              NULL |
| 180_00  |   180 | JPY           | 0              | 0         |              NULL |
| 190_00  |   190 | JPY           | 0              | 0         |              NULL |
| 200_00  |   200 | JPY           | 0              | 0         |              NULL |
| 210_00  |   210 | JPY           | 0              | 0         |              NULL |
| 220_00  |   220 | JPY           | 0              | 0         |              NULL |
| 230_00  |   230 | JPY           | 0              | 0         |              NULL |
| 240_00  |   240 | JPY           | 0              | 0         |              NULL |
| 250_00  |   250 | JPY           | 0              | 0         |              NULL |
| 260_00  |   260 | JPY           | 0              | 0         |              NULL |
+---------+-------+---------------+----------------+-----------+-------------------+
10 rows in set (0.00 sec)

mysql>

これでGTFSとして提供されているデータをRDBMSに格納することができました。

まとめ

GTFSデータをSQLに変換するスクリプトを紹介しました。
RDBMSにデータを格納することで、CSVの形ではちょっと煩雑になりがちなデータの結合や抽出等も、SQLクエリの実行という形で楽に処理できそうです。