CODEWITHSUNDEEP
sqlgtfs
devha
devha

Reputation: 3377

How to group similar GTFS trips

I need to group GTFS trips to human understandable "route variants". As one route can have run different trips based on day/time etc.

Is there any preferred way to group similar trips? Trip shape_id looks promising, but is there any guarantee that all similar trips has same shape_id?

My GTFS data is imported my sql database and the database structure is the same as GTFS txt files.

UPDATE Im not looking sql query example, im looking high level example how to group similar trips to user friendly "route variants".

Many route planning apps (like Moovit) use GTFS data as source and they display different route variants to users.

Upvotes: 1

Views: 304

Answers (1)

abeboparebop
abeboparebop

Reputation: 7775

There is no official way to do this. The best way is probably to group by the ordered list of stops on each trip, sometimes known as the "stopping pattern" of the trip. The idea is discussed at a conceptual level here by Mapzen.

In practice, I have created concatenated strings of all stops on a given trip (from stop_times), and grouped by that to define similar trips. E.g., if the stops on a given trip are A, B, C, D, and E, create a string A-B-C-D-E or A_B_C_D_E and group trips on that string. This functionality is not part of the SQL spec, although MySQL implements it as GROUP_CONCAT and PostgreSQL uses arrays and array_to_string. You may also want to add route_id and shape_id into the grouping as well, to handle some corner cases.

Upvotes: 2

Related Questions