Fares database

[soil]

Apparently the program was not working properly.

I have uploaded an updated version now in the OP.

 

[Sacro]

Don't suppose there's a chance of open sourcing the code? I'd consider writing a nice WPF based UI around it!

 

[soil]

Don't suppose there's a chance of open sourcing the code? I'd consider writing a nice WPF based UI around it!

The importer doesn't need much UI since you just run it - the data might do though (you could write a little app to accomplish various queries), and that's entirely open since it's accessed (if you pardon the pun) from the database directly using SQL, so there's nothing to open there.

 

[Sacro]

The importer doesn't need much UI since you just run it - the data might do though (you could write a little app to accomplish various queries), and that's entirely open since it's accessed (if you pardon the pun) from the database directly using SQL, so there's nothing to open there.

I was going to rewrite the importer to use SQLite or SQL Express therefore not requiring SQL server to be running!

 

[soil]

It's already written for SQL Express.

If you were writing an app that offers specific functions then sqlite would make sense, but for querying the actual data then SQL Express is much richer - the user experience for querying SQLlite directly is much worse. Obviously if you write an app on top of that then there are advantages.

 

[Sacro]

Yes, the plan was to write an app over the top of it, I use FluentNHibernate so I get to throw LINQ queries quite easily!

 

[RJ]

Would just like to thank soil for this excellent piece of work. I've identified some "interesting" fares that I would never have found otherwise.

My skills in this sort of area are non existent. I've had a play around but drawn a blank. Can anybody work out how to do the following?

- Use NLCs instead of CRS codes to identify origins/destinations for fares.
- Get output that shows all permutations of fares, i.e using "all" for the origin and destination?

TIA!

 

[soil]

Would just like to thank soil for this excellent piece of work. I've identified some "interesting" fares that I would never have found otherwise.

My skills in this sort of area are non existent. I've had a play around but drawn a blank. Can anybody work out how to do the following?

- Use NLCs instead of CRS codes to identify origins/destinations for fares.

Run this:

http://pastebin.com/ncZXMfMr

Then you can query either:

exec spGetFaresFrom @OriginCRS = 'WOK' , @TicketCode = 'CDR'
or
exec spGetFaresFrom @OriginNLC = '5685' , @TicketCode = 'CDR'

to get the same result.

- Get output that shows all permutations of fares, i.e using "all" for the origin and destination?

Hmm, that's a rather big data set. At the least you would probably want to filter by ticket type.

You can try the vwFlows view

e.g.
select * from vwFlows where ticketType = 'OPR'

gives all the OPR tickets defined in the flows/flowsfare table (114,356)

select * from vwFlows where ticketType = 'OPR' and tickettocid = 'LBR'
order by fare

is all London-Midland priced OPRs in fare order.

etc.

Note that the vwFlows view is a quick hack really, and it doesn't include the non-derivable fares table. It contains 126,514,688 records. Some fares are found only in that table:

select * from NonDerivableFares where TicketCode = 'OPR' and RailcardCode = ''

would show those missing fares, but not in such a nice format.

 

[RJ]

soil that's perfect, thank you. That query allows me to find all tickets with a specific route i.e
select * from vwFlows where ticketType = 'SDS' and RouteCode = '150' . Yes, I was disappointed that I could not find anything to get me on a helicopter.

There are so many weird and potentially useful fares in the system that I would never have come across otherwise!

 

[greatkingrat]

You can get a ticket valid via helicopter if you want

http://www.brfares.com/#!fares?orig=1072&dest=K686

 

[soil]

soil that's perfect, thank you. That query allows me to find all tickets with a specific route i.e
select * from vwFlows where ticketType = 'SDS' and RouteCode = '150' . Yes, I was disappointed that I could not find anything to get me on a helicopter.

There are so many weird and potentially useful fares in the system that I would never have come across otherwise!

Indeed, there are many things that become trivial, e.g.,

declare @NLCCode char(4)
select @NLCCode = '0428' -- canterbury stations

select OriginDescription, DestinationDescription, Fare
, TicketTocs.Name TocName
from
(
select
OriginDescription, DestinationDescription, Fare, TicketTOCId,
ROW_NUMBER() OVER (PARTITION BY TicketTocId Order by fare asc ) as RankNr
from
vwFlows
join TicketTypes on TicketTypes.Code = VwFlows.TicketType and TicketTypes.RestrictedByTrain = 1
where OriginNLCCode = @NLCCode
or (DestinationNlcCode = @NLCCode and VwFlows.Reversable = 1)
) AS Ranked
join TicketTocs on TicketTocs.ID = TicketTOCId
where Ranked.RankNr = 1

This query returns the cheapest possible advance fares by TOC from a given origin (takes almost a minute to run on my system), in this case:

Pewsey £10.50 FGW
Corby £12 East Midlands
Rugby £12 Virgin
Bury St Edmunds £18.10 AGA
P'bo £21 EC
Dublin Ferryport £38 ATW

It doesn't guarantee availability at that price of course.

I'm currently offered for this Friday on the 09:23 from Canterbury to St Pancras

Pewsey £33.90
Corby £22
Rugby £30
BSE £25.50
PBO £66.50
Dublin Ferry Port £38 on the 07:18 or 17:25 - this appears to be a flat fare

You can increase the RankNr, to 10, which shows, inter alia, that there is a cheap Advance cluster (cheaper than to Bristol) to the Severn Beach Line routed AP Salisbury.

This results in a much cheaper fare than Pewsey, £13.30 for Friday:

CBW - Waterloo East 10:07
Waterloo - Bristol TM 12:20
Bristol TM - Severn Beach 15:16

(tickets routed St Pancras - Paddington - Bristol are also available, but they are priced over £30)

 

[RJ]

Ah yes, this is something I have thought about in the past and included as part of my dissertation on the fares system. Coventry is preferable to Rugby as there around three times as many Virgin services stopping there. That said, Southeastern are a special case because they've had time restrictions slapped on many (but not all!) Advance "&Connections" ticket types.

I'm still trying out various queries and to be frank, I am flabbergasted at just how many loopholes have been left wide open by those who price the flows, particularly in the case of local season tickets. All fully corroborated by NRE too . This database is a very powerful tool indeed and I'm more than happy to make a small donation towards its development, if you'll have one soil.

 

[soil]

There's not really any development being done on it, not really any funds needed. You can do Birmingham also - same fare group from Canterbury.

There's definitely a lot more that could be done, the timetable data is useful obviously but without an official source of up-to-date routeing data it's difficult to take it much further.

 

[bnm]

You can get a ticket valid via helicopter if you want

http://www.brfares.com/#!fares?orig=1072&dest=K686

Except the helicopter service no longer operates to The Scilly Isles, so those tickets should not be sold.

 

[jon0844]

Can't wait for RJ's next adventure (sorry, journey) including a ticket valid on a helicopter!

Would it work the gates at St Pancras?

 

[RJ]

Quick note to say thanks - got it working perfectly.

Dissapointed to find no Via London tickets priced lower than the return to London Terminals, mind

There are in many cases though! I made an impromptu journey between Moor Street to Marylebone yesterday and went for a straight Condition 19b split. I used the database on the train towards Brum and discovered that the Any Permitted Banbury to Radlett/St Albans fare was cheaper than the Banbury to London Terminals fare. In the end I stuck with splitting at Banbury and High Wycombe as it was the cheapest I could find at the time. The clerk at Moor Street seemed surprised that I saved as much as £6.45.

I'm completely elementary to using databases like this, but I found some operators that might be quite useful to other beginners.

'<' = Less than
'>' = Greater than
'<=' = Less than or equal to
'>=' = Greater than or equal to
'!=' = Exclude

The last one is useful for excluding certain routes from searches. This document is handy for identifying the numeric route codes.

 

[soil]

It's often useful to copy and paste the entire output of something like

exec spGetFaresFrom 'BAN', @TicketCode = 'CDR'

into Excel

Then click the filter button in Excel, and you can filter things out, e.g., by route, whether it's valid across London, whether the time restriction is not one that works with your intended travel pattern, and so on. (It won't pickup tickets valid via London that aren't marked with a maltese cross or routed Via London, however.)

For routes the 'like' operator is helpful.

E.g.,




select * from vwFlows where
(origincrscode = 'WOK' or destinationcrscode = 'WOK')
and routedescription like '%clapham%'


shows tickets to or from Woking with routes containing Clapham in some position (%clapham% matches <anything>Clapham<anything>; clapham% would match Clapham<anything>, and so on).

Also you can do things like


select * from restrictions where description like '%virgin%'

to get restrictioncodes for restrictions named something with Virgin, and likewise


select * from routes where description like '%virgin%'

for routes (also including Virginia Water!)

 

[CyrusWuff]

I used the database on the train towards Brum and discovered that the Any Permitted Banbury to Radlett/St Albans fare was cheaper than the Banbury to London Terminals fare.

Not entirely unusual for flows owned by FCC, it has to be said...Another example being Beaconsfield to St Albans, where the Anytime Day Return is £23.10, compared with a Beaconsfield to Zone U1 Anytime Day Return at £28.00 or Anytime Day Travelcard at £27.60. Or for the same flow, an Off-Peak Day Return is £15.20, whereas the Off-Peak Day Travelcard is £19.80.

 

[RJ]

Can't wait for RJ's next adventure (sorry, journey) including a ticket valid on a helicopter!

Would it work the gates at St Pancras?

You mean a ticket like this? Unfortunately I don't think it's valid at St Pancras

 

[SickyNicky]

You mean a ticket like this? Unfortunately I don't think it's valid at St Pancras

I'm impressed - I didn't think that ticket could be issued. Anyway, I'm going to have to find a different custom user title now that the secret of route 00150 is out.

 

[antimega]

Thanks for this work, it should make looking up fares a lot easier.

I'm getting an error in the Load Fares application - something about the datetime string format being incorrect. Error attached. Any help?

thanks
Chris.

 

[OwlMan]

The January faes are now available (file dated 27/1/2014) on the ATOC download site

 

[Paul Kelly]

That took a while! I had been checking it for a while; I wonder if they forgot. Strange, as the timetable feed seems to be automatically updated with no human intervention.

 

[CyrusWuff]

Suspect they may have been waiting for the London fares changes to settle before doing the extract, or something along those lines, given London only changed a week ago.

 

[antimega]

Thanks - now works with the new extract. A file naming issue halfway through import, but worked around that.

Thanks again.

 

[soil]

Thanks - now works with the new extract. A file naming issue halfway through import, but worked around that.

Thanks again.

Error something like this?
"Could not find a part of the path 'X:\RJFAF207\RJFAF207.NDF'."

FWIW this is a logic error in my code, basically the current fares are called:

RJFAF273 (i.e. this is release number 273 of the data) but the non-derived fares are from an older release, RJFAF207.

It does a find/replace for RJFAF273 and changes it to 207

If you downloaded and extracted the fares to a directory called RJFAF273, then it will break in that manner, because the directory RJFAF273 exists, but the RJFAF207 directory doesn't, and as it changes all instances of 'RJFAF273', it will also change the directory name, if that's what it's called.

If you rename the directory to say 'Fares', or anything not containing RJFAF273, it will work fine.

Anyway, just a heads-up that to avoid this error, don't extract the fares to a directory with that name. Anything else will work fine.

I will update the loader at some point.

 

[antimega]

Yes, exactly that

No worries, I worked round by renaming the folder.

Thanks again!

 

[RJ]

Thanks for the heads up. I extracted the new fares data to a folder and renamed it "NFM17," it all installed without any problems. Previously I didn't rename the folder, got the error during the loading of the non derivable fares, continued anyway and found that most fares were missing.

All sorted now!
--- old post above --- --- new post below ---

Suspect they may have been waiting for the London fares changes to settle before doing the extract, or something along those lines, given London only changed a week ago.

That was probably it. My NFM17 CD only arrived on the 18th of January, the day before the new fares for the London area finally went live.

 

[greatkingrat]

There appears to have been a bit of a problem with some Merseyrail fares in NFM17. There are a few flows that have gone down drastically in price. This creates some useful anomalies (at least until they notice and put the prices back up again!).

 

[RJ]

Hmmm - I don't think all the fares have loaded. I try to do a vwflow query from all fares from Liverpool Lime Street and only 59 results come up for Southern/LM advances. Will have a go at a clean reinstall, is anyone else having this problem?

use trains
go

select * from vwflows where OriginCRSCode = 'LIV'