CODEWITHSUNDEEP
sqlgtfs
devha
devha

Reputation: 3377

How to match static GTFS with GTFS-RT trip updates

Im consuming couple of different GTFS & GTFS-RT feeds, im storing this data to SQL database.

How should I match/link static GTFS data with GTFS-RT trip updates?

Im trying to display stop times for specific stop_id. Im able to get stop times from static GTFS but I want to "patch" these times with GTFS-RT trip update stop time updates.

It looks like GTFS-RT is very loosely specified, only few of the fields are mandatory. One of the GTFS-RT feeds which im consuming, has trip_id in its trip updates, another feed does not have trip_id.

Update 4.11.2017

I managed to match GTFS-RT trip update to static GTFS using query below. In some reason in not able to get trip_id for all trip updates. Maybe the issue is in data itself, of is there something wrong with my query?

const trip = await knex('trips')
  .select('trips.trip_id')
  .innerJoin('routes', 'routes.route_id', 'trips.route_id')
  .innerJoin('stop_times', 'stop_times.trip_id', 'trips.trip_id')
  .whereIn('trips.service_id', *day active service ids*)
  .andwhere('routes.route_id', tripUpdate.route_id)
  .andWhere('stop_times.departure_time', tripUpdate.trip_start_time)
  .andWhere('trips.direction_id', tripUpdate.direction_id)
  .orderBy('stop_times.stop_sequence')
  .first();

Upvotes: 0

Views: 696

Answers (1)

paulh
paulh

Reputation: 314

The problem could be the

.andWhere('stop_times.departure_time', tripUpdate.trip_start_time)

clause in the select statement.

The gtfs spec states Times must be eight digits in HH:MM:SS format (H:MM:SS is also accepted, if the hour begins with 0) If your schedule has times like 5:37:00 and the start_time in TripUpdates is 05:37:00 your statement will not return the trip id. If this is the problem you will need to prepend times like this with "0" when adding them from stop_times.txt to the database or alternatively adjust your clause above.

Upvotes: 0

Related Questions