GTFS Advent Calendar 3日目の記事です。
今日はGTFSデータをSQL文に変換する方法を紹介しようと思います。
2日目の記事では、GTFSデータをRDBMSに格納する手順を紹介しました。その際、あらかじめGTFSデータをSQLに変換しておき、それをMySQLに流し込むという方法でデータを用意していました。
具体的なSQLファイルは以下になります。
用意したRDBMS環境では、GTFSのファイル(CSVファイル)名をDBのテーブル名にし、CSVのフィールドをテーブルのフィールド名に対応付けているので、比較的簡単にGTFSデータをSQLに変換できています。
変換用のスクリプトは以下の場所に用意してあります。
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クエリの実行という形で楽に処理できそうです。