降りつぶし.net~同期するWebアプリ・スマホアプリの開発・運用~

第2回 駅データベースの構築と運用

この記事を読むのに必要な時間:およそ 3 分

路線テーブル:lines

路線テーブル:lines

l_id路線コード
line路線名
o_id事業者コード
l_type路線種別
enabled有効フラグ

路線コードは4バイト整数ですが,人力管理の名残りで,ソートした場合に

  • 前項の事業者種別順
  • 路線の地域順
  • 本線が先,支線が後

となるように割り振っています。

路線種別の詳細は省略しますが,鉄道事業者の種別よりも細かく,普通鉄道と路面電車を区別できるようにしてあります。

駅テーブル:stations

駅テーブル:stations

s_id駅コード
station駅名
yomi読みがな
wiki日本語版ウィキペディア項目名
pref都道府県コード(全国地方公共団体コード)
address住所(地方自治体以下の部分)
lat緯度
lng経度
o_id事業者コード
enabled有効フラグ
weight地図表示優先順位

駅コードは4バイト整数値で,後から開業する駅を後ろに追加しているため,数値の規則性はありません。

ウィキペディア項目名は,ウィキペディアの駅紹介ページへのリンクのために必要です。空文字列の場合,stationの値を項目名とし,そうでない場合,この値を項目名とします。これにより,⁠白石駅_(JR北海道)」「白石駅_(宮城県)」などの「同一名の別駅」や,隣接する「富山駅」の項目内に包含されている「富山駅北駅」などのケースにも対応できます。

緯度および経度は小数点以下6桁の固定小数点で,4バイト整数値として格納しています。Android Maps APIではこのままの値で,iOS Map Kit APIでは浮動小数点となるので1E6で除算して利用します。

地図表示優先順位は,表示時の重み付けです。たとえば日本全図を表示した場合,すべての駅をアイコン表示することは不可能であるため,この値が小さいものを優先して表示しています。つまりカラム名と内容が逆の意味となってしまっており,明らかな命名ミスです……。

この重み付けについては,後の回で改めて説明します。

駅-路線対応テーブル:stations_lines

駅-路線対応テーブル:stations_lines

sl_id駅-路線コード
l_id路線コード
s_id駅コード
s_sort表示順
enabled有効フラグ

駅と路線を対応付けるテーブルです。

ほとんどの路線には複数の駅が所属しています(例外的に,京成東成田線のように,分岐点が信号場のため,駅が1つしかない路線もあります⁠⁠。また,接続駅1つは複数の路線に属します。すなわち,m:nテーブルとなります。そのため,駅コードと路線コードの複合キーを主キーとすることも可能でしたが,独自の駅-路線コードを主キーとし,次々回に解説予定の同期機能において活用しています。

表示順は,⁠路線を選択し駅一覧を表示」した場合の順序を,昇順で設定しています。デフォルトは10の倍数で,間に駅が開業した場合に挿入可能としています。

例として,富山地方鉄道本線の一部を,s_sortで昇順ソートしてみます。新庄田中駅は昨年2012年末に新規開業した駅で,sl_idおよびs_idは他駅とはまったく異なる値です。またs_sortの末尾は5で,稲荷町駅と東新庄駅の間に挿入されたことがわかります。

富山地方鉄道本線の駅をソートした結果(一部)

sl_ids_sorts_idstation
831754250304425電鉄富山駅
831854260304426稲荷町駅
1030954265306008新庄田中駅
831954270304427東新庄駅
832054280304428越中荏原駅

乗下車記録テーブル:completions

乗下車記録テーブル:completions

u_idユーザID(Geeklogが発行)
s_id駅コード
comp_date乗下車日
update_date更新日時
memoメモ

ここまでのテーブルは管理者のみが操作しますが,これはエンドユーザの操作で更新されるテーブルです。工夫したのは次の3点です。

まず,乗下車日ですが,DATE型ではなく,4バイト整数値として管理します。実際に格納する値は,たとえば以下のとおりです。

乗下車日として記録される整数値とその意味

0未乗下車
1乗下車済(年月日不明)
197800001978年に乗下車済(月日不明)
198107001981年7月に乗下車済(日不明)
199609301996年9月30日に乗下車済

これにより,乗下車済駅の抽出は条件「!= 0」で可能となります。また,Webアプリ・スマホアプリの両方で,乗下車日の入力用UIと値の対応付けが平易となりました。

図2 乗下車日入力画面と乗下車日の値の生成。 Webアプリの記録入力画面では,年・月・日のプルダウンメニューに「不明」⁠頃」という項目を追加し,選ばれた場合に0とする。年が不明の場合は特別に全体で1とする

図2 乗下車日入力画面と乗下車日の値の生成。 Webアプリの記録入力画面では,年・月・日のプルダウンメニューに「不明」「頃」という項目を追加し,選ばれた場合に0とする。年が不明の場合は特別に全体で1とする

次に,同期機能のために必須の更新日時ですが,これにはDATETIME型を使わず,UNIX時間を整数値で格納しています。Webアプリで用いるMySQLにはTIMESTAMP型がありますが,これは2038年問題に対応していません。また現在,降りつぶし.net用のWebサーバは32ビットOSであるため,そもそも2038年問題に対応できません。

近い将来,OSを64ビット化するタイミングにおいて,このカラムを8バイト整数に置き換えるだけで問題を解決できることから,通常「悪手」とされる「UNIX時間を整数として格納する」という実装を敢えて採りました。このカラムの使用法は,次々回の同期機能の解説で紹介します。

最後に,駅コードは,駅テーブルstationsの外部キーとしています。これにより,同期機能などを通じて,存在しない駅コードの乗下車記録が挿入されてしまう事態を防いでいます。

駅データベースのメンテナンス

データメンテナンスは,以下の4項目について行います。

  • 新規開業した駅・路線・鉄道事業者の項目を追加
  • 移転した駅,改名した駅・路線・鉄道事業者の項目を修正
  • 廃止された,または長期休止となった駅・路線・鉄道事業者の項目の有効フラグを0に変更
  • 長期休止から復活した駅・路線・鉄道事業者の項目の有効フラグを1に変更

データベース構造から,これらの作業が簡易に行えることはおわかりいただけるかと思います。

ただし,これらを更新しただけでは,それをスマホアプリに通知することができません。そのために必要なのが更新情報テーブルupdatesですが,これも次々回の同期機能の解説において採り上げます。

更新情報の収集

現在は内容が安定してきた駅データベースですが,公開当初は漏れや誤りが多々ありました。幸い,掲示板やTwitterでのご指摘により,順次修正できました。

一方,新たな改変には困難が伴います。新線の開業や駅の廃止は全国ニュースで採り上げられ,ニュースサイトのチェックでほぼ情報が得られますが,問題は新駅の開業や改名です。これらの認可は,国交省のサイトで公開されません。全国ニュースにならないこともしばしばです。

結局,ウィキペディアの「20xx年の鉄道」項目(例として,今年2013年分を元にし,その裏付けを適宜取って行く,という手法に,現在は落ち着いています。

今後の予定

今回は,降りつぶし.netのための駅データベースの構造を解説しました。

次回は,このデータベースを活用し,最も早くから稼働しているWebアプリケーションの構築について解説します。また,データベースの一部のテーブルの機能については,次々回に予定している同期機能の解説の中で改めて触れたいと考えています。

著者プロフィール

よねざわいずみ

合資会社ダブルエスエフ代表社員。学習塾講師やら芸能ライターやら劇団主宰やらいろいろ経て現在はよろず請負プログラマ。最近の開発はPHP,JavaScript,Java,MTプラグインなど。お仕事随時募集中。

Twitter:@yonezawaizumi

鉄道旅行ブログ:http://feelfine.blog.izumichan.com/