Project Aim :
We are developing bus timing Api where user will search for buses.
Following are my table structure
I have following tables
buses
id | bus_name Description of table: Store all buses Names
routes
id | route_name Description of table: Store All city names
stops
id | stop_name Description of table: All stop names
stop_orders
id | route_id | stop_id | stop_order Description of table: here i will assign stops for city and stop_order column help to identify which stop next to each other
bus_timing
id | stop_order_id | bus_id | bus_timing | trip | trip_direction Description of table: Here i will assign buses for route stops along with time and trip and direction
Output Expecting:
When user search between source to destination with time then Api must return all buses list with time
if direct buses not there then interconnected buses should show
For example if user search between stop_8 to stop_18 with 01:00:00 to 12:00:00 then all buses list with time should show.if direct buses not there to travel between two stops then interconnected link buses list should show
Output what i got is
PHP compare associative array based on condition
Present return result issue is
It will return all buses even though if bus is only travel to
stop_8but notstop_18.But my result must return only those buses which will travel between two stops i mean it must fall between both stops .Even i have no idea how to find interconnected buses list
When time range is long then there is chance of same bus will travel(trip and direction) multiple times
for Sql file https://github.com/codeforfungit/bustiming
2 Answers
Answers 1
Because stop_id cannot be two different values in the same row.
Aggregation is one way to do what you want:
SELECT b.bus_name FROM buses b JOIN route_connect rc ON rc.busid = b.id JOIN stops s ON s.id = rc.stop_id GROUP BY b.bus_name HAVING SUM( s.stop_name = 'Sydney' ) > 0 AND SUM( s.stop_name = 'Melbourne' ) > 0; This returns buses that have stops with the name of both cities.
Given that buses can have lots of stops, it might be more efficient to do:
SELECT b.bus_name FROM buses b JOIN route_connect rc ON rc.busid = b.id JOIN stops s ON s.id = rc.stop_id WHERE s.stop_name in ('Sydney', 'Melbourne') GROUP BY b.bus_name HAVING COUNT(DISTINCT s.stop_name) = 2; Answers 2
Also if buses are not directly travel between two city then i need to show inter connected buses.
That's a massive problem in a class of problems called routing problems.. For it, you need a better tool: consider migrating or integrating PostgreSQL, and examining PgRouting specifically you'll likely want Dijkstra's Shortest Path. PgRouting runs atop the PostGIS extension.
Or, consider working on integrating with Esri.
Alternatively you can mess around with this, but I wouldn't advise it.
OQgraph (update)
From symcbean in the comments, you could use the "OQgraph database engine" to do this too. There is an example of shortest path here.
0 comments:
Post a Comment