I've had a look at this - am going to go back to it tomorrow, but I think you're going to have to think of every route as a number of sub-routes. Route A - F is also routes A - E, A - D.... D - E and E - F.
BTW, I'm using PostgreSQL - D. Richard Hipp (the author of SQLite) has said that he uses PostgreSQL as a template for his own flavour of SQL, so it's the closest mainstream server to SQLite.
I created two tables - route and stop:
CREATE TABLE route(route_id SERIAL, route_name VARCHAR(20));
and
CREATE TABLE stop
(
stop_id SERIAL,
stop_route INT,
stop_position INT,
stop_name VARCHAR(20),
time_to_next_stop int
);
I then populated them like this:
INSERT INTO route (route_name) VALUES('Route_1');
and
INSERT INTO stop (stop_route, stop_position, stop_name, time_to_next_stop) VALUES(1, 1, 'Stop_1', 35);
INSERT INTO stop (stop_route, stop_position, stop_name, time_to_next_stop) VALUES(1, 2, 'Stop_2', 50);
INSERT INTO stop (stop_route, stop_position, stop_name, time_to_next_stop) VALUES(1, 3, 'Stop_3', 90);
INSERT INTO stop (stop_route, stop_position, stop_name, time_to_next_stop) VALUES(1, 4, 'Stop_4', 120);
INSERT INTO stop (stop_route, stop_position, stop_name, time_to_next_stop) VALUES(1, 5, 'Stop_5', 45);
INSERT INTO stop (stop_route, stop_position, stop_name, time_to_next_stop) VALUES(1, 6, 'Stop_6', 30);
and came up with the following query:
WITH cte AS
(
SELECT
r.route_name,
st1.stop_route AS route_id,
st1.stop_position as stop1_pos,
st1.stop_name as stname1,
st1.time_to_next_stop as st1_time,
st2.stop_position as stop2_pos,
st2.stop_name as st2_sname
FROM stop st1
LEFT JOIN stop st2
ON st1.stop_route = st2.stop_route
JOIN route r
ON st1.stop_route = r.route_id
),
all_routes AS
(
SELECT * FROM cte
WHERE stop1_pos != stop2_pos
AND stop1_pos < stop2_pos
ORDER BY stop1_pos, stop2_pos
)
SELECT * FROM all_routes;
The result is:
route_name | route_id | stop1_pos | stname1 | st1_time | stop2_pos | st2_sname
------------+----------+-----------+---------+----------+-----------+-----------
Route_1 | 1 | 1 | Stop_1 | 35 | 2 | Stop_2
Route_1 | 1 | 1 | Stop_1 | 35 | 3 | Stop_3
Route_1 | 1 | 1 | Stop_1 | 35 | 4 | Stop_4
Route_1 | 1 | 1 | Stop_1 | 35 | 5 | Stop_5
Route_1 | 1 | 1 | Stop_1 | 35 | 6 | Stop_6
Route_1 | 1 | 2 | Stop_2 | 50 | 3 | Stop_3
Route_1 | 1 | 2 | Stop_2 | 50 | 4 | Stop_4
Route_1 | 1 | 2 | Stop_2 | 50 | 5 | Stop_5
Route_1 | 1 | 2 | Stop_2 | 50 | 6 | Stop_6
Route_1 | 1 | 3 | Stop_3 | 90 | 4 | Stop_4
Route_1 | 1 | 3 | Stop_3 | 90 | 5 | Stop_5
Route_1 | 1 | 3 | Stop_3 | 90 | 6 | Stop_6
Route_1 | 1 | 4 | Stop_4 | 120 | 5 | Stop_5
Route_1 | 1 | 4 | Stop_4 | 120 | 6 | Stop_6
Route_1 | 1 | 5 | Stop_5 | 45 | 6 | Stop_6
(15 rows)
I haven't been able to make more progress than that, but you may be able to do a CROSSTAB
and/or use windowing/analytic functions for cumulating the times in some fashion? I'm giving the question a +1 though as I found it interesting - will look again tomorrow. You will have (n * (n - 1))/2 (= 6*5/2 in this example) "routes" where n = the number of stops (one-way).
You'll have to put in individual times anyway - since the times between stops will presumably vary by time of day (rush hour &c.).