Skip to content

Instantly share code, notes, and snippets.

@engelmav
Last active October 13, 2015 03:58
Show Gist options
  • Save engelmav/4136271 to your computer and use it in GitHub Desktop.
Save engelmav/4136271 to your computer and use it in GitHub Desktop.
use strict;
use DBI;
use Data::Dumper;
use DateTime;
use Text::CSV_XS;
my $pg_dbname = "gtfs_njtransit";
my $pg_host = "dbi:Pg:dbname=$pg_dbname";
my $pg_user = "xxxx";
my $pg_pass = "xxxx";
my $pg_dbh = DBI->connect($pg_host,$pg_user,$pg_pass) or die "no db for you: $!\n";
my $sql = q {
SELECT
BusRoute.route_id
,bustrip.trip_id
,bustrip.trip_headsign
,BusStopTime.arrival_time
,busstopTime.stop_sequence
,busstop.stop_name
--,BusTripShape.shape_pt_sequence
FROM routes BusRoute
LEFT JOIN trips BusTrip
ON BusRoute.route_id = BusTrip.route_id
LEFT JOIN stop_times BusStopTime
ON BusTrip.trip_id = BusStopTime.trip_id
LEFT JOIN stops BusStop
ON BusStopTime.stop_id = BusStop.stop_id
WHERE
BusTrip.direction_id = '0' --this is outbound to NY
AND BusTrip.service_id = '9' --this is the weekday service
and
bustrip.trip_id IN (
SELECT
StopTime.trip_id
FROM stops StopName
LEFT JOIN stop_times StopTime
ON StopName.stop_id = StopTime.stop_id
WHERE
StopName.stop_name like '%AUTHORITY%'
AND StopTime.trip_id IN (
SELECT StopTime.trip_id
FROM stops StopName
LEFT JOIN stop_times StopTime
ON StopName.stop_id = StopTime.stop_id
WHERE StopTime.arrival_time < '09:00'
)
)
--AND
-- BusTrip.trip_headsign ~ '^113.*'
--AND BusStopTime.arrival_time ~ '^05.*'
ORDER BY
BusRoute.route_id
,BusTrip.trip_id
,BusStopTime.stop_sequence
ASC
};
my $pg_sth = $pg_dbh->prepare($sql);
$pg_sth->execute();
my ( $routeid, $tripid, $tripheadsign, $arrivaltime, $sqnce, $stopname );
$pg_sth->bind_columns (undef, \( $routeid, $tripid, $tripheadsign, $arrivaltime, $sqnce, $stopname ) );
my %time_comparator;
while ( $pg_sth->fetch() ) {
if ( $stopname !~ /AUTHORITY/i ) {
$time_comparator{$routeid}{$tripid}{$tripheadsign}{'some_stop'}{"$stopname sqnce $sqnce"} = $arrivaltime;
} else {
$time_comparator{$routeid}{$tripid}{$tripheadsign}{'port_auth'}{$stopname} = $arrivaltime;
}
}
my $csv = Text::CSV_XS->new ( { binary => 1, eol => $/ } );
my ($begin,$end,$duration);
open my $fh, ">", "elapsed.csv" or die "elapsed.csv: $!";
foreach my $route_out ( keys %time_comparator ){
foreach my $trip_out (keys $time_comparator{$route_out} ){
foreach my $hs_out ( keys $time_comparator{$route_out}{$trip_out} ){
foreach my $some_name_out ( keys $time_comparator{$route_out}{$trip_out}{$hs_out}{'some_stop'}) {
print "$trip_out,";
print "$some_name_out,";
$begin = $time_comparator{$route_out}{$trip_out}{$hs_out}{'some_stop'}{$some_name_out};
$end = $time_comparator{$route_out}{$trip_out}{$hs_out}{'port_auth'}{'PORT AUTHORITY BUS TERMINAL'};
$duration = time_diff($begin,$end);
print "'$begin,'$end,$duration\n";
my @arr_ref = ( $route_out,$trip_out,$hs_out,$some_name_out,$begin,$end,$duration );
$csv->print($fh,\@arr_ref) or $csv->error_diag;
}
}
}
}
close $fh or die "elapsed.csv: $!";
sub get_dt_obj {
my $time_str = shift;
my ( $hr,$min,$sec ) = split(/:/, $time_str);
return DateTime->new(
year => 2012,
month => 11,
day => 22,
hour => $hr,
minute => $min,
second => $sec,
);
}
sub time_diff {
my ($begin,$end) = @_;
my $begin_obj = get_dt_obj($begin);
my $end_obj = get_dt_obj($end);
my $dur_obj = $begin_obj->subtract_datetime_absolute( $end_obj );
return $dur_obj->seconds();
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment