mattwigway / gtfsrdb

GTFSrDB is a tool to archive gtfs-realtime data to a database.

GTFSrDB - GTFS-realtime to Database

GTFSrDB loads GTFS-realtime data to a database.

GTFSrDB supports all 3 types of GTFS-realtime feeds:

1. TripUpdates - specify url with -t option
2. Service Alerts - specify url with -a option
3. VehiclePositions - specify url with -p option

You can process multiple types of GTFS-realtime feeds in the same execution by using multiple command line options.

GTFSrDB will run and keep a database up-to-date with the latest GTFSr data. It can also be used to archive this data for historical or statistical purposes. GTFSrDB is designed to work in tandem with gtfsdb. GTFSrDB uses SQLAlchemy, so it should work with most any database system; So far its been used with SQLite, Postgres, and Microsoft SQL Server. Just specify a database url on the command line with -d.

Example Use

1. Bay Area Rapid Transit with GTFS-realtime TripUpdates:

a. Using SQLite:

`gtfsrdb.py -t http://www.bart.gov/dev/gtrtfs/tripupdate.aspx -d sqlite:///test.db -c`

b. Using Microsoft SQL Server (note you'll need pyodbc):

`gtfsrdb.py -t http://www.bart.gov/dev/gtrtfs/tripupdate.aspx -d 
  mssql+pyodbc://<username>:<password>@<public_database_server_name>/<database_name> -c`

So, if the username=jdoe, password=pswd, public_database_server_name=my.public.database.org, database_name=gtfsrdb, the command is:

`gtfsrdb.py -t http://www.bart.gov/dev/gtrtfs/tripupdate.aspx -d 
  mssql+pyodbc://jdoe:[email protected]/gtfsrdb -c`

2. Massachusetts Bay Transportation Authority with GTFS-realtime VehiclePositions:

a. Using SQLite:

`gtfsrdb.py -p http://developer.mbta.com/lib/gtrtfs/Vehicles.pb -d sqlite:///test.db -c`

The model for the data is in model.py; you should be able to use this standalone with SQLAlchemy to process the data in Python.

The -o command line option instructs GTFSrDB to keep the database up-to-date by deleting outdated trip updates, vehicle positions, and alerts. Omitting this option will cause each update to be saved forever (useful for historical purposes). Note that using this option will ERASE ALL TRIP UPDATES, ALL ALERTS, and ALL VEHICLE POSITIONS from the database on each iteration - even those that were in the database before the session was started.

This is GTFSrDB's biggest strength - if you pass the -o option, your database will be perpetually up-to-date with the GTFS-realtime feed, so you can write scripts &c that refer to it without worrying about the plumbing to get the data in place.

Other command line parameters:

• -w = Time to wait between requests (in seconds) (default=30s)
• -v = Print generated SQL (verbose mode)
• -l = When multiple translations are available, prefer this language

It is recommended that you run VACUUM ANALYZE frequently, as GTFSrDB generates quite a few creations and deletions.

KNOWN LIMITATIONS

The following fields that are separate messages in GTFSr are collapsed into columns in the parent in the SQL database (to avoid creating many joined tables):

• TripUpdate.trip becomes trip_id, route_id, trip_start_time, trip_start_date
• TripUpdate.vehicle becomes vehicle_id, vehicle_label and vehicle_license_plate
• StopTimeUpdate.arrival becomes arrival_time, arrival_delay & arrival_uncertainty
• StopTimeUpdate.departure becomes departure_time &c.
• Alert.active_period is condensed to Alert.start and Alert.end; if there are multiple active periods, only the first one is stored.
• All TranslatedStrings are converted to plain strings, using a) the language specified with the -l option, b) any untranslated string if a string for the language is not found, or c) the only string in the case of a single string in the file.
• Position.latitude becomes position_latitude
• Position.longitude becomes position_longitude
• Position.bearing becomes position_bearing
• Position.speed becomes position_speed
• VehicleDescriptor.id becomes vehicle_id
• VehicleDescriptor.label becomes vehicle_label
• VehicleDescriptor.license_plate becomes vehicle_license_plate

USING IT WITH GTFSDB

It's not hard to use GTFSrDB in conjunction with GTFSDB. Simply point GTFSrDB at a GTFSDB database, and it will add its data into the database alongside the static GTFS data. Use the -c option to create tables. You can then use SQL's relational features to mash up the data any way you want. (Keep in mind that GTFS uses strings for IDs, and SQL generally uses numbers. trip_updates and stop_time_updates, as well as alerts and entity_selectors, are related on the oid column, which is a sequential integer primary key. All of the GTFS ID fields are left intact, for joining with the static data. You can't just cast the strings to numbers; take a look at BART's stop IDs in the examples below).

Here are some example queries (both designed to work with the -o option). Note that the first two are for BART, which embeds stop_ids in GTFSr; other agencies (e.g., TriMet) specify stops as trip_updates.trip_id and stop_time_updates.stop_sequence; you'll need to use slightly more complex queries for those.

This query shows all of the stop time updates that relate cleanly to the stops table. Keep in mind that trips.trip_id = trip_updates.trip_id only works for trips that are not frequency-expanded (i.e. multiple trips with the same trip_id)

SELECT trips.route_id, trips.trip_id, trips.trip_headsign, trip_updates.schedule_relationship, stop_time_updates.stop_id, stop_time_updates.arrival_delay
FROM trip_updates, stop_time_updates, trips
WHERE trips.trip_id::text = trip_updates.trip_id::text AND trip_updates.oid = stop_time_updates.trip_update_id
ORDER BY stop_time_updates.stop_id;

route_id | trip_id |     trip_headsign     | schedule_relationship | stop_id | arrival_delay 
----------+---------+-----------------------+-----------------------+---------+---------------
04       | 66F1    | FREMONT               | SCHEDULED             | 19TH    |             0
12       | 67ED1   | EAST DUBLIN           | SCHEDULED             | 24TH    |             0
11       | 66DCM2  | DALY CITY             | SCHEDULED             | BAYF    |             0
12       | 65ED1   | EAST DUBLIN           | SCHEDULED             | CAST    |             0
02       | 83PB1   | PITTSBURG / BAY POINT | SCHEDULED             | COLM    |             0
03       | 67R1    | RICHMOND              | SCHEDULED             | COLS    |             0
02       | 80PB1   | PITTSBURG / BAY POINT | SCHEDULED             | CONC    |             0
12       | 69ED1   | EAST DUBLIN           | SCHEDULED             | DALY    |             0
12       | 68ED1   | EAST DUBLIN           | SCHEDULED             | DALY    |             0
04       | 67F1    | FREMONT               | SCHEDULED             | DELN    |             0
11       | 69DCM2  | DALY CITY             | SCHEDULED             | DUBL    |             0
11       | 67DCM2  | DALY CITY             | SCHEDULED             | DUBL    |             0
11       | 68DCM2  | DALY CITY             | SCHEDULED             | DUBL    |             0
03       | 69R1    | RICHMOND              | SCHEDULED             | FRMT    |             0
03       | 70R1    | RICHMOND              | SCHEDULED             | FRMT    |             0
11       | 64DCM2  | DALY CITY             | SCHEDULED             | GLEN    |             0
12       | 66ED1   | EAST DUBLIN           | SCHEDULED             | LAKE    |             0
03       | 66R1    | RICHMOND              | SCHEDULED             | MCAR    |             0
02       | 81PB1   | PITTSBURG / BAY POINT | SCHEDULED             | MCAR    |             0
02       | 85PB1   | PITTSBURG / BAY POINT | SCHEDULED             | MLBR    |             0
02       | 82PB1   | PITTSBURG / BAY POINT | SCHEDULED             | POWL    |             0
04       | 69F1    | FREMONT               | SCHEDULED             | RICH    |             0
04       | 68F1    | FREMONT               | SCHEDULED             | RICH    |             0
04       | 65F1    | FREMONT               | SCHEDULED             | SANL    |             0
02       | 84PB1   | PITTSBURG / BAY POINT | SCHEDULED             | SFIA    |             0
03       | 68R1    | RICHMOND              | SCHEDULED             | UCTY    |             0
11       | 65DCM2  | DALY CITY             | SCHEDULED             | WOAK    |             0
(27 rows)

This query gives you an overview of the entire BART system, with average delays for each stop where trains are predicted. I may spatially enable this database and make a heatmap of where delays are in a given transit system by interpolating between points.

SELECT stops.stop_id, stops.stop_name, stops.stop_lat, stops.stop_lon, avg(stop_time_updates.arrival_delay) AS avg
FROM stop_time_updates, stops
WHERE stops.stop_id::text = stop_time_updates.stop_id::text
GROUP BY stops.stop_id, stops.stop_name, stops.stop_lat, stops.stop_lon
ORDER BY stops.stop_name;

stop_id |           stop_name           |   stop_lat   |    stop_lon    |          avg           
---------+-------------------------------+--------------+----------------+------------------------
BALB    | Balboa Park BART              | 37.721980868 | -122.447414196 | 0.00000000000000000000
CIVC    | Civic Center/UN Plaza BART    | 37.779605587 | -122.413851084 | 0.00000000000000000000
COLS    | Coliseum/Oakland Airport BART | 37.754281380 | -122.197788821 | 0.00000000000000000000
DALY    | Daly City BART                | 37.706120549 | -122.469080674 | 0.00000000000000000000
DUBL    | Dublin/Pleasanton BART        | 37.701673617 | -121.900352519 | 0.00000000000000000000
EMBR    | Embarcadero BART              | 37.793022441 | -122.396813153 | 0.00000000000000000000
FRMT    | Fremont BART                  | 37.557334282 | -121.976395442 | 0.00000000000000000000
FTVL    | Fruitvale BART                | 37.774623806 | -122.224327698 | 0.00000000000000000000
GLEN    | Glen Park BART                | 37.732941544 | -122.434114331 | 0.00000000000000000000
HAYW    | Hayward Station BART          | 37.670386894 | -122.088002125 | 0.00000000000000000000
LAKE    | Lake Merritt BART             | 37.797602372 | -122.265498391 | 0.00000000000000000000
MLBR    | Millbrae BART                 | 37.600006000 | -122.386534000 | 0.00000000000000000000
NBRK    | North Berkeley BART           | 37.874026140 | -122.283881911 | 0.00000000000000000000
NCON    | North Concord/Martinez BART   | 38.002576647 | -122.025106029 | 0.00000000000000000000
ORIN    | Orinda BART                   | 37.878360870 | -122.183791135 | 0.00000000000000000000
PITT    | Pittsburg/Bay Point BART      | 38.018934339 | -121.941904488 | 0.00000000000000000000
RICH    | Richmond BART                 | 37.937169908 | -122.353400100 | 0.00000000000000000000
SFIA    | San Francisco Int BART        | 37.615900000 | -122.392534000 | 0.00000000000000000000
SHAY    | South Hayward BART            | 37.634799539 | -122.057550587 | 0.00000000000000000000
UCTY    | Union City BART               | 37.591202687 | -122.017857962 | 0.00000000000000000000
WDUB    | West Dublin/Pleasanton BART   | 37.699800000 | -121.928100000 | 0.00000000000000000000
WOAK    | West Oakland BART             | 37.804674760 | -122.294582214 | 0.00000000000000000000

A demo for TriMet

GTFSrDB allows you to connect GTFS-realtime with an SQL database, allowing app developers to use realtime data through SQL, just as easily as they use static data. Rather than worry about plumbing to connect GTFS and GTFS-realtime, they can focus on writing apps.

It accomplishes two primary tasks:

• Keeping a database up-to-date with the latest realtime data, and
• Archiving historic real-time data.

It’s designed to work with GTFSdb; it will coexist with static GTFS data in a database, so you can easily relate them. Keep in mind that if you update the GTFS data, you’ll lose archived GTFSr data. Here is an example query to find what stops have the largest delays (in seconds, for the TriMet system in Portland, OR:

SELECT stops.stop_id, stops.stop_name, stops.stop_lat, stops.stop_lon, stop_delays.avg
FROM stops, stop_delays
WHERE stops.stop_id = stop_delays.stop_id
ORDER BY avg DESC;

The stop_delays view looks like this:

SELECT stop_times.stop_id, avg(stop_time_updates.arrival_delay) AS avg
FROM stop_time_updates, stop_times, trip_updates
WHERE stop_times.trip_id::text = trip_updates.trip_id::text AND stop_times.stop_sequence = stop_time_updates.stop_sequence AND stop_time_updates.trip_update_id = trip_updates.oid
GROUP BY stop_times.stop_id
ORDER BY avg(stop_time_updates.arrival_delay) DESC;

(I had to pull in the trip_updates table for TriMet because they don’t have a stop_id in their stop_time_updates; they instead specify trip_id and stop_sequence.)

(I’ve removed the lat and lon columns from the following table for readability)

stop_id |            stop_name            |         avg
---------+---------------------------------+----------------------
10853   | Parkrose/ Sumner Transit Center | 473.8260869565217391
7999    | NE 82nd & MAX Overpass          | 350.3050847457627119
9610    | Willow Creek Transit Center     | 310.2352941176470588
5846    | Tigard Transit Center           | 260.2093023255813953
12849   | 16200 Block SW Langer           | 244.6111111111111111

. . .

gtfsrdb's Issues

Error loading feed into postgres

First time playing around with GTFS, so potentially may be making an obvious mistake. I previously had gtfsrdb + gtfsrdb working with SQLITE, but can't get the same to work with postgres.

I'm using the following configuration string:
./gtfsrdb.py -t https://gtfsrt.api.translink.com.au/Feed/SEQ -p https://gtfsrt.api.translink.com.au/Feed/SEQ -a https://gtfsrt.api.translink.com.au/Feed/SEQ -d postgresql://username:password@localhost:5432/translink -c -o

The error I'm receiving is:
Adding 3575 trip updates Exception occurred in iteration (<class 'sqlalchemy.exc.DataError'>, DataError('(psycopg2.DataError) value too long for type character varying(10)\n',), <traceback object at 0x7f74086117e8>) Exception occurred in iteration
I tried updating the column lengths in postgres, but that didn't seem to work.

Cheers

Possible memory leak (on connection errors?)

After running several instances of gtfsrdb for a few days on the same server, one instance grew to over 3GB in memory and slowed the server down to a crawl. The other instances seemed more reasonable, between 58MB and 195MB. Growth of memory usage may be related to how many objects are processed in the feed, which would explain the seemingly different rates of growth. These instances were all connecting to a SQL Server 2008 database on a remote machine.

Here's the view of the Windows Task Manager:

This error message appeared in the console window:

r][TCP/IP Sockets]ConnectionRead (recv()). (65534) (SQLExecDirectW); [08S01] [Mi
crosoft][ODBC SQL Server Driver][TCP/IP Sockets]General network error. Check you
r network documentation. (11)') u'INSERT INTO trip_updates (trip_id, route_id, t
rip_start_time, trip_start_date, schedule_relationship, vehicle_id, vehicle_labe
l, vehicle_license_plate, timestamp) OUTPUT inserted.oid VALUES (?, ?, ?, ?, ?,
?, ?, ?, ?)' (u'19885030', u'', u'', u'20130501', 'SCHEDULED', u'', u'', u'', da
tetime.datetime(2013, 5, 1, 14, 0, 2))",), <traceback object at 0x00000001B2BDA6
48>)
Warning: feed version has changed: found 0.1, expected 1.0
Adding 874 trip updates
Warning: feed version has changed: found 0.1, expected 1.0
Adding 411 vehicle_positions
Exception occurred in iteration
(<class 'sqlalchemy.exc.InvalidRequestError'>, InvalidRequestError("This Session
's transaction has been rolled back due to a previous exception during flush. To
begin a new transaction with this Session, first issue Session.rollback(). Orig
inal exception was: (Error) ('01000', '[01000] [Microsoft][ODBC SQL Server Drive
r][TCP/IP Sockets]ConnectionRead (recv()). (65534) (SQLExecDirectW); [08S01] [Mi
crosoft][ODBC SQL Server Driver][TCP/IP Sockets]General network error. Check you
r network documentation. (11)') u'INSERT INTO trip_updates (trip_id, route_id, t
rip_start_time, trip_start_date, schedule_relationship, vehicle_id, vehicle_labe
l, vehicle_license_plate, timestamp) OUTPUT inserted.oid VALUES (?, ?, ?, ?, ?,
?, ?, ?, ?)' (u'19885030', u'', u'', u'20130501', 'SCHEDULED', u'', u'', u'', da
tetime.datetime(2013, 5, 1, 14, 0, 2))",), <traceback object at 0x00000001B37093
88>)
Warning: feed version has changed: found 0.1, expected 1.0
Adding 874 trip updates
Warning: feed version has changed: found 0.1, expected 1.0
Adding 411 vehicle_positions
Exception occurred in iteration
(<class 'sqlalchemy.exc.InvalidRequestError'>, InvalidRequestError("This Session
's transaction has been rolled back due to a previous exception during flush. To
begin a new transaction with this Session, first issue Session.rollback(). Orig
inal exception was: (Error) ('01000', '[01000] [Microsoft][ODBC SQL Server Drive
r][TCP/IP Sockets]ConnectionRead (recv()). (65534) (SQLExecDirectW); [08S01] [Mi
crosoft][ODBC SQL Server Driver][TCP/IP Sockets]General network error. Check you
r network documentation. (11)') u'INSERT INTO trip_updates (trip_id, route_id, t
rip_start_time, trip_start_date, schedule_relationship, vehicle_id, vehicle_labe
l, vehicle_license_plate, timestamp) OUTPUT inserted.oid VALUES (?, ?, ?, ?, ?,
?, ?, ?, ?)' (u'19885030', u'', u'', u'20130501', 'SCHEDULED', u'', u'', u'', da
tetime.datetime(2013, 5, 1, 14, 0, 2))",), <traceback object at 0x00000001B3F091
48>)
Warning: feed version has changed: found 0.1, expected 1.0
Adding 866 trip updates
Warning: feed version has changed: found 0.1, expected 1.0
Adding 416 vehicle_positions
Exception occurred in iteration
(<class 'sqlalchemy.exc.InvalidRequestError'>, InvalidRequestError("This Session
's transaction has been rolled back due to a previous exception during flush. To
begin a new transaction with this Session, first issue Session.rollback(). Orig
inal exception was: (Error) ('01000', '[01000] [Microsoft][ODBC SQL Server Drive
r][TCP/IP Sockets]ConnectionRead (recv()). (65534) (SQLExecDirectW); [08S01] [Mi
crosoft][ODBC SQL Server Driver][TCP/IP Sockets]General network error. Check you
r network documentation. (11)') u'INSERT INTO trip_updates (trip_id, route_id, t
rip_start_time, trip_start_date, schedule_relationship, vehicle_id, vehicle_labe
l, vehicle_license_plate, timestamp) OUTPUT inserted.oid VALUES (?, ?, ?, ?, ?,
?, ?, ?, ?)' (u'19885030', u'', u'', u'20130501', 'SCHEDULED', u'', u'', u'', da
tetime.datetime(2013, 5, 1, 14, 0, 2))",), <traceback object at 0x00000001B4629F
88>)
Warning: feed version has changed: found 0.1, expected 1.0
Adding 866 trip updates
Warning: feed version has changed: found 0.1, expected 1.0
Adding 416 vehicle_positions
Exception occurred in iteration
(<class 'sqlalchemy.exc.InvalidRequestError'>, InvalidRequestError("This Session
's transaction has been rolled back due to a previous exception during flush. To
begin a new transaction with this Session, first issue Session.rollback(). Orig
inal exception was: (Error) ('01000', '[01000] [Microsoft][ODBC SQL Server Drive
r][TCP/IP Sockets]ConnectionRead (recv()). (65534) (SQLExecDirectW); [08S01] [Mi
crosoft][ODBC SQL Server Driver][TCP/IP Sockets]General network error. Check you
r network documentation. (11)') u'INSERT INTO trip_updates (trip_id, route_id, t
rip_start_time, trip_start_date, schedule_relationship, vehicle_id, vehicle_labe
l, vehicle_license_plate, timestamp) OUTPUT inserted.oid VALUES (?, ?, ?, ?, ?,
?, ?, ?, ?)' (u'19885030', u'', u'', u'20130501', 'SCHEDULED', u'', u'', u'', da
tetime.datetime(2013, 5, 1, 14, 0, 2))",), <traceback object at 0x00000001B5109F
48>)
Warning: feed version has changed: found 0.1, expected 1.0
Adding 857 trip updates
Warning: feed version has changed: found 0.1, expected 1.0
Adding 418 vehicle_positions
Exception occurred in iteration
(<class 'sqlalchemy.exc.InvalidRequestError'>, InvalidRequestError("This Session
's transaction has been rolled back due to a previous exception during flush. To
begin a new transaction with this Session, first issue Session.rollback(). Orig
inal exception was: (Error) ('01000', '[01000] [Microsoft][ODBC SQL Server Drive
r][TCP/IP Sockets]ConnectionRead (recv()). (65534) (SQLExecDirectW); [08S01] [Mi
crosoft][ODBC SQL Server Driver][TCP/IP Sockets]General network error. Check you
r network documentation. (11)') u'INSERT INTO trip_updates (trip_id, route_id, t
rip_start_time, trip_start_date, schedule_relationship, vehicle_id, vehicle_labe
l, vehicle_license_plate, timestamp) OUTPUT inserted.oid VALUES (?, ?, ?, ?, ?,
?, ?, ?, ?)' (u'19885030', u'', u'', u'20130501', 'SCHEDULED', u'', u'', u'', da
tetime.datetime(2013, 5, 1, 14, 0, 2))",), <traceback object at 0x00000001B594C5
88>)
Warning: feed version has changed: found 0.1, expected 1.0
Adding 857 trip updates
Warning: feed version has changed: found 0.1, expected 1.0
Adding 418 vehicle_positions
Exception occurred in iteration
(<class 'sqlalchemy.exc.InvalidRequestError'>, InvalidRequestError("This Session
's transaction has been rolled back due to a previous exception during flush. To
begin a new transaction with this Session, first issue Session.rollback(). Orig
inal exception was: (Error) ('01000', '[01000] [Microsoft][ODBC SQL Server Drive
r][TCP/IP Sockets]ConnectionRead (recv()). (65534) (SQLExecDirectW); [08S01] [Mi
crosoft][ODBC SQL Server Driver][TCP/IP Sockets]General network error. Check you
r network documentation. (11)') u'INSERT INTO trip_updates (trip_id, route_id, t
rip_start_time, trip_start_date, schedule_relationship, vehicle_id, vehicle_labe
l, vehicle_license_plate, timestamp) OUTPUT inserted.oid VALUES (?, ?, ?, ?, ?,
?, ?, ?, ?)' (u'19885030', u'', u'', u'20130501', 'SCHEDULED', u'', u'', u'', da
tetime.datetime(2013, 5, 1, 14, 0, 2))",), <traceback object at 0x00000001A6483F
C8>)
Warning: feed version has changed: found 0.1, expected 1.0
Adding 874 trip updates
Warning: feed version has changed: found 0.1, expected 1.0
Adding 418 vehicle_positions
Exception occurred in iteration
(<class 'sqlalchemy.exc.InvalidRequestError'>, InvalidRequestError("This Session
's transaction has been rolled back due to a previous exception during flush. To
begin a new transaction with this Session, first issue Session.rollback(). Orig
inal exception was: (Error) ('01000', '[01000] [Microsoft][ODBC SQL Server Drive
r][TCP/IP Sockets]ConnectionRead (recv()). (65534) (SQLExecDirectW); [08S01] [Mi
crosoft][ODBC SQL Server Driver][TCP/IP Sockets]General network error. Check you
r network documentation. (11)') u'INSERT INTO trip_updates (trip_id, route_id, t
rip_start_time, trip_start_date, schedule_relationship, vehicle_id, vehicle_labe
l, vehicle_license_plate, timestamp) OUTPUT inserted.oid VALUES (?, ?, ?, ?, ?,
?, ?, ?, ?)' (u'19885030', u'', u'', u'20130501', 'SCHEDULED', u'', u'', u'', da
tetime.datetime(2013, 5, 1, 14, 0, 2))",), <traceback object at 0x00000001B6DF06
88>)
Warning: feed version has changed: found 0.1, expected 1.0
Adding 874 trip updates
Warning: feed version has changed: found 0.1, expected 1.0
Adding 420 vehicle_positions
Exception occurred in iteration
(<class 'sqlalchemy.exc.InvalidRequestError'>, InvalidRequestError("This Session
's transaction has been rolled back due to a previous exception during flush. To
begin a new transaction with this Session, first issue Session.rollback(). Orig
inal exception was: (Error) ('01000', '[01000] [Microsoft][ODBC SQL Server Drive
r][TCP/IP Sockets]ConnectionRead (recv()). (65534) (SQLExecDirectW); [08S01] [Mi
crosoft][ODBC SQL Server Driver][TCP/IP Sockets]General network error. Check you
r network documentation. (11)') u'INSERT INTO trip_updates (trip_id, route_id, t
rip_start_time, trip_start_date, schedule_relationship, vehicle_id, vehicle_labe
l, vehicle_license_plate, timestamp) OUTPUT inserted.oid VALUES (?, ?, ?, ?, ?,
?, ?, ?, ?)' (u'19885030', u'', u'', u'20130501', 'SCHEDULED', u'', u'', u'', da
tetime.datetime(2013, 5, 1, 14, 0, 2))",), <traceback object at 0x00000001B5109F
48>)
Warning: feed version has changed: found 0.1, expected 1.0
Adding 874 trip updates
Warning: feed version has changed: found 0.1, expected 1.0
Adding 420 vehicle_positions
Exception occurred in iteration
(<class 'sqlalchemy.exc.InvalidRequestError'>, InvalidRequestError("This Session
's transaction has been rolled back due to a previous exception during flush. To
begin a new transaction with this Session, first issue Session.rollback(). Orig
inal exception was: (Error) ('01000', '[01000] [Microsoft][ODBC SQL Server Drive
r][TCP/IP Sockets]ConnectionRead (recv()). (65534) (SQLExecDirectW); [08S01] [Mi
crosoft][ODBC SQL Server Driver][TCP/IP Sockets]General network error. Check you
r network documentation. (11)') u'INSERT INTO trip_updates (trip_id, route_id, t
rip_start_time, trip_start_date, schedule_relationship, vehicle_id, vehicle_labe
l, vehicle_license_plate, timestamp) OUTPUT inserted.oid VALUES (?, ?, ?, ?, ?,
?, ?, ?, ?)' (u'19885030', u'', u'', u'20130501', 'SCHEDULED', u'', u'', u'', da
tetime.datetime(2013, 5, 1, 14, 0, 2))",), <traceback object at 0x00000001AE2BAF
88>)
Warning: feed version has changed: found 0.1, expected 1.0
Adding 871 trip updates
Warning: feed version has changed: found 0.1, expected 1.0
Adding 421 vehicle_positions
Exception occurred in iteration
(<class 'sqlalchemy.exc.InvalidRequestError'>, InvalidRequestError("This Session
's transaction has been rolled back due to a previous exception during flush. To
begin a new transaction with this Session, first issue Session.rollback(). Orig
inal exception was: (Error) ('01000', '[01000] [Microsoft][ODBC SQL Server Drive
r][TCP/IP Sockets]ConnectionRead (recv()). (65534) (SQLExecDirectW); [08S01] [Mi
crosoft][ODBC SQL Server Driver][TCP/IP Sockets]General network error. Check you
r network documentation. (11)') u'INSERT INTO trip_updates (trip_id, route_id, t
rip_start_time, trip_start_date, schedule_relationship, vehicle_id, vehicle_labe
l, vehicle_license_plate, timestamp) OUTPUT inserted.oid VALUES (?, ?, ?, ?, ?,
?, ?, ?, ?)' (u'19885030', u'', u'', u'20130501', 'SCHEDULED', u'', u'', u'', da
tetime.datetime(2013, 5, 1, 14, 0, 2))",), <traceback object at 0x00000001B5109F
C8>)
Warning: feed version has changed: found 0.1, expected 1.0
Adding 871 trip updates
Warning: feed version has changed: found 0.1, expected 1.0
Adding 421 vehicle_positions
Exception occurred in iteration
(<class 'sqlalchemy.exc.InvalidRequestError'>, InvalidRequestError("This Session
's transaction has been rolled back due to a previous exception during flush. To
begin a new transaction with this Session, first issue Session.rollback(). Orig
inal exception was: (Error) ('01000', '[01000] [Microsoft][ODBC SQL Server Drive
r][TCP/IP Sockets]ConnectionRead (recv()). (65534) (SQLExecDirectW); [08S01] [Mi
crosoft][ODBC SQL Server Driver][TCP/IP Sockets]General network error. Check you
r network documentation. (11)') u'INSERT INTO trip_updates (trip_id, route_id, t
rip_start_time, trip_start_date, schedule_relationship, vehicle_id, vehicle_labe
l, vehicle_license_plate, timestamp) OUTPUT inserted.oid VALUES (?, ?, ?, ?, ?,
?, ?, ?, ?)' (u'19885030', u'', u'', u'20130501', 'SCHEDULED', u'', u'', u'', da
tetime.datetime(2013, 5, 1, 14, 0, 2))",), <traceback object at 0x00000001B926C1
48>)
Warning: feed version has changed: found 0.1, expected 1.0
Adding 867 trip updates
Warning: feed version has changed: found 0.1, expected 1.0
Adding 420 vehicle_positions
Exception occurred in iteration
(<class 'sqlalchemy.exc.InvalidRequestError'>, InvalidRequestError("This Session
's transaction has been rolled back due to a previous exception during flush. To
begin a new transaction with this Session, first issue Session.rollback(). Orig
inal exception was: (Error) ('01000', '[01000] [Microsoft][ODBC SQL Server Drive
r][TCP/IP Sockets]ConnectionRead (recv()). (65534) (SQLExecDirectW); [08S01] [Mi
crosoft][ODBC SQL Server Driver][TCP/IP Sockets]General network error. Check you
r network documentation. (11)') u'INSERT INTO trip_updates (trip_id, route_id, t
rip_start_time, trip_start_date, schedule_relationship, vehicle_id, vehicle_labe
l, vehicle_license_plate, timestamp) OUTPUT inserted.oid VALUES (?, ?, ?, ?, ?,
?, ?, ?, ?)' (u'19885030', u'', u'', u'20130501', 'SCHEDULED', u'', u'', u'', da
tetime.datetime(2013, 5, 1, 14, 0, 2))",), <traceback object at 0x00000001AE2BAF
88>)
Warning: feed version has changed: found 0.1, expected 1.0
Adding 867 trip updates
Warning: feed version has changed: found 0.1, expected 1.0
Adding 420 vehicle_positions
Exception occurred in iteration
(<class 'sqlalchemy.exc.InvalidRequestError'>, InvalidRequestError("This Session
's transaction has been rolled back due to a previous exception during flush. To
begin a new transaction with this Session, first issue Session.rollback(). Orig
inal exception was: (Error) ('01000', '[01000] [Microsoft][ODBC SQL Server Drive
r][TCP/IP Sockets]ConnectionRead (recv()). (65534) (SQLExecDirectW); [08S01] [Mi
crosoft][ODBC SQL Server Driver][TCP/IP Sockets]General network error. Check you
r network documentation. (11)') u'INSERT INTO trip_updates (trip_id, route_id, t
rip_start_time, trip_start_date, schedule_relationship, vehicle_id, vehicle_labe
l, vehicle_license_plate, timestamp) OUTPUT inserted.oid VALUES (?, ?, ?, ?, ?,
?, ?, ?, ?)' (u'19885030', u'', u'', u'20130501', 'SCHEDULED', u'', u'', u'', da
tetime.datetime(2013, 5, 1, 14, 0, 2))",), <traceback object at 0x00000001BA2F49
08>)
Warning: feed version has changed: found 0.1, expected 1.0
Adding 866 trip updates
Warning: feed version has changed: found 0.1, expected 1.0
Adding 420 vehicle_positions
Exception occurred in iteration
(<class 'sqlalchemy.exc.InvalidRequestError'>, InvalidRequestError("This Session
's transaction has been rolled back due to a previous exception during flush. To
begin a new transaction with this Session, first issue Session.rollback(). Orig
inal exception was: (Error) ('01000', '[01000] [Microsoft][ODBC SQL Server Drive
r][TCP/IP Sockets]ConnectionRead (recv()). (65534) (SQLExecDirectW); [08S01] [Mi
crosoft][ODBC SQL Server Driver][TCP/IP Sockets]General network error. Check you
r network documentation. (11)') u'INSERT INTO trip_updates (trip_id, route_id, t
rip_start_time, trip_start_date, schedule_relationship, vehicle_id, vehicle_labe
l, vehicle_license_plate, timestamp) OUTPUT inserted.oid VALUES (?, ?, ?, ?, ?,
?, ?, ?, ?)' (u'19885030', u'', u'', u'20130501', 'SCHEDULED', u'', u'', u'', da
tetime.datetime(2013, 5, 1, 14, 0, 2))",), <traceback object at 0x00000001BAC516
48>)
Warning: feed version has changed: found 0.1, expected 1.0
Adding 866 trip updates
Warning: feed version has changed: found 0.1, expected 1.0
Adding 420 vehicle_positions
Exception occurred in iteration
(<class 'sqlalchemy.exc.InvalidRequestError'>, InvalidRequestError("This Session
's transaction has been rolled back due to a previous exception during flush. To
begin a new transaction with this Session, first issue Session.rollback(). Orig
inal exception was: (Error) ('01000', '[01000] [Microsoft][ODBC SQL Server Drive
r][TCP/IP Sockets]ConnectionRead (recv()). (65534) (SQLExecDirectW); [08S01] [Mi
crosoft][ODBC SQL Server Driver][TCP/IP Sockets]General network error. Check you
r network documentation. (11)') u'INSERT INTO trip_updates (trip_id, route_id, t
rip_start_time, trip_start_date, schedule_relationship, vehicle_id, vehicle_labe
l, vehicle_license_plate, timestamp) OUTPUT inserted.oid VALUES (?, ?, ?, ?, ?,
?, ?, ?, ?)' (u'19885030', u'', u'', u'20130501', 'SCHEDULED', u'', u'', u'', da
tetime.datetime(2013, 5, 1, 14, 0, 2))",), <traceback object at 0x00000001BA14EE
48>)
Warning: feed version has changed: found 0.1, expected 1.0
Adding 879 trip updates
Warning: feed version has changed: found 0.1, expected 1.0
Adding 418 vehicle_positions
Exception occurred in iteration
(<class 'sqlalchemy.exc.InvalidRequestError'>, InvalidRequestError("This Session
's transaction has been rolled back due to a previous exception during flush. To
begin a new transaction with this Session, first issue Session.rollback(). Orig
inal exception was: (Error) ('01000', '[01000] [Microsoft][ODBC SQL Server Drive
r][TCP/IP Sockets]ConnectionRead (recv()). (65534) (SQLExecDirectW); [08S01] [Mi
crosoft][ODBC SQL Server Driver][TCP/IP Sockets]General network error. Check you
r network documentation. (11)') u'INSERT INTO trip_updates (trip_id, route_id, t
rip_start_time, trip_start_date, schedule_relationship, vehicle_id, vehicle_labe
l, vehicle_license_plate, timestamp) OUTPUT inserted.oid VALUES (?, ?, ?, ?, ?,
?, ?, ?, ?)' (u'19885030', u'', u'', u'20130501', 'SCHEDULED', u'', u'', u'', da
tetime.datetime(2013, 5, 1, 14, 0, 2))",), <traceback object at 0x00000001BC6719
48>)
Warning: feed version has changed: found 0.1, expected 1.0
Adding 879 trip updates
Warning: feed version has changed: found 0.1, expected 1.0
Adding 418 vehicle_positions
Exception occurred in iteration
(<class 'sqlalchemy.exc.InvalidRequestError'>, InvalidRequestError("This Session
's transaction has been rolled back due to a previous exception during flush. To
begin a new transaction with this Session, first issue Session.rollback(). Orig
inal exception was: (Error) ('01000', '[01000] [Microsoft][ODBC SQL Server Drive
r][TCP/IP Sockets]ConnectionRead (recv()). (65534) (SQLExecDirectW); [08S01] [Mi
crosoft][ODBC SQL Server Driver][TCP/IP Sockets]General network error. Check you
r network documentation. (11)') u'INSERT INTO trip_updates (trip_id, route_id, t
rip_start_time, trip_start_date, schedule_relationship, vehicle_id, vehicle_labe
l, vehicle_license_plate, timestamp) OUTPUT inserted.oid VALUES (?, ?, ?, ?, ?,
?, ?, ?, ?)' (u'19885030', u'', u'', u'20130501', 'SCHEDULED', u'', u'', u'', da
tetime.datetime(2013, 5, 1, 14, 0, 2))",), <traceback object at 0x00000001BC93A1
88>)
Warning: feed version has changed: found 0.1, expected 1.0
Adding 877 trip updates

Add support for GTFS-rt occupancy

OccupancyStatus is an "experimental/proposed" GTFS-realtime field, as discussed on the gtfs-realtime list.

Here’s an explanation of the enumeration values:
https://groups.google.com/forum/#!msg/gtfs-realtime/_HtNTGp5LxM/0mvvEW1WuMMJ

...and the same text inline, for convenience:

 enum OccupancyStatus {
    // The vehicle is considered empty by most measures, and has few or no
    // passengers onboard, but is still accepting passengers.
    EMPTY = 0;

    // The vehicle has a relatively large percentage of seats available.
    // What percentage of free seats out of the total seats available is to be
    // considered large enough to fall into this category is determined at the
    // discretion of the producer.
    MANY_SEATS_AVAILABLE = 1;

    // The vehicle has a relatively small percentage of seats available.
    // What percentage of free seats out of the total seats available is to be
    // considered small enough to fall into this category is determined at the
    // discretion of the feed producer.
    FEW_SEATS_AVAILABLE = 2;

    // The vehicle can currently accommodate only standing passengers.
    STANDING_ROOM_ONLY = 3;

    // The vehicle can currently accommodate only standing passengers
    // and has limited space for them.
    CRUSHED_STANDING_ROOM_ONLY = 4;

    // The vehicle is considered full by most measures, but may still be
    // allowing passengers to board.
    FULL = 5;

    // The vehicle is not accepting additional passengers.
    NOT_ACCEPTING_PASSENGERS = 6;
  }

I'd like to add support for archiving the occupancy field.

We have a live GTFS-rt feed with this field for our USF Bull Runner shuttle:

• Protobuf - http://mobullity.forest.usf.edu:8088/vehicle-positions
• Plain text - http://mobullity.forest.usf.edu:8088/vehicle-positions?debug

Need to specify long ints in some cases

All of the int columns are just specified as integer. Some (epoch seconds, for example) should be uint64, &c.

Sequences are not used on primary key columns

Sequences should be used on primary key columns so that they auto-increment.

Fields in alerts are condensed, sometimes dropping information

When there is more than one TimeRange in an alert, only the first is used.
When there is more than one language available, the language specified on the command line or the string with no language (i.e., the default language for the feed) is used.

This is a tradeoff between creating more tables (to hold repeated elements), placing ugly, difficult to query JSON in the database, or losing some data.