afimb/gtfslib-python

Fare Rules - origin id requirement

3vivekb opened this issue · 2 comments

3vivekb commented

I have only just begun to use your package.

If you are using your program to import fare rules, it seems to be required to have an origin_id as well.

I am using the VTA GTFS http://transitfeeds.com/p/vta/45/20161229
The fare rules document is quite odd: http://transitfeeds.com/p/vta/45/20161229/file/fare_rules.txt
Our origin_id, destination_id, and contains_id are blank. As these are optional fields this is okay but not ideal.

If you run the load software towards postgres it fails with

  File "/Users/vivek/anaconda3/envs/py36/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 470, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (psycopg2.IntegrityError) null value in column "origin_id" violates not-null constraint
DETAIL:  Failing row contains (vta_2_2, 2, 101, null, null, null).
 [SQL: 'INSERT INTO fare_rules (feed_id, fare_id, route_id) VALUES (%(feed_id)s, %(fare_id)s, %(route_id)s)'] [parameters: {'feed_id': 'vta_2_2', 'fare_id': '2', 'route_id': '101'}]
(/Users/vivek/anaconda3/envs/py36) bash-3.2$ gtfsdbloader postgresql://vivek@localhost/gtfs --load Archive.zip --id vta_2_2 --lenient

I also removed the origin_id, destination_id, and contains_id columns and ran the loader again but it still failed with the same error.

I resolved this by removing both the fare_rules.txt as well as the fare_attributes.txt and the loader ran better but ran into different errors.

laurentg commented

Thanks for reporting. Indeed zone IDs in fare rules are optional in the GTFS specification and should be specified as such in the database model, so it's a bug. I'll have a look at this, this should be in all probability easy to solve. More information soon.

  • ❤️1
laurentg commented

It's a bit more complex than what I expected. The fields are correctly marked as nullable in the ORM layer, however as there is no unique ID for rules, and SQLAlchemy require a primary key for all tables, we use the combination (feed_id, fare_id, route_id, origin_id, destination_id, contains_id) as a composite primary key for this table (this combination is normally globally unique). BUT this has the side-effect of marking all fields used in this composite key as non-nullable (a primary key can't be declared as nullable in many databases, including postgresql).

The only solution is thus to add a new dummy primary key (autoincrement integer) for the table. This will break database compatibility with existing database, one must recreate the database and reload the data, or alter the fare rules table.

  • 👍1