Many of the locations in Stranger's Wrath. I couldn't find a shot of New Yolk City, so here's Buzzarton.
Syberia constantly drew me in to the locations, just about everywhere I went. I love the architecture (not a common praise for games) and the atmosphere the game conveys overall.
Rez was already mentioned, and I remember when Rez was first shown, a lot of comparisons were made to the movie Tron. So it's only fair that we include Tron 2.0 in this thread, as it is pretty damn good.
For something older, I gotta mention NiGHTS. My favorite location (level) is definitely the Soft Museum. It's just such a unique, and beautiful area. It doesn't look like an actual place, but it very much looks like a place you'd visit in dreams. Instead of a screen, here's a clip. (Not an amazing clip, it's just the first youtube I found)
Finally, this may seem like a strange choice, but, the train in The Last Express. More a "setting" than a location, but the game used its setting so beautifully. It's not about the visual aspect or anything, it's just that the way the narrative works is so compelling.