I have created a program that imports the ATOC fares feed into a database. (This is the complete national rail fares database, with various supporting data such as supplements, railcards, rovers, etc.)
The program is attached to this message.
To make use of it you will have to download + install Microsoft SQL Server.
You can get it here:
https://www.microsoft.com/betaexperience/pd/SQLEXPWT/enus/
You will need a copy of the fares data from here:
http://data.atoc.org/?q=user/register
(free signup required)
Steps required:
1. Download and unzip the fares data into its own folder.
2. Download and install SQL Server (Express). You can accept the defaults, except that when it says 'Named Instance', I suggest you instead choose 'Default Instance'.
3. Download and unzip the attached 'faresdb.zip' file.
4. Having installed SQL Server, you now need to double click the '1. create database'(.sql) file from wherever you unzipped the attachment here to. This should load SQL Server Management Studio, just click Connect, and then when the file has loaded, click 'Execute'. This should complete with no errors in a few seconds. This creates the fares database.
5. Now you need to import the data. Run 'Load Fares' (again, from whereever you unzipped my attachment to) and click the 'Load Fares' button. You will need to browse to the location of the fares data you downloaded and unzipped the fares database from ATOC, then click ok.
If you did as I said and installed a 'Default Instance', this should work fine. If not, and you get an error, you probably need to edit the config file (wherever you unzipped my attachment to) changing localhost to localhost\SQLEXPRESS.
The data load will take probably 10 minutes or so. The last two stages take the longest - there are around 8 lines of '.'s when loading the 'non-derivable fares' (each dot is 20,000 records out of 17 million total). It will let you know when it's done.
6. Now there are two separate cleanup files. Double click 'cleanup trains part1', and then 'execute'. This may take over 10 minutes. Repeate with 'clean up trains part 2'. Again this may take a few minutes. Do it in the correct order or it might not work.
7. Now run '5. create indexes.sql'. These are not an exhaustive set of indexes, they are more of a base set in order to get the system to do some basic queries without taking 10 minutes each time. You definitely want these ones though.
All done....
All the data are in the database, but I haven't written too many queries for it.
Here are some examples of what you can do:
1. Load SQL Server Management Studio.
2. Hit New/Query Window, paste this:
use trains
go
exec spGetFaresFrom
'WOK'
then hit the execute button
This should give all the available (non-advance) fares from Woking to anywhere.
You can add parameters to this, e.g.,:
exec spGetFaresFrom
'WOK',
@IncludeAdvances = 1
adds in the advances as well
exec spGetFaresFrom
'WOK',
@TicketCode = 'SDS'
finds all SDS fares from Woking
exec spGetFaresFrom
'WOK',
@Seasons = 1 -- default is 0, no seasons
finds all seasons from Woking
(note, this may appear to include duplicates, this is because seasons are reversible, so I have included both directions here, most of the time the fares are the same, but not always, if you can't find the fare specified, try reversing the flows)
exec spGetFaresFrom
'WOK',
@Seasons = 1,
@RouteCode = 734,
@TicketClass = 1 -- 2 is the other option
Finds season tickets in 1st class routed via Virginia Water (note: the route id is given in the output, so you don't need to guess)
You can also do:
exec spGetFaresBetween 'WOK', 'BTN'
to get fares between Woking and Brighton
exec spGetFaresBetween 'WOK', 'YRK',
@IncludeAdvances = 0
Woking - York excluding advances
exec spGetFaresBetween 'LST', 'SNF', @Seasons = 1
Seasons between Liverpool Street and Shenfield
Another procedure:
exec spGetRestrictionDetails 'UT'
will give you the database details on a given restriction code - days the restriction applies (first table), stations and times (second table), exceptions (third table)
There is a database diagrams file, if you know what a database diagram is, you can run this and then have a look, but otherwise don't bother!
Just to add finally, you will need Windows to make this work. The import uses .NET. If you don't have the right version installed, it won't work, but let me know if you have any problems....
The program is attached to this message.
To make use of it you will have to download + install Microsoft SQL Server.
You can get it here:
https://www.microsoft.com/betaexperience/pd/SQLEXPWT/enus/
You will need a copy of the fares data from here:
http://data.atoc.org/?q=user/register
(free signup required)
Steps required:
1. Download and unzip the fares data into its own folder.
2. Download and install SQL Server (Express). You can accept the defaults, except that when it says 'Named Instance', I suggest you instead choose 'Default Instance'.
3. Download and unzip the attached 'faresdb.zip' file.
4. Having installed SQL Server, you now need to double click the '1. create database'(.sql) file from wherever you unzipped the attachment here to. This should load SQL Server Management Studio, just click Connect, and then when the file has loaded, click 'Execute'. This should complete with no errors in a few seconds. This creates the fares database.
5. Now you need to import the data. Run 'Load Fares' (again, from whereever you unzipped my attachment to) and click the 'Load Fares' button. You will need to browse to the location of the fares data you downloaded and unzipped the fares database from ATOC, then click ok.
If you did as I said and installed a 'Default Instance', this should work fine. If not, and you get an error, you probably need to edit the config file (wherever you unzipped my attachment to) changing localhost to localhost\SQLEXPRESS.
The data load will take probably 10 minutes or so. The last two stages take the longest - there are around 8 lines of '.'s when loading the 'non-derivable fares' (each dot is 20,000 records out of 17 million total). It will let you know when it's done.
6. Now there are two separate cleanup files. Double click 'cleanup trains part1', and then 'execute'. This may take over 10 minutes. Repeate with 'clean up trains part 2'. Again this may take a few minutes. Do it in the correct order or it might not work.
7. Now run '5. create indexes.sql'. These are not an exhaustive set of indexes, they are more of a base set in order to get the system to do some basic queries without taking 10 minutes each time. You definitely want these ones though.
All done....
All the data are in the database, but I haven't written too many queries for it.
Here are some examples of what you can do:
1. Load SQL Server Management Studio.
2. Hit New/Query Window, paste this:
use trains
go
exec spGetFaresFrom
'WOK'
then hit the execute button
This should give all the available (non-advance) fares from Woking to anywhere.
You can add parameters to this, e.g.,:
exec spGetFaresFrom
'WOK',
@IncludeAdvances = 1
adds in the advances as well
exec spGetFaresFrom
'WOK',
@TicketCode = 'SDS'
finds all SDS fares from Woking
exec spGetFaresFrom
'WOK',
@Seasons = 1 -- default is 0, no seasons
finds all seasons from Woking
(note, this may appear to include duplicates, this is because seasons are reversible, so I have included both directions here, most of the time the fares are the same, but not always, if you can't find the fare specified, try reversing the flows)
exec spGetFaresFrom
'WOK',
@Seasons = 1,
@RouteCode = 734,
@TicketClass = 1 -- 2 is the other option
Finds season tickets in 1st class routed via Virginia Water (note: the route id is given in the output, so you don't need to guess)
You can also do:
exec spGetFaresBetween 'WOK', 'BTN'
to get fares between Woking and Brighton
exec spGetFaresBetween 'WOK', 'YRK',
@IncludeAdvances = 0
Woking - York excluding advances
exec spGetFaresBetween 'LST', 'SNF', @Seasons = 1
Seasons between Liverpool Street and Shenfield
Another procedure:
exec spGetRestrictionDetails 'UT'
will give you the database details on a given restriction code - days the restriction applies (first table), stations and times (second table), exceptions (third table)
There is a database diagrams file, if you know what a database diagram is, you can run this and then have a look, but otherwise don't bother!
Just to add finally, you will need Windows to make this work. The import uses .NET. If you don't have the right version installed, it won't work, but let me know if you have any problems....
Last edited:
