Detect stop type using GTFS feed

Question

I have a GTFS feed and I am trying to detect stoptype (bus, tram or multiple in my case) using SQL. To do that, I have to get all routes on each stop and compare route.route_type field which contains information about route type (0 - tram, 3 - bus).

Here's my query:

SELECT stop.stop_id, route.route_type from stop
INNER JOIN stop_time on stop_time.stop_id = stop.stop_id
INNER JOIN trip on trip.trip_id = stop_time.trip_id
INNER JOIN route on route.route_id = trip.route_id
GROUP BY stop.stop_id, route.route_type 
ORDER BY stop.stop_id, route.route_type 

This query returns:

stop_id,route_type
1,0
1,3
2,0
3,3

Which means that stop_id = 1 has:

• bus routes,
• tram routes.

stop_id = 2 has only tram routes.

stop_id = 3 has only bus routes.

I would like to return:

stop_id,stop_type
1,multiple
2,tram
3,bus

Or maybe even easier would be just to modify only these stops which have bus and tram routes. So it would be something like this:

stop_id,stop_type
1,-1
2,0
3,3

How to achieve this result? I'm not sure but I think that I should count number of rows per stop_id and then somehow modify the result but I don't know how to make it.

Answer 1

You can check the COUNT of DISTINCT route_type for each stop_id and if it is more than 1 then output multiple, otherwise use the MAX (or MIN, it doesn't matter since there is only one distinct value, but we must use an aggregation function to avoid violating group by rules) of route_type to set the stop_type to bus or tram:

SELECT stop.stop_id, 
       CASE WHEN COUNT(DISTINCT route.route_type) > 1 THEN 'multiple'
            WHEN MAX(route.route_type) = 0 THEN 'tram'
            ELSE 'bus'
       END AS stop_type
FROM stop
INNER JOIN stop_time on stop_time.stop_id = stop.stop_id
INNER JOIN trip on trip.trip_id = stop_time.trip_id
INNER JOIN route on route.route_id = trip.route_id
GROUP BY stop.stop_id
ORDER BY stop.stop_id