In need of help: mySQL >>> GTFS-Realtime

[Maria Smith]

I have just acquired live data feed from our system and I would like to provide it to Google.  In my research I believe I am making this harder than it needs to be.  Our live data is posting to a table on our SQL server (mySQL) in a database.  I have the connections string.  I'm asking if someone can tell me the easy 1 step process or if it is in fact a complicated process and I need to dig deeper and figure out what a protocol buffer is.  What type of link or file is Google looking for in the end?  An XML?  Advice, links help appreciated.  

Thank you,

Maria Smith

Mountain Line Transit

Morgantown, WV

busride.org

#istudiedmarketingnotfreakingcomputerscience

[Stefan de Konink]

Hi Maria,

On Wednesday, September 17, 2014 6:24:41 PM CEST, Maria Smith wrote:
> I have just acquired live data feed from our system and I would
> like to provide it to Google. In my research I believe I am
> making this harder than it needs to be. Our live data is
> posting to a table on our SQL server (mySQL) in a database. I
> have the connections string. I'm asking if someone can tell me
> the easy 1 step process or if it is in fact a complicated
> process and I need to dig deeper and figure out what a protocol
> buffer is. What type of link or file is Google looking for in
> the end? An XML? Advice, links help appreciated.

The protobuf files are what you are required to put on a webserver (and
update them) so Google can download them.

Ideally you should grab the data feed from your AVL system without the
intermediate database step and transform it directly to a protobuf file
and/or a websocket server. What you could do is define some views which may
help you to match the semantic model of the protocol buffer format.

So yes, you should dig into both the model and the format. I would suggest
to use the one bus away sourcecode as reference or at our producer which
serves all data for The Netherlands.

<https://github.com/bliksemlabs/bliksemintegration-realtime>

Stefan

[Brian Ferris]

Hey Maria,

This is the OneBusAway demo that Stefan mentioned:

https://github.com/OneBusAway/onebusaway-gtfs-realtime-trip-updates-producer-demo

But yeah, you will need some glue-code to get your database data in the proper format.  For GTFS-realtime, that means protobuf, but there are libraries out there to simplify the process.

--
You received this message because you are subscribed to the Google Groups "GTFS-realtime" group.
To unsubscribe from this group and stop receiving emails from it, send an email to gtfs-realtime+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/gtfs-realtime/968e896e-1080-4d47-96b4-9395271855c4%40konink.de.
For more options, visit https://groups.google.com/d/optout.

[Maria Smith]

Thank you both.  It's a third party that we have worked with that is providing the live data to me, their database sync program outputs it to the MySQL table, I'm not sure I can intercept the data.  I have a feeling I'm now going to have to go backwards and get software that will use the connection string to then bring the data back out into another format --protobuf...??  Well here I go, thank you for giving me a direction to head in.

[Sean Barbeau]

Maria,
If you can't intercept the data and you have to pull from the database, you may want to look at our open-source project that's being used to produce GTFS-rt in Tampa, FL for HART:

https://github.com/CUTR-at-USF/HART-GTFS-realtimeGenerator/wiki

We were in a very similar scenario - we were provided the real-time data in a MS SQL Server database (from an Orbital OrbCAD system), without the ability to intercept the data before it hit the database.  So, HART created a SQL View that provided the same fields used in GTFS-rt for TripUpdates and Vehicle Positions - in our Github project above, we simply poll that view every X seconds by executing this SQL statement:

SELECT [vehicle] as vehicle_id,[latitude],[longitude],[time],[delay],[speed],[bearing],[route] as route_id,
[trip] as trip_id,[stop] as stop_id,[sequence] as seq FROM h_BusEvents ORDER BY trip_id, vehicle_id, seq ASC;

...and then output the data into GTFS-rt protobuf format within an embedded Jetty server instance.

You should be able to re-use our same software, as long as you configure your database with a SQL View named "h_BusEvents" with the fields named the same as the above (and obviously configure it to point to your database server and log in with the right user/credentials, etc.).  Alternately, just modify the query in the code to pull the correct fields from your database, if you can't set up a particular View in your database.

The Github wiki above has pretty extensive documentation, but feel free to ask if you have any additional questions.

Sean

[Maria Smith]

Oh, that is very helpful Sean, thank you!