CODEWITHSUNDEEP
sqlgtfs
Slevin
Slevin

Reputation: 4232

How can I list a stop's directly connected neighbors using GTFS?

Lets say that these statemates are included in GTFS data:

1. stop A is connected with stop B and stop C
2. stop B is connected with stop D and stop E
3. stop C is connected with Stop F and stop G

Now I want to query all stops, that are directly connected with stop A. The result should be stop B and stop C.

Are those SQL queries possible with GTFS data?

Upvotes: 0

Views: 344

Answers (1)

user473305
user473305

Reputation:

Yes, although depending on your application the performance might not be everything you expect.

Following your example, let's call stop A the origin stop and stops B and C the connected stops. Then to build the set of connected stops, we want to find all the stops that

  • Share a trip with the origin stop, and

  • On that shared trip are visited either immediately before or immediately after the origin stop.

We can use the stop_sequence field of the stop_times table to test the second condition. Try this SQL:

SELECT DISTINCT stops.id, stops.code, stops.name
  FROM stop_times AS origin_stop
  INNER JOIN stop_times AS connected_stops
    ON connected_stops.trip_id = origin_stop.trip_id
      AND ABS(connected_stops.stop_sequence - origin_stop.stop_sequence) = 1
  INNER JOIN stops
    ON stops.id = connected_stops.stop_id
  WHERE origin_stop.stop_id = <origin stop ID>;

This joins the stops table for a prettier view; if all your application cares about are the connected stops' IDs you can remove that join and change the first line to simply "SELECT DISTINCT connected_stops.stop_id".

To make performance acceptable you'll want to create indices on stop_times.stop_id and stop_times.trip_id:

CREATE INDEX stop_times_stop_id_index ON stop_times(stop_id);

CREATE INDEX stop_times_trip_id_index ON stop_times(trip_id);

But if you're developing an online application, this is probably not the way to go. You'd be better off precalculating connections between stops using a graph representation of the transit network in memory and then querying that at runtime.

Upvotes: 1

Related Questions