如何制作一个简单的公交路线搜索引擎?

2021-12-15 00:00:00 sql database web mysql

[不是:e 用户在 铁路查询系统的开发，如何对列车、车站和停靠站进行建模? ]我的问题描述:

[Not:e user is asking this again at Development of railway enquiry system, how to model Trains, Stations and Stops? ] My Problem Description:

假设我在 ROUTE-1 中有一个 BUS-123，它将通过 A、B、C、D、E、F、G、H 和 BUS-321 在 ROUTE-2 通过 D、E、F、X、Y、Z.如果有人输入 B 作为源点，F 作为目标点，则结果中应显示带有 BUS-123 的 ROUTE-1.但是，如果有人输入 H 作为源，A 作为目的地，则不应显示结果，因为返回的结果可能与旅行的结果并不总是相同.但是如果一个人输入 A 作为源，输入 Z 作为目的地，那么 BUS-123 和 ROUTE-1 和 BUS-321 和 ROUTE-2 应显示.

Suppose I have a BUS-123 in ROUTE-1 it will travel through A, B, C, D, E, F, G, H and BUS-321 in ROUTE-2 through D, E, F, X, Y, Z . if someone enters B as a source point and F as a destination point then ROUTE-1 with BUS-123 should display in the result. But if someone enters H as a source and A as destination result should not display, because returning may not always same with one that is traveled. But if a person enters A as a source and Z as destination then BUS-123 with ROUTE-1 and BUS-321 with ROUTE-2 should display.

我的问题是:如何将该路线信息存储在数据库中?如果我像下面这样存储在 RDBMS 中

My Problem is: How do I store that route information in Database? if i store in RDBMS like the following

BUS_NUMBER   ROUTE_NUMBER    VIA_ROUTES
BUS-123      ROUTE-1         A, B, C, D, E, F, G, H
BUS-321      ROUTE-2         D, E, F, X, Y, Z

然后我的搜索将如何工作.我的意思是如何在字符串中搜索它.如果我将所有 VIA_ROUTES 存储在不同的不同列中，那么它将如何..?请用你自己的技术给我建议.这并不紧急，但我打算进行基本的巴士路线搜索，因此感谢您提供帮助的评论.

Then how my search will work. I mean how to search it in a string. And if I store all the VIA_ROUTES in different different columns then how it will be..? Please suggest me with your own technique. It is not urgent but I am planning to make a basic bus route search, so your comment with help is appreciated.

推荐答案

我将其建模为循环图.每个公交车站都由一个顶点表示.两个停靠点之间的每个直接连接都由标有路线编号的边表示；因此，每条路线都是一系列相连的边.使边缘也定向.并非所有从停靠站 A 到停靠站 B 的路线都必然也会从停靠站 B 沿另一个方向行驶到停靠站 A.

I'd model it as a cyclic graph. Each bus stop is represented by a vertice. Each direct connection between two stops is represented by an edge labelled with the route number; consequently, each route is a sequence of connected edges. Make the edges directed, too. Not all routes travelling from stop A to stop B will necessarily also travel from stop B to stop A in the other direction.

可能想用估计的旅行时间填充每条边，那条腿的一个(或多个)方差度量——在周日晚上的凌晨 2 点，方差可能很低，但在周五晚上的下午 5 点，它可能会很高，还有出发时间列表.

Probably want to populate each edge with the estimated travel time, a measure (or measures) of variance for that leg -- at 2am on a Sunday night, the variance might be low, but at 5pm on a Friday evening, it might be very high, and list of departure times as well.

然后是图遍历和找到最低成本"路线的问题，但是您选择定义最低成本"——您可能要考虑的因素包括:

Then its a matter of graph traversal and finding the "least cost" route, however you choose to define "least cost" -- Factors you might want to consider would include:

  • 总行程时间
  • 等待下一站出发的总时间.
  • 在任何一个站点等待时间.
  • 距离?

人们应该注意到，等待时间过长是不好的(在 1 月 -10 华氏度的时候花 40 分钟等公共汽车?).太少也不好，因为它增加了错过连接的可能性，因为公交车对当地交通状况的波动反应灵敏，因此它们的时刻表往往有相当大的可变性.

One should note that too much wait time is bad (ever spend 40 minutes waiting for a bus in January when it's -10 F?). Too little is bad, too, as it increases the probability of missing a connection, given that buses tend to have a fairly large variability to their schedules since they are highly responsive to fluctuations in local traffic conditions.

这就是我要做的.

不过，我不相信我会尝试直接在 SQL 中解决它.

I don't believe I'd try to solve it directly in SQL, though.

不过，该模型非常适合 SQL.您需要以下实体，然后是一些实体，因为您需要表示时间表等:

The model is a good fit for SQL, though. You need the following entities, and then some, since you'll need to represent schedules, etc.:

  • 停止.一个公共汽车站.图的顶点.
  • 路线.公交路线.
  • 细分.两站之间的直接联系.图的边.
  • RouteSegment. 表示组成路线的有序段序列的关联实体.
  • Stop. A Bus stop. The vertices of the graph.
  • Route. A bus route.
  • Segment. The direct link between two stops. The edges of the graph.
  • RouteSegment. An associative entity representing ordered sequence of segments that composes the route.

相关文章