PHP+MySQLで、電車の進路を判定する良い方法は何ですか？

この度、電車の到着予定時刻の算出機能を実装することになりました。

そこまで厳密にする必要はなく、目的駅までの進路にチェックポイント座標をいくつか設けていて、
いずれかの座標周辺に到着したら、チェックポイント座標番号に応じて、駅まで残り何分と表示する感じです。
予定時刻より到着が遅れても特に問題ないです

まず、以下の要件があります。

要件

電車の止まる駅は2つだけ。始発駅と終着駅。途中下車はないものとする(ケーブルカーみたいなイメージです)
始発駅or終着駅に止まったら、ぐるりとUターンして、停車する。
しばらく停車した後、始発駅→終着駅or終着駅→始発駅に向けて発進する。
電車の現在地座標は、GPSにより10秒ごとにDBに記録される
到着予定時刻を表示するのは、目的の駅に向かってくる電車についてのみとする。
始発駅から見て、帰りの電車と、終着駅から見て、行きの電車で表示する(電車が離れていくのに、残り何分って出ても困りますもんね)
始発駅から発車したが、何らかのトラブルで終着駅にたどり着かず、途中でUターンして帰ってきましたみたいなイレギュラー要素は考えないものとする
GPSのバッテリー切れにより座標記録が途絶えるといったケースは発生しないものとする

DBテーブル定義

今、用意してあるDBテーブルは次の2つです。
(守秘義務に関わるため、多少実際のテーブル構造、データと異なります)

1. 現在地情報

mysql> DESC locations;
+--------------+------------------+------+-----+-------------------+----------------+
| Field        | Type             | Null | Key | Default           | Extra          |
+--------------+------------------+------+-----+-------------------+----------------+
| id           | int(10) unsigned | NO   | PRI | NULL              | auto_increment |
| train_number | smallint(6)      | NO   |     | NULL              |                |
| latitude     | double(9,7)      | NO   |     | NULL              |                |
| longitude    | double(10,7)     | NO   |     | NULL              |                |
| created_at   | timestamp        | NO   |     | CURRENT_TIMESTAMP |                |
+--------------+------------------+------+-----+-------------------+----------------+

mysql> SELECT * FROM locations ORDER BY created_at DESC LIMIT 3;
+--------+--------------+------------+-------------+---------------------+
| id     | train_number | latitude   | longitude   | created_at          |
+--------+--------------+------------+-------------+---------------------+
| 716276 |            2 | 35.6889590 | 139.7567090 | 2020-10-05 10:02:56 |
| 716275 |            1 | 35.6841900 | 139.7558600 | 2020-10-05 10:02:19 |
| 716274 |            1 | 35.6841920 | 139.7558670 | 2020-10-05 10:02:04 |
+--------+--------------+------------+-------------+---------------------+

2. 電車マスタ

mysql> DESC trains;
+--------------+------------------+------+-----+-------------------+----------------+
| Field        | Type             | Null | Key | Default           | Extra          |
+--------------+------------------+------+-----+-------------------+----------------+
| id           | int(10) unsigned | NO   | PRI | NULL              | auto_increment |
| train_number | smallint(6)      | NO   |     | NULL              |                |
| name         | varchar(30)      | YES  |     | NULL              |                |
| created_at   | timestamp        | NO   |     | CURRENT_TIMESTAMP |                |
| deleted_at   | timestamp        | YES  |     | NULL              |                |
+--------------+------------------+------+-----+-------------------+----------------+

SELECT * FROM trains LIMIT 3;
+----+--------------+----------+---------------------+------------+
| id | train_number | name     | created_at          | deleted_at |
+----+--------------+----------+---------------------+------------+
|  1 |            1 | かしわ号 | 2020-10-04 18:27:15 | NULL       |
|  2 |            2 | もみじ号 | 2020-10-04 18:27:15 | NULL       |
|  3 |            3 | さくら号 | 2020-10-04 18:27:15 | NULL       |
+----+--------------+----------+---------------------+------------+

電車が通電中であれば、走行状態に関わらず、
10秒毎に現在地情報テーブルにレコードがINSERTされていくイメージです

何がしたいか

電車の進行方向を判定したいです。
行きの電車(始発駅→終着駅)なのか、帰りの電車(終着駅→始発駅)なのか。

今、私が考えているやり方は、次の通りです。

帰りの電車の判定方法

1 始発駅の手前の部分に、チェックポイント座標をひとつ用意

2 電車の現在地座標がチェックポイント座標を中心点とした、
半径50メートル以内の円に入った時、DBからその列車に関する直近2つのレコードを取得

mysql> SELECT * FROM locations WHERE train_number = 1 ORDER BY created_at DESC LIMIT 2;
+--------+--------------+------------+-------------+---------------------+
| id     | train_number | latitude   | longitude   | created_at          |
+--------+--------------+------------+-------------+---------------------+
| 716275 |            1 | 35.6841900 | 139.7558600 | 2020-10-05 10:02:19 |
| 716274 |            1 | 35.6841920 | 139.7558670 | 2020-10-05 10:02:04 |
+--------+--------------+------------+-------------+---------------------+

3 2点のレコードについて、目的地座標までの距離を三平方の定理などを使って計算。
※最新のレコードの距離の値が、二つ目のレコードよりも小さい時、駅に向かってきていると見なす

4 電車マスタの対象電車について、帰りの電車フラグをセットする

mysql> UPDATE trains SET is_return = true WHERE train_number = 1;

行きの電車の判定は同じように、終着駅の手前で、
距離が近づいてきているかを計算して、帰りの電車フラグをfalseにするみたいな。

…以上のような方法で考えているのですが、どう思いますか？

何かこう、進行方向テーブルのようなものを別に作って管理すべきでしょうか。

このようなテーブル設計は初めてなので、いかんせんベストプラクティスがわからず、
調べても求めている情報が見つからなかったので質問させて頂きました次第です。

経験豊富な皆様、どうかアドバイスよろしくお願いいたします。

hentaiman

2020/10/13 06:12

調べて出てきた「期待する情報ではなかったもの」とはどんなものですか？あえて自作しようとした理由はありますか？

tabuu

2020/10/13 06:20 編集

線路はクロスすることありますか？時刻表が無いのでなんともですが単純に一定時間(1分とか)駅の座標にいるかどうかで判定ではダメ？ (追記)やりたいことだけに反応したので進行方向を求めて、どのようにしたいかまで書いていただくとより良い回答があると思います。

qwe001

2020/10/13 06:20

ご返信ありがとうございます。まず、参考にしたのは駅データというサイトです → https://ekidata.jp/doc/station.php 私が期待していた情報は、進行方向フラグのテーブル定義です。boolean型にすべきなのか、intなのか、それ以外か。はたまた、そのフラグの反転方法についての、皆様のアイデアを教えて頂きたいです。

hentaiman

2020/10/13 06:29

なるほど。一応自作しようとした理由を聞きたいんですが・・・質問に書いてある駅の設定も、質問用とか勉強用（もしくは課題用）の仮想のものかも分かんないですし。自作したい理由が薄ければ、リアルな路線の事であればG社かY社のAPI使うというのがベストになるからです。

qwe001

2020/10/13 06:38

tabuu様入れ違いとなり申し訳ございません。線路のクロスはないです。線路は一本だけです。 >単純に一定時間(1分とか)駅の座標にいるかどうかで判定ではダメ？停車駅フラグを持たせるということでしょうか？それですと、発進する時にフラグが反転して、離れていくのに残り時間が表示されることになりそうな気がしますがいかがですか。残り時間を表示したいのは、目的地に向かってくる電車のみです。離れていく電車には表示しないようにしたいです

qwe001

2020/10/13 07:06

hentaiman様またも入れ違いで申し訳ございません…。勉強用ではなく、実務の質問になります。守秘義務があるので詳しくはお伝えできませんが、ゴルフカートのように敷地内の一定区間を往復で走る小型車両です。便宜上、電車と形容しましたが、実際は自動車です。いわゆる路面電車ではないので、ご提示頂いている他社様のAPIは使う予定がありません。

行動規範の内容に同意します

回答1件

進行方向テーブルのようなもの

さすがに作らないと管理できませんよ。

項目名	値	内容
cart_no	1	車番号
from_id	716274	開始地点id
to_id	716275	終了地点id
latitude	35.6841910	現在地点(緯度)
longitude	139.7558620	現在地点(経度)
bef_latitude	35.6841911	前回地点(経度)
bef_longitude	139.7558630	前回地点(経度)
progress	1	進行方向(0:停止/1:Toへ移動/-1:Fromへ移動)
lastupdate	2020-10-05 10:02:05	最終更新日時(車の座標を取得した日時)
beflastupdate	2020-10-05 10:02:04	前回最終更新日時
changeprogress	2020-10-05 10:00:00	進行方向が変化したときの日時