ATOC fares data and Linux users - software recommendations ?

[General Zod]

Having downloaded the various data files from the ATOC site I would like to query the data preferably in a non-windows environment. Has anyone used the files in Linux with open source database software such as MySQL ? Perhaps there are more suitable programs that could be recommended ? Pointers would be greatly appreciated.

Thanks again.

Z

 

[table38]

I'm sure someone already wrote a script to get the raw data into MySqk, and I think there's a link to it on this forum somewhere! I will try to locate it.

--- update ---

Try here: http://www.railforums.co.uk/showthread.php?t=81177

 

[Hyphen]

Try here: http://www.railforums.co.uk/showthread.php?t=81177

That's instructions for getting it into MSSQL - which most definitely won't run on Linux

I've got the latest NFM in MSSQL already - when I get a spare hour I'll have a play and see if I can use one of the migration tools to convert it into a MySQL DB.

 

[londiscape]

Looking at the interface specification for ATOC data feeds it seems that the data is provided as flat text files, which Linux is much better suited to manipulating (no doubt the PowerShell lovers will disagree with me ). I'm trying to download a fares data feed so I can look more closely, but I'm on a hotel wi-fi which has restricted bandwidth, even 68Mb is proving a struggle

From the tech specs I suspect they are not standard CSV or XML, but if they are flat text (and are one-line-per-record) then the application of *nix parsing and string manipulation tools should permit translating into a more common standard delimited format which can then be imported into MySQL with statements like LOAD DATA INFILE.

Bear with me, more to follow once I've seen the files "in the flesh" so to speak.

 

[General Zod]

I installed SQL Server Express and Management Studio on Win7 but got repeated errors when running the loadfares.exe. It loaded some data but baulked at the remainder, hence, me having to terminate the program
I'm fairly competent in using Perl, Sed, Awk and shell scripts and would obviously like to make use of these together with MySQL to query the data.

cheers

 

[Emyr]

From the tech specs I suspect they are not standard CSV or XML, but if they are flat text (and are one-line-per-record) then the application of *nix parsing and string manipulation tools should permit translating into a more common standard delimited format which can then be imported into MySQL with statements like LOAD DATA INFILE.

Haven't looked at the specs or the data, but I expect they'll be fixed-length records, or at least fixed-length fields. This is a very Mainframey way of doing things and dates back to Beeching's Great Freight Wagon Cull.
--- old post above --- --- new post below ---
If someone could take the source files that are on Pastebin (linked here http://www.railforums.co.uk/showpost.php?p=1407388&postcount=13) and upload them to Github or codeplex, I'd be willing to spend a little time on this.

I'm a c#/ASP.Net developer with a penchant for LINQ-to-SQL and access to some nifty tools (e.g LINQPad with autocomplete enabled and RedGate ANTS Performance Profiler)...

...but Pastebin isn't accessible here and I object to software development without source control.

 

[PermitToTravel]

They are fixed-length records with fixed-length fields

 

[Emyr]

Bingo!

 

[table38]

They are fixed-length records with fixed-length fields

Doesn't anyone use COBOL any more these days?

Actually you could easily map the fields into a C "struct" and do it that way (but watch out for alignment problems if your compiler does that to optimise structures - you'll need to "pack" the data).

 

[Emyr]

Doesn't anyone use COBOL any more these days?

Enough for a new version of the COBOL spec to be issued by ISO/IEC:
https://en.wikipedia.org/wiki/COBOL#COBOL_2014

 

[krus_aragon]

I've had a brief spurt at parsing them into MySQL over the past few days (and discovering how rusty my database skills are!). Here's a few pointers from what I've done:

The structure for the fixed-width records in all the files are given in the PDF at the bottom of this page: http://data.atoc.org/fares-data.

After downloading and unzipping them, you'll find that the general structure is something along the lines of:

/!! Start of file                                                               
/!! Content type:  FSC                                                          
/!! Sequence:      504                                                          
/!! Records:       31078                                                        
/!! Generated:     09/09/2014                                                   
/!! Exporter:      RJIS RjEhrFrs v 4.6.5                                        
RGL0004493112299912122008
RGL0053553112299912122008
RGL0054103112299912122008
RGL0168673112299902012009
RGL0169343112299902012009
RGL0169613112299902012009

...

RZ23407263112299926072000
RZ23407273112299926072000
/!! End of file (09/09/2014)

MySQL can easily skip n lines at the beginning of a file, but seemingly not at the end of a file. So to trim the "end of file" off each each file, use head -n -1 inputfile > outputfile on them, e.g.:

cd /folder/with/fares
mkdir trimmed
for i in 'ls *'; do head -n -1 $i > ./trimmed/$i ;done

and you'll have the trimmed files all in a new folder, ready to go.

Assuming you've set up a database, given yourself permission to read files, ad have conected to the database, here's the SQL queries I used to import the station clusters data into a table from RJFAF504.FSC:

CREATE TABLE station_clusters (id INT NOT NULL AUTO_INCREMENT, 
CLUSTER_ID CHAR(4), CLUSTER_NLC CHAR(4), 
END_DATE DATE, START_DATE DATE, PRIMARY KEY(id));

LOAD DATA LOCAL INFILE 'RJFAF504.FSC' INTO TABLE station_clusters 
IGNORE 6 LINES (@var1)
SET CLUSTER_ID=SUBSTR(@var1,2,4),
CLUSTER_NLC=SUBSTR(@var1,6,4), 
END_DATE=STR_TO_DATE(SUBSTR(@var1,10,8),'%d%m%Y'), 
START_DATE=STR_TO_DATE(SUBSTR(@var1,18,8),'%d%m%Y');

Each line is read in as a temporary variable @var1, and then the SET command assigns the value of the strings selected by SUBSTR to the appropriate columns. STR_TO_DATE is used to convert the date from DDMMYY format to the YYYY-MM-DD format that MYSQL expects.


Dealing with the "FLOW" file (RJFAF504.FFL) was trickier, as the file contains two different types of records, which I'd want to put in two different tables: flow records (49 characters) and fare records (20 or 22 characters: the last field may be empty). The second character of each line indicates whether it's a fare record "F" or flow "T". I imported the file into a temporary table providing for both types of record, (filling the fields depending on the value of the second character) and then selected the appropriate rows for the final tables:

CREATE TABLE tempflow (id INT NOT NULL AUTO_INCREMENT, RECORD_TYPE CHAR(1), 
ORIGIN_CODE CHAR(4), DESTINATION_CODE CHAR(4), ROUTE_CODE CHAR(5), 
STATUS_CODE CHAR(3), USAGE_CODE CHAR(1), DIRECTION CHAR(1), END_DATE DATE, 
START_DATE DATE, TOC CHAR(3), CROSS_LONDON_IND CHAR(1), 
NS_DISC_IND CHAR(1), PUBLICATION_IND CHAR(1), FLOW_ID CHAR(7), 
TICKET_CODE CHAR(3), FARE CHAR(8), RESTRICTION_CODE CHAR(2), 
PRIMARY KEY(id));

LOAD DATA INFILE 'RJFAF504.FFL' INTO TABLE tempflow IGNORE 6 LINES (@var1)
SET RECORD_TYPE=SUBSTR(@var1,2,1),
/*columns for flows only*/
ORIGIN_CODE=IF(RECORD_TYPE='F',SUBSTR(@var1,3,4),NULL),
DESTINATION_CODE=IF(RECORD_TYPE='F',SUBSTR(@var1,7,4),NULL),
ROUTE_CODE=IF(RECORD_TYPE='F',SUBSTR(@var1,11,5),NULL),
STATUS_CODE=IF(RECORD_TYPE='F',SUBSTR(@var1,16,3),NULL),
USAGE_CODE=IF(RECORD_TYPE='F',SUBSTR(@var1,19,1),NULL),
DIRECTION=IF(RECORD_TYPE='F',SUBSTR(@var1,20,1),NULL), 
END_DATE=IF(RECORD_TYPE='F',STR_TO_DATE(SUBSTR(@var1,21,8),'%d%m%Y'),NULL),
START_DATE=IF(RECORD_TYPE='F',STR_TO_DATE(SUBSTR(@var1,29,8),'%d%m%Y'),NULL),
TOC=IF(RECORD_TYPE='F',SUBSTR(@var1,37,3),NULL),
CROSS_LONDON_IND=IF(RECORD_TYPE='F',SUBSTR(@var1,40,1),NULL),
NS_DISC_IND=IF(RECORD_TYPE='F',SUBSTR(@var1,41,1),NULL),
PUBLICATION_IND=IF(RECORD_TYPE='F',SUBSTR(@var1,42,1),NULL),
/*columns for both*/
FLOW_ID=IF(RECORD_TYPE='F',SUBSTR(@var1,43,7),SUBSTR(@var1,3,7)),
columns for fares only*/
TICKET_CODE=IF(RECORD_TYPE='T',SUBSTR(@var1,10,3),NULL),
FARE=IF(RECORD_TYPE='T',SUBSTR(@var1,13,8),NULL),
RESTRICTION_CODE=IF(RECORD_TYPE='T',SUBSTR(@var1,21,2),NULL);


CREATE TABLE flow (id INT NOT NULL AUTO_INCREMENT, ORIGIN_CODE CHAR(4), 
DESTINATION_CODE CHAR(4), ROUTE_CODE CHAR(5), STATUS_CODE CHAR(3), 
USAGE_CODE CHAR(1), DIRECTION CHAR(1), END_DATE DATE, START_DATE DATE, 
TOC CHAR(3), CROSS_LONDON_IND CHAR(1), NS_DISC_IND CHAR(1), 
PUBLICATION_IND CHAR(1), FLOW_ID CHAR(7), PRIMARY KEY(id));

INSERT INTO flow SELECT tempflow.id, tempflow.ORIGIN_CODE, 
tempflow.DESTINATION_CODE, tempflow.ROUTE_CODE, tempflow.STATUS_CODE, 
tempflow.USAGE_CODE, tempflow.DIRECTION, tempflow.END_DATE, 
tempflow.START_DATE, tempflow.TOC, tempflow.CROSS_LONDON_IND, 
tempflow.NS_DISC_IND, tempflow.PUBLICATION_IND, tempflow.FLOW_ID 
FROM tempflow WHERE RECORD_TYPE='F';


CREATE TABLE flow_fares (id INT NOT NULL AUTO_INCREMENT, FLOW_ID CHAR(7), 
TICKET_CODE CHAR(3), FARE CHAR(8), RESTRICTION_CODE CHAR(2), PRIMARY KEY(id));

INSERT INTO flow_fares SELECT tempflow.id, tempflow.FLOW_ID, 
tempflow.TICKET_CODE, tempflow.FARE, tempflow.RESTRICTION_CODE 
FROM tempflow WHERE RECORD_TYPE='T';

DROP TABLE tempflow;

...and that's about as far as I'd gotten.

 

[Hyphen]

I installed SQL Server Express and Management Studio on Win7 but got repeated errors when running the loadfares.exe. It loaded some data but baulked at the remainder, hence, me having to terminate the program
I'm fairly competent in using Perl, Sed, Awk and shell scripts and would obviously like to make use of these together with MySQL to query the data.

cheers

There's an odd issue regarding the latest Fares data and the FCC split - it's therefore got three records in the flat file which loadfares.exe is parsing and attempting to enter into the database with 'FCC', leading to a primary key issue (and causing the exception). The previous Fares had a single entry for FCC.

My solution to this was to edit RJFAF504.TOC in an editor of choice (I recommend Notepad++ on Windows, though I'm sure Notepad will work) and edit the latter two "FIRST CAPITAL CONNECT" lines. Change the lines beginning FFCCGN and FFCCTL to FFC1GN and FFC2TL. Once you've edited and saved this, the import according to soil's instructions will now work.

I don't know if it was the correct way to do it or what record linking issues this might cause along the way - I've not really played with the new Fares much; I just wanted the darn thing into the database. I'd hope, though, that there shouldn't be many fares still relying on FCC.

Any lookups on the three-character FARE_TOC_ID (primary key) will return First Capital Connect as normal, any lookups on the two-character TOC_ID should also return the correct split of FCC, albeit with FC1 or FC2 as the primary key.

 

[londiscape]

I've had a brief spurt at parsing them into MySQL over the past few days (and discovering how rusty my database skills are!). Here's a few pointers from what I've done:

The structure for the fixed-width records in all the files are given in the PDF at the bottom of this page: http://data.atoc.org/fares-data.

After downloading and unzipping them, you'll find that the general structure is something along the lines of:

/!! Start of file                                                               
/!! Content type:  FSC                                                          
/!! Sequence:      504                                                          
/!! Records:       31078                                                        
/!! Generated:     09/09/2014                                                   
/!! Exporter:      RJIS RjEhrFrs v 4.6.5                                        
RGL0004493112299912122008
RGL0053553112299912122008
RGL0054103112299912122008
RGL0168673112299902012009
RGL0169343112299902012009
RGL0169613112299902012009

...

RZ23407263112299926072000
RZ23407273112299926072000
/!! End of file (09/09/2014)

MySQL can easily skip n lines at the beginning of a file, but seemingly not at the end of a file. So to trim the "end of file" off each each file, use head -n -1 inputfile > outputfile on them, e.g.:

cd /folder/with/fares
mkdir trimmed
for i in 'ls *'; do head -n -1 $i > ./trimmed/$i ;done

and you'll have the trimmed files all in a new folder, ready to go.

How about

cat $inputfile | grep -v '/!!' > $outputfile

to get rid of all the comments?
--- old post above --- --- new post below ---

There's an odd issue regarding the latest Fares data and the FCC split - it's therefore got three records in the flat file which loadfares.exe is parsing and attempting to enter into the database with 'FCC', leading to a primary key issue (and causing the exception). The previous Fares had a single entry for FCC.

Urgh... yes indeed that is an issue. According to ATOC's data specifications the 3 char FARE_TOC_ID field for Fare TOC records is labelled as a key field so this is a bug - changing to unique values as you described would be the only way to solve it, until later feeds come out with unique TOC ID values.

 

[krus_aragon]

How about

cat $inputfile | grep -v '/!!' > $outputfile

to get rid of all the comments?

Equally good, yes, but I was only thinking about the last comment line at the time. There may be advantage in keeping the header comments as they are slightly descriptive about the contents of the file. Or there may not...

 

[krus_aragon]

There's an odd issue regarding the latest Fares data and the FCC split - it's therefore got three records in the flat file which loadfares.exe is parsing and attempting to enter into the database with 'FCC', leading to a primary key issue (and causing the exception). The previous Fares had a single entry for FCC.

...

According to the specification (attached), in the Fare TOC record the Record Type, Fare TOC ID and TOC Id are all keys, i.e. a compound key.

/!! Start of file                                                               
/!! Content type:  TOC                                                          
/!! Sequence:      504                                                          
/!! Records:       114                                                          
/!! Generated:     09/09/2014                                                   
/!! Exporter:      RJIS RjEhrFrs v 4.6.5                                        
FATO  ATOC-RSP                      
FDBA  DBA ADMINISTRATOR             
FFCCFCFIRST CAPITAL CONNECT         
FFCCGNFIRST CAPITAL CONNECT         
FFCCTLFIRST CAPITAL CONNECT         
FGCRGCGRAND CENTRAL RAILWAY

The data seems to be valid according to their specification.