GTFS Advent Calendar 2日目の記事です。
今日はGTFSデータをデータベース(RDBMS)に格納するための手順を紹介しようと思います。
GTFSデータとRDBMS
GTFS(General Transit Feed Specification)は零細事業者の利用を視野に入れており、テキストエディタや表計算ソフトでの閲覧が容易なCSV形式を採用しています。具体的には経路情報(routes.txt)や便情報(trips.txt)のような情報の単位でCSVファイルが分かれています。
CSVファイル内のフィールドには他のCSVファイルのフィールドと関連付く項目があり、RDBMSにおける表結合の操作と相性が良さそうです。
というワケで、実際にGTFSデータをRDBMSに格納してSQLを介してGTFSデータを処理してみましょう。
RDBMS環境の準備
まずはRDBMS環境の準備です。Dockerを使用して手早く用意しましょう。
Dockerイメージ作成用のDockerfileは以下のリポジトリに用意してあります。
$ 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データの処理をいろいろ試してみようと思います。