Agreed, Silent Hill is the only good video game-based movie I've ever seen. It didn't really scare me (SH is a much scarier place when you are the one who has to trek through it), but it did a good job with the story and it was structured like an SH game. I even appreciated Pyramid Head's presence, even though "BLATANT FAN SERVICE" should have been written on his great knife, since they didn't really explain what he was or why he was there.
If Hollywood figures out this whole video game thing, I'd like to see Jet Force Gemini, Resistance FoM, Assassin's Creed, Mass Effect, and maybe even Call of Duty 4 (although you could more or less get the CoD4 experience by putting all the Tom Clancy movies in a hat and pulling one out).