Menu
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_8 but not stop_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
Updates Still looking for answer .Right now given answer has some points so offered bounty
250
You can use LIKE with OR operator which works same as IN operator.
MySQL allows you to specify multiple WHERE clauses. These clauses may be used in two ways: as AND clauses or as OR clauses. What is Operator? An operator is a special keyword used to join or change clauses within a WHERE clause.
Here's the generic SQL query to two compare columns (column1, column2) in a table (table1). mysql> select * from table1 where column1 not in (select column2 from table1); In the above query, update table1, column1 and column2 as per your requirement.
Yep. And it would be possible to use a check constraint to ensure that only one row exists (using Celko's technique here sqlmonster.com/Uwe/Forum.aspx/ms-sql-server/3453/…)
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; 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.
From symcbean in the comments, you could use the "OQgraph database engine" to do this too. There is an example of shortest path here.
45
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
