Producing a GTFS-realtime feed for OBA from Orbital SQL Server database

[Sean Barbeau]

Hi OneBusAway developers,
We're currently working on setting up a OneBusAway instance in Tampa, Fl.

The main data source that we have to work with from HART is an Orbital MS SQL Server database that contains AVL bus position updates.  We have a secondary source of data from a web service, which provides estimated arrival times for a single stop specified by the query.  My understanding of the internals is that the estimated arrival times are synthesized in OrbCAD software based on the contents of the SQL Server database - in other words, the estimates aren't stored in the database.

Our current plan is to develop a SQL Server-to-GTFS-realtime adapter that would publish the AVL bus position updates as a GTFS-realtime Vehicle Positions feed.  My understanding is that OneBusAway should be able to consume this and produce estimated arrival times based on the internal OneBusAway prediction mechanism.

We are planning on using the onebusaway-gtfs-realtime-exporter tool as a basis for the SQL Server-to-GTFS-realtime adapter:
https://github.com/OneBusAway/onebusaway-gtfs-realtime-exporter

So, sanity check:
1) Are we missing anything important? 
2) Any other suggestions/better approaches for standing up OneBusAway based on this data? 
3) Any suggestions for using the onebusaway-gtfs-realtime-exporter tool?

Thanks,
Sean

Sean J. Barbeau
Center for Urban Transportation Research
University of South Florida

[Frumin, Michael]

Sean,

 

OBA (currently) accepts tuples of: (bus location, trip_id, block_id, schedule_deviation).  It generates all downstream stop level “predictions” based on schedule deviation only (i.e. “prediction” = scheduled time + schedule deviation).

 

Our recent experimentation shows that when you call certain functions to set externally-derived stop-level predictions, they don’t stick.  This is something we mean to raise with the OBA community post-haste.  You may run into this issue or not, but we should work together or at least coordinate our efforts to improve OBA’s functionality to accept and handle independently/externally-derived stop level predictions.

 

Thanks,

Mike

--
You received this message because you are subscribed to the Google Groups "onebusaway-developers" group.
To view this discussion on the web visit https://groups.google.com/d/msg/onebusaway-developers/-/P2rMJOkPmKAJ.
To post to this group, send email to onebusaway...@googlegroups.com.
To unsubscribe from this group, send email to onebusaway-devel...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/onebusaway-developers?hl=en.

[Jeff Maki]

Yeah, and speaking of this issue, Brian, we need the ability to
associate a prediction (brought in from a third-party system) with a
stop/vehicle combo, and as Mike alludes to, OBA currently seems to
come up with a sampled average for the vehicle, but not associated at
all with a particular stop. Even though the predictions fed in are
tagged with the stop ID.

Any future plans on the OBA community front to fix that? (If this
question makes sense...)

-Jeff

--
Jeff Maki
OpenPlans Transportation
917-388-9088
jm...@openplans.org

[Kurt Raschke]

Sean,

Based on what I've seen of other OrbCAD installations (which is
admittedly limited), my understanding is that OrbCAD does store a
per-vehicle schedule deviation value somewhere in its database. Using
that to build GTFS-realtime StopTimeUpdate messages is the easiest way
to get that data into OBA.

As Mike Frumin explained, vehicle positions alone aren't enough for
OBA as it exists today.

Montgomery County Ride On uses OrbCAD, and they've recently launched a
GTFS-realtime feed which provides StopTimeUpdate and VehiclePosition
messages:

http://kb.g-and-o.com/wiki/index.php/Live_Transit_API/API/gtfs_realtime

The resulting feed works well with OneBusAway.

The software behind that implementation is open-source and may prove
useful to you, although it is not based on the OBA GTFS-realtime
tools:

https://github.com/ipublic/live_transit_api
http://kb.g-and-o.com/wiki/index.php/Live_Transit_API/Architecture


-Kurt Raschke

[Brian Ferris]

Regarding Orbcad support, you might also look at the built-in adapters
for Orbcad already in OneBusAway:

https://github.com/OneBusAway/onebusaway-application-modules/tree/master/onebusaway-transit-data-federation/src/main/java/org/onebusaway/transit_data_federation/impl/realtime/orbcad

Pierce Transit and IntercityTransit both use Orbcad in the Puget Sound
region. They are exporting their data slightly differently (one as
FTP files the other as a file on a webserver) but the schema seems to
be the same and a schedule deviation value does seem to be included.
I'm wondering / hoping that their SQL format is similar as well?

If all you have is schedule deviation with no info about predicted
arrival time at a next stop, I've actually got a GTFS-realtime
extension in OBA for this:

https://github.com/OneBusAway/onebusaway-gtfs-realtime-api/blob/master/src/main/resources/com/google/transit/realtime/gtfs-realtime-OneBusAway.proto

We might try to get it officially added to the spec if it proves useful?

Brian

[Brian Ferris]

Sounds like a bug? I can try to take a look but any more details you
could provide would certainly help.

Brian

[Sean Barbeau]

Mike, Jeff, Kurt, and Brian,
Thanks!  That definitely clarifies things and is very helpful.

We are just starting to dig into the data dictionary and database fields ourselves, and there does seem to be a "predicted_deviation" field in the "vehicles" table that is "the most recent predicted schedule deviation of a vehicle."  Unfortunately we're working off of a replicated database and replication is currently broken (they're working on fixing it), so we can't confirm that this is correct yet.  One suspicious item is that units aren't listed for this field in the data dictionary and it uses the value "63" for "no data."

Kurt,
Could you point us to the database queries you used to piece together the info for the StopTimeUpdate?

Also, have you had any latency challenges based on the combination of vehicle position reporting frequency, propagation delays (e.g., network, database replication), and processing/publishing delays to the GTFS-realtime feed?  We've seen some delays from the OrbCAD system that were worrisome in past projects, but we don't yet have a feeling of whether or not this would affect OBA.

Brian,
One thing I'm not clear on is the difference between the GTFS-realtime trip_update.stop_time_update.arrival.delay field and the OBA extension OneBusAwayTripUpdate.delay field.  It seems that Kurt was able to get OBA working with only the schedule deviation (and not predicted arrival time at next stop) using vanilla GTFS-realtime and didn't need the OBA extension for this?
http://kb.g-and-o.com/wiki/index.php/Live_Transit_API/API/gtfs_realtime

Thanks,
Sean

>> To post to this group, send email to onebusaway-developers@googlegroups.com.

>> To unsubscribe from this group, send email to

>> onebusaway-developers+unsub...@googlegroups.com.

>> For more options, visit this group at
>> http://groups.google.com/group/onebusaway-developers?hl=en.
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "onebusaway-developers" group.

>> To post to this group, send email to onebusaway-developers@googlegroups.com.

>> To unsubscribe from this group, send email to

>> onebusaway-developers+unsub...@googlegroups.com.

>> For more options, visit this group at
>> http://groups.google.com/group/onebusaway-developers?hl=en.
>
>
>
> --
> Jeff Maki
> OpenPlans Transportation
> 917-388-9088
> jm...@openplans.org
>
> --
> You received this message because you are subscribed to the Google Groups "onebusaway-developers" group.

> To post to this group, send email to onebusaway-developers@googlegroups.com.
> To unsubscribe from this group, send email to onebusaway-developers+unsub...@googlegroups.com.

>> To post to this group, send email to onebusaway-developers@googlegroups.com.

>> To unsubscribe from this group, send email to

>> onebusaway-developers+unsub...@googlegroups.com.

>> For more options, visit this group at
>> http://groups.google.com/group/onebusaway-developers?hl=en.
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "onebusaway-developers" group.

>> To post to this group, send email to onebusaway-developers@googlegroups.com.

>> To unsubscribe from this group, send email to

>> onebusaway-developers+unsub...@googlegroups.com.

>> For more options, visit this group at
>> http://groups.google.com/group/onebusaway-developers?hl=en.
>
>
>
> --
> Jeff Maki
> OpenPlans Transportation
> 917-388-9088
> jm...@openplans.org
>
> --
> You received this message because you are subscribed to the Google Groups "onebusaway-developers" group.

> To post to this group, send email to onebusaway-developers@googlegroups.com.
> To unsubscribe from this group, send email to onebusaway-developers+unsub...@googlegroups.com.

[Brian Ferris]

What I mean to say is that if you don't have a "next stop id" (or
something equivalent) in your db, then you won't be able to generate a
StopTimeUpdate. For some reason, I though maybe the Orbcad feeds I
had worked with didn't have a next stop id, but maybe I'm remembering
it wrong.

>> >> onebusaway...@googlegroups.com.

>> >> To unsubscribe from this group, send email to

>> >> onebusaway-devel...@googlegroups.com.

>> >> For more options, visit this group at
>> >> http://groups.google.com/group/onebusaway-developers?hl=en.
>> >>
>> >> --
>> >> You received this message because you are subscribed to the Google
>> >> Groups
>> >> "onebusaway-developers" group.
>> >> To post to this group, send email to

>> >> onebusaway...@googlegroups.com.

>> >> To unsubscribe from this group, send email to

>> >> onebusaway-devel...@googlegroups.com.

>> >> For more options, visit this group at
>> >> http://groups.google.com/group/onebusaway-developers?hl=en.
>> >
>> >
>> >
>> > --
>> > Jeff Maki
>> > OpenPlans Transportation
>> > 917-388-9088
>> > jm...@openplans.org
>> >
>> > --
>> > You received this message because you are subscribed to the Google
>> > Groups "onebusaway-developers" group.
>> > To post to this group, send email to

>> > onebusaway...@googlegroups.com.

>> > To unsubscribe from this group, send email to

>> > onebusaway-devel...@googlegroups.com.

>> >> onebusaway...@googlegroups.com.

>> >> To unsubscribe from this group, send email to

>> >> onebusaway-devel...@googlegroups.com.

>> >> For more options, visit this group at
>> >> http://groups.google.com/group/onebusaway-developers?hl=en.
>> >>
>> >> --
>> >> You received this message because you are subscribed to the Google
>> >> Groups
>> >> "onebusaway-developers" group.
>> >> To post to this group, send email to

>> >> onebusaway...@googlegroups.com.

>> >> To unsubscribe from this group, send email to

>> >> onebusaway-devel...@googlegroups.com.

>> >> For more options, visit this group at
>> >> http://groups.google.com/group/onebusaway-developers?hl=en.
>> >
>> >
>> >
>> > --
>> > Jeff Maki
>> > OpenPlans Transportation
>> > 917-388-9088
>> > jm...@openplans.org
>> >
>> > --
>> > You received this message because you are subscribed to the Google
>> > Groups "onebusaway-developers" group.
>> > To post to this group, send email to

>> > onebusaway...@googlegroups.com.

>> > To unsubscribe from this group, send email to

>> > onebusaway-devel...@googlegroups.com.

>> > For more options, visit this group at
>> > http://groups.google.com/group/onebusaway-developers?hl=en.
>> >
>
> --
> You received this message because you are subscribed to the Google Groups
> "onebusaway-developers" group.

> To view this discussion on the web visit

> https://groups.google.com/d/msg/onebusaway-developers/-/ORjG_Y65NvoJ.
>
> To post to this group, send email to onebusaway...@googlegroups.com.

> To unsubscribe from this group, send email to

> onebusaway-devel...@googlegroups.com.

[Kurt Raschke]

Sean,

I'm not actually with Ride On (just a transit nerd), so I don't have much insight into how they're extracting data from the OrbCAD database.

As far as the construction of StopTimeUpdate messages, they use the stop_sequence of the last stop for the vehicle's current trip. So, the server-side process actually requires access to the current GTFS along with the data from OrbCAD (which provides the vehicle's current trip id).

Brian, from reading the GTFS-realtime documentation, it seems to me that generating a feed where the stop sequence is always the last stop of the trip may not be totally kosher; it certainly causes OneBusAway to log warnings, but the feed still works.  Can you clarify if this is actually permitted? It's certainly easier to find the last stop id or sequence for a trip than to find the next stop id for a vehicle as it is traveling.

-Kurt

Sent from my iPad

To view this discussion on the web visit https://groups.google.com/d/msg/onebusaway-developers/-/ORjG_Y65NvoJ.
To post to this group, send email to onebusaway...@googlegroups.com.
To unsubscribe from this group, send email to onebusaway-devel...@googlegroups.com.

[Sean Barbeau]

I exchanged some emails with Kurt off-list, but wanted to post the below link that Kurt tracked down in case its helpful to others.

In the Ride On project, it looks like this is the SQL query that is extracting real-time vehicle information from the OrbCAD database:
https://github.com/ipublic/live_transit_event_trigger/blob/master/VEHICLE_STATUS_CLR_SOURCE_CODE/VIEW_VEHICLE_STATUS.sql

This is very close to the schema we're working with, with the exception that we're missing the bs_id (i.e., bus stop ID) field in the trip_timepoint table.  So, unfortunately, the query doesn't work as-is.  We'll be digging in to see if we can find a substitute field from another table.

Sean

[Sean Barbeau]

Just to close the loop on this thread with an update, since we now have our OneBusAway application up and running based on a GTFS-realtime feed that's pulling data from HART's OrbCAD SQL Server database:

Source code for our HART-GTFS-realtimeGenerator is on Github:
https://github.com/CUTR-at-USF/HART-GTFS-realtimeGenerator/wiki

SQL query we're using in the above application accesses a View, but below is the underlying SQL statement used to grab the data from the OrbCAD database tables.  We're currently using the OrbCAD "predicted_deviation" value for the "delay" field in the GTFS-realtime data, which seems to be working well. 

From recent ground truth tests using the "predicted_deviation" value, (i.e., stand at a stop and wait for bus to arrive, and capture difference in estimated time vs. actual arrival time, with NOW meaning no error), mean arrival time error is 0.26 min (15 seconds), while the mean absolute error is 0.4 min (24 seconds), n = 15.  Note that precision is in minutes.  OBA is set to 15 sec refresh, and GTFS-realtime feed is set to 5 sec refresh.  From what HART has told us, maximum time between bus position updates to OrbCAD is approx. 2 minutes, with positions being updated each time a bus passes a timepoint.  "predicted_deviation" value is generated using some kind of OrbCAD magic based on timepoint values, and we don't know exactly how this value is calculated.

Sean

--------------------

SELECT     cps.vehicle_id AS vehicle, v.loc_x AS latitude, v.loc_y AS longitude, v.vehicle_position_date_time AS time, cps.deviation * -60 AS delay, v.predicted_deviation * -60 AS predicted_delay, v.average_speed AS speed,

                    v.heading AS bearing, cps.current_route_id AS route, LEFT(t.trip_id_external, LEN(t.trip_id_external) - 3) AS trip, bsp.bs_id AS stop, bsp.bs_seq AS sequence

FROM         dbo.current_performance_status AS cps INNER JOIN

                    dbo.vehicle AS v ON cps.vehicle_id = v.vehicle_id INNER JOIN

                    dbo.trip AS t ON cps.trip_id = t.trip_id AND cps.sched_version = t.sched_version INNER JOIN

                    dbo.vehicle_schedule AS vs ON cps.sched_version = vs.sched_version INNER JOIN

                    dbo.booking AS b ON vs.booking_id = b.booking_id INNER JOIN

                    dbo.trip_timepoint AS ctp ON cps.trip_id = ctp.trip_id AND cps.tp_id = ctp.tp_id AND cps.sched_version = ctp.sched_version AND DATEDIFF(n, ctp.eta, cps.sched_time)

                    % 1440 = 0 INNER JOIN

                    dbo.bus_stop_pattern AS bsp ON b.booking_num = bsp.booking_num AND cps.current_route_id = bsp.route_id AND cps.direction_code_id = bsp.direction_code_id AND

                     t.variation = bsp.variation AND ctp.seq_num = bsp.tp_seq

WHERE     (cps.vehicle_id <> 0) AND (v.logon_state = 1) AND (cps.transit_date_time =

                        (SELECT     MAX(transit_date_time) AS Expr1

                          FROM          dbo.current_performance_status))
--------------------