I've been to San Francisco once and it is spectacular. By far the most beautiful urban area in the US with its architecture, hills, bay setting, cable cars, and fog. I loved the comfortable weather too. If I had an unlimited budget, it would be at the top of my list.
As far as modern urban areas that have ballooned in recent years, I really like the Dallas - Ft. Worth Metroplex. Downtown Dallas is surprisingly dull (Downtown Indy has far more going on), but the whole metro area is very nice and top of the line. I was impressed with the highway infrastructure which is adequately constructed to handle the heavy load.
One city that I've always thought is horribly overrated is Chicago. Don't get me wrong, it's a fun place to go to, but everything there can be found better in other places. Chicago is not the best in any sort of major category. New York is where you go if you want the best place for mass urbanity and the "center of the universe" feel. San Francisco is where you go for unparalleled urban beauty. LA is the entertainment leader. DC is the capital. Boston has the old colonial history. Miami is where you get the tropical glitzy beach atmosphere. All of the cities I just mentioned are the best in some sort of major category, but I honestly cannot think of one major area where Chicago is the best.