1

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.

devha
  • 3,307
  • 4
  • 28
  • 52
  • Show us db schema, sample data and expected output. Please read [**How-to-Ask**](http://stackoverflow.com/help/how-to-ask) And here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) to learn how improve your question quality and get better answers. – Juan Carlos Oropeza Mar 29 '17 at 13:33
  • Edited the original question – devha Mar 29 '17 at 14:02
  • I know `avl`, I know `routing`, dont know `GTFS`or `Moovit`.I wont do research to find out what those are. So you need to define what is `friendly route variant`. That mean provide a sample data and desrire output. – Juan Carlos Oropeza Mar 29 '17 at 14:05

1 Answers1

2

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.

abeboparebop
  • 7,396
  • 6
  • 37
  • 46