|
A Week in The Life of the PocketGPSWorld.com Database Admin |
1st April 2010 |
|
Article by Matthew Morley (MaFt)
Sorry everyone, but none of this is true... It is, in fact, an April Fool...!
Quick Menu: Introduction | Processing the Submissions | Heading/Directional Data | File Creation | How Many New Cameras? | Upload & Release | New Lifers | Any Questions?
Tip: click on any of the images for a larger version!
Introduction
Practically every Wednesday we update our Speed Camera database; but what happens in between these updates? Do I simply galavant around wasting your subscription money on fast cars and cheap women like a true Yorkshireman? Do I 'eckers like (I have a battered old Rover 25 for one thing...)! Following on from DennisN's 'A Day in The Life of a PocketGPSWorld.com Verifier' from back in November 2007 here is a rare insight into one of my average weeks maintaining and releasing the Speed Camera Database.
I'd like to point out that it's nothing glorious. I love the job I do, but not the environment. You see, I live out in the sticks of Bradford where 'internet' is where a fish goes after catching it – as such, my daily commute is to the outer edge of the City of Bradford to the nearest McDonalds (POI reference: 154633 in case you're interested) to make use of their free WiFi and free coffee offer. They are open 24 hours, 7 days a week, which is very useful for those late nights that are required when, for example, a load of new speed cameras have been submitted for new roadworks. A couple of times a week I get a free coffee from collecting the little stickers on the cups too so it's not all that bad. Unfortunately the only power supply in the whole 'restaurant' is in the children's party area – so, here it is in all it's glory, the PocketGPSWorld.com Database Admin HQ:
Processing the Submissions
You get the database release on a Wednesday and the new Lifetime Members are announced on a Thursday so my story will begin shortly after that. On a Thursday afternoon I start to process your speed camera submissions. You submit around 800 per day on average but it can range from 500 right up to 5,000 on the days after our iPhone app, CamerAlert, was released!
The submissions are received through a dedicated email account with the details of the submission.
In the example above we have a 'seen again' report for MOBILE:75575 so now I open up my main spreadsheet files (I have to use the free OpenOffice as there are no other office suites for Mac), scroll down to find ID 75575 and simply add the date it was last seen along with the name of the user who reported it (see below). Every once in a while I go through these dates and remove any Mobile sites that have not been reported as seen again for some time. Due to the amount of time it takes to look through the list I do not always get around to purging ALL the old mobile sites every time; this is why some older sites still remain after a purge.
The other submissions are not quite as simple. When a new camera site is submitted the first check I have to make is that it is not already in the database! Surprisingly we do actually get a lot of submissions for existing cameras. The initial check involves sorting the database by latitude and simply looking for matching co-ordinates. If none are found then I head over to Google maps, copy and paste the co-ordinates from the submission email, locate them and then check to see if it is on a road. If it looks feasible then I check the details in our verifiers file. This is a sub-set of the main database files that just contain the pending submissions – if there are a few similar submissions in the verifiers files (refer back to DennisN's article on how the verifiers work with these files) then I treat this as gospel and the details are copied and pasted back into the main database files. As you may be aware, it is often said that if the cross-hairs icon shows on the map then there is a high chance that you were the first to submit that location - the truth is, it means you definately were 100% the first submitter BUT you may not actually get the free subscription. Why not? Well, there is no definitive method to camera placement; if we have multiple submissions then, quite often, I simply look for a submission from a Lifetme Member and mark that as the active one - it saves me money (see later). If there are no submissions from Lifetime Members then it's a case of working out a rough average of the submitted locations and finding the nearest submission to that.
Removals and change of speed, location or type are handled in a similar way – i.e. I check in the verifiers files for any similar submissions that would confirm the change and then update the co-ordinates in the main file as shown below.
On average I spend around 7 hours every day processing speed camera submissions (although I usually limit myself to 5 hours on a Saturday and 3 hours on a Sunday) but depending how many submissions there have been this could easily jump up to 8 or 9 hours. There have been days where I've finished processing the submissions and left McDonalds at around 3am (as anyone who has me as a contact on their IM will be able to confirm). Thursdays are obviously busier too as I have the submissions from Wednesday to process as well – these are put to one side while the release versions are created. On top of this there are the support tickets that need to be dealt with as well as all the forum queries! Hopefully you will start to see now why we get frustrated when people reply to the newsletter email address – there simply isn't the time for one more job! I'm also going to use this as my excuse for my lack of capitalisation in forum posts! This article is different as I'm composing it in OpenOffice which does all that for me.
Heading/Directional Data
So, onto 'Database Wednesday' or, rather, 'Database Preparation Tuesday' as the work begins on a Tuesday evening, usually around 4pm. The first task is to add the heading value as text – this needs doing each week as quite often we receive submissions updating the heading values. We also have a rather keen verifier, M8TJT [remove name from final version?], who goes through the database every week, matches the locations on the map, and emails me his corrections – EVERY WEEK! Sometimes, due to being so overloaded with submissions, I forget (or don't get time) to process his corrections. One of the more depressing parts of my job is having to receive the offensive emails from this verifier. I won't name him publicly but here is an example email I received a few weeks ago:
Going through all the cameras and adding the direction (see images below) takes around 5 or 6 hours to complete – hence why I start on a Tuesday evening!
File Creation
I then need to separate the main database file into the individual types and speeds for the different database types we offer – 'single file', 'consolidated by type' and 'speed zoned'. This is quite a laborious task; 'single file' is easy - I just press 'Save'! The 'consolidated' files takes a while longer as I need to order the cameras by type first – previously I could simply 'sort' by camera ID in the spreadsheet but now we have the directional text it messes up the sort order; a lot of manual work is required. Once they are split down into the 'consolidated' files I then break each of these down into speeds. Again, this requires a lot of manual work but in general I can usually export the file as a CSV, open it in TextWrangler (Mac text-editing software) and find/replace all the colons with a comma. Then, when I open the CSV back up in OpenOffice all the speed values are in their own column. I can then sort the cameras by speed and, after using the 'concatenate' function, export them to their own 'speed zoned' CSV files. Sounds complicated, doesn't it? Well, it is (another excuse for any errors that creep in...)
Next up is the file conversion process. As we are all Mac users here at PocketGPSWorld.com there is no possible way for us to use Windows software such as POIEdit to convert our data to all the different formats we offer. As such we rely heavily on sites such as POI Editor and the GPS-Data-Team. While this isn't 100% ideal it is the fastest and easiest way to do these conversions on a Mac.
How Many New Cameras?
Once all the files are converted then I'm almost ready to release the database. By this time I've usually had about 3 hours sleep since Tuesday and it's Midday Wednesday. The next step is to estimate the number of changes from the previous database – there's no hard and fast rules other than comparing the 2 CSV files and take an educated guess. Here's an exclusive insight for you though: sometimes we just simply make it up! For example, back in February when I was at MWC we didn't release a database. Our iPhone app, CamerAlert, was also released that week and so after a few all nighters there simply wasn't time to compare the previous release – I simply guessed based on the fact we had extra submissions as it was a 2 week release and the extra work from CamerAlert. Chances are there were a lot of submissions by a lot of users. Nobody complained, so, the chances are that I was about right. Slack? Maybe, but it gets the job done.
Upload & Release
OK, I've processed the submissions, added the direction data, created the different formats and taken a stab at how many changes there are for this weeks database. Next I need to actually upload the files to the server. There are no FTP programs for the Mac so I rely on the amazing FireFox add-on FireFTP to upload the database files. This can take anywhere from half an hour to two hours depending on what the McDonalds WiFi connection is like and if it cuts off partway through.
Another limitation of the Mac operating system over Windows is the lack of HTML (web page) editing software – if only Microsoft would port FrontPage Express over to the Mac! The only option is to use a plain old text editor, TextWrangler, to manually edit the pages related to the database release and then upload these to the server. At that point the database is released and up to date! Woo hoo! Now go and download it! Make my week worthwhile (please!)
New Lifers
It's not over yet though. There's the small issue of our Lifetime Subscriptions offer. As you may well already know, these are given out to friends and family of the staff and verifiers who were the first to submit a new camera. If the number of new cameras has been properly worked out (as opposed to the MWC situation mentioned earlier) then I can find the usernames of those who qualify, check their subscription in our PayPal account and extend it if necessary. I try to keep the number of new 'Lifers' to a minimum though because for each one that gets awarded I lose £19.99 from that weeks pay. It could be worse though as Rob (perhaps that is short for 'Robbery'?!) could charge me the full £399.80 value of a 20 year subscription, so I thank the team for their generosity and for leaving eight of my fingers unbroken - just enough to use a computer adequtely. I mentioned earlier that at busy times I don't always get chance to accurately guess the new cameras, so how do I get around this for the new Lifetime Subscribers? In the words of a talking meerkat: 'Simples'. I scan through the list for three or four new cameras and simply add those usernames to the list of last weeks qualifying users so that the list looks full. It works a treat, you guys have fallen for it for years! If someone then complains that they qualified but were not awarded then I will just add them anyway – it's far easier than the effort involved in checking properly.
Once the 'New Lifers' are out of the way the whole process continues starting with catching up on all the submissions that were sent in whilst messing about with this foolish, archaic excuse for a database administration system.
Any Questions?
So, there you have it – a week in the life of the PocketGPSWorld.com database admin. Hopefully it has given you a valuable insight into the work involved in keeping your database up to date. It's not an easy task but it's one that I'd never change. Mainly because I've been doing it every day for over three years and I now find it hard to get out and socialise to any degree; I also fear any public area lacking in free WiFi and unhealthy burgers.
If you still have any questions about the database admin then please do ask in the forum thread where I will treat them with the utter contempt that they deserve.
| Comments
|
 Posted by yeramihi on Thu Apr 01, 2010 3:25 am |
 |
Lol... respect man.... And I was going to send you a moaning email about my application for verifier (from last newsletter) ;)
BTW: Interesting time to share your insights about it :D Got a deal with this McDonald's store or something? :P
|
|
| Posted by sn00p on Thu Apr 01, 2010 6:19 am |
|
Wow. You're seriously doing that all by hand? That would drive me mental, well, at least to the point that I wrote a piece of software to help automate the process.
|
|
| Posted by The Rudd on Thu Apr 01, 2010 6:45 am |
|
Maft
You deserve a medal for the work that goes into a weekly update (and the admins as well) I never though that so much time and effort was used to do the speed cameras.
When you just upload your weekly update you take it for granted not knowing what's involved - now I do and I look on warning Gatso slow down with fresh eyes, £19.99 isn't a huge amount to pay for a year
It would be interesting to see if the other speed cams companies did the same amount of detail in doing this. though I don't think so.
Jezza aka Jerry
|
|
| Posted by DennisN on Thu Apr 01, 2010 7:03 am |
|
Disappointing article - I used to look up to and admire you - I thought you just took our money and swanned around in fast cars, drinking pink champagne and stuff with a girl on each arm, when in reality you're just that hard working guy sitting in the corner at McD's.  Do you ever meet up with the TomTom, Garmin et al database guys for a quick McShake?
Dennis
TT Mapshare NEVER.
Phone 08451610009 - A) issue, B) challenge, C) problem, D) cockup - fastest finger first, no friends, 50/50 nor audience |
|
| Posted by SSR on Thu Apr 01, 2010 8:24 am |
|
How do I become a friend or family member of PGW staff?
And will you get more fingers broken for this typo? "I mentioned earlier that at busy times I don't always get chance to accurately guess the new cameras, so how do I get around this for the new Lifetime Subscribers?"
|
|
| Posted by MaFt on Thu Apr 01, 2010 11:11 am |
|
Well, It's now 12 noon and I suppose I should let you all into a little secret... Only a handful of you figured it out, even DennisN and Anita were caught out too!
The whole article is, in fact, an APRIL FOOL!
Not one bit of it is true - there are a small number of little truths in there (I do a couple of hours of my work in McDonalds 1 or 2 days a week while my daughter is at the pre-school over the road) but the rest is a load of tosh. I have 10mb cable broadband at home - even out in the sticks!
Your submissions get sent directly to the Submissions Database and these are then processed via a very technical admin set up that shows all nearby submissions on a map as well as loads of details to help verify them. We don't get anywhere near 800 submissions per day either! I also spend a more realistic 2-3 hours each day on processing submissions. In general I have the weekend off too. Oh, and MikeB's submission is a Photoshop job!
M8TJT does, in fact, email me every week with a load of heading / direction updates however the email in the article is completely fabricated. I do occasionally forget to process his heading updates but he's never sent such a rude email!
We've mentioned countless times about how the database files themselves are generated 'on-the-fly' and 'tagged' with a traceable code (for anti-piracy) - i.e. I don't do any of the manual editing in a spreadsheet file. None. There IS no spreadsheet file! I.e. we DO NOT use 3rd party sites to create the different formats.
Because everything is stored on a central database and we store a lot of information we have set up a system that generates a list of the first submitters for qualifying cameras. We do not pick and choose according to subscription status as suggested in the spoof article. I wouldn't dream of guessing the number of changes each week, or the users who qualify for a lifetime subscription. We give the free subscription to all who qualify - we DO NOT pick and choose family and friends! If we did then we wouldn't be as quick to publish the names of all who qualify every single week.
All those silly things about there being no software for Mac's is all sheer nonsense too! I run Windows on my Mac for some custom software that we have as well as using DreamWeaver for web stuff. Although I do actually use OpenOffice there are Office Suites available from Apple and others.
And, finally, I don't get 'billed' for the new lifer's subs money and Rob is, in fact, short for 'Robot' not 'Robbery' as we work among cyborgs. Oh, wait, it's after midday isn't it...
Oh well, I hope you enjoyed it 
MaFt
This is not the smallest writing in the world. This is just a tribute. |
|
| Posted by MaFt on Thu Apr 01, 2010 11:14 am |
|
and for a bit more fun, here's a couple of PM's i received today:
| Quote: | From: Jellyroll
To: MaFt
Posted: Thu Apr 01, 2010 8:44 am
Subject: MacDonalds and Working Too Hard Quote message
It pains me to see all that effort.
Perhaps 90% of it is needed, but there are things that can be done with spreadsheets, even OpenOffice.org ones, to simplify the sorting and camera name changes, for example, and there are more programs on (and available for) yer average Mac than you suspect, so FireFTP is only one option. As for web page design - well, FrontPage is a heap of pimply shite that produces bloated garbage. There are other solutions than text editors (although Real Men make web pages in VI or EMACS, naturally {hehe}).
While I make it a general rule never to go into a MacDonald - certainly never to eat in one - I'd not be averse to crossing the Pennines and looking over your shoulder one day to see if there's anything I can do to speed up, simplify or automate some of the processes for you. It's what I do. |
| Quote: | From: BigPerk
To: MaFt
Posted: Thu Apr 01, 2010 11:27 am
Subject: A Day in the Life ... Quote message
Hi MaFt - congratulations on all that work (Rob doesn't really break your fingers, does he? You would have to work even longer hours! )
I was just wondering, though, if you meant to leave M8TJT's name in (unless it's tongue-in-cheek - it doesn't quite look like it)? If not you might have time to remove it while he's in the garden.
Also, with the spreadsheet, I presume there is a reason you can't have the directional info in a separate field, so you could still sort on type, as you used to. I would have thought it needn't mess up the file format, but maybe it does? |
This is not the smallest writing in the world. This is just a tribute. |
|
| Posted by DennisN on Thu Apr 01, 2010 11:43 am |
|
| MaFt Wrote: | and for a bit more fun, here's a couple of PM's i received today:
[SNIP] |
Naughty boy - shouldn't have left the poor souls' names in!!
Anyway, to get my own back, here's a quote from Earlier today.......
| maft Wrote: | | DennisN Wrote: |  I think Moderators should get advance warning of spoofs so that we don't get egg on our faces from stuff like this - I believed it at first!! |
Tough - that's what you get for being a Northerner living Down South! |
Dennis
TT Mapshare NEVER.
Phone 08451610009 - A) issue, B) challenge, C) problem, D) cockup - fastest finger first, no friends, 50/50 nor audience |
|
|
| Click here to view more comments... |
|
