"Great views, but feels remote for being in the middle of the city"
Most of Corbett Avenue winds through the hills, parallel with Upper Market. The high-up location gives you one of the best views of the city, and there's very little foot or car traffic, so the street is fairly quiet and clean by San Francisco standards. The drawback is that, unlike much of San Francisco, you don't having corner stores, restaurants, cafes and bars within a few blocks walk. You can still get to the Castro or Portola districts on foot, but it will be a bit of a hike. Parking also gets snapped up quickly, and bus service isn't as good as in some parts of SF.