Pixel Bender.

Pixel Bender is a plug-in for Photoshop (free from Adobe) it's a set of filters essentially, the best one is the Oil paint effect, coupled with a graphic tablet and very very basic artistic skills (and I mean basic) you can easily come up with effects like this. These are all based on "normal" images and just "played" with in Photoshop. You don't even really need a graphics tablet, it just makes it slightly easier.
A word of warning though, it's heavy on memory so the more ram and better graphics you have the better it'll run.