Why does my (HP Pavilion) notebook have an accelerometer?

I have an HP Pavilion DM3 notebook, running Debian Squeeze GNU/Linux (although I doubt that's relevant to my question). Browsing through the device manager listing, I found an entry for an "ST LIS3LV02DL Accelerometer."

What is this actually for? And can I do anything really nifty with it?

It's for HDD protection, most likely. If it detects high speed movement (ie: exceeds the set 'safe zone'), the HDD stops and it protects the HDD. Pretty nice.

How to get it working...
On Windows, you need to install the driver from your manufacturer to enable the sensor. Once installed, you will find the device in your Device Manager. On a Dell Latitude, it's called "Free Fall Sensor".

On Linux, you must have a 'Linux compatible laptop'. By that I mean your laptop needs to have a module for this functionality in the kernel. ThinkPad laptops for example with recent kernels have it out of the box. It may also be possible that your laptop model has a module somewhere available that you can compile and load yourself.

You can use this sensor for many thing... like...
http://www.youtube.com/watch?v=xIFdGQNf0-c
http://www.youtube.com/watch?v=2NzmEkwynmo

For this, you need to find the sensor's raw value 'somewhere'. One example. On Linux, you should check the kernel module's source code. Once you find the location (ie. under /dev), you have to read the raw value, and using that, implement some kind of controls for games and applications. Either you can convert the input into some kind of virtual joystick input - thus getting a universal input method. Or, you can simply use the value from your application.

** This was the case in 2010, when I posted this answer back then.
Since then, Dell also became a big Linux player.

It is indeed for protecting your hard drive. If the accelerometer detects the laptop is suddenly in free-fall, it will disengage the read head, significantly reducing the chance of your hdd being destroyed in the fall.