Sunlight

Some time ago we installed “solar thermal” on our roof, as described in: “The Dorothy Hopkinson Memorial Solar Panel”. This heats water, it’s significantly reduced our gas usage over the summer when the central heating is not on but during the winter when most of our gas is consumed the effect is minimal.

Next step on the renewable energy list is solar photovoltaic (PV) – this converts light to electricity directly. Solar PV systems are rather more expensive than solar thermal systems, until recently the economics of this were a bit questionable - £10,000 is a lot to spend to reduce your electricity consumption by half. However, the feed-in tariff scheme was introduced in the UK in April 2010. This scheme pays a generous tariff based on the total amount of renewable electricity generated by an installation in an effort to increase the uptake of such systems. A similar scheme has been in place in Germany since 2000, where it has been pretty successful in reaching this goal.

We have a medium-sized 3 bed semi-detached house with East-West facing roof elements which are relatively small. Two of us live in the house, our annual electricity consumption is typically 6.5 kWh  per day or 2400kWh per year (*see below for a comment on units). When we first moved into the house our consumption was around 11kWh per day (4000kWh per year). We reduced this by replacing all our light bulbs with low energy versions; switching off a second fridge/freezer; almost entirely stopping using a tumble drier and on replacing fridges and washing machines we bought the lowest energy versions we could find. So these measure achieved roughly 40% reduction. I know all this because I’ve been recording our electricity and gas consumption once a week for the last 3 years!

A colleague at work has recently installed both solar thermal and solar PV, and he gave a talk describing his experiences. After some delay we arranged for a survey by The Green Electrician, who were the installers recommended locally by Segen. The surveyor did a few measurements of our roof and then went through sample power generation calculations and costings based on those measurements. The calculation is described in “The Government’s Standard Assessment Procedure for Energy Rating of Dwellings” (SAP 2005 page 69). Beforehand I was under the impression that this wasn’t going to be worthwhile since only our East-facing roof is available for installation and this is always described as ‘non-ideal’. However, the calculations show that the penalty for an East-facing roof compared to a south-facing one is only about 15%. Unlike solar thermal, direct sunlight is not required – useful energy is still generated on a cloudy day or when the panel is in shadow.

Slight diversion: we were original quoted for an 8 panel system, however once on the roof the installers found they could fit two more panels. I’ve redone the calculations to allow for this – they may be a little bit inaccurate but not much.

The system recommended was a 1.85kWp Suntech system (ten 1580 x 808mm panels = 12.64m²) limited by the size of our roof. The calculated output of this system is 1393kWh per year i.e. about 60% of our annual usage. The price quoted for this system is ~£8,600 (excluding cost of scaffolding which was just over £400). The feed-in tariff is £575 per year based on 1393kwH per year production at a price of 41.3p per kWh. The savings on our electricity bill should be £98 per year (based on 50% of generated electricity being used by us at 14p per Kwh) and £21 based on selling the other 50% back to the grid at 3p per kWh. The feed-in tariff is inflation linked, and it’s reasonable to assume that the buy and sell prices of electricity will go up in the future. As you can see the feed-in tariff is what makes this financially sensible. In theory we will be getting £694 back on the system every year, so it will pay for itself in 13 years or less, mainly due to the feed-in tariff. The tariff is paid for 25 years so at the end of 25 years we will have been “re-paid” at least £8350 more than we spent. This is an embarrassingly high “middle-class benefit”. There are companies who will install renewable systems, paying the upfront costs, providing significantly cheaper electricity and taking the tariff (see Guardian article here): in summary this could work for you but the companies aren’t doing you a favour.

Installation is pretty straightforward: we needed substantial scaffolding across the front of the house to provide roof access; there’s some electrical gear to go in the loft (this is a panel about 1m²) and there’s a cable run from there down to our consumer unit under the stairs where there is a further small meter the size of a central heating controller. In our house this is fairly straightforward: the cable runs up to the loft via ducting up the stairwell – we’ll bury this in the wall when we next decorate. Three chaps were working for a substantial fraction of two days but they also cleaned out my gutter and re-pointed the ridge-tiles. At this point I’d like to commend Ian, Danny and John: the installers, who did a fine job and were most polite.  

There’s still some paperwork for us to do, but essentially the power companies are obliged to pay the feed-in tariff and accept energy back from us onto the grid.

At the moment I’m going up and down the loft stairs to look at my power generation at 30 minute intervals! Overall I’m very pleased with the system: survey to installation was a day under 3 weeks and on a not particularly sunny day I’ve been generating 1000W since 10:30am and peaked at around 1500W. As of 3pm I’ve generated 4.4Kwh which is nearly 70% of our average daily usage. We expect to get less electricity from the system as we head into the shorter days of winter with a sun lower in the sky, but it’s not a bad start.

As an additional bonus we can now electrocute unsuspecting electricians in a sustainable fashion – unlike a normal house you need to switch off supply from the grid and from the solar panel before sticking your screwdriver anywhere electrical. There are big signs to this effect next to the consumer unit.

*Note on units: Power is the rate at which something consumes energy, and the units for this are Watts (W), 1000 Watts is known as 1kiloWatt (kW) – a kettle uses about 2kW when it is running, the PC I’m using about 300W and the electric shower about 9kW. Ultimately what you buy from the electricity company is an amount of energy. For domestic electricity consumption we talk about “kWh” or “kilowatt-hours” this is a power multiplied by a time which in physical terms is “energy” which physicists would normally quote in units of “Joules” however, we’re not in physics at the moment. The quoted output of our system is in “kWp” or “Kilowatt-hours (peak)” – this is the maximum power we could possibly obtain from the system.


Some pictures of the system, including a graph of gas and electricity consumption over the last three years.

View Full Album

More colours than the rainbow

This post is about making the bridge between how a physicist understands colour, and something a bit more useful.

Light is a collection of electromagnetic waves; for a physicist the most important property of a wave is its wavelength, its "size". The wavelengths of visible light fall roughly in the range 1/1000 of a millimetre to 1/2000 of a millimetre. (1/1000 of a millimetre is a micron). Blue light has a shorter wavelength than red light.

Things have colour either because they generate light or because of the way they interact with light that falls upon them. The light we see is made of many different wavelengths, the visible spectrum. Each wavelength has a colour, and the colour we perceive is a result of adding all of these colours together.

The diagram to the right summarises this: it’s called a chromaticity diagram, the numbers around the edge are wavelengths in nanometres (a millionths of a millimetre), pure, single wavelength light falls on this line; any point inside the line is formed from the mixture of wavelengths. The line represents "all the colours of the rainbow"; colours inside the line are not in the rainbow. The chromaticity diagram is the “periodic table” for colour scientists, it’s iconic and it summarises the world of colour.
This chromaticity diagram is just a slice through a volume, we could draw another one a little bit dimmer, and a little bit dimmer than that until we reached black.

How do we get to this diagram? The central issue to understanding perceived colour is that although the light in the environment comes as a mixture of a multitude of wavelengths, our eyes are limited by the light sensitive cells they contain, known as “cones”. In humans cones come in three types, which are sensitive in three different ranges of the spectrum. Roughly there are red-, blue- and green sensitive cones. So the eye gives just three readings in terms of colour description. The chromaticity diagram comes from a calculation trying to predict these three values and combining them to fit on a flat page (which only gives you two dimensions to play with).

Some other animals don’t have three sorts of cones. Birds, for example, have four – this is known as tetrachromacy which sounds to me like some sort of wizardry involving chairs (I’m reading Terry Pratchett at the moment). Birds have an extra type of cone in the ultra-violet part of the spectrum – so they are sensitive to wavelengths which we are not. Most mammals are dichromatic, but other primates, like humans, are also trichromatic. Dichromatic animals will be able to perceive a smaller range of colours than us. The evolutionary implication here is that earlier mammals lost some colour sensing ability, possibly for a gain in low light sensitivity but some mammals subsequently regained the ability.

The chromaticity diagram is still something of a physicists play-thing, it’s useful for doing calculations. There are other ways of describing colour which are related to human perception, they are developments based on the first steps used in constructing the chromaticity diagram. The aim of these methods is to similar numbers to colours that look similar; make those numbers reasonably easy to explain from a conceptual standpoint and try to give numbers twice as big to colours that are twice as bright. My favoured system in this respect is the CIE LAB system. The colours are expressed as three numbers L, a and b: L tells you something about overall brightness, a tells you the point on a scale between red and green and b tells you a point on the scale between yellow and blue.

But all of this is a bit of a fraud, because actually the colours you’re seeing on your monitor aren’t the real colours I’m trying to show you. The problem is that display devices contain red, green and blue elements but they don’t fall anywhere near the extremes of the chromaticity diagram and we can only get colours inside the the triangle defined by the red, green and blue elements in the monitor. A typical monitor gamut is shown here.

All this is based on the study of ideal colour stimuli (little square patches) on grey backgrounds, things get an awful lot more complicated if we start to worry about context. This is best illustrated with an image:

As far as my computer is concerned squares A and B have the same colour, my brain and your brain are interpreting the scene context and giving them different colours. This is called Adelson’s checker shadow illusion.

“Intimidating equations”

Taking lessons from Goldacregate, I’ve removed all the rant and sarcasm from this post.
In this article in today’s Observer, we’re advised that this:
P^A = gU_G + min(k - g, (1 - g)(1 - r))
is an “intimidating" equation”. Only if we’re easily scared! It relates the profit gained from dynamically priced airline tickets to some variables. This equation really is a very straightforward it says:
“profit equals two things multiplied together plus the smallest of two other things”
Using a Greek letter (capital pi) with a superscript following is a bit of showmanship, P would have done perfectly well in this instance. You can read the paper from which it is drawn here. It is written in the style of a paper in pure mathematics, which might explain the intimidation of the journalists in question.
I wrote a little bit about maths a while back: maths is the language of much of the science I do, but its a convenient tool – it’s not an end in itself. The seed of “Goldacregate” was a query by a journalist as to how to read out an equation, the thing is that practitioners rarely speak equations out loud: they scribble them on the nearest available surface (often illegibly, and incorrectly) or fight endless battles with machines to get them into electronic documents. Furthermore there is a long and dishonourable history of public relations companies using essentially meaningless equations to promote products and services.
For non-users of equations they are simply a cloak, a cloud of chaff thrown up to hide the truth beneath. For users, they are a compact and exact way of writing down the truth.
The next time you see an equation, don’t be scared beneath it there is something simple which can be said.
Unexpurgated version: Ah, bless, the economists are playing at being scientists by using an equation and the journalists have got the vapours at the impossible complexity of it all. Nasty equation: please, don’t hurt me.

100 days later: A Lib Dem view

I woke this morning to the sound of a bandwagon rolling past, grabbing my keyboard I jumped aboard. It is 100 days since the Coalition formed following the General Election. For people wedded to the decimal system of counting 100 is a nice round number, for programmers of a certain generation 128 is preferable. Perversely the Marquis de Sade chose 120 days, but I can't wait 20 days.

As a member of the Liberal Democrats for 20 years, I thought my opinions on a 100 days of partly Liberal Democrat government might be interesting to at least a few people. You can see my previous political postings here, to get a bit more context.

Things I'm pleased about:
Pupil premium; a rising lower tax threshold; increased capital gains tax; Ken Clarke sounding like a liberal on prison policy, an end to ID cards and over-enthusiastic lawmaking for every occasion; some hope of constitutional reform both in the Lords and for general elections;  no changes to the married tax allowance;

I'm also pleased by the very existence of a coalition government, it seems far more healthy to me that government is composed of members from two parties representing a majority of voters in the country, rather than one party who through a quirk of the electoral system has scraped in with a majority of seats based on a minority of votes. Far better coalition than more opposition where our influence is minimal.

I see my vote as delegating my views to the Liberal Democrats based on their manifesto, if they were in government alone I'd expect them to attempt to implement the entire manifesto (even if I didn't like all of it). In coalition I expect them to negotiate using that manifesto as a basis, the fact that the entire manifesto is not being implemented is a result of them not achieving an overall majority. The inability to implement the entire manifesto is a fact of electoral arithmetic.

Things I'm not so pleased about:
fatuous comparisons of civil servant pay with the Prime Ministers pay; ostentatious "dipping of hands in blood" following the Budget, at times it felt like the only people cabinet ministers defending it were Liberal Democrats; David Laws' rapid exit from government; Trident - I'm not particularly anti-nuclear but now was the perfect time to get shot of a piece of Cold War willy-waving.

As far as the economy is concerned, I believe we'd be in approximately the same place as we are now regardless of which party was in government prior to the election. The logic of this is also that regardless of who would have won the election they would have ended up doing approximately the same thing now (or in the near future): cutting government spending fairly dramatically. Arguments about timing are largely political; economics, it seems to me, is a "science" too imprecise to tell us much about the future and the fervent calls for cuts now, or cuts later are largely political. There is some marginal argument about the scale of the cuts, but given a Labour government we would be facing cuts of broadly the same magnitude.

I suspect there is a lot of departmental spinning going on at the moment: they've been asked to make fairly large cuts and they're leaking the ideas for cuts that they know will be politically the most unpalatable in order to give themselves some leverage for the spending review.

There's much enthusiasm about the LibDem's apparent problems in the polls, however they're generally at levels comparable with the last 10 years or so (see the Guardian Datablog). They are only low if you compare them to the heady heights of the election campaign which were quite evidently wildly inaccurate - the only accurate poll was the exit poll. I suspect a LibDem party in coalition with Labour would find itself in very much the same position.

It's worth highlighting again the inequity of first past the post system: plug the latest opinion poll into the BBC's calculator: (Lab:37% Con: 37% LibDem: 18%) and you get (Lab: 336 Con: 244 LibDem: 42). Labour get a 92 seat advantage over the Tories for an identical percentage of the vote and they get  8 times the number of seats as the Liberal Democrats for twice the vote. The Electoral Reform Society did a report for "Conservative Action for Electoral Reform", on this subject - interesting conclusion is that equalising constituency size doesn't really address the problem.

After the General Election the Liberal Democrats had three options: one it seems was unworkable, one was simply lazy, we chose to do the other thing. The only principle the Liberal Democrats have given up is the principle of not being a party of government.

Journalists unable to cope with the conditional?

A short rant on the newspapers today. Is there something in the style guides that says either something must happen or something is not happening? I take as an example, this piece in the Observer:

In its first few months in government, the coalition has delivered one major housing reform after another – from plans to cut down on "garden-grabbing" to crackdowns on housing benefit and the unexpected announcement by the prime minister that council tenants would no longer be guaranteed a right to lifetime occupancy.

This (emphasised) statement is simply not correct. Or it’s only correct if you believe it’s an accurate reflection of this reply the prime minister made to a question at a PM Direct event:

At the moment we have a system very much where, if you get a council house or an affordable house, it is yours forever and in some cases people actually hand them down to their children. And actually it ought to be about need. Your need has got greater ... and yet there isn't really the opportunity to move."

"There is a question mark about whether, in future, should we be asking, actually, when you are given a council home, is it for a fixed period, because maybe in five or 10 years you will be doing a different job and be better paid and you won't need that home, you will be able to go into the private sector....

"So I think a more flexible system - that not everyone will support and will lead to quite a big argument... looking at a more flexible system, I think makes sense.

I’m a simple scientist not trained in the intricacies of the English language (particularly the apostrophe), but even I can tell the difference between asking a question and making a definite statement of policy. It seems important to me that events should be reported accurately and not simply re-worded to suit your prejudices. The article I quoted here is actually quite good, and interesting, but given this example of a deviation between what was said and what was written, how can I trust the rest of it?

*Preparation for this blog post hindered by @HappyMouffetard’s Tourette’s Syndrome breaking out whenever she hears the voice of David Cameron.

A piece of land

I am the under-gardener to The Inelegant Gardener, more specifically I am the under-allotmenteer. For those outside the UK, allotments are standard sized vegetable gardens enshrined in UK law. We took possession of our first half allotment plot on 1st October 2006.  I remember our visit to the colony to see the allotment; we’d taken our traditional Saturday morning wander around town when The Inelegant Gardener mentioned, apparently in passing, that she’d like to look at an allotment (or rather a half-plot). I was instantly wary of this idea, I remember watering and weeding on my dad’s allotment as a child, back breaking and boring work. But when I stood at the foot of the overgrown plot I was instantly converted. A strange feeling came over me, of land, food, honest toil and soil – it was like my own Soviet propaganda film. Here I could work the land and provide!

The half-plot on possession, October 2006

But more was to come: walking back to the car some months later, after working on the half allotment, I pointed and laughed at the risible start someone had made on digging a neighbouring whole plot. A sheepish Inelegant Gardener admitted to being the owner of the risible start and a new whole plot. It turns out she was an aggressive territorial expansionist, albeit a pathetic digger. The Inelegant Gardener did her best “feeble female” look, and I agreed to dig the new allotment. A task I was to complete some 18 months later, almost exactly two years ago.

A risible start to the digging of the whole plot, April 2007

As under-gardener my principle tasks are digging and construction: sheds, paths, compost bins and the like. It’s rather satisfying work compared to my day job, which mostly concerns generating abstractions inside a computer. Allotment work produces tangible output: an hours digging produces a patch of turned soil and a bucket of roots. Construction produces sturdy, useful structures of which a man can be proud. As a result of this toil I am able to identify perennial weeds purely from their roots: dock, bindweed, nettles, couch grass, horsetails, ground elder. I sometimes worry that The Inelegant Gardener will pimp me out to other allotmenteers for digging work.

The Inelegant Gardener is still subject to unrealistic fancies, having directed me to spread about a ton of farmyard manure on the potato-patch-to-be she seemed to believe the worms would quickly incorporate it into the soil. Bollocks would they, not in two weeks, not without the aid of little squad of wormy JCBs! It was muggins wot' dug in the manure. I still think potatoes are magic though, turning over the soil with a fork and white egg-shaped edible things appearing – it’s magic. More realistically the potatoes came out all manner of shapes and sizes, several years they suffered from blight.

For us allotmenteering is a bit of fun, if a crop fails it doesn’t matter. Seeing the blight-wilted potato-tops and unearthing the rotten, stinking tubers gives an insight into what it is to rely so closely on the vagaries of nature for your livelihood. Seasonality becomes much more obvious; despite being relatively clued up about agriculture in truth I had little sense of when what vegetables were in season. Now I know, and it’s cabbage for most of the winter. Currently in season are courgettes (bloody hundreds of them), carrots, sweetcorn, potatoes, mangetoute, French beans, raspberries, beetroot. It’s fair to say we haven’t entirely cracked planting appropriate quantities, to start with we had one or two exemplars of any particular vegetable per meal, now we have massive, short-lived gluts.

We achieved a crop on our second visit to the allotment or rather Henry, a fellow allotmenteer, gave us some produce to keep us interested. He has continued to provide advice ever since, but now we swap vegetables and fruit. When we started only a small fraction of the plots on our colony were in cultivation, and Henry seemed to be keeping the place alive. These days most of the site is cultivated, it’s a friendly sort of place – most people will stop to say a few words as they pass on their way to their own plots. I can do a passable impression of a someone who knows how to grown stuff, if interrogated.

Our other neighbour at the allotments keeps chickens, at one point they had free range across the whole site they would come and supervise digging, jumping into the trench at inopportune moments to pluck out a tasty grub. Nowadays they are behind chicken wire, but still come to the fence if you’re digging or weeding by them, making approving clucking noises. There is something very reassuring about this companionship.

These days the allotment is looking almost ship-shape, at least it does when we’ve caught up with all the weeds. I continue to be proud of my construction efforts.

New paths! June 2010

An Englishman's Home is his Castle

Corfe Castle*

Back to rant for the blog post, this time on housing.

A house is like a millstone around your neck, once you're in it the reluctance to do anything that might cause you to move out is massive.

I've been somewhat itinerant since leaving university after my degree, I lived in Durham, in Cambridge and then in Poynton and now in Chester. It goes with the job, I'm sufficiently specialised that I need to travel to find work. For families containing two academics this leads to an even greater "two-body" problem; not every town or village needs a research scientist of my ilk. The downside of this is a degree of rootlessness and a lack of a handy family network. I'm not sure how common this rootlessness is across the population as a whole, it's true for many of the people I know.

It was when I was house hunting in 2000 that I got some hint of the credit crunch, I'd gone off to see the financial advisor upstairs from my estate agent to ask about offset mortgages (having been mildly burnt on payment protection insurance, I was trying to work out the hitch on offset mortgages). We had a bit of a chat; after some reassurance on what I was trying to get he pointed out that I was ultra-cautious and if I wanted he  could get me a x4 joint salary mortgage. I'd done the sums on this, and frankly it was scary but clearly a lot of people were doing this.

People often have a go at estate agents but personally I think it's the other punters that really fuck you up. Estate agents at least have to make some pretence of professionalism whilst the punter is free to do as they see fit and since they're unlikely to have bought and sold more than a couple of houses they can either by malice or ignorance make your life miserable. The bank and the solicitor's ability to find another little fee to slice off you on the way irritated me too. "Searches" caused me particular ire - it's not like they actually went and "searched", they got someone else to do an indexed retrieval, it's not like they went rummaging anywhere for something lost. Searching for documents these days takes bugger all time and effort. It's perhaps for this reason that I thought HIPS were a good idea, because I was pretty unimpressed by the system currently in place.

House price inflation is apparently the only good sort of inflation: no one is pleased if cars, carrots, or computers get more expensive every year but for houses it's different. For those of us on the housing ladder this inflation is no problem, for those not on the ladder it is the sight of the bottom rung being wound up beyond reach. Compared to the 1950's houses are about x4 more expensive in real terms today, they're about twice as expensive in real terms as when we bought our first house, about 12 years ago.

The real point of this post was a mild bit of ranting about care for the elderly and the sale of houses. Houses appear to be sacrosanct, you can be sitting on a house worth half a million pounds but rather than sell that to pay for your care the expectation is that the State should provide. Personally I'm hoping for my parents to piss away the inheritance in their twilight years and leave nothing to me - this includes the house. This attitude to housing and inheritance seems to affect every strata of society:

Owners of country estates apparently expect the public to pick up the cost of maintenance. And in the news this week, council houses - I must admit I didn't realise that council house tenancies were for life and potentially beyond. This strikes me as a nightmare for those responsible for the councils responsible for social housing provision, particularly given the 'right-to-buy' legislation. An obligation to provide housing for all is a good thing, the mechanism that via council houses, housing associations and housing benefit doesn't look like a great way to do it. Actually, housing associations do look like a good idea to me. If you were a company with this obligation you'd want to make bulk arrangements with landlords, and you'd be fantastically nervous about handing over valuable assets for decades. 

The move towards mass ownership of housing is relatively recent - mainly post-war in the UK (see page 12 here), and around Europe home ownership rates are broadly comparable, there are a couple of anomalies. I guess the reason for this is that home ownership fulfils a deep need for security, and literature and recent history reveal plenty of evil landlords.

I suppose the general point I'm making here is that we all want to pass on an inheritance, this is a very natural feeling but the effect of this desire impacts those that are still living and don't benefit from an inheritance. I actually quite like Billy-Gotta-Jobs proposal on taxing all houses as capital gains on death, as a way of cooling house price inflation.

Update: as supergoonybird points out in the comments, BillyGottaJob's proposal is actually for capital gains tax on *all* house sales - not just on death. This is a radical idea - but certainly one that strikes in the right place.

*Corfe castle because it's close to where I was born and lived until I was 18. Image from here.

Bamboo Pen and Touch

This is a tiny technology review. I recently bought a Wacom Bamboo Pen and Touch tablet, I say recently - I picked it up from the post office a little over 2 hours ago!

I used a Wacom tablet extensively as a mouse replacement at work some years back, and rather liked it. I only gave up because I moved into industry from academia and they are a bit more fussy about what I attach to the PC with which I am provided. At home the pen interface didn't work quite so well because at the time I was quite keen on computer games, first-person shooters in particular, and controlling the little man in such a game using a pen is exceedingly challenging, to say the least.

The big benefit of a pen interface to my mind is that I can hold the pen whilst typing, then when I need to do some "mousing" I don't have to scrabble around for the mouse. The whole process is just much smoother.

Unlike my original Wacom tablet this model will accept both pen and touch (with your fingers) input - I must admit I'm not really anticipating using the touch input. It appears to support the range of multi-touch gestures that a smartphone will accept. The active area of the tablet is roughly journalists notepad size which makes it small for an artist type tablet.

In terms of software, the installation attempts to guide you into some spoken tutorials which treat you like a moron - praising fulsomely your every successful pen-stroke. None of this is actually necessary since using a pen is largely intuitive and all you really need to learn is what the right-click simulation is (it's the button on the body of the pen) and what the configurable buttons on the tablet do.

I have the tablet installed on a Windows 7 system, which is "ink" aware - it understands interacting via a pen: A little palette will pop out wherein the miracle of the "handwriting recognition" is performed - being able to read my scrawl is impressive but it's a bit like a talking dog: it's very clever but ultimately pointless on a computer with a keyboard. Office 2007 is also ink aware, you can open up a document and scribble on a separate layer - obviously the first thing I did was draw a big circle around something and wrote "bollocks" next to it.

The tablet also comes with some little mini-apps in which you can doodle and play games, you can also download more mini-apps. They don't look fantastically useful.

All in all I think we're going to be happy together - holding the pen feels just like old times!

A set of blog posts on SQL

This is a roundup post for a rather specialist set of posts I wrote on SQL (Structured Query Language), a computer language for creating and querying databases. Basically the posts are my notes on the language which I'm learning because a couple of programming projects I have in mind will need it. The main source for these notes is the Head First SQL book. I've used a another book in this series (Head First Design Patterns) - I quite like the presentational style. The code in the posts is formatted for display using this SQL to HTML formatter.

Topics covered:
Some notes on SQL: 1 - Creation
Some notes on SQL: 2 - Basic SELECT
Some notes on SQL: 3 - Changing a table
Some notes on SQL: 4 - Advanced SELECT
Some notes on SQL: 5 - Database design
Some notes on SQL: 6 - Multi-table operations
Some notes on SQL: 7 - Subqueries and views

Of course you can find SQL cheatsheets elsewhere.

The Head First SQL book also has material on transactions and security, if I get a renewed bout of enthusiasm I will add a post on these items.

I used MySQL via its command line client to do the exercises, because it's about as straightforward as you can get. Notepad++ recognises SQL as a language and will do syntax highlighting, so I type my commands into it and copy them into the MySQL command line client. MySQL is pretty straightforward to install. I also installed Microsoft SQL Server Express 2008, which turned out to be a bit more of a struggle but on the plus side integration the C# .NET, which is what I normally program in, looks better than for MySQL.

I've been using with the SQL Server via SQL Management Studio (a graphical interface to databases) on the general election data compiled by The Guardian. First contact with actual data, as opposed to learning exercises has proved interesting! A lot of things that are fiddly to do in a spreadsheet are straightforward using  SQL.

SQL was designed in the early 1970's, with commercial implementations appearing towards the end of the decade. It's influence visible is visible in more modern languages, such as the LINQ extensions to C# (this influence is pretty explicitly stated). Some of the ideas of database design (normalisation) seem relevant to object-oriented programming.

It's been an interesting learning experience, my scientific background in programming has me stuffing pretty much any sort of data into an array in the first instance. SQL and a database look like a much better solution for many situations.

Some notes on SQL: 7 - Subqueries, combining selections and views

This is the seventh, and final, in a series of blog posts on SQL, the first covered creating a database, the second selecting information from a database, the third commands to modify the structure and contents of an existing database, the fourth, advanced selection. The fifth post covered database design. The sixth post covered multi-table database operations. This post covers subqueries and views. No claim of authority is made for these posts, they are mainly intended as my notes on the topic.

This is largely a wrapping up blog post to cover a couple of items.

As an alternative to the joins described in a previous post, "subqueries" can often be used. A subquery is essentially an entire query embedded in another query. Subqueries can be used with UPDATE, INSERT and DELETE statements, whilst joins cannot. However, joins can be used to bring columns from multiple tables. There are no special keywords involved in creating a subquery. There is some discussion on the pros and cons of subqueries and joins here on Stackoverflow.

In an uncorrelated subquery the so-called inner query can be evaluated with no knowledge of the contents of the outer query. This expression contains an uncorrelated subquery:

UPDATE raw_candidate_data
SET    gender = 'f'
WHERE  firstname IN (SELECT firstname
                     FROM   firstname_gender_lookup
                     WHERE  gender = 'f'); 

The inner query is the clause in brackets, in this instance it is a shorthand way of building a list for an "IN" comparison. Often an inner query returns a single value, i.e. for an average or maximum in a list.

This expression contains a correlated subquery:

UPDATE raw_candidate_data
SET    party_id = (SELECT party_id
                   FROM   parties
                   WHERE  raw_candidate_data.party = parties.party_name); 


The inner query requires information from the outer query to work, this expression acts as a look up.

Complex queries can be given aliases using the VIEW keyword, for example:

CREATE VIEW web_designer AS SELECT mc.first_name, mc.last_name, mc.phone,
mc.email FROM my_contacts mc NATURAL JOIN job_desired jd WHERE jd.title='Web Designer'; 


Can subsequently be used by:

SELECT * FROM   web_designers; 

The view web_designers can be treated just as any other table.

The results of multiple select commands can be combined using the keywords: UNION, UNION ALL, INTERSECT and EXCEPT. UNION returns the distinct union of the results of all the selects, i.e. with no repeats, UNION ALL includes the repeats. INTERSECT returns items that are common to both SELECTs and EXCEPT returns those items that are returned by the first SELECT but not the second. The general form for these combination operations is as follows:

SELECT title FROM   job_current
UNION
SELECT title FROM   job_desired
UNION
SELECT title FROM   job_listings
ORDER  BY title; 


Each SELECT statement must return the same number of columns and each column must be coercible to the same datatype. Only a single ORDER BY statement for the set of SELECTs can be used.

Keywords: UNION, UNION ALL, INTERSECT, EXCEPT, VIEW