Fares Data question

[edjeff]

I've been poking around with the fares data available here:

http://data.atoc.org/fares-data

(by converting the text files into a postgres db)

And wondered if anyone here was familiar enough with it to tell me where I'm going wrong.

What I'm trying to get to is something where I can spit out, e.g. the cheapest (non-advance) return ticket from a given station, and I think I'm successfully connecting stations via flows and non-derivable fares using station clusters where appropriate - still ending up with some gaps though, e.g. my output for Chichester (CCH) gives the following 'missing' stations:

SAN | Sandown
SHN | Shanklin
BDN | Brading
CCH | Chichester
HOH | Harrow-on-the-Hill
LKE | Lake
RYR | Ryde St Johns Road
SAB | Smallbrook Junction

Now, obviously makes sense that there wouldn't be a fare from a station to itself, so I can ignore that.

Harrow I'm guessing maybe I could find using the group locations from the Locations - i.e. there's probably a fare from Chichester to "London Zone 1-6" which Harrow seems to be grouped into. Although why it would be the only London station I need to do that for I don't know - is that likely or would it suggest I've screwed my logic up somewhere?

Where I'm really stumped is the other stations, which are all on the Isle of Wight - they're all part of a cluster which I can connect to various other clusters, but none of which appear to contain Chichester (and there don't appear to be any useful group locations).

Am I missing some other grouping/clustering rule that would help me join the dots here?

Thanks in advance for any help!

 

[yorkie]

Welcome to the forum.

Hopefully someone can give you an answer to your question, but do you want the data in a SQL Database? If so, get yourself to one of our fares data workshops. Whereabouts are you based?

 

[edjeff]

Yeah, I've got the raw data in an SQL database, just not 100% sure I'm joining it all up correctly. I'm in London.

 

[ForTheLoveOf]

The reason you're not seeing fares to Harrow is because there are no fares defined specifically to Harrow from Chichester - it is all to station or fare groups which include Harrow implicitly, such as London Zones 1-6 as you've said.

www.brfares.com's Expert Mode makes this easy to see - you can tell where the fares are defined to and from.

The reason you're not seeing fares to stations on the Isle of Wight other than Ryde Esplanade is because, from Chichester, there are no through fares defined to the Isle of Wight other than to Ryde Esplanade. If you want to go to any of the other stations you'll need to obtain a combination of tickets.

Again, www.brfares.com shows this very clearly.

Without seeing how your solution works, I can't comment on whether or not it works properly, but I think for Chichester the stations it's giving as having no through fares are correct in the case of the IoW ones, and technically correct but practically speaking incorrect for Harrow.

Unfortunately, I think you can see here how preventing issues like Harrow appearing to have no fares defined, will require you to take it to a further level of complexity, and consider station and fares groups when establishing what fares are set to/from a particular origin/destination.

It's entirely possible to develop this up to the level of a booking engine, or even a program that also integrates data about how the Routeing Guide works, to find out the cheapest possible ticket(s) for a specific journey. But that is so far beyond a mere SQL database that it'd take many months or years of hard work to get there!

 

[edjeff]

Thanks! That's really helpful, hadn't spotted the brfares expert mode!