Leaving work in NYC I take a bus.. then the subway.. and I hustle to the marquee to figure out what my next train home is. So far so good. Then I grab my cell phone and call my wife to tell her what train I'm on and the inevitable question arises. What time will you be in Little Silver?
Well, I have grabbed many schedules and lost them all so I take a wild guess that is usually wrong. Add to the problem that fact that the schedule changes frequently.
One day when I was looking up train schedules on New Jersey Transit's web site I made a discovery. Always one to look at other folks' HTML source I noticed a line that piqued my interest:
Aha... so they have a CGI script that I can call and drag data from!<!-- This is the URL-ENCODED message that was sent to ATIS... http://atisweb.njtransit.com/cgi-bin/atis81.pl?railline= &dd=9 &mina=0 &resptype=U &action=entry &lineext=NJCL%3ANorth+Jersey+Coast+Line &oloc=New+York+Penn+Station &mm=6 &dtime=12%3A00+PM &minb=0 &back=sf_tr_schedules.shtml &fare=Y &date=6%2F9%2F2005 &ori=105+++++%3ANew+York+Penn+Station &rtime=1%3A00+PM &dsid=73++++++ &dloc=Little+Silver &dow=W &line=NJCL &linelookup= &osid=105+++++ &yyyy=2005 &lineext2=NJCL%3ANorth+Jersey+Coast+Line &des=73++++++%3ALittle+Silver &linedesc=North+Jersey+Coast+Line and this is the message that was received from ATIS... etc...
The data that comes back is nicely delimited and looks like:
Of real interest to me were the lines that started with "TMTI". The lines that had the "NEC" are useless to me so can be ignored.0 N N RAY 06/09/2005 16:22:22 0 N N RVN 8.4 A 1 N N RTMTI81 TSP New York Penn Station 105 TSP Little Silver 73 TMTD F TMTI 00.0 NJCL 105 12:37 AM 73 01:58 AM 3201 Y +YYYYNNN TMTI 01.0 NEC 105 01:41 AM 107 01:58 AM 3805 Y +YYYYNNN TMTI 01.1 NJCLL 107 02:10 AM 73 03:10 AM 5347 +YYYYYNNN TMTI 02.0 NJCL 105 04:44 AM 73 06:03 AM 3209 Y +YYYYNNN TMTI 03.0 NJCL 105 05:41 AM 73 07:03 AM 3215 Y +YYYYNNN TMTI 04.0 NJCL 105 06:25 AM 73 07:43 AM 3217 Y +YYYYNNN TMTI 05.0 NJCL 105 07:01 AM 73 08:25 AM 3221 Y +YYYYNNN
So the solution has three parts to it. First I need to set up a database schema to store the data in. To that end I have the following:
drop sequence train_info_id_seq; create sequence train_info_id_seq; drop table train_info; create table train_info ( train_info_id integer default nextval('train_info_id_seq'), train_number varchar(20) not null, leaves time, arrives time, primary key (train_info_id) ); create or replace function add_train(train_no text,depart time,arrival + time) returns void as ' BEGIN LOOP update train_info set train_number = train_no where train_number = train_no ; IF found THEN RETURN; END IF; insert into train_info(train_number,leaves,arrives) values ( train_no,depart,arrival); RETURN; END LOOP; END; ' language 'plpgsql';
I used PostgreSQL as my database because unlike MySQL it supports stored procedures which you'll note I'm making use of. My stored procedure makes sure that duplicate entries don't happen. Later on as this application matures and gets more features added to it there will be more stored procedures and more teeth given to this one.
Next comes an application that runs out of cron on my Linux box to capture the scheduled runs of the North Jersey Coast Line train in its travels from NY Penn Station to Little Silver.
This runs once a week and updates the database with the latest version of NJTs schedule. Now that the data is in the database, I need to present it somehow.#!/usr/bin/perl -w ###################################################################### ## ## ## ###################################################################### +## use strict; require LWP::UserAgent; use DBI; # and no... that's not the real password... don't even try my $dbh=DBI->connect("DBI:Pg:database=trains;host=10.1.1.1","peter",'$ +3cr3t!') or die $DBI::errstr; # # This URL clipped from the source of a lookup I did manually # my $url=qq( http://atisweb.njtransit.com/cgi-bin/atis81.pl?railline= &dd=9 &mina=0 &resptype=U &action=entry &lineext=NJCL%3ANorth+Jersey+Coast+Line &oloc=New+York+Penn+Station &mm=6 &dtime=12%3A00+PM &minb=0 &back=sf_tr_schedules.shtml &fare=Y &date=6%2F9%2F2005 &ori=105+++++%3ANew+York+Penn+Station &rtime=1%3A00+PM &dsid=73++++++ &dloc=Little+Silver &dow=W &line=NJCL &linelookup= &osid=105+++++ &yyyy=2005 &lineext2=NJCL%3ANorth+Jersey+Coast+Line &des=73++++++%3ALittle+Silver &linedesc=North+Jersey+Coast+Line ); $url =~ s/\n//g; $url =~ s/\s+//g; my $ua=LWP::UserAgent->new; my $response=$ua->get($url); my @lines=split("\n",$response->content); printf "Returned: %d lines\n",$#lines+1; # # Clear the table my $drop=$dbh->prepare(qq(delete from train_info where 1=1)) or die $dbh->errstr; $drop->execute or die $drop->errstr; # # Set up our insert. my $sth=$dbh->prepare('select add_train(?,?,?)') or die $dbh->errstr; foreach my $line(@lines){ chomp $line; # Remove unsightly cruft at end of line. # # Split the content line on spaces my @f=split(/[\s\t]+/,$line); my ($marker,$rail_line,$timeLeave,$timeHalfLeave, $timeArrive,$timeHalfArrive,$train)= ($f[0],$f[2],$f[4],$f[5],$f[7],$f[8],$f[9]); next if $marker ne 'TMTI'; # Not a schedule line. next if ($rail_line ne 'NJCL') and ( $rail_line ne 'NJCLL' ); # Not the train line I want # # Normalize our times to 24 hour clock format my $leave_time=fix_time($timeLeave,$timeHalfLeave); my $arrive_time=fix_time($timeArrive,$timeHalfArrive); # Insert the record $sth->execute($train,$leave_time,$arrive_time) or die $sth->errstr; } $sth->finish; $dbh->disconnect; exit(0); # # Convert 12 hour time to 24 hour time correctly. sub fix_time { my ($time,$marker)=@_; my @f=split(":",$time); if ( $f[0] == 12 ) { $f[0] = "00" if $marker eq 'AM'; } else { $f[0] += 12 if $marker eq 'PM'; } return join(":",@f); }
I decided that I would keep it simple for now and wrote a quick CGI script with very simple functionality:
#!/usr/bin/perl -w ###################################################################### +## # # ###################################################################### +## # use strict; use DBI; use CGI qw/ :all /; use HTML::Template; # # Grab our template my $OLDIFS=$/; $/=undef; my $template=<DATA>; $/=$OLDIFS; # # instantiate template object my $t = HTML::Template->new (scalarref => \$template , option => 'value' ); # # Connect to the database my $dbh = DBI->connect('DBI:Pg:database=trains;host=10.1.1.1', 'peter','$3cr3t') or die $DBI::errstr; # # Create a statement handle for our query my $sth=$dbh->prepare(qq( select train_number,leaves,arrives from train_info where leaves > current_time order by leaves limit 3 )) or die $dbh->errstr; # # Execute the query $sth->execute or die $sth->errstr; # # Process the results. my @train_list_ary = (); while (my $row=$sth->fetchrow_hashref) { push @train_list_ary,{ train_number => $row->{train_number}, leaves => $row->{leaves}, arrives => $row->{arrives} }; } # Store the parameter $t -> param( train_list => [ @train_list_ary ]); # # Start our engines.... print header,start_html,$t->output,end_html; exit(0); __END__ <table border="1" width="100%"> <tr><th>Num</th><th>Lv NYC</th><th>Arr LS</th></tr> <TMPL_LOOP NAME="train_list"> <tr> <td><TMPL_VAR NAME="train_number"></td> <td><TMPL_VAR NAME="leaves"></td> <td><TMPL_VAR NAME="arrives"></td> </tr> </TMPL_LOOP> </table>
Using my very old Kyocera telephone I tried it out. Works like a charm!
Lest any monk think me complacent. This is still as far as I'm concerned a work in progress.
My future plan is to fully figure out the syntax to the query sent to the ATIS CGI program maintained by NJT with a little experimentation. I'll then load the full schedule of the North Jersey Coast Line into my database, expanding the schema to accomodate the functionality I'm after as well as expand the CGI program so I can figure out trips in either direction and to/from any two points on the line.
But that will be another post...
In reply to What time is the next train? by blue_cowdawg
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |