timetable database tips

[Sandy Forrest]

Thanks to those on here that have helped me with this stuff so far :) I've actually managed to get somewhere with this now and I'm getting close to some usable data coming form the darwin system

I'm looking for tips / suggestions on the best way to build a timetable database, preferable using the national rail data from the ftp server. but open to suggestions on the NWR feeds also if that is better

so i suppose the questions I'm asking is ....

1. the files are updated nightly? should i update the entire database on a nightly basis or is there a better way to do this?
2. I'm storing everything in mysql database on a raspberry pi, with limited mysql knowledge, is there a quick way to write 'thousands' of entries to the database?
3. whats the best way to just import passenger services, i was using only trains with OR tag, but that failed, found a few trains with OPOR tags that became actual passenger services
4. should i just drag everything into the database and then filter that based on passenger service?

and i suppose referring to the database itself .... my original thought was to go for 2 databases, one for services, as i origin and dest, then another for calling points suing the train ID as a link between the 2 ... seemed like a decent idea at the time, but I'm not convinced ... again ... not a database designer, i know the basics of SQL to keep me moving but thats about it, would anyone be willing to share there database structure to help me out on that front, or point me in the right direction with this ?

cheers everyone

[Mike Flynn]

Hi Sandy,

 

See attached sql file of the schema I've created for the Network Rail timetable feed.  I'm no DB expert, more an 'all-rounder', so what you'll find is probably not best normalised and not necessarily the best way to go.  That said, there is no correct way to structure your schema.  A bit like building a house, you can go with any number of alternative designs.  Please note I'm uploading to give you ideas of how a working system might look rather than to provide any specific answers.  I won't have time for any detailed follow up answers.  (Please do ask if it's a one or two answer question though.)

I'd also say what you want to do, you're probably aware, is no mean feat.  My previous analogy, building a house, comes back to mind.  It's a lot of data.  And originally designed using flat files, maybe before even databases had been invented (he, just a joke)!  Depending on your objectives, this will also have a bearing on your approach.  If you need any kind of speedy output you will need to consider extra index tables, as I have done.  I also wonder whether a Rasperberry pi could cope, again depending on what your application is, though I have no actual experience with the Pi.

I guess at some point Network Rail, or whichever authority is in charge at the time, will want to be rolling out an up-to-date 2.0 version.  Until then we are left to our devices.  

Mike

[petermount]

Just for comparison, my current timetable schema: https://github.com/peter-mount/opendata/blob/master/nrod/nrod-timetable/src/main/sql/timetable.sql

I say current as it needs some serious TLC so is going to get rewritten at some point.

Peter

[Sandy Forrest]

cheers guys, some interesting reading there ... bit more thought required into how i build my database i think :)

i guess the next question is what source are you guys using for the data, and how often do you update it ... daily  / weekly ??

[Peter Mount]

I use the cif feed importing the daily updates with occasionally running a full clear & import the full update.

On 17 May 2016 21:21, "Sandy Forrest" <s...@ndman.co.uk> wrote:

cheers guys, some interesting reading there ... bit more thought required into how i build my database i think :)

i guess the next question is what source are you guys using for the data, and how often do you update it ... daily  / weekly ??

--
You received this message because you are subscribed to the Google Groups "A gathering place for the Open Rail Data community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to openraildata-t...@googlegroups.com.
To post to this group, send email to openrail...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Mike Flynn]

I currently use the weekly CIF Network Rail Timetable Data feed from http://data.atoc.org/.  This is the weekly download.  I then manually run a batch job to repopulate the data into MySQL.  I use PHP for this task and the job run takes well over an hour to complete.  And this includes the filtering out of the non-passenger trains.

Like Peter I'm looking at a complete re-write sooner rather than later.  My current system has served me well for quite a few years but I created it before the daily files were available and before I had good access to VPS's.  My first version was designed, I can't explain why, without taking into consideration the 'association' data.  After including, it's now a bit of a patchwork.  My plan is to create a newly designed DB schema populate it weekly and update daily.  I'll use Java and aim to run cron jobs automating it completely.  

As a note, I'd like to say this is an interesting and challenging task.  The CIF design is an old one and getting it into a database in such a way that queries can be run quickly, I found quite tricky.  For example, had I not filtered out freight traffic my current system would simply be too slow.  It was interesting to see someone else's, Peter's, design and I immediately see there are quite a few more tables used.  I also see he has used table and variable names that are understandable!

Good luck with your task, Sandy.  Can I ask, what's the application?  I'll be interested to know if you get a working system.