I'm not as interested in exploration as a mechanic in most games, mainly because it's done poorly.
Shadow of the Colossus had a large, very prettily designed environment that wasn't broken up by Generic Monster #73 attacking you every five minutes, and as such, you could wander around with impunity. It's one of the few close to worth exploring for cosmetic appreciation, but it still wasn't enough to convince me (granted, I didn't know about the lizards/fruit for the whole game.)
I'd consider Thief and Thief 2 to be the best games exploration-wise that I've played. There's a lot of nooks and crannies, and other ways to approach a guard from behind; the exploration gives you loot (but even the hardest difficulties don't force you to grab it all) but also other ways to tackle a level. It's not "I want all the heart pieces" or "I must find 13 MacGuffins to continue the plot" but rather looking for a wooden beam onto the second story or into the vault, and the good design of the levels means that there are usually multiple ways to get where you want. It's much more integrated into the gameplay, because the advantages you get from exploring are optional and don't feel contrived into a scavenger hunt. Also, the maps help to maintain the feel of the game as well as giving you a decent amount of information (a few exceptions apply, like the Lost City.)
Wind Waker's exploration was poor. Yes, you had lots of space; yes, through manipulation of the wind/warping magic you could get around it at a moderate speed; but the constantly spawning enemies knock you out right when you start to enjoy it and the endless expanse of blue never changes from one place to the other. There are lots of regularly spaced islands, but they're still mostly "do action X to get optional equipment boost Y here, and you're done with this sector." The Triforce hunt late in the game turned the exploration into a mandatory punishment as well: it becomes less about enjoying the scenery as it is about combing every inch of an area, a practice which is as fun as using every inventory object on every object in the game in adventure games.
That's where I stopped playing Metroid Prime - hunting down those artifacts by pacing again and again over previously cleared areas with respawning enemies to find the random little dongle labeled "Bomb me here" is a great way to get players to loathe expansive environments. Outside of that treasure hunt, it still had a pretty linear progression in what you were forced to do (a recurring problem with most Metroidvania games, especially when they're not clear on where the game designers decide you need to go next. Valve's major "genius" to me seems to be integrating that last bit into the game environment.)
I haven't played Batman: Arkham Asylum or Silent Hill, so I can't comment.
Bethesda's games are a good example of expansive environments that can encourage exploration, though I was really only personally impressed by Morrowind's (because it was new.) But they fell to the problem of making the exploration redundant - Morrowind's had no interesting characters nor quests nor much reason to actually visit all the areas the game offered. There were hundreds of caves, but randomly generated loot that would generally be worse than what you had, and leveling/moneymaking was done much more effectively through repetitive grinding. Oblivion did a good bit better on quests and characters, but shot itself in the head with its horrifically applied auto-leveling of monsters that made enemy selection a matter of what level you were, not where you were, and often punished you for leveling up more than the actual level gained you. Fallout 3 seemed a bit better, but still got repetitive and I stopped playing after a series of crashes that come standard to all Bethesda games (I was still rather burnt out on Bethesda's overall style, too).
It's nice to see Yahtzee back up his opinions solidly, so that idiots in the aforementioned twitter post can go play in traffic XD
