Of course, the Roku also does Hulu and Pandora, and I have a second one upstairs on the non-SD tv. From what I've gathered the Apple TV 2 doesn't play nice on SD.
Roku 2 has access to a lot more content, but the experience of surfing channels is kind of fragmented. The included Angry Birds game and motion remote rock tho!
I have all the users that upload old Formula 1, so I can watch old races whenever I want.
Democracy Now also has it's own Roku channel, as do many many other things like Ted that you might not have thought of. It's worth spending some time on Google searching this stuff out.
I'd say I spend more time watching YouTube, NetFlix and HULU+ on the Roku than any other consumption mechanism.
Local news on OTA HD follows behind.
One slightly sad note, I only have the base Roku, and it's quite slow (maybe buffering), so my 2 year old is now utterly tolerant of loading bars, and will watch the loading bar, since she knows that content will follow. Her absolutely favorite thing on TV, though she doesn't watch much (seriously), is the mime segment on Nihongo Quick Lesson, NHK's little Japanese-lessons show that runs about 2:15pm, right after her lunch. I guess really, it's not much different to me as a kid waiting for the TV to warm up and show a picture...