I actually think many parts of SF are in a suboptimal density band where it is quite dense, but still car dependent, dense enough to add a lot of cars and concrete but not quite dense enough to reclaim some of the pedestrian space… this link explains it pretty well.
SF really is pretty diverse, especially in terms of language diversity (I read somewhere that the number of languages spoken by 1000 or more households is higher in SF than even NY, though I don't have a cite for this). SF also does have decent density by US standards as long as you're not talking about NY. (http://beyonddc.com/?p=4808). I also find that getting out of the city for the countryside or natural surroundings is faster and easier in SF than almost anywhere else (ie. the time it takes to get from a very high density urban neighborhood into a very quiet forest or rugged, undeveloped coast is amazingly short in SF). Since I really value both things, I do get a lot out of living in SF, but there is a kind of truly urban neighborhood, with trees and open pedestrian areas, that is much more prevalent in larger, more dense cities like NY or Paris. The images in that link about curb cuts, contrasting the street scape between Park Slope and Dolores Street in SF, pretty much sums it up. The image of Park Slope manages to both peaceful and highly urban at the same time, largely because it's urban enough to give up on cars and driveways. It almost seems like SF got just close enough and then blinked.
Aside from NY, though, and maybe a very few parts of Boston or Chicago, there really isn't much else out there, at least in the US. For the west coast, you just aren't going to find as many of those little red dots clustered (high density), even in Seattle. Interestingly, LA is becoming much more urban. That said, SF's high density urban neighborhoods are pretty limited in size, and they are breathtakingly expensive.
But you're right, there aren't many places in the US that hit that balance. I grew up around Boston (found it too small and casually racist), lived in Chicago (you still need a car), have spent a ton of time in SF for work, and have lived in Brooklyn now for about 9 years. When I think of where I'd move if I weren't in New York I usually end up with Mexico City, London, Bogota, Medellin, Tokyo, and other cities abroad.
But it turns out I really like having space to play more. YMMV.