Vehicle Database

[E400]

RE: Vehicle Database

(21/06/2015 15:49)B10B6514 Wrote:  I feel your idea is excellent but I would not include sightings unless they were from a different depot or operator as you could end up being inundated with eg. 7653 on route 10,43,437 or whatever 20 times a day. But say 7653 working out of Preston instead of Birkenhead would be of use.

I was thinking more along the lines of if you wanted to list a bus as being on a particular route then you select that route and it gets logged along with the time you saw it and the bus stop. Routes could be imported from an online service called TransportAPI and so the database could forecast where that vehicle would be at particular times, incase somebody wanted to flag it down for photos if it were an unusual allocation, or for whatever reason.

The sightings would only be displayed on the bus' own page under a 'recent sightings' section - if it were sighted at another depot then that could be listed as a depot realocation either on a temporary or permanent basis.

In theory, using this TransportAPI software, you could show the locations of buses at any given time. So, say I saw 1234 on the 464 at Town Lane at around 14:30 on Saturday, I put in route 464 14:30 on Saturday and it shows me a route map with the supposed locations of each vehicle allocation for that time and day.

If the 464 has 3 vehicles, with one in Liverpool, one near Town Lane and one in Shorefields at 14:30, then the one near Town Lane is undoubtedly the one you sighted, so you'd select it.

If the bus has not been 'sighted', it could show as a 'ghost' - ie: unknown vehicle - but when you click on the ghost and enter 1234 (for arguments sake, a Gemini Hybrid) it then maps out for the rest of that day that particular vehicle as a Gemini Hybrid, unless somebody lists otherwise, in which case they may include details of a vehicle swap due to breakdown. That's more what I envisaged the sightings to be.

[E400]

RE: Vehicle Database

(21/06/2015 16:28)Mayneway Wrote:  I certainly think it's a good idea but a mammoth task getting it started.

I've had a think about this and using the fleetlists available, I may be able to mass-import a bunch of 'base' data for vehicles, depots, liveries and operators, which will give us a point to work from. Over time a community effort would fill in the gaps and build up the bigger picture. After some time, it will just be a case of maintaining new information rather than including old information.

[SK15 GZG (Moderator)]

RE: Vehicle Database
I think it would be a good idea. I will be happy to assist with the fleetlists that I presently maintain on the main website

[H101GEV (Administrator)]

RE: Vehicle Database
Just to add, I'd be happy to help with this if necessary. I have a more updated version of the Arriva NW and Wales fleet lists to upload now that I've rescued it from my old laptop, so you'd be welcome to use those once they are online.

[E400]

RE: Vehicle Database

(21/06/2015 21:45)507009 Wrote:  Just to add, I'd be happy to help with this if necessary. I have a more updated version of the Arriva NW and Wales fleet lists to upload now that I've rescued it from my old laptop, so you'd be welcome to use those once they are online.

Excellent, thanks - at the moment I've not started on anything other than planning so its a bit of a way off any actual system yet, but any help once it is going would be very much appreciated

[Quackdave]

RE: Vehicle Database
I'd actually been thinking of doing something very similar myself! I got myself a web hosting account with MySQL and PHP a few months ago, with exactly that in mind, but haven't got round to doing anything with it yet, except putting a few static fleet lists in place. So, I'd certainly be interested in helping with the programming - as long as it's to be a case of starting from a clean slate rather than working with any sort of pre-written software. I've got a good grounding in database and UI design, SQL, JavaScript and regular expressions from my day job in an IT department; I'm fairly new to PHP but I imagine I'll soon get into it.

Some valid points have been made regarding the differing opinions of those who may edit it. For example, whilst I would agree with Dentonian that things shouldn't be changed based on advance rumours until they have actually happened, I disagree on the other two points : I am far more interested in whether a bus is actually in use, and if so, what depot it is physically operating from, than its status and/or allocation "on paper". A vehicle removed from a list can always be put back in should it be returned to use. (Think Cumfybus, Lewis of Maenclochog, or South Notts of Gotham as examples where a list of vehicles owned is/was far from indicative of the current fleet.) Of course, neither of us is "right" or "wrong" here, it's a case of personal preference, and, whereas a static fleet list must decide upon one approach or the other, a good database can and should enable data to be held in such a way that both viewpoints are supported, and users should be able to choose what statuses to include in a list, and whether to show official or actual allocations, or both.

The idea about allowing moderators to validate entries can be made to work - I believe something similar is in place on the German Wikipedia. Readers can opt to view the latest version, which may include dodgy edits; or the most recent approved version, which has been reviewed by a trusted user. It's not universally popular, as a reviewing backlog can mean that blockages occur when an editor needs to make a change that builds upon one that has not yet been reviewed. The alternative, as used on the English Wikipedia, is to commit all changes immediately but then allow them to be rolled back. This isn't without its drawbacks (good editing can still be at risk of being lost if it's been placed on top of bad and the reverter fails to take it into account) but may be slightly easier to implement.

It might be nice to implement the ability for the system to communicate with other databases, the main ones that spring to mind being DVLA and Bus Lists On The Web.

A final feature suggestion would be something to minimise the hassle caused by marques being changed. For examples, when I'm searching for Darts, I have to remember to select Dennis, TransBus and Alexander Dennis; and when I'm sorting by chassis type, I don't want DAF SB120s at the top of the list and VDL SB120s at the end, with Dennises and Scanias in between. I haven't entirely got it clear in my mind yet how this would work, but I do mean to put more thought into it!

[E400]

RE: Vehicle Database
Great to hear that you're thinking along the same lines Quackdave! I'd be happy to share the development with you if you were interested.

I've started on the databases but I've been busy with work so haven't really got very far at all.

What I was thinking was having a Vehicles table containing the reg, chassis, body work, decks etc, with a VehicleID field to use as an identifier. I've then got another table containing the VehicleID and fields for operator, depot, fleet number, livery, seats, doors etc. This means that the vehicle data once in the database will only change if there is a registration change, but both the old and the new would be listed alongside the unique VehicleID. This way, I can store in the vehicle details table the person who made the edit and the person who approved it, so we have a running history of accountable changes for each vehicle. So every time the vehicle changes depot or is sold to another operator, the vehicle details table gets a new entry, leaving historical entries intact.

I like your idea of viewing the latest edits or latest approved data - this would be easy to introduce if all data is accountable to users and moderators, as described above, and I'd quite like that functionality myself now you've mentioned it.

As I say I'm busy right now, and I'm away from home sporadically for the next couple of weeks, so I'll really get down to starting work shortly and hopefully have a decent prototype in not too long. A couple of users have already expressed interest in testing it, so fingers crossed it can be made to work.

[Quackdave]

RE: Vehicle Database
I have put together a script that parses a string describing a batch of registrations, and converts it into a list of individual vehicles. So, you can enter something like MX61 AUT/U/YN-P/S-V/C-M/XR-YB/XD/F-K/M-P/KY/Z/LO/U/MO, or WLT 301-999.

Depending on how big batches are, and how neatly the registrations are arranged, something like this has the potential to speed up data input quite significantly, as any information common to the whole batch could be entered at the same time.

The tool is here. (Note: this is not very well tested as yet, so I can't promise that you won't end up in an infinite loop that crashes your browser!)

On the minus side, I tried to do something with my suggestions for automatically querying DVLA or Bus Lists On The Web, and they seem doomed to fail, since neither server appears to have CORS enabled, nor to accept HTTP GET requests. So, for now at least, it looks like if we want fields like taxation status, they will have to be looked up and entered manually.

Thinking back to the actual database, I'd been visualising a somewhat more severely normalised structure. I don't have particularly strong views on this, but one thing I definitely would suggest is putting operators in a separate table(s) linked by a key. As well as making it easier to ensure consistency, it would enable us to easily store additional information about operators and depots. I'd go for a hierarchy of at least three levels : operator as the middle one (or division, if it's a major group), with group above and depot below.

I'd also consider putting registrations in a separate table, because then the data in the vehicles table really would be static (assuming that a rebody or rebuild is considered a new vehicle). But maybe that's not such a major consideration.

I may try out a few more ideas soon, I'll let you know what I come up with.