Distances between stops in GTFS

interstop_distances.sql

--- Distances between stops
create table stop_sequences 
as select
distinct
shape_id,
stop_times.stop_id,
stop_sequence,
stop_name || ' ' || coalesce(platform_code,'') stop_name,
shape_dist_traveled

from stop_times
join stops using (stop_id)
join trips using (trip_id)
where location_type = 0 and zone_id <> ''
order by shape_id, CAST(stop_sequence as integer);

select distinct * from (
select
shape_id,
stop_id,
lead(stop_id) over (partition by shape_id) stop_id2,
stop_name,
lead(stop_name) over (partition by shape_id) stop2,
round(lead(shape_dist_traveled) over (partition by shape_id) - shape_dist_traveled,3) distance
from stop_sequences)
where distance is not null
group by stop_id, stop_id2 and distance -- comment to show all pairs
order by distance