So now that Dark Souls has ripened fully, and I remembered I made this thread, I am going to list my favorite areas in terms of aesthetics and personal impact. In order of appearance.
Blight town: Up until I entered this place, I still felt pretty sure about myself, Demon's Souls veteran that I am. Undead Burg/Parish were great, and the Depths had a fucked up boss, but It wasn't until I descended into that cistern and made my way out onto those rickety platforms that I seriously felt like my life and taken a serious turn somewhere. Then the descent begins through this mad shanty town lashed to giant stone construction, closer and closer to the Great Swamp, which appears to be your goal and also nothing you want anything to do with.
Darkroot Garden/Basin: You can easily encounter this place in full before Blight town, but I didn't the first time through, and the change of locale was shocking. The choice between murderous defenders of a seemingly benign bit of wood and a lake full of Hydra made the comparatively idyllic area foreboding as hell. Cap that off with a couple of the more interesting and mysterious NPCs, and one of the more tragic bosses, and subtle signs that something greater once existed here. Just great.
Sen's Fortress: This place is your enemy from the second you walk through the door. A murderous slog through traps and guards with seemingly no respite. The constant grind of machines, the pounding of the boulder trap, and the hiss of the snake people are your soundtrack as you creep along narrow halls and stone catwalks. The feeling I got when I reached the roof was paralleled only by the feeling I got when I found the hidden bonfire.
Anor Londo: Probably the only back to back feature here, but this place knocked it out of the park. From the grit, grime, and decay below you literally ascend to the city of the Gods, and right off the bat that's where you feel like you are. Nothing here is man sized. This is a place of gods, but also a place abandoned. There are many secrets to be revealed here, but they're just icing on the cake.
Tomb of Giants: This place is just plain terrifying. I've beaten this game a dozen times, and there is still a room in this area that I have never cleared and, in all likelihood, will never clear. It is full of fucking monsters, and I don't care what that item is over there, it's not worth it. You don't so much explore this place as survive it. Of special note, I once had a gravelord affect my game while I was in here. It was the worst thing.
Cavern of the Abyss: This is new to the DLC/PC and is the final area of the new content. I really don't want to say too much about it, because the very nature of the place is a little spoilery, but suffice it to say that my first inclination upon entering was to turn around and go back the way I came. No one was meant to tread in this place.
Kiln of the First Flame: There really isn't much to this area, but it tells a visual story of a an absolutely catastrophic event, and terrible loss.
The Great Hollow/Ash Lake: THIS FUCKING PLACE. Completely optional, hidden, off the beaten path, and huge beyond belief. First you find the way in, then you labor your way down and down and down, and when you finally emerge you find yourself in a place lost to legend. Old, huge, forgotten, forlorn, and beautiful. This place is it's own reward, and if you've never found it, I urge you to try. I set the fucking controller down when I first made my way out onto the grey shores. Then I picked it back up real quick because roots of the world got some crazy shit in them.
This post could have been "Whole fucking game", but those are the places that stand out to me.