Some of the most memorable locations of video games I have played include: Shadow Moses Island(MGS), Sanctuary(Diablo series), Expel & Energy Nede (StarOcean:2nd Story), Little Big Planet(Little Big Planet), Ozwell E. Spencer's Mansion (Resident Evil 5: Lost In Nightmares DLC), Earth (Onimusha series), Final Fantasy games also do a good job with the setting of their games for the most part, they tend to be very interesting places with a good amount of depth into the story behind the specific locations as well as being very visually appealing.I am sure there are more great locations but this is what I came up with off the top of my head. Also, Skyrim! (Skyrim).
(Side note: so far I am intrigued by the setting of Resident Evil 6)
I agree that a games setting is very important. If you want your game to have any replay value whatsoever you need to have a location that makes the player want to return.