CODEWITHSUNDEEP
mysqlsqlgtfs
r_t
r_t

Reputation: 335

GTFS get last stop on trip

Using a standard GTFS database, I'm trying to add the name of the last stop on a trip to my current query which returns the following:

| trip_id                         | service_id | departure_stop | departure_time | arrival_stop    | arrival_time | end_departure |
|---------------------------------|------------|----------------|----------------|-----------------|--------------|---------------|
| 15693832.T6.2-EPP-E-mjp-1.11.R  | T6_1       | Clifton Hill   | 04:56:00       | Flinders Street | 05:07:00     | 05:07:00      |

and so on.

My current query is:

select `t`.`trip_id`,
       `c`.`service_id`,
       `start_s`.`stop_name` as `departure_stop`,
       `start_st`.`departure_time`,
       `end_s`.`stop_name` as `arrival_stop`,
       `end_st`.`arrival_time`,
       `end_st`.`departure_time` as `end_departure`
from `trips` as `t`
    inner join `calendar` as `c` on `t`.`service_id` = `c`.`service_id`
    inner join `routes` as `r` on `t`.`route_id` = `r`.`route_id` 
    inner join `stop_times` as `start_st` on `t`.`trip_id` = `start_st`.`trip_id` 
    inner join `stops` as `start_s` on `start_st`.`stop_id` = `start_s`.`stop_id` 
    inner join `stop_times` as `end_st` on `t`.`trip_id` = `end_st`.`trip_id` 
    inner join `stops` as `end_s` on `end_st`.`stop_id` = `end_s`.`stop_id` 
where `start_st`.`departure_time` > '00:00:00' 
    and `start_st`.`departure_time` < '23:59:59' 
    and `start_s`.`stop_id` = 19974 
    and `end_s`.`stop_id` = 19854 
    and start_st.departure_time < end_st.arrival_time
order by arrival_time asc

I'm stuck trying to figure out how I can get the last stop in the stop_times table for the trip_id that my query returns for each row.

So in addition to what I have currently, I'd like to:

Update:

I've tried selecting s.stop_name and adding the following inner join:

inner join (
    SELECT s.stop_name, trip_id
        FROM stop_times 
        INNER JOIN stops as s on `s`.`stop_id` = `stop_times`.`stop_id`
        ORDER BY stop_sequence DESC
) s on `t`.`trip_id` = `s`.`trip_id`

However, this adds extra rows for every single stop in trip, where I only want the last one and adding LIMIT 1 returns no results.

Upvotes: 1

Views: 725

Answers (1)

user2638401
user2638401

Reputation: 411

One caution, arrival and departure times may be later than midnight in the GTFS spec (hour value could be 24, 25, etc.)

select "t"."trip_id",
       "c"."service_id",
       "start_s"."stop_name" as "departure_stop",
       "start_st"."departure_time",
       "end_s"."stop_name" as "arrival_stop",
       "end_st"."arrival_time",
       "end_st"."departure_time" as "end_departure",
       "last_st"."arrival_time" as "last_arrival",
       "last_s"."stop_name" as "last_stop"
from "trips" as "t"
    inner join "calendar" as "c" on "t"."service_id" = "c"."service_id"
    inner join "routes" as "r" on "t"."route_id" = "r"."route_id" 
    inner join "stop_times" as "start_st" on "t"."trip_id" = "start_st"."trip_id" 
    inner join "stops" as "start_s" on "start_st"."stop_id" = "start_s"."stop_id" 
    inner join "stop_times" as "end_st" on "t"."trip_id" = "end_st"."trip_id" 
    inner join "stops" as "end_s" on "end_st"."stop_id" = "end_s"."stop_id" 
    inner join "stop_times" as "last_st" on "t"."trip_id" = "last_st"."trip_id" 
    inner join "stops" as "last_s" on "last_st"."stop_id" = "last_s"."stop_id" 
where "start_s"."stop_id" = '245' -- my data's stop id
    and "end_s"."stop_id" = '762' -- my data's stop id
    and "last_st"."stop_sequence" = (select max("stop_sequence") from "stop_times" where "t"."trip_id" = "trip_id")
    and start_st.departure_time < end_st.arrival_time
order by arrival_time asc

Upvotes: 2

Related Questions