SMS case study for TRB 2018
select prev_stop_departure_time, count(*) from demand.<das_table>
group by prev_stop_departure_time
order by prev_stop_departure_time asc;
(a) Change the x-axis labels to a more readable format
(b) Plot in Excel
Use the Python script 'ProcessTaxiTrajectory.py'
Use the Python script 'makePlot.py'
(a) Make appropriate color legend in DynaVis setting
(b) Take out.txt after running supply and run DynaVis
(b) Take screenshots of Dynavis at specific timestamps
[1] Consider served trips only, i.e. trips with waiting time less than 1 hour
drop table if exists output.served_trips_rb;
create table output.served_trips_rb
as
select distinct person_id, trip_origin_id, trip_dest_id from output.travel_time
where (person_id, trip_origin_id, trip_dest_id) not in
(select distinct person_id, trip_origin_id, trip_dest_id from output.travel_time
where travel_mode in ('WAIT_BUS', 'WAIT_MRT', 'WAIT_TAXI', 'WAIT_SMS', 'WAIT_RAIL_SMS')
and travel_time > 3600);`
[2] Create CDFs for total travel time, in-vehicle travel time, waiting time for served trips segmented by mode
-- Sample query shown below for Bus -- Need to change mode and time periods for morning peak, off peak and evening peak
Filename: CDF_TT_Serv_Bus_Base.csv OR CDF_TT_Serv_Bus_AllModes.csv OR CDF_TT_Serv_Bus_NoPT.csv
select (sub.tt/3600) as tt_hr, sub.tt, sum(count(*)) over (order by sub.tt) as cumul_count, sum(count(*)) over (order by sub.tt)/(select count(*)
from
(select t1.person_id, t1.trip_origin_id, t1.trip_dest_id, sum(t1.travel_time) as tt from output.travel_time as t1`
inner join output.served_trips_rb as t2
on t1.person_id = t2.person_id and t1.trip_origin_id = t2.trip_origin_id and t1.trip_dest_id = t2.trip_dest_id
inner join demand.das_vc_no_public_no_pvt_bus_06072017 as das
on das.person_id = t2.person_id and das.prev_stop_location = cast (t2.trip_origin_id as integer) and das.stop_location = cast (t2.trip_dest_id as integer)
where t1.travel_mode in ('ON_BUS', 'ON_PBUS', 'WAIT_BUS')
group by t1.person_id, t1.trip_origin_id, t1.trip_dest_id
order by tt asc) as subq) as prob, 'All' as indicator from
(select t1.person_id, t1.trip_origin_id, t1.trip_dest_id, sum(t1.travel_time) as tt from output.travel_time as t1
inner join output.served_trips_rb as t2
on t1.person_id = t2.person_id and t1.trip_origin_id = t2.trip_origin_id and t1.trip_dest_id = t2.trip_dest_id
inner join demand.das_vc_no_public_no_pvt_bus_06072017 as das
on das.person_id = t2.person_id and das.prev_stop_location = cast (t2.trip_origin_id as integer) and das.stop_location = cast (t2.trip_dest_id as integer)
where t1.travel_mode in ('ON_BUS', 'ON_PBUS', 'WAIT_BUS')
group by t1.person_id, t1.trip_origin_id, t1.trip_dest_id
order by tt asc) as sub
group by sub.tt
union
select (sub.tt/3600) as tt_hr, sub.tt, sum(count(*)) over (order by sub.tt) as cumul_count, sum(count(*)) over (order by sub.tt)/(select count(*) from
(select t1.person_id, t1.trip_origin_id, t1.trip_dest_id, sum(t1.travel_time) as tt from output.travel_time as t1
inner join output.served_trips_rb as t2
on t1.person_id = t2.person_id and t1.trip_origin_id = t2.trip_origin_id and t1.trip_dest_id = t2.trip_dest_id
inner join demand.das_vc_no_public_no_pvt_bus_06072017 as das
on das.person_id = t2.person_id and das.prev_stop_location = cast (t2.trip_origin_id as integer) and das.stop_location = cast (t2.trip_dest_id as integer)
where t1.travel_mode in ('ON_BUS', 'ON_PBUS', 'WAIT_BUS') and das.prev_stop_departure_time >= 8.25 and das.prev_stop_departure_time <= 9.75
group by t1.person_id, t1.trip_origin_id, t1.trip_dest_id
order by tt asc) as subq) as prob, 'MP' as indicator from
(select t1.person_id, t1.trip_origin_id, t1.trip_dest_id, sum(t1.travel_time) as tt from output.travel_time as t1
inner join output.served_trips_rb as t2
on t1.person_id = t2.person_id and t1.trip_origin_id = t2.trip_origin_id and t1.trip_dest_id = t2.trip_dest_id
inner join demand.das_vc_no_public_no_pvt_bus_06072017 as das
on das.person_id = t2.person_id and das.prev_stop_location = cast (t2.trip_origin_id as integer) and das.stop_location = cast (t2.trip_dest_id as integer)
where t1.travel_mode in ('ON_BUS', 'ON_PBUS', 'WAIT_BUS') and das.prev_stop_departure_time >= 8.25 and das.prev_stop_departure_time <= 9.75
group by t1.person_id, t1.trip_origin_id, t1.trip_dest_id
order by tt asc) as sub
group by sub.tt
union
select (sub.tt/3600) as tt_hr, sub.tt, sum(count(*)) over (order by sub.tt) as cumul_count, sum(count(*)) over (order by sub.tt)/(select count(*) from
(select t1.person_id, t1.trip_origin_id, t1.trip_dest_id, sum(t1.travel_time) as tt from output.travel_time as t1
inner join output.served_trips_rb as t2
on t1.person_id = t2.person_id and t1.trip_origin_id = t2.trip_origin_id and t1.trip_dest_id = t2.trip_dest_id
inner join demand.das_vc_no_public_no_pvt_bus_06072017 as das
on das.person_id = t2.person_id and das.prev_stop_location = cast (t2.trip_origin_id as integer) and das.stop_location = cast (t2.trip_dest_id as integer)
where t1.travel_mode in ('ON_BUS', 'ON_PBUS', 'WAIT_BUS') and das.prev_stop_departure_time >= 13.25 and das.prev_stop_departure_time <= 14.75
group by t1.person_id, t1.trip_origin_id, t1.trip_dest_id
order by tt asc) as subq) as prob, 'OP' as indicator from
(select t1.person_id, t1.trip_origin_id, t1.trip_dest_id, sum(t1.travel_time) as tt from output.travel_time as t1
inner join output.served_trips_rb as t2
on t1.person_id = t2.person_id and t1.trip_origin_id = t2.trip_origin_id and t1.trip_dest_id = t2.trip_dest_id
inner join demand.das_vc_no_public_no_pvt_bus_06072017 as das
on das.person_id = t2.person_id and das.prev_stop_location = cast (t2.trip_origin_id as integer) and das.stop_location = cast (t2.trip_dest_id as integer)
where t1.travel_mode in ('ON_BUS', 'ON_PBUS', 'WAIT_BUS') and das.prev_stop_departure_time >= 13.25 and das.prev_stop_departure_time <= 14.75
group by t1.person_id, t1.trip_origin_id, t1.trip_dest_id
order by tt asc) as sub
group by sub.tt
union
select (sub.tt/3600) as tt_hr, sub.tt, sum(count(*)) over (order by sub.tt) as cumul_count, sum(count(*)) over (order by sub.tt)/(select count(*) from
(select t1.person_id, t1.trip_origin_id, t1.trip_dest_id, sum(t1.travel_time) as tt from output.travel_time as t1
inner join output.served_trips_rb as t2
on t1.person_id = t2.person_id and t1.trip_origin_id = t2.trip_origin_id and t1.trip_dest_id = t2.trip_dest_id
inner join demand.das_vc_no_public_no_pvt_bus_06072017 as das
on das.person_id = t2.person_id and das.prev_stop_location = cast (t2.trip_origin_id as integer) and das.stop_location = cast (t2.trip_dest_id as integer)
where t1.travel_mode in ('ON_BUS', 'ON_PBUS', 'WAIT_BUS') and das.prev_stop_departure_time >= 17.25 and das.prev_stop_departure_time <= 18.75
group by t1.person_id, t1.trip_origin_id, t1.trip_dest_id
order by tt asc) as subq) as prob, 'EP' as indicator from
(select t1.person_id, t1.trip_origin_id, t1.trip_dest_id, sum(t1.travel_time) as tt from output.travel_time as t1
inner join output.served_trips_rb as t2
on t1.person_id = t2.person_id and t1.trip_origin_id = t2.trip_origin_id and t1.trip_dest_id = t2.trip_dest_id
inner join demand.das_vc_no_public_no_pvt_bus_06072017 as das
on das.person_id = t2.person_id and das.prev_stop_location = cast (t2.trip_origin_id as integer) and das.stop_location = cast (t2.trip_dest_id as integer)
where t1.travel_mode in ('ON_BUS', 'ON_PBUS', 'WAIT_BUS') and das.prev_stop_departure_time >= 17.25 and das.prev_stop_departure_time <= 18.75
group by t1.person_id, t1.trip_origin_id, t1.trip_dest_id
order by tt asc) as sub
group by sub.tt
order by indicator, prob asc;
Look at the image sample.
To obtain the CDFs of the travel times with the following scripts, you need to have gnuplot and octave (with nan package) installed. Go to a folder of your choice. Create these two scripts there: compute_cdf.m
#!/usr/bin/octave-cli -qf
## usage ./compute_cdf.m filename
arg_list = argv ();
filename = arg_list{1};
pkg load nan;
rowdata = dlmread(filename);
[f,x] = ecdf(rowdata);
plot(x,f);
grid minor;
#axis ([0 290])
outfile = sprintf("%s.cdf.eps", filename);
outdatafile = sprintf("%s.cdf.dat", filename);
saveas(gcf,outfile)
printf("\n\nImage written %s\n" ,outfile)
dlmwrite(outdatafile, [x,f], " ")
printf("Data file written %s\n\n" ,outdatafile)
and compute_travel_time_cdf.bash
#!/usr/bin/octave-cli -qf
## usage ./compute_cdf.m filename
arg_list = argv ();
filename = arg_list{1};
pkg load nan;
rowdata = dlmread(filename);
[f,x] = ecdf(rowdata);
plot(x,f);
grid minor;
#axis ([0 290])
outfile = sprintf("%s.cdf.eps", filename);
outdatafile = sprintf("%s.cdf.dat", filename);
saveas(gcf,outfile)
printf("\n\nImage written %s\n" ,outfile)
dlmwrite(outdatafile, [x,f], " ")
printf("Data file written %s\n\n" ,outdatafile)
Create a DATA folder and put your subtrip_metrics.csv there. Then, invoke:
./compute_travel_time_cdf.bash DATA/subtrip_metrics.csv MRT
This will create DATA/MRT.dat.cdf.eps, i.e., the image of the CDF of the MRT travel times. You can invoke the same command for TaxiTravel, Car, etc. Note also that files like DATA/MRT.dat.cdf.dat are also created. You can use these files to plot all the CDFs together, e.g., with a gnuplot script like:
travel_time_cdfs.gp
set ylabel "Frequency"
set xlabel "Travel Time (h)"
set grid
plot 'DATA/TaxiTravel.dat.cdf.dat' using 1:2 title "Taxi" with lines,\
'DATA/BusTravel.dat.cdf.dat' using 1:2 title "Bus" with lines,\
'DATA/Car.dat.cdf.dat' using 1:2 title "Taxi" with lines,\
'DATA/Motorcycle.dat.cdf.dat' using 1:2 title "Taxi" with lines,\
'DATA/MRT.dat.cdf.dat' using 1:2 title "MRT" with lines,\
'DATA/ALL.dat.cdf.dat' using 1:2 title "All" with lines linewidth 3,\