GTFS data - stop data

Question

Feeling bit rusty about my SQL skills at the moment. What I would like to achieve is connect two SQL queries into one which give me basically all information about particular stop.

First query is simple one. It takes all stop informations. Piece of cake I would say ;)

    SELECT stop_id, stop_name, stop_lat, stop_lon, zone_id 
FROM stops WHERE stop_id = 97

Second one is bit more complicated but found my solution here and with small changes it's just like I want it.

select GROUP_CONCAT( distinct rt.route_short_name) as routes
from routes rt, trips tr, stop_times st
where rt.route_id = tr.route_id
and tr.trip_id = st.trip_id
and st.stop_id = 97

Now, I would like to add second query result as another column in first query result. Thanks to that after json_encode I will have nice JSON with all informations that I need!

Thanks!

score 2 · Accepted Answer · answered Jul 28 '13 at 23:46

2

You can do it with the hammer method:

SElECT * FROM (
   SELECT stop_id, stop_name, stop_lat, stop_lon, zone_id 
   FROM stops WHERE stop_id = 97
) a INNER JOIN (
   select st.stop_id, GROUP_CONCAT( distinct rt.route_short_name) as routes
   from routes rt, trips tr, stop_times st
   where rt.route_id = tr.route_id
   and tr.trip_id = st.trip_id
   and st.stop_id = 97
) b ON a.stop_id = b.stop_id

or by joining them:

select stops.stop_id, 
stops.stop_name, 
stops.stop_lat, 
stops.stop_lon, 
stops.zone_id, 
GROUP_CONCAT( distinct rt.route_short_name) as routes
from routes rt, trips tr, stop_times st, stops
where rt.route_id = tr.route_id
and tr.trip_id = st.trip_id
and st.stop_id = stops.stop_id
and st.stop_id = 97
group by st.stop_id

answered Jul 28 '13 at 23:46

fancyPants

50,732
33
89
96

but how to speed it up? when I call it in phpMyAdmin is pretty much okay, but in php it takes ages.. – dargod Aug 03 '13 at 15:20
apparently hammer method is faster :) – dargod Aug 03 '13 at 15:22
To speed the queries up, you'd have to post the output of `explain select ...` of both both queries and the table schemas, so we can have a look at the indexes. But all that would maybe better be suited in another question. – fancyPants Aug 03 '13 at 16:35
How to add same column in multiple search result ex. SELECT stop_id, stop_name, stop_lat, stop_lon, zone_id FROM stops WHERE stop_name LIKE '%$search_term%' – dargod Sep 29 '13 at 14:34
Works great on Postgres too with some minor modifications! Just use `array_agg(distinct rt.route_short_name) as routes` instead. – Avishai Jan 12 '14 at 04:29

GTFS data - stop data

1 Answers1