Procedure for entering new locos in database
Entry posted by D1001
548 views
I have spent some time entering locomotives into BRDatabase.info and I thought I would share with you the reasoning and procedure, so you can see what goes on in the background.
I was entering data for 1949 allocations and withdrawals etc from a copy of the Stephenson Locomotive Society journal. I came across an engine that didn't appear in the database - the reason being that I had it's proposed BR number but not it's LMS (ex L&Y) one, and it was withdrawn still carrying the LMS one. So, before I went any further I decided to look it up in my Baxter 3B - it turned out to be an ex L&Y Class 25 0-6-0, ordered by Aspinall to a design by Barton Wright. I had 126 of these locos in the database (including those that were converted to saddle tanks - 0-6-0ST, L&Y Class 23) but 280 were built, 240 of which ended up as tanks.
My rule regarding which engines to include and which to exclude from the database is simple: if any loco from a class was in service on 1st January 1923, include the *whole* class. Therefore all 280 locos had to appear!
So, I had to insert 154 new locos and update the remaining 126. I like to have contiguous serial numbers for a class of loco and also, gradually, I am renumbering each class (internally) so that the parent company and wheel arrangement can be derived - the Class 25 had an internal serial number 401 which I have changed to 406003 (4 for ex LMS, 060 for wheel arrangement and 03 for unique occurence of ex-LMS 0-6-0's), and class 23 was 399, changed to 406052 (the 5 indicating a tank loco). Complicated? It gets worse.
Then, I scanned in all of the data from Baxter for the 9 different lots of Class 25 0-6-0's from 4 or 5 different manufacturers. Parse errors are normal and alot of correcting, formatting and aligning (using a text editor and excel) is necessary. I then added a unique locomotive id (internal for the database) - the new format is 9 digits, the first corresponding to the class id as built and the remaining 3 being unique to the loco, so the first built (L&Y 528) became 406003001 and the last engine (L&Y 977) became 406003280. However, 126 engines already had serial numbers of the old format (6 digits, sequenced in the order in which I originally built the database). So, I loaded the spreadsheet into a temporary database table using phpmyadmin, added an old loco id field and populated that by matching the Baxter British Railways numbers with those I had already on the database. I have a database 'stored routine' to renumber the id of a loco so I had to run that 126 times against the locos already present to give them their new id numbers. This involves changing the id in the main table and the rebuilds/allocations/mods/numbers/sightings/works_visits etc... tables too).
Finally, I was able to insert and update data based on the temporary table of Baxter data. This includes entries in a rebuild table which changes the class id. This is for the 240 saddle tank rebuilds. Once all this is done, I run an update script (most of the tables are base tables and the update script builds tables that are eventually presented on the website - this was done in order to avoid repetition - for instance the main table and number table have dates in them - if the build date and the first number date were different, things went wrong, so now the number table uses the date from the main table for the first_number flag being set to Yes.)
One last job - my website uses a cache system - by that, I mean if a query has already been run, don't run it again - look in the cache directory and use that html page. However, the cache page needs to be removed when you change data otherwise the changes don't show up.
Job done? Not quite - I can now happily view the data for the class 23 and class 25 locomotives of the L&Y on my home computer, but, as of yet, you can't - at some time in the near(ish) future, I need to do a major upgrade and load these changes, and all the others I have done since March to the live server.
So, that has taken me about 6 or 7 hours to execute - including research, scanning, parsing, correcting and loading data, just for one class of loco that most people will never look at! Is it worth it? I think so, and one day, I will have every locomotive that ran on Britain's public standard gauge rail network on the database. So, you can see the scope for errors to creep in - that's why I need eagle eyed observers and visitors to spot and report things.
All the best for now.
-
1
2 Comments
Recommended Comments
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now