Thursday, 16 October 2008

OGRBB updated

OGRBB is our rather clumsy acronym for the Online Guide to Rarer British Birds, and it's one of the unsung highlights of our Bird News Extra service. It's a database containing all accepted reports of past rarities in Britain and Ireland, since the year dot. It complements our other bird news services in that it contains only accepted records, and is compiled retrospectively when the dust has settled and the "ten rare men" (and their Irish equivalents) have made their deliberations. Anyway I've spent much of this week updating OGRBB to include the records from the latest British Birds Rarity Report so we're up to date to the end of 2007, for the UK at least.

I have to say this isn't my favourite job, for lots of reasons. Firstly, it's one of those tricky jobs that you only do once or twice a year, so it's a case of remembering/relearning how to do it every time. Secondly, it's horribly complicated. To save me having to reinvent the wheel, I did write myself a set of instructions a couple of iterations ago - which now runs to over 100 steps, some of them individually quite time-consuming. But something always seems to change each year to throw a new spanner in the works, so it's never an entirely smooth or familiar process. This is one reason why I haven't done it for two years, so we had got a bit behind.

Our collaborator Keith Naylor has made it his life's work to compile and maintain the underlying database of reports, spending hours poring over BB reports, checking references and consulting obscure journals for details of long-lost rarities. However, Keith works (for lots of very good reasons) in Microsoft Access offline, whereas our online database uses SQL Server. Both are Microsoft database products, so you'd think it would be pretty easy to transfer data between them, wouldn't you? Sadly not. For example, Access stores dates differently from SQL Server, and the latter can't cope with dates (at least in the data type we use) that are earlier than January 1st 1753 - and a few of the records in the database are older than that, so there's a bit of fiddling about to do there. The earliest record in the database, by the way, concerns a pair of White Storks that nested on Edinburgh Cathedral in 1416. So it's a bit of a chore to get the new data successfully into SQL Server, even before I start working on it. And then I have a list of integrity checks to make sure the species codes Keith uses match ours - and they never do - and so on. "Splits" are a particular nuisance here, and there have been quite a few in the last couple of years, as well as a bunch of tweaks to scientific names: fuscata becoming fuscatus and the like.

The service provides online lists of records, but also maps and graphs them. So the next chore is to produce all the necessary graphics. (Fiona said gaily yesterday "oh, I thought those were compiled on the fly" - I wish, Fi.) For the maps, we use a 3rd-party mapping program called DMAP, which is a handly little program written specifically for mapping wildlife data and saves me a lot of messing about with grid references and base maps - but its user interface is a little, shall we say, "individual", so it's not exactly a pleasure to use.

The other graphs and charts, showing records by year, month, sex and age for each species, are then produced using our own little application, written by John a few years ago in Visual Basic. Unfortunately, it uses a 3rd-party graphing API which, it seems, can only churn out 32 graphics at a time - and I've got to produce four different graphs for each of 290+ species we cover. So that takes a while, as I have to run the program 30 or 40 times. Oh, and it produces BMP (bitmap) files, which are too big for web use, so they have to be converted to more web-friendly GIFs. For that I use IrfanView, a handy bit of shareware, but which also has its quirks.

And then I have to upload all the maps and charts to the web server - 19MB of data at the last count, update a few introductory pages, and lastly transfer the new data from the local SQL Server database to the live one (at which point I cross my fingers and hope it all works).

Anyway - the job has had me tearing my hair out more than once, but it's done now...except I seemed to have cocked up the colours on the maps, so I'm going to have to compile, process and upload those again. Oh, and we're expecting a new database with 2005 and 2006's Irish reports in a few weeks, so I'll have to do it all again.

So...I hope you enjoy it and I hope this insight into the work required to compile it is interesting. We've made Arctic Warbler available as a demonstration: see

No comments: