GTFS SQLite database query

[Ramdane SPunkt]

Does anyone created a SQLite database based on GTFS data?
(Maybe in Java)?

Does anyone have a query to find a path between two stops?


Best regards,
RS

[Donald Hayward]

Hi RS,

I don't think a simple SQLite database will be sufficient to find a path between two stops.  You would need something like GraphServer (http://graphserver.github.com/graphserver/) or OpenTripPlanner (which is built on top of a Java port of GraphServer IIRC -- https://github.com/openplans/OpenTripPlanner/wiki/) to perform this kind of routing.  Graphserver uses SQLite databases to store its information, and it comes with scripts to build them.

PostGIS has a routing library you might be interested in if you prefer doing it within a pure database context (http://www.pgrouting.org/) but I have no experience with it, and I'm not sure if anyone has written code to import a GTFS to PostGIS in a way that you can do this.  Additionally, you would need to use PostgreSQL instead of SQLite to go this route.

I hope this helps.

- Donald

--
You received this message because you are subscribed to the Google Groups "Transit Developers" group.
To post to this group, send email to transit-d...@googlegroups.com.
To unsubscribe from this group, send email to transit-develop...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/transit-developers?hl=en.

[R S]

Thx Donald, I will check the first two links.

Best regards,

RS

Sent from my iPhone

[S. Morris Rose]

I've written a perl script that stores many GTFS files in an SQLite3 database. I use it for my own purposes, but you are welcome to it. Standard perl, but you would need DBD::CSV.

https://docs.google.com/open?id=0B1bNG1437gDnOXc2NU1RY3pQR3M

Does nothing to find a path- just stores the data.

[Luitien Pan]

The ArcGIS tool that came up here the other day:
http://www.transit.melindamorang.com
includes free-standing Python code to load GTFS data into a SQLite
database, keeping to the same organizational scheme. However, it
only handles the parts of the data that I needed for calculating
paths, which is the following files:
calendar.txt
routes.txt
stop_times.txt
stops.txt
trips.txt
It does convert numeric things (times, lat-lon) into numbers, and
it sets up indices. It also munges *_ids --- prepends a dataset
identifier, to allow for multiple GTFS datasets in a single database.
Other than that, it doesn't do anything weird.

For calculating a path between two stops: this requires recursion,
and I'm pretty sure sqlite doesn't do recursive queries.

[ewoh...@gmail.com]

The Spatialite extension to SQLite can support routing queries,

 (provided you generate a routing network/graph from your roads/routes).

http://www.gaia-gis.it/spatialite-2.3.0/spatialite-network-2.3.0.html 

You'd probably have to create a real spatial/geometry table first from 'shapes.txt', 

then build the routing network over that, you couldn't do it without modifying the GTFS schema somewhat.

Edward

On Friday, 27 April 2012 17:46:32 UTC+1, Donald wrote:

Hi RS,

I don't think a simple SQLite database will be sufficient to find a path between two stops.  You would need something like GraphServer (http://graphserver.github.com/graphserver/) or OpenTripPlanner (which is built on top of a Java port of GraphServer IIRC -- https://github.com/openplans/OpenTripPlanner/wiki/) to perform this kind of routing.  Graphserver uses SQLite databases to store its information, and it comes with scripts to build them.

PostGIS has a routing library you might be interested in if you prefer doing it within a pure database context (http://www.pgrouting.org/) but I have no experience with it, and I'm not sure if anyone has written code to import a GTFS to PostGIS in a way that you can do this.  Additionally, you would need to use PostgreSQL instead of SQLite to go this route.

I hope this helps.

- Donald

On Fri, Apr 27, 2012 at 11:56 AM, Ramdane SPunkt <buiss...@googlemail.com> wrote:

Does anyone created a SQLite database based on GTFS data?
(Maybe in Java)?

Does anyone have a query to find a path between two stops?


Best regards,
RS

--
You received this message because you are subscribed to the Google Groups "Transit Developers" group.

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

[Kelvin Nicholson]

Raju,

Please look at the answer on stackoverflow for this exact question. I think you may have asked it

http://stackoverflow.com/questions/11135811/how-to-save-gtfs-data-into-sqlite-database

The beta gtfs file has one zip per provider, I think, so you should be able to use the merge tool from the transitfeed tools.

Since Sydney appears in Google Maps now you can try finding out if gtfs will be released more regularly.

On Jul 27, 2012 3:15 AM, <karana...@gmail.com> wrote:

hello R S

        hi this is raju, iam creatin trip plan for sydney by using gtfs data from www.131500.com site, i saw ur post in googlegroups please explain what is the procedure you done for saving gtfs data into sqlite and how to calculate the path between two points ,please explain and help me for my project
                                   thanks in advance
WITH REGARDS
  RAJU

--
You received this message because you are subscribed to the Google Groups "Transit Developers" group.

To view this discussion on the web visit https://groups.google.com/d/msg/transit-developers/-/QAeuJH01VrwJ.