opentransittools / gtfsdb Goto Github PK

gtfsdb is not (yet .. 2024) compatible with SqlAlchemy 2.x
installing from pypi might not be explicit regarding this requirement
improve this

(fyi, installing with buildout and using versions.cfg pin's the install to SqlAlchemy 1.x)

need a current patterns table like we have for stops & routes

I just tried doing a gtfsdb-load, and there is no longer a bin folder that is created after buildout. Can you update the readme to reflect the appropriate load instructions in its current form?

Thanks!

Adam

What was implemented was the simple option 1 for calculating the distance param ... a better option exists. Test whether the better option is needed, and then decided to execute that work (or close this ticket).

See #53, where it's detailed a 2nd better way to calculate the distance variable.
Also see #50 for more context...

Like #50, there is another (optional) distance value in GTFS. And to no surprise, RTD's gtfs is not populating those values. So as part of the post-process, we should also populate stop_time's shape_dist_traveled

Ideas:

• I guess we could do the 'as-the-crow-flies for stop-to-stop pairs too. That would be easier, and maybe a good first pass (and just good enough in general).
• a second idea would be to refer to the shape points to determine the stop_time distance (thinking this might be more accurate and more logically
• with this idea, we'd populate shapes first, then stop_time next, since stop_time will rely on those initial values (so this would be executed within shape's post process)
• the idea is to spatially match stop X to an entry in shape which matches both the trip's shape_id and also the nearest query. Thus we'd get our distance value. The problem here would be where a trip / shape visits a stop multiple times in the trip...would then be doing some gymnastics on which distance value to use--could be both slow and complicated.

Please add license information to the repo.

I'm building some tools to run gtfsdb and gtfsrdb automatically "in the cloud". The project is at https://github.com/znmeb/gtfs-collector; comments, etc. greatly appreciated. I have the basics running on Digital Ocean, but it turns out that a late stage of the database insertion process peaks around 5 GB, thus requiring an 8 GB droplet!

So I've dropped back to running gtfsdb on a workstation and creating a pg_dump backup, which I'll restore before collecting real-time data. But it would be nice if there was some way to limit the RAM usage of gtfsdb to something less expensive.

P.S.: I'm using https://github.com/CUTR-at-USF/gtfsrdb for the real-time collection. That seems to be working in a 1 GB droplet. Is there any reason I should or should not switch to https://github.com/OpenTransitTools/gtfsdb_realtime? I don't have any hard deadlines on this and I have plenty of operational things to do either way.

what: allow gtfsdb to connect to multiple databases and/or different schemas in a single instance
problem: currently gtfsdb monkey patches ORM classes when using either schema or geom tables (postgres), etc... Because of these 'global' changes, we can only connect to a single db / schema.
why: would like to connect to agency X and agency Y in same memory space.

RTD's gtfs does not have the (optional) shape_dist_traveled field populated.
Let's run thru each shape, and calculate those values.

Thus this:
shape_id,shape_pt_lat,shape_pt_lon,shape_pt_sequence
1160070,39.857378,-105.054026,1
1160070,39.857013,-105.053717,2
1160070,39.857013,-105.053718,3
...

Becomes this:
shape_id,shape_pt_lat,shape_pt_lon,shape_pt_sequence,shape_dist_traveled
1160070,39.857378,-105.054026,1,0.0
1160070,39.857013,-105.053717,2,55.0
1160070,39.857013,-105.053718,3,111.0
...

This will help with https://github.com/OpenTransitTools/trafficdb

Hi,
when I run
gtfsdb-load --database_url sqlite:///db.sqlite --is_geospatial http://www.comune.palermo.it/gtfs/amat_feed_gtfs_v7.zip

I have this error:
Traceback (most recent call last):
File "/usr/local/bin/gtfsdb-load", line 9, in
load_entry_point('gtfsdb==0.1.5', 'console_scripts', 'gtfsdb-load')()
File "/usr/local/lib/python2.7/dist-packages/gtfsdb/scripts.py", line 33, in gtfsdb_load
database_load(args.file, **kwargs)
File "/usr/local/lib/python2.7/dist-packages/gtfsdb/api.py", line 18, in database_load
db = Database(**kwargs)
File "/usr/local/lib/python2.7/dist-packages/gtfsdb/model/db.py", line 21, in init
config.DEFAULT_IS_GEOSPATIAL)
File "/usr/local/lib/python2.7/dist-packages/gtfsdb/model/db.py", line 56, in is_geospatial
cls.add_geometry_column()
File "/usr/local/lib/python2.7/dist-packages/gtfsdb/model/route.py", line 98, in add_geometry_column
from geoalchemy import GeometryColumn, GeometryDDL, MultiLineString
File "/usr/local/lib/python2.7/dist-packages/geoalchemy/init.py", line 2, in
from geoalchemy.base import *
File "/usr/local/lib/python2.7/dist-packages/geoalchemy/base.py", line 242, in
class SpatialComparator(ColumnProperty.ColumnComparator):
AttributeError: type object 'ColumnProperty' has no attribute 'ColumnComparator'

Some tips to solve.

Thank you very much

On a t2.small EC2 instance with 2GB of RAM, bin/gtfsdb-load http://developer.onebusaway.org/tmp/sound/gtfs/modified/1_gtfs.zip crashed with a segmentation fault during Block.post_process.

(The same load process completed successfully when the instance type was changed temporarily to an m4.10xlarge instance with 160GB of RAM.)

gtfsdb is currently (May 2024) SqlAlchemy 1.x bound
see #65

Hi,
I would add in the readme in Install and use via the gtfsdb source tree this point:

• install "buildout" with pip install zc.buildout

Thank you

While trying to load zipped GTFS data, as follow:

gtfsdb-load.exe --database_url postgresql://user:pass@localhost:5432/gtfs --is_geospatial "D:\work\projects\gtfs\gtfsdata.zip"

I receive an error message caused by urllib's urlretrieve method:

Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "C:\Users\Patrick\Anaconda3\envs\py27\lib\urllib.py", line 98, in urlretrieve
    return opener.retrieve(url, filename, reporthook, data)
  File "C:\Users\Patrick\Anaconda3\envs\py27\lib\urllib.py", line 245, in retrieve
    fp = self.open(url, data)
  File "C:\Users\Patrick\Anaconda3\envs\py27\lib\urllib.py", line 210, in open
    return self.open_unknown(fullurl, data)
  File "C:\Users\Patrick\Anaconda3\envs\py27\lib\urllib.py", line 222, in open_unknown
    raise IOError, ('url error', 'unknown url type', type)
IOError: [Errno url error] unknown url type: 'd'

However, if I preappend file:/// to the filename in gtfs.py, everything runs smoothly:

    def __init__(self, filename):
        self.file = filename
        print filename    # "D:\work\projects\gtfs\gtfsdata.zip"
        self.local_file = urlretrieve("file:///" + filename)[0]  # only for demonstration purposes

Is this really a bug?

Hi,
I have tied to upload the HVV GTFS
http://daten.transparenz.hamburg.de/Dataport.HmbTG.ZS.Webservice.GetRessource100/GetRessource100.svc/1fd45eb0-1f44-4f4d-82b4-26a9c3f49255/Upload__HVV_Rohdaten_GTFS_Fpl_20170907.zip
to mysql.

endet in a stacktrace:

 ./bin/gtfsdb-load --database_url mysql://gtfshvv:secret@localhost/gtfshvv ~/Downloads/Upload__HVV_Rohdaten_GTFS_Fpl_20170907.zip
14:25:45,622 DEBUG [/home/sven/git/gtfsdb/gtfsdb/model/db.pyc] create table: route_type
14:25:47,638 DEBUG [/home/sven/git/gtfsdb/gtfsdb/model/db.pyc] create table: route_filters
Traceback (most recent call last):
  File "./bin/gtfsdb-load", line 14, in <module>
    sys.exit(gtfsdb.scripts.gtfsdb_load())
  File "/home/sven/git/gtfsdb/gtfsdb/scripts.py", line 10, in gtfsdb_load
    database_load(args.file, **kwargs)
  File "/home/sven/git/gtfsdb/gtfsdb/api.py", line 18, in database_load
    db.create()
  File "/home/sven/git/gtfsdb/gtfsdb/model/db.py", line 40, in create
    cls.__table__.create(self.engine)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/schema.py", line 754, in create
    checkfirst=checkfirst)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1856, in _run_visitor
    conn._run_visitor(visitorcallable, element, **kwargs)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1481, in _run_visitor
    **kwargs).traverse_single(element)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/visitors.py", line 121, in traverse_single
    return meth(obj, **kw)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/ddl.py", line 764, in visit_table
    include_foreign_key_constraints=include_foreign_key_constraints
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 914, in execute
    return meth(self, multiparams, params)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/ddl.py", line 68, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 962, in _execute_ddl
    compiled = ddl.compile(dialect=dialect)
  File "<string>", line 1, in <lambda>
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/elements.py", line 494, in compile
    return self._compiler(dialect, bind=bind, **kw)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/ddl.py", line 26, in _compiler
    return dialect.ddl_compiler(dialect, self, **kw)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/compiler.py", line 190, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/compiler.py", line 213, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
    return meth(self, **kw)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/compiler.py", line 2168, in visit_create_table
    (table.description, column.name, ce.args[0])
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/util/compat.py", line 202, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/compiler.py", line 2157, in visit_create_table
    and not first_pk)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/compiler.py", line 213, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
    return meth(self, **kw)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/compiler.py", line 2188, in visit_create_column
    first_pk=first_pk
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/dialects/mysql/base.py", line 1977, in get_column_specification
    column.type, type_expression=column)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/compiler.py", line 261, in process
    return type_._compiler_dispatch(self, **kw)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
    return meth(self, **kw)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/compiler.py", line 2589, in visit_string
    return self.visit_VARCHAR(type_, **kw)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/dialects/mysql/base.py", line 2329, in visit_VARCHAR
    self.dialect.name)
sqlalchemy.exc.CompileError: (in table 'route_filters', column 'description'): VARCHAR requires a length on dialect mysql

gtfsdb firstly will need to write .gtfs files out (not yet implemented)
secondly, there will be a process to filter the export based on route id
any other filters? dates? region?

extra credit:

• be able to merge multiple agencies (that reside in different schemas) in the export
• that would mean doing something with id's (hyphen ... or one id ... or ???)

I'm trying to build a database with information from multiple gtfs feeds around the US. gtfsdb does a great job of representing the data in postgres, but overwrites existing data. Is there any way to change this?

puppet is a better build environment ... time to make the switch?

buildout install prod postgresql
Develop: '/common/git/gtfsdb/.'
/usr/local/lib/pypy2.7/dist-packages/setuptools/dist.py:285: UserWarning: Normalizing '0.1.6dev' to '0.1.6.dev0'
normalized_version,
Updating prod.
Installing postgresql.
Getting distribution for 'psycopg2>=2.4.2'.
psycopg/pqpath.c: In function ‘pq_read_replication_message’:
psycopg/pqpath.c:1673:5: warning: implicit declaration of function ‘gettimeofday’ [-Wimplicit-function-declaration]
gettimeofday(&repl->last_io, NULL);
^
psycopg/pqpath.c: In function ‘pq_copy_both’:
psycopg/pqpath.c:1820:13: warning: implicit declaration of function ‘timeradd’ [-Wimplicit-function-declaration]
timeradd(&repl->last_io, &keep_intr, &ping_time);
^
psycopg/pqpath.c:1821:13: warning: implicit declaration of function ‘timersub’ [-Wimplicit-function-declaration]
timersub(&ping_time, &curr_time, &timeout);
^
psycopg/libpq_support.c: In function ‘feGetCurrentTimestamp’:
psycopg/libpq_support.c:55:5: warning: implicit declaration of function ‘gettimeofday’ [-Wimplicit-function-declaration]
gettimeofday(&tp, NULL);
^
psycopg/connection_int.c: In function ‘conn_get_python_codec’:
psycopg/connection_int.c:479:21: warning: implicit declaration of function ‘PyCodec_Encoder’ [-Wimplicit-function-declaration]
if (!(enc_tmp = PyCodec_Encoder(Bytes_AS_STRING(encname)))) { goto exit; }
^
psycopg/connection_int.c:479:19: warning: assignment makes pointer from integer without a cast [-Wint-conversion]
if (!(enc_tmp = PyCodec_Encoder(Bytes_AS_STRING(encname)))) { goto exit; }
^
psycopg/connection_int.c:480:21: warning: implicit declaration of function ‘PyCodec_Decoder’ [-Wimplicit-function-declaration]
if (!(dec_tmp = PyCodec_Decoder(Bytes_AS_STRING(encname)))) { goto exit; }
^
psycopg/connection_int.c:480:19: warning: assignment makes pointer from integer without a cast [-Wint-conversion]
if (!(dec_tmp = PyCodec_Decoder(Bytes_AS_STRING(encname)))) { goto exit; }
^
psycopg/replication_cursor_type.c: In function ‘psyco_repl_curs_start_replication_expert’:
psycopg/replication_cursor_type.c:77:9: warning: implicit declaration of function ‘gettimeofday’ [-Wimplicit-function-declaration]
gettimeofday(&self->last_io, NULL);
^
psycopg/adapter_datetime.c: In function ‘_pydatetime_string_delta’:
psycopg/adapter_datetime.c:103:16: error: ‘PyDateTime_Delta {aka struct }’ has no member named ‘microseconds’
int a = obj->microseconds;
^
psycopg/adapter_datetime.c:112:32: error: ‘PyDateTime_Delta {aka struct }’ has no member named ‘days’
obj->days, obj->seconds, buffer);
^
psycopg/adapter_datetime.c:112:43: error: ‘PyDateTime_Delta {aka struct }’ has no member named ‘seconds’
obj->days, obj->seconds, buffer);
^
error: Setup script exited with error: command 'cc' failed with exit status 1
An error occurred when trying to install /tmp/tmpm_VB_Lget_dist/psycopg2-2.7.3.2.tar.gz. Look above this message for any errors that were output by easy_install.
While:
Installing postgresql.
Getting distribution for 'psycopg2>=2.4.2'.

An internal error occurred due to a bug in either zc.buildout or in a
recipe being used:
Traceback (most recent call last):
File "/usr/local/lib/pypy2.7/dist-packages/zc/buildout/buildout.py", line 2123, in main
getattr(buildout, command)(args)
File "/usr/local/lib/pypy2.7/dist-packages/zc/buildout/buildout.py", line 796, in install
installed_files = self[part]._call(recipe.install)
File "/usr/local/lib/pypy2.7/dist-packages/zc/buildout/buildout.py", line 1553, in _call
return f()
File "/common/git/gtfsdb/eggs/zc.recipe.egg-2.0.4-py2.7.egg/zc/recipe/egg/egg.py", line 193, in install
reqs, ws = self.working_set()
File "/common/git/gtfsdb/eggs/zc.recipe.egg-2.0.4-py2.7.egg/zc/recipe/egg/egg.py", line 82, in working_set
allow_hosts=self.allow_hosts,
File "/common/git/gtfsdb/eggs/zc.recipe.egg-2.0.4-py2.7.egg/zc/recipe/egg/egg.py", line 135, in _working_set
allow_hosts=allow_hosts)
File "/usr/local/lib/pypy2.7/dist-packages/zc/buildout/easy_install.py", line 913, in install
return installer.install(specs, working_set)
File "/usr/local/lib/pypy2.7/dist-packages/zc/buildout/easy_install.py", line 714, in install
for dist in self._get_dist(req, ws):
File "/usr/local/lib/pypy2.7/dist-packages/zc/buildout/easy_install.py", line 563, in _get_dist
dists = [_move_to_eggs_dir_and_compile(dist, self._dest)]
File "/usr/local/lib/pypy2.7/dist-packages/zc/buildout/easy_install.py", line 1699, in _move_to_eggs_dir_and_compile
[tmp_loc] = glob.glob(os.path.join(tmp_dest, '*'))
ValueError: expected length 1, got 0

calculate all the patterns belonging to a route into route_pattern

further, do we need a current_route_pattern? alt, do we really need a current_pattern table, ala #45 ?

I think we need current_route_pattern, since we currently have route_id 100 (which doesn't change over time), but the list of patterns for route_id 100 do change over time.

And with a current_route_pattern, do we need a current_pattern table?

I've encountered this error while imporing the german VBB gtfs data from http://daten.berlin.de/datensaetze/vbb-fahrplandaten-april-bis-dezember-2016

The tool also crashes completely when using mysql because VBB has many empty columns which are not taken care of

13:54:21,280 DEBUG [gtfsdb.model.route] RouteType.load (1 seconds)
13:54:21,702 DEBUG [gtfsdb.model.route] RouteFilter.load (0 seconds)
13:54:21,702 DEBUG [gtfsdb.model.feed_info] FeedInfo.load (0 seconds)
Traceback (most recent call last):
  File "bin/gtfsdb-load", line 14, in <module>
    sys.exit(gtfsdb.scripts.gtfsdb_load())
  File "/opt/gtfsdb/gtfsdb/scripts.py", line 10, in gtfsdb_load
    database_load(args.file, **kwargs)
  File "/opt/gtfsdb/gtfsdb/api.py", line 19, in database_load
    gtfs.load(db, **kwargs)
  File "/opt/gtfsdb/gtfsdb/model/gtfs.py", line 34, in load
    cls.load(db, **load_kwargs)
  File "/opt/gtfsdb/gtfsdb/model/base.py", line 143, in load
    db.engine.execute(table.insert(), records)
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1991, in execute
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 914, in execute
  File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1146, in _execute_context
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception
  File "build/bdist.linux-x86_64/egg/sqlalchemy/util/compat.py", line 200, in raise_from_cause
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1116, in _execute_context
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/default.py", line 447, in do_executemany
sqlalchemy.exc.ProgrammingError: (sqlite3.ProgrammingError) You must not use 8-bit bytestrings unless you use a text_factory that can interpret 8-bit bytestrings (like text_factory = str). It is highly recommended that you instead just switch your application to Unicode strings. [SQL: u'INSERT INTO agency (agency_id, agency_name, agency_url, agency_timezone, agency_lang, agency_phone) VALUES (?, ?, ?, ?, ?, ?)'] [parameters: (('VBB', 'Verkehrsverbund Brandenburg-Berlin', 'http://www.vbb.de', 'Europe/Berlin', 'de', None), ('ANG---', 'G\xc3\xbcnter Anger G\xc3\xbcterverkehrs GmbH & Co. Omnibusvermietung KG', 'http://www.anger-busvermietung.de', 'Europe/Berlin', 'de', '033208 22010'), ('BEH---', 'Fritz Behrendt OHG', 'http://www.behrendt-touristik.de', 'Europe/Berlin', 'de', '0331 7491400'), ('BMO---', 'Busverkehr M\xc3\xa4rkisch-Oderland GmbH', 'http://www.busmol.de', 'Europe/Berlin', 'de', '03341 478383'), ('BON---', 'Busverkehr Oder-Spree GmbH', 'http://www.bos-fw.de', 'Europe/Berlin', 'de', '03361 556133'), ('BRB---', 'Verkehrsbetriebe Brandenburg an der Havel GmbH', 'http://www.vbbr.de', 'Europe/Berlin', 'de', '03381 5340'), ('BRT---', 'Verkehrsbetriebe Brandenburg an der Havel GmbH', 'http://www.vbbr.de', 'Europe/Berlin', 'de', '03381 5340'), ('BTS___', 'Barnimer Busgesellschaft mbH', 'http://www.bbg-eberswalde.de', 'Europe/Berlin', 'de', '03334 235003')  ... displaying 10 of 60 total bound parameter sets ...  ('WEZ---', 'Wetzel', 'http://www.wetzel-cammer.de', 'Europe/Berlin', 'de', '033835 228'), ('WS----', 'Woltersdorfer Stra\xc3\x9fenbahn GmbH', 'http://www.woltersdorfer-strassenbahn.com', 'Europe/Berlin', 'de', '03362 881230'))]

https://requests.readthedocs.io/en/master/

RTD gives a 403 error with current implementation.
Plus, the current urlretrieve(filename)[0] is mentioned in the py 3.x docs as possibly deprecated in the future

Having a list of sql and orm queries could help users.

https://stackoverflow.com/questions/30419707/sample-gtfs-sql-queries (just found this one):

• Given the current time, list the positions of all vehicles along a particular route. I'm not sure if it's possible to estimate the lat/long for the point between two stops given a route and its shape? Maybe this is more complex or not required.
• List all stop locations (lat/long) along a particular route.
• List all transfer locations with options (lat/long) along a particular route.

Hello,

I hope this is the right place to post this kind of question. When I run gtfsdb-load, I get a bunch of debug messages and I can't find any evidence that a sqlite database was created. Here's what I get. (I am using ipython, thus the ! to run shell commands).

In [34]: !gtfsdb-load https://code.google.com/transit/spec/sample-feed.zip
13:10:12,464 DEBUG [gtfsdb.model.gtfs] GTFS.load: https://code.google.com/transit/spec/sample-feed.zip
13:10:12,471 DEBUG [gtfsdb.model.route] RouteType.load (0 seconds)
13:10:12,474 DEBUG [gtfsdb.model.stop_feature] StopFeatureType.load (0 seconds)
13:10:12,474 DEBUG [gtfsdb.model.feed_info] FeedInfo.load (0 seconds)
13:10:12,475 DEBUG [gtfsdb.model.agency] Agency.load (0 seconds)
13:10:12,480 DEBUG [gtfsdb.model.calendar] Calendar.load (0 seconds)
13:10:12,482 DEBUG [gtfsdb.model.calendar] CalendarDate.load (0 seconds)
13:10:12,483 DEBUG [gtfsdb.model.route] Route.load (0 seconds)
13:10:12,485 DEBUG [gtfsdb.model.stop] Stop.load (0 seconds)
13:10:12,486 DEBUG [gtfsdb.model.stop_feature] StopFeature.load (0 seconds)
13:10:12,486 DEBUG [gtfsdb.model.transfer] Transfer.load (0 seconds)
13:10:12,486 DEBUG [gtfsdb.model.shape] Shape.load (0 seconds)
/Users/herbie/Library/Enthought/Canopy_64bit/User/lib/python2.7/site-packages/SQLAlchemy-1.1.2-py2.7-macosx-10.6-x86_64.egg/sqlalchemy/sql/sqltypes.py:596: SAWarning: Dialect sqlite+pysqlite does *not* support Decimal objects natively, and SQLAlchemy must convert from floating point - rounding errors and other issues may occur. Please consider storing Decimal numbers as strings or integers on this platform for lossless storage.
13:10:12,489 DEBUG [gtfsdb.model.shape] Pattern.load (0 seconds)
13:10:12,491 DEBUG [gtfsdb.model.trip] Trip.load (0 seconds)
13:10:12,495 DEBUG [gtfsdb.model.stop_time] StopTime.load (0 seconds)
13:10:12,496 DEBUG [gtfsdb.model.frequency] Frequency.load (0 seconds)
13:10:12,498 DEBUG [gtfsdb.model.fare] FareAttribute.load (0 seconds)
13:10:12,500 DEBUG [gtfsdb.model.fare] FareRule.load (0 seconds)
13:10:12,930 DEBUG [gtfsdb.model.calendar] UniversalCalendar.load (0 seconds)
13:10:12,931 DEBUG [gtfsdb.model.gtfs] GTFS.load (0 seconds)

Any help or advice is much appreciated.

I'm currently building a database from the NJ Transit bus GTFS files. The data is about 200MB uncompressed. It's taking 3+ days to build the database. Is that expected? Or do you think this could be an issue with my Postgres host?

I've searched around the repo's discussions to see if expected processing times is mentioned anywhere and haven't had any luck.

Hi, sorry to bother you, i just start to use python and i want to try out gtfsdb but i must miss something on the command line:
bin\gtfsdb-load --database_url jdbc:mysql://localhost:3306/ file:///C:/Users/Utente/Desktop/ac-transit_20150218_1708.zip

Is very similar to a past issue but i'm just stuck whit that, so forgive me if the error is patently stupid.

The error i get is:

Traceback (most recent call last):
File "C:\Users\Utente\Desktop\gtfsdb\bin\gtfsdb-load-script.py", line 13, in
sys.exit(gtfsdb.scripts.gtfsdb_load())
File "c:\users\utente\desktop\gtfsdb\gtfsdb\scripts.py", line 10, in gtfsdb_load
database_load(args.file, **kwargs)
File "c:\users\utente\desktop\gtfsdb\gtfsdb\api.py", line 16, in database_load
db = Database(**kwargs)
File "c:\users\utente\desktop\gtfsdb\gtfsdb\model\db.py", line 21, in init
self.url = kwargs.get('url', config.DEFAULT_DATABASE_URL)
File "c:\users\utente\desktop\gtfsdb\gtfsdb\model\db.py", line 96, in url
self.engine = create_engine(val)
File "c:\users\utente\desktop\gtfsdb\eggs\sqlalchemy-1.0.12-py2.7.egg\sqlalchemy\engine__init__.py", line 386, in create_engine
return strategy.create(_args, *_kwargs)
File "c:\users\utente\desktop\gtfsdb\eggs\sqlalchemy-1.0.12-py2.7.egg\sqlalchemy\engine\strategies.py", line 49, in create
u = url.make_url(name_or_url)
File "c:\users\utente\desktop\gtfsdb\eggs\sqlalchemy-1.0.12-py2.7.egg\sqlalchemy\engine\url.py", line 186, in make_url
return _parse_rfc1738_args(name_or_url)
File "c:\users\utente\desktop\gtfsdb\eggs\sqlalchemy-1.0.12-py2.7.egg\sqlalchemy\engine\url.py", line 235, in _parse_rfc1738_args
"Could not parse rfc1738 URL from string '%s'" % name)
sqlalchemy.exc.ArgumentError: Could not parse rfc1738 URL from string 'jdbc:mysql://localhost:3306/'

TY in advance for any help.

Hi all,

Been struggling for couple days to get this working but I am failing each time and running out of answers. I can't seem to get the buildout to run properly:

(try) C:\gtfs\gtfsdb>buildout install prod
Develop: 'C:\gtfs\gtfsdb\.'
c:\gtfs\try\lib\site-packages\setuptools\dist.py:352: UserWarning: Normalizing '0.1.6dev' to '0.1.6.dev0'
normalized_version,
Installing prod.
Getting distribution for 'geoalchemy2>=0.2.4'.
While:
Installing prod.
Getting distribution for 'geoalchemy2>=0.2.4'.
An internal error occurred due to a bug in either zc.buildout or in a
recipe being used:
Traceback (most recent call last):
File "c:\gtfs\try\lib\site-packages\zc.buildout-2.9.4-py2.7.egg\zc\buildout\buildout.py", line 2123, in main
getattr(buildout, command)(args)
File "c:\gtfs\try\lib\site-packages\zc.buildout-2.9.4-py2.7.egg\zc\buildout\buildout.py", line 796, in install
installed_files = self[part]._call(recipe.install)
File "c:\gtfs\try\lib\site-packages\zc.buildout-2.9.4-py2.7.egg\zc\buildout\buildout.py", line 1553, in _call
return f()
File "c:\gtfs\gtfsdb\eggs\zc.recipe.egg-2.0.3-py2.7.egg\zc\recipe\egg\egg.py", line 126, in install
reqs, ws = self.working_set()
File "c:\gtfs\gtfsdb\eggs\zc.recipe.egg-2.0.3-py2.7.egg\zc\recipe\egg\egg.py", line 84, in working_set
allow_hosts=self.allow_hosts)
File "c:\gtfs\try\lib\site-packages\zc.buildout-2.9.4-py2.7.egg\zc\buildout\easy_install.py", line 913, in install
return installer.install(specs, working_set)
File "c:\gtfs\try\lib\site-packages\zc.buildout-2.9.4-py2.7.egg\zc\buildout\easy_install.py", line 714, in install
for dist in self._get_dist(req, ws):
File "c:\gtfs\try\lib\site-packages\zc.buildout-2.9.4-py2.7.egg\zc\buildout\easy_install.py", line 563, in _get_dist
dists = [_move_to_eggs_dir_and_compile(dist, self._dest)]
File "c:\gtfs\try\lib\site-packages\zc.buildout-2.9.4-py2.7.egg\zc\buildout\easy_install.py", line 1730, in _move_to_eggs_dir_and_compile
assert newdist is not None # newloc above is missing our dist?!
AssertionError

is_interline = trip_id's end_stop_id === next_trip_id's begin_stop_id

dwell_time = next_trip_id's stop_time at its begin_stop_id - trip_id's stop_time at its end_stop_id
(e.g., begin_stop_id === end_stop_id ala is_interline shown above)

After figuring out how to finally get passed the AssertionError I stumbled upon yet another one:

(try) C:\gtfs\try\gtfsdb>bin\gtfsdb-load --database_url postgres://postgres:kevin@localhost:5432/number1 --is_geospatial C:\gtfs\data\gtfs.zip
21:10:21,648 DEBUG [c:\gtfs\try\gtfsdb\gtfsdb\model\db.pyc] create table: route_type
21:10:21,707 DEBUG [c:\gtfs\try\gtfsdb\gtfsdb\model\db.pyc] create table: route_filters
21:10:21,717 DEBUG [c:\gtfs\try\gtfsdb\gtfsdb\model\db.pyc] create table: feed_info
21:10:21,724 DEBUG [c:\gtfs\try\gtfsdb\gtfsdb\model\db.pyc] create table: agency
21:10:21,744 DEBUG [c:\gtfs\try\gtfsdb\gtfsdb\model\db.pyc] create table: blocks
21:10:21,773 DEBUG [c:\gtfs\try\gtfsdb\gtfsdb\model\db.pyc] create table: calendar
21:10:21,786 DEBUG [c:\gtfs\try\gtfsdb\gtfsdb\model\db.pyc] create table: calendar_dates
21:10:21,796 DEBUG [c:\gtfs\try\gtfsdb\gtfsdb\model\db.pyc] create table: routes
21:10:21,846 DEBUG [c:\gtfs\try\gtfsdb\gtfsdb\model\db.pyc] create table: route_directions
21:10:21,861 DEBUG [c:\gtfs\try\gtfsdb\gtfsdb\model\db.pyc] create table: stops
21:10:21,881 DEBUG [c:\gtfs\try\gtfsdb\gtfsdb\model\db.pyc] create table: stop_features
21:10:21,898 DEBUG [c:\gtfs\try\gtfsdb\gtfsdb\model\db.pyc] create table: transfers
21:10:21,911 DEBUG [c:\gtfs\try\gtfsdb\gtfsdb\model\db.pyc] create table: shapes
21:10:21,928 DEBUG [c:\gtfs\try\gtfsdb\gtfsdb\model\db.pyc] create table: patterns
21:10:21,943 DEBUG [c:\gtfs\try\gtfsdb\gtfsdb\model\db.pyc] create table: trips
21:10:21,971 DEBUG [c:\gtfs\try\gtfsdb\gtfsdb\model\db.pyc] create table: stop_times
21:10:21,996 DEBUG [c:\gtfs\try\gtfsdb\gtfsdb\model\db.pyc] create table: route_stops
21:10:22,025 DEBUG [c:\gtfs\try\gtfsdb\gtfsdb\model\db.pyc] create table: frequencies
21:10:22,030 DEBUG [c:\gtfs\try\gtfsdb\gtfsdb\model\db.pyc] create table: fare_attributes
21:10:22,042 DEBUG [c:\gtfs\try\gtfsdb\gtfsdb\model\db.pyc] create table: fare_rules
21:10:22,055 DEBUG [c:\gtfs\try\gtfsdb\gtfsdb\model\db.pyc] create table: universal_calendar
Traceback (most recent call last):
File "C:\gtfs\try\gtfsdb\bin\gtfsdb-load-script.py", line 14, in
sys.exit(gtfsdb.scripts.gtfsdb_load())
File "c:\gtfs\try\gtfsdb\gtfsdb\scripts.py", line 10, in gtfsdb_load
database_load(args.file, **kwargs)
File "c:\gtfs\try\gtfsdb\gtfsdb\api.py", line 19, in database_load
gtfs = GTFS(filename)
File "c:\gtfs\try\gtfsdb\gtfsdb\model\gtfs.py", line 20, in init
self.local_file = urlretrieve(filename)[0]
File "c:\python27\Lib\urllib.py", line 98, in urlretrieve
return opener.retrieve(url, filename, reporthook, data)
File "c:\python27\Lib\urllib.py", line 245, in retrieve
fp = self.open(url, data)
File "c:\python27\Lib\urllib.py", line 210, in open
return self.open_unknown(fullurl, data)
File "c:\python27\Lib\urllib.py", line 222, in open_unknown
raise IOError, ('url error', 'unknown url type', type)
IOError: [Errno url error] unknown url type: 'c'

From what I can tell the URL I provided is incorrect. But I don't understand why this can be incorrect.

Whth python 3.8, I found an error while building GTFSDB with zc.buildout. I think this is du to incompatibility of psycopg2, so updated version.conf and now it works. I also found some warnings caused by zc.buildout, but it somehow works well.

Error:

$ buildout install prod postgresql
/usr/local/lib/python3.8/site-packages/setuptools/distutils_patch.py:25: UserWarning: Distutils was imported before Setuptools. This usage is discouraged and may exhibit undesirable behaviors or errors. Please use Setuptools' objects directly or at least import Setuptools first.
warnings.warn(
Creating directory '/Users/XXX/gtfsdb/eggs'.
Creating directory '/Users/XXX/gtfsdb/bin'.
Creating directory '/Users/XXX/gtfsdb/parts'.
Creating directory '/Users/XXX/gtfsdb/develop-eggs'.
Develop: '/Users/XXX/gtfsdb/.'
Warning: 'classifiers' should be a list, got type 'tuple'
Getting distribution for 'zc.recipe.egg==2.0.7'.
WARNING: The easy_install command is deprecated and will be removed in a future version.
Got zc.recipe.egg 2.0.7.
Unused options for buildout: 'allowed-eggs-from-site-packages' 'app-egg-name' 'include-site-packages'.
Installing prod.
Getting distribution for 'psycopg2==2.7.7'.
WARNING: The easy_install command is deprecated and will be removed in a future version.
psycopg/psycopgmodule.c:689:18: error: incomplete definition of type 'struct _is'
while (interp->next)
~~~~~~^
/usr/local/Cellar/[email protected]/3.8.5/Frameworks/Python.framework/Versions/3.8/include/python3.8/pystate.h:20:8: note: forward declaration of 'struct _is'
struct _is;
^
psycopg/psycopgmodule.c:690:24: error: incomplete definition of type 'struct _is'
interp = interp->next;
~~~~~~^
/usr/local/Cellar/[email protected]/3.8.5/Frameworks/Python.framework/Versions/3.8/include/python3.8/pystate.h:20:8: note: forward declaration of 'struct _is'
struct _is;
^
2 errors generated.

It appears you are missing some prerequisite to build the package from source.

You may install a binary package by installing 'psycopg2-binary' from PyPI.
If you want to install psycopg2 from source, please install the packages
required for the build and try again.

For further information please check the 'doc/src/install.rst' file (also at
http://initd.org/psycopg/docs/install.html).

error: Setup script exited with error: command 'clang' failed with exit status 1
An error occurred when trying to install /var/folders/b9/lqn5qty9425_wnngw1bz2n2r0000gn/T/tmp9xbfppb2get_dist/psycopg2-2.7.7.tar.gz. Look above this message for any errors that were output by easy_install.
While:
Installing prod.
Getting distribution for 'psycopg2==2.7.7'.

An internal error occurred due to a bug in either zc.buildout or in a
recipe being used:
Traceback (most recent call last):
File "/usr/local/lib/python3.8/site-packages/zc/buildout/buildout.py", line 2174, in main
getattr(buildout, command)(args)
File "/usr/local/lib/python3.8/site-packages/zc/buildout/buildout.py", line 817, in install
installed_files = self[part]._call(recipe.install)
File "/usr/local/lib/python3.8/site-packages/zc/buildout/buildout.py", line 1603, in _call
return f()
File "/Users/XXX/gtfsdb/eggs/zc.recipe.egg-2.0.7-py3.8.egg/zc/recipe/egg/egg.py", line 227, in install
reqs, ws = self.working_set()
File "/Users/XXX/gtfsdb/eggs/zc.recipe.egg-2.0.7-py3.8.egg/zc/recipe/egg/egg.py", line 78, in working_set
ws = self._working_set(
File "/Users/XXX/gtfsdb/eggs/zc.recipe.egg-2.0.7-py3.8.egg/zc/recipe/egg/egg.py", line 161, in _working_set
ws = zc.buildout.easy_install.install(
File "/usr/local/lib/python3.8/site-packages/zc/buildout/easy_install.py", line 957, in install
return installer.install(specs, working_set)
File "/usr/local/lib/python3.8/site-packages/zc/buildout/easy_install.py", line 730, in install
for dist in self._get_dist(req, ws):
File "/usr/local/lib/python3.8/site-packages/zc/buildout/easy_install.py", line 574, in _get_dist
dists = [_move_to_eggs_dir_and_compile(dist, self._dest)]
File "/usr/local/lib/python3.8/site-packages/zc/buildout/easy_install.py", line 1745, in _move_to_eggs_dir_and_compile
[tmp_loc] = glob.glob(os.path.join(tmp_dest, '*'))
ValueError: not enough values to unpack (expected 1, got 0)
$

Quick Fix:
Fix "psycopg2 = 2.7.7" in versions.cfg into psycopg2 = 2.8.5

Environment:
macOS Catalina 10.15.6
Homebrew 2.4.13-97-g1b4e1ae
Python 3.8.5
zc.buildout 2.13.3

stop.py:85: SADeprecationWarning: The joinedload_all() function is deprecated, and will be removed in a future release. Please use method chaining with joinedload() instead

should have a versions.cfg lock file in this project
lock to latest SqlAlchemy and other deps
document any issues to upgrade to latest SqlAlchemy, if current code can't be easily up'd
cut pypi release -- new major or point version?

I'm trying to import this file (ftp://ftp.geo.euskadi.net/cartografia/Transporte/Moveuskadi/ATTG/lurraldebus_ekialdebus/google_transit.zip) into my database, but after executing the command

gtfsdb-load --database_url postgresql://postgres@ip:5432/lurraldebus_ekialdebus --is_geospatial google_transit.zip

I get the following error:

12:55:15,103 DEBUG [/home/joninazio/automatizar/gtfsdb/gtfsdb/model/db.pyc] create table: route_type
12:55:15,492 DEBUG [/home/joninazio/automatizar/gtfsdb/gtfsdb/model/db.pyc] create table: route_filters
12:55:15,810 DEBUG [/home/joninazio/automatizar/gtfsdb/gtfsdb/model/db.pyc] create table: feed_info
12:55:15,920 DEBUG [/home/joninazio/automatizar/gtfsdb/gtfsdb/model/db.pyc] create table: agency
12:55:16,110 DEBUG [/home/joninazio/automatizar/gtfsdb/gtfsdb/model/db.pyc] create table: calendar
12:55:16,318 DEBUG [/home/joninazio/automatizar/gtfsdb/gtfsdb/model/db.pyc] create table: calendar_dates
12:55:16,535 DEBUG [/home/joninazio/automatizar/gtfsdb/gtfsdb/model/db.pyc] create table: routes
12:55:16,985 DEBUG [/home/joninazio/automatizar/gtfsdb/gtfsdb/model/db.pyc] create table: route_directions
12:55:17,394 DEBUG [/home/joninazio/automatizar/gtfsdb/gtfsdb/model/db.pyc] create table: stops
12:55:17,722 DEBUG [/home/joninazio/automatizar/gtfsdb/gtfsdb/model/db.pyc] create table: stop_features
12:55:18,005 DEBUG [/home/joninazio/automatizar/gtfsdb/gtfsdb/model/db.pyc] create table: transfers
12:55:18,188 DEBUG [/home/joninazio/automatizar/gtfsdb/gtfsdb/model/db.pyc] create table: shapes
12:55:18,521 DEBUG [/home/joninazio/automatizar/gtfsdb/gtfsdb/model/db.pyc] create table: patterns
12:55:18,755 DEBUG [/home/joninazio/automatizar/gtfsdb/gtfsdb/model/db.pyc] create table: trips
12:55:19,130 DEBUG [/home/joninazio/automatizar/gtfsdb/gtfsdb/model/db.pyc] create table: stop_times
12:55:19,516 DEBUG [/home/joninazio/automatizar/gtfsdb/gtfsdb/model/db.pyc] create table: route_stops
12:55:19,925 DEBUG [/home/joninazio/automatizar/gtfsdb/gtfsdb/model/db.pyc] create table: frequencies
12:55:20,008 DEBUG [/home/joninazio/automatizar/gtfsdb/gtfsdb/model/db.pyc] create table: fare_attributes
12:55:20,108 DEBUG [/home/joninazio/automatizar/gtfsdb/gtfsdb/model/db.pyc] create table: fare_rules
12:55:20,341 DEBUG [/home/joninazio/automatizar/gtfsdb/gtfsdb/model/db.pyc] create table: universal_calendar
12:55:20,554 DEBUG [gtfsdb.model.gtfs] GTFS.load: /home/joninazio/descargas_automatizacion/lurraldebus_ekialdebus/20151105131037/google_transit.zip
12:55:20,701 DEBUG [gtfsdb.model.route] RouteType.load (0 seconds)
12:55:20,718 DEBUG [gtfsdb.model.route] RouteFilter.load (0 seconds)
12:55:20,718 DEBUG [gtfsdb.model.feed_info] FeedInfo.load (0 seconds)
12:55:20,733 DEBUG [gtfsdb.model.agency] Agency.load (0 seconds)
12:55:20,734 DEBUG [gtfsdb.model.calendar] Calendar.load (0 seconds)
************12:56:19,349 DEBUG [gtfsdb.model.calendar] CalendarDate.load (59 seconds)
12:56:19,372 DEBUG [gtfsdb.model.route] Route.load (0 seconds)
12:56:19,372 DEBUG [gtfsdb.model.route] RouteDirection.load (0 seconds)
12:56:19,489 DEBUG [gtfsdb.model.stop] Stop.load (0 seconds)
12:56:19,489 DEBUG [gtfsdb.model.stop_feature] StopFeature.load (0 seconds)
12:56:19,489 DEBUG [gtfsdb.model.transfer] Transfer.load (0 seconds)
***_12:57:08,222 DEBUG [gtfsdb.model.shape] Shape.load (49 seconds)
12:57:33,950 DEBUG [gtfsdb.model.shape] Pattern.load (26 seconds)
12:57:34,563 DEBUG [gtfsdb.model.trip] Trip.load (1 seconds)
*_Traceback (most recent call last):
File "/home/joninazio/automatizar/gtfsdb/bin/gtfsdb-load", line 13, in
sys.exit(gtfsdb.scripts.gtfsdb_load())
File "/home/joninazio/automatizar/gtfsdb/gtfsdb/scripts.py", line 10, in gtfsdb_load
database_load(args.file, *_kwargs)
File "/home/joninazio/automatizar/gtfsdb/gtfsdb/api.py", line 19, in database_load
gtfs.load(db, *_kwargs)
File "/home/joninazio/automatizar/gtfsdb/gtfsdb/model/gtfs.py", line 34, in load
cls.load(db, *_load_kwargs)
File "/home/joninazio/automatizar/gtfsdb/gtfsdb/model/base.py", line 143, in load
db.engine.execute(table.insert(), records)
File "/home/joninazio/automatizar/gtfsdb/eggs/SQLAlchemy-1.0.9-py2.7-linux-i686.egg/sqlalchemy/engine/base.py", line 1991, in execute
return connection.execute(statement, *multiparams, *_params)
File "/home/joninazio/automatizar/gtfsdb/eggs/SQLAlchemy-1.0.9-py2.7-linux-i686.egg/sqlalchemy/engine/base.py", line 914, in execute
return meth(self, multiparams, params)
File "/home/joninazio/automatizar/gtfsdb/eggs/SQLAlchemy-1.0.9-py2.7-linux-i686.egg/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "/home/joninazio/automatizar/gtfsdb/eggs/SQLAlchemy-1.0.9-py2.7-linux-i686.egg/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
compiled_sql, distilled_params
File "/home/joninazio/automatizar/gtfsdb/eggs/SQLAlchemy-1.0.9-py2.7-linux-i686.egg/sqlalchemy/engine/base.py", line 1146, in _execute_context
context)
File "/home/joninazio/automatizar/gtfsdb/eggs/SQLAlchemy-1.0.9-py2.7-linux-i686.egg/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception
exc_info
File "/home/joninazio/automatizar/gtfsdb/eggs/SQLAlchemy-1.0.9-py2.7-linux-i686.egg/sqlalchemy/util/compat.py", line 199, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb)
File "/home/joninazio/automatizar/gtfsdb/eggs/SQLAlchemy-1.0.9-py2.7-linux-i686.egg/sqlalchemy/engine/base.py", line 1116, in _execute_context
context)
File "/home/joninazio/automatizar/gtfsdb/eggs/SQLAlchemy-1.0.9-py2.7-linux-i686.egg/sqlalchemy/engine/default.py", line 447, in do_executemany
cursor.executemany(statement, parameters)
sqlalchemy.exc.DataError: (psycopg2.DataError) value too long for type character varying(8)
[SQL: 'INSERT INTO stop_times (trip_id, arrival_time, departure_time, stop_id, stop_sequence, stop_headsign, pickup_type, drop_off_type, shape_dist_traveled, timepoint) VALUES (%(trip_id)s, %(arrival_time)s, %(departure_time)s, %(stop_id)s, %(stop_sequence)s, %(stop_headsign)s, %(pickup_type)s, %(drop_off_type)s, %(shape_dist_traveled)s, %(timepoint)s)'] [parameters: ({'pickup_type': '0', 'stop_headsign': None, 'shape_dist_traveled': '2403.31', 'timepoint': False, 'arrival_time': '09:35:18', 'stop_sequence': '3', 'stop_id': '1446', 'drop_off_type': '1', 'trip_id': '4840', 'departure_time': '09:35:18'}, {'pickup_type': '0', 'stop_headsign': None, 'shape_dist_traveled': '7386.8', 'timepoint': False, 'arrival_time': '09:42:15', 'stop_sequence': '4', 'stop_id': '1469', 'drop_off_type': '1', 'trip_id': '4840', 'departure_time': '09:42:15'}, {'pickup_type': '0', 'stop_headsign': None, 'shape_dist_traveled': '15794.98', 'timepoint': False, 'arrival_time': '09:47:28', 'stop_sequence': '5', 'stop_id': '1396', 'drop_off_type': '1', 'trip_id': '4840', 'departure_time': '09:47:28'}, {'pickup_type': '0', 'stop_headsign': None, 'shape_dist_traveled': '19868.86', 'timepoint': False, 'arrival_time': '09:50:50', 'stop_sequence': '6', 'stop_id': '1397', 'drop_off_type': '1', 'trip_id': '4840', 'departure_time': '09:50:50'}, {'pickup_type': '0', 'stop_headsign': None, 'shape_dist_traveled': '20203.69', 'timepoint': False, 'arrival_time': '09:51:32', 'stop_sequence': '7', 'stop_id': '1492', 'drop_off_type': '1', 'trip_id': '4840', 'departure_time': '09:51:32'}, {'pickup_type': '0', 'stop_headsign': None, 'shape_dist_traveled': '20593.2', 'timepoint': False, 'arrival_time': '09:52:21', 'stop_sequence': '8', 'stop_id': '1493', 'drop_off_type': '1', 'trip_id': '4840', 'departure_time': '09:52:21'}, {'pickup_type': '0', 'stop_headsign': None, 'shape_dist_traveled': '21100.25', 'timepoint': False, 'arrival_time': '09:53:24', 'stop_sequence': '9', 'stop_id': '1494', 'drop_off_type': '1', 'trip_id': '4840', 'departure_time': '09:53:24'}, {'pickup_type': '0', 'stop_headsign': None, 'shape_dist_traveled': '21462.81', 'timepoint': False, 'arrival_time': '09:54:09', 'stop_sequence': '10', 'stop_id': '1495', 'drop_off_type': '1', 'trip_id': '4840', 'departure_time': '09:54:09'} ... displaying 10 of 8536 total bound parameter sets ... {'pickup_type': '0', 'stop_headsign': None, 'shape_dist_traveled': '7274.36', 'timepoint': False, 'arrival_time': '17:43:17', 'stop_sequence': '13', 'stop_id': '1478', 'drop_off_type': '0', 'trip_id': '38991', 'departure_time': '17:43:17'}, {'pickup_type': '0', 'stop_headsign': None, 'shape_dist_traveled': '7768.13', 'timepoint': False, 'arrival_time': '17:44:45', 'stop_sequence': '14', 'stop_id': '1479', 'drop_off_type': '0', 'trip_id': '38991', 'departure_time': '17:44:45'})]

Is there something wrong with the script, or it the gtfs file malformed?

Thank you !

Possibility to import and manage multi gtfs keeping track of historical data.
Add an import_id in the model's primary keys.

Dear authors,

I have run the given example:
bin/gtfsdb-load --database_url sqlite:///gtfs.db http://developer.trimet.org/schedule/gtfs.zip

and get a long list of DEBUG outputs which hangs forever at this point:

16:41:00,630 DEBUG [gtfsdb.model.route] Route.load (0 seconds)
16:41:00,646 DEBUG [gtfsdb.model.route] RouteDirection.load (0 seconds)
16:41:01,324 DEBUG [gtfsdb.model.stop] Stop.load (1 seconds)
****16:41:02,624 DEBUG [gtfsdb.model.stop_feature] StopFeature.load (1 seconds)
16:41:02,759 DEBUG [gtfsdb.model.transfer] Transfer.load (0 seconds)
***************************************************************************************************************16:42:02,480 DEBUG [gtfsdb.model.shape] Shape.load (60 seconds)
/Library/Python/2.7/site-packages/sqlalchemy/sql/sqltypes.py:596: SAWarning: Dialect sqlite+pysqlite does not support Decimal objects natively, and SQLAlchemy must convert from floating point - rounding errors and other issues may occur. Please consider storing Decimal numbers as strings or integers on this platform for lossless storage.
'storage.' % (dialect.name, dialect.driver))
16:42:04,079 DEBUG [gtfsdb.model.shape] Pattern.load (2 seconds)
****16:42:07,540 DEBUG [gtfsdb.model.trip] Trip.load (3 seconds)

Is there a way to stop it without corrupting the resulting DB?

Besides that, when feeding it with the following open-source gtfs
https://www.donneesquebec.ca/recherche/dataset/e82b9141-09d8-4f85-af37-d84937bc2503/resource/b7f43b2a-2557-4e3b-ba12-5a5c6d4de5b1/download/gtfssherbrooke.zip

Traceback (most recent call last):
File "bin/gtfsdb-load", line 13, in
sys.exit(gtfsdb.scripts.gtfsdb_load())
File "/Users/lavieestuntoucan/Documents/projets_perso/Start-up/Civilia/tech/STS/GTFS-rt/gtfsdb/gtfsdb/scripts.py", line 10, in gtfsdb_load
database_load(args.file, **kwargs)
File "/Users/lavieestuntoucan/Documents/projets_perso/Start-up/Civilia/tech/STS/GTFS-rt/gtfsdb/gtfsdb/api.py", line 20, in database_load
gtfs.load(db, **kwargs)
File "/Users/lavieestuntoucan/Documents/projets_perso/Start-up/Civilia/tech/STS/GTFS-rt/gtfsdb/gtfsdb/model/gtfs.py", line 34, in load
cls.load(db, **load_kwargs)
File "/Users/lavieestuntoucan/Documents/projets_perso/Start-up/Civilia/tech/STS/GTFS-rt/gtfsdb/gtfsdb/model/base.py", line 141, in load
db.engine.execute(table.insert(), records)
File "/Library/Python/2.7/site-packages/sqlalchemy/engine/base.py", line 2055, in execute
return connection.execute(statement, *multiparams, **params)
File "/Library/Python/2.7/site-packages/sqlalchemy/engine/base.py", line 945, in execute
return meth(self, multiparams, params)
File "/Library/Python/2.7/site-packages/sqlalchemy/sql/elements.py", line 263, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "/Library/Python/2.7/site-packages/sqlalchemy/engine/base.py", line 1053, in _execute_clauseelement
compiled_sql, distilled_params
File "/Library/Python/2.7/site-packages/sqlalchemy/engine/base.py", line 1189, in _execute_context
context)
File "/Library/Python/2.7/site-packages/sqlalchemy/engine/base.py", line 1393, in _handle_dbapi_exception
exc_info
File "/Library/Python/2.7/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File "/Library/Python/2.7/site-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context
context)
File "/Library/Python/2.7/site-packages/sqlalchemy/engine/default.py", line 470, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (sqlite3.ProgrammingError) You must not use 8-bit bytestrings unless you use a text_factory that can interpret 8-bit bytestrings (like text_factory = str). It is highly recommended that you instead just switch your application to Unicode strings. [SQL: u'INSERT INTO agency (agency_id, agency_name, agency_url, agency_timezone, agency_lang, agency_phone) VALUES (?, ?, ?, ?, ?, ?)'] [parameters: ('0', 'Soci\xc3\xa9t\xc3\xa9 de Transport de Sherbrooke', 'http://www.sts.qc.ca/', 'America/Montreal', 'FR', '819-564-2687')]

I am quite confused by the "You must not use 8-bit bytestrings", cannot it deal with any text file?

Thanks in advance,
regards,
Xavier

I've tried to upload a GTFS to my Amazon AWS RDS Instance (PostgreSQL). Unfortunately, an error was thrown, as no password was supplied in the command line. I've looking for any parameter to provide the password, but there isn't. Is there any chance this could be resolved? Thank you so much!

I'm using OpenTransitTools to import tables into mysql. In general it works fine except for calendars.txt. For some reason all the values (monday to sunday) are set to one, even though there are distinct weekend and weekday schedules

I've gone through the code and it reads and parses the file correctly, I suspect that it has something to do with the boolean datatype and mysql in sqlalchemy, and am wondering if it has problem translating the 0's and 1's as False and True with mysql.

Unfortunately I'm unfamiliar with SQLalchemy and can't tell where the problem lies.

Here's the file

service_id,monday,tuesday,wednesday,thursday,friday,saturday,sunday,start_date,end_date
1,1,1,1,1,1,0,0,20170427,20181231
2,1,1,1,1,1,0,0,20170601,20181231
3,1,1,1,1,1,0,0,20170801,20181231
4,0,0,0,0,0,1,1,20170427,20181231
5,0,0,0,0,0,1,1,20170601,20181231

If you could provide some direction where I might be able to look to resolve the issue, it would be appreciated. I'm looking into possibly changing the column type

gtfs feed: http://gtfs.ovapi.nl/new/gtfs-nl.zip

using ms sql server 2017

C:\WINDOWS\system32> gtfsdb-load --database_url mssql+pyodbc://USER:PASS@gtfs_nl http://gtfs.ovapi.nl/new/gtfs-nl.zip
23:19:35,325 DEBUG [gtfsdb.model.gtfs] GTFS.load: http://gtfs.ovapi.nl/new/gtfs-nl.zip
23:19:42,966 DEBUG [gtfsdb.model.route] RouteType.load (0 seconds)
23:19:42,976 DEBUG [gtfsdb.model.stop_feature] StopFeatureType.load (0 seconds)
23:19:42,980 DEBUG [gtfsdb.model.feed_info] FeedInfo.load (0 seconds)
23:19:43,009 DEBUG [gtfsdb.model.agency] Agency.load (0 seconds)
23:19:43,009 DEBUG [gtfsdb.model.calendar] Calendar.load (0 seconds)
******************23:19:58,115 DEBUG [gtfsdb.model.calendar] CalendarDate.load (15 seconds)
Traceback (most recent call last):
File "c:\python27\lib\runpy.py", line 174, in _run_module_as_main
"main", fname, loader, pkg_name)
File "c:\python27\lib\runpy.py", line 72, in run_code
exec code in run_globals
File "C:\Python27\Scripts\gtfsdb-load.exe_main.py", line 9, in
File "c:\python27\lib\site-packages\gtfsdb\scripts.py", line 33, in gtfsdb_load
database_load(args.file, **kwargs)
File "c:\python27\lib\site-packages\gtfsdb\api.py", line 21, in database_load
gtfs.load(db, **kwargs)
File "c:\python27\lib\site-packages\gtfsdb\model\gtfs.py", line 34, in load
cls.load(db, **load_kwargs)
File "c:\python27\lib\site-packages\gtfsdb\model\base.py", line 83, in load
db.engine.execute(table.insert(), records)
File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line 2064, in execute
return connection.execute(statement, *multiparams, **params)
File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line 945, in execute
return meth(self, multiparams, params)
File "c:\python27\lib\site-packages\sqlalchemy\sql\elements.py", line 263, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line 1053, in _execute_clauseelement
compiled_sql, distilled_params
File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line 1189, in _execute_context
context)
File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line 1405, in _handle_dbapi_exception
util.reraise(*exc_info)
File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line 1159, in _execute_context
context)
File "c:\python27\lib\site-packages\sqlalchemy\engine\default.py", line 467, in do_executemany
cursor.executemany(statement, parameters)
UnicodeDecodeError: 'ascii' codec can't decode byte 0xc3 in position 1: ordinal not in range(128)

Add a materialized view to determine what is currently running.

Qs:

• now and in the next N days? or today until end of active date?
• if post-process tables are not run, how to populate these views?
• could these views be run nightly, and that would eliminate the need for post-processing?
• provide a future view as well?

Ns:

• https://stackoverflow.com/questions/29437650/how-can-i-ensure-that-a-materialized-view-is-always-up-to-date

I followed the instructions and am trying to run it on Docker, but keep getting this error.

2024-05-04 22:18:19.305 GMT [43] FATAL: --database_url requires a value