"Ah, Rapture. A city where the artist would not fear the censor. Where the scientist would not be bound by petty morality. Where giants in diving suits stomp about in the company of mutated little girls, beating up all the nutty consumers who've chosen to play doctor with their own genetic code. Rapture is a leaking masterpiece of art deco design, a city built upon the floor of the ocean itself, and as such it's easily the coolest entry on this list. Of course, it's also an extremely dangerous place, thanks largely to the insane governorship of its creator and ruler, Andrew Ryan. Yes, this is Ryan's city - but with the sweat of your brow - and a copy of BioShock - Rapture can become your city as well."
Learning the story of Rapture is the driving force behind the game; the city was built to represent an ideal, a paradise; somewhere that 'the best and brightest' had the freedom to pursue their own achievements. Obviously this plan was flawed, as when the player explores it it is a hollow shell of it's former self. But the decor and remnants echoing back to it's former glory evoke a very unique, specific emotional response in the player - a strange mix of nostalgia and fear.
I haven't really played a wide range of games, so I can't really speak from my own experience, however the list linked above gives a great insight into how these cities are all significant in their story, not to mention very well designed and crafted. Examples include Half Life 2's City 17, which is a unique atmospheric blend of East European architecture and dystopian oppression.
Many settings are, in the game world, as iconic as Norman Bates' mansion or the Overlook Hotel, and similarly have psychological side-effects on the player and unique 'personalities' themselves.
A recent example would also be Dishonored's setting of the plague-infested Dunwall. Again heavily oppressed as a result of the disease, it is a sprawling labyrinth of grungy alleyways, dilapidated buildings and grimy underground paths.