Matango in Secret of Mana... I don't know why but the trippy music, awesome mood lighting, and the fact that's a freaking mushroom kingdom just set a certain tone that I still love, it's a very cathartic place and I love just hanging out there for some reason, even though it's a peaceful town and there's little to actually DO there.
Navarro in Fallout 2: For me this was always the big division in the game, where you really go from some wasteland scavenger junkie to a bad-ass plasma cannon toting mercenary of death, the area was tons of fun to explore and try to get everything accomplished without blowing your cover and walk away with thousands of caps worth of military grade hardware.
Super Mario RPG had so many great locations it's hard to pick, but I'd have to say Booster's Tower was amazing, it just had so much craziness going on, each floor felt unique and had it's own little twist to it, it was just a lot of fun to play, close runners up are Bowser's Castle and the Sunken Ship
Final Fantasy VII, speaking of towers, Shinra Tower is my all time favorite part of that game, like Booster's, each floor boasted it's own unique flavor and objectives, and everything from the initial climb up from Wall Market to the crazy chase at the end was just amazing, you literally were riding off the excitement Shinra Tower generated all the way up to Junon, it's such a perfect area.
The Castle (North Window) In Suikoden II, It's hard to argue that Suikoden II's castle was incredible, I loved checking back after doing a few story quests to see what had changed, to partake in another chef showdown mission, to build a new statue, talk to all my new NPCs, check out my new shops, it was just a perfect by-all-end-all base that had EVERYTHING you could want.