I've always thought that the atmosphere of a game is determined primarily by the soundtrack - so the most atmospheric places are the ones with the best soundtrack, including:
-> Argentrock Isle from Ultima 8, a place the player is bound to doom.
-> Vibrant Hong Kong, ominous Shipyard, and the isolation of the Ocean Lab in Deus Ex (also note that maybe the best track - the last visit to Hell's Kitchen - is missing in a standard release of the game).
-> Feuerstelle (Glory's mission - the quest is a mundane story of a cult, but those
and nice visuals make it worth it) and Aztechnology (Bloodline run - the story of cloning and the potential genocide) in Dragonfall.
-> Ilos in Mass Effect, finding the remnants of the dead civilization.
Also, I think there is an inverse correlation between the atmospheric qualities of a place and the amount of encounters - that is, the lonelier, the better.
As for my favourite singular locations, among the ones not mentioned:
-> Heart of Moander from Pools of Darkness - which looked and worked the way a human heart is roughly expected to work, flushing the party every once in a while to another place.
-> Both hubs in Deus Ex: Human Revolution (Detroit, Hengsha), containing lots of secrets, tons of things to do, with my abilities determining what I can do and what I can't.