As all stereotypes, it's an oversimplification. Our culture is focused mainly (or almost exclusively) on the material, so the "important" traits are physical: strength, beauty, knowledge/intelligence (often wrong intertwined), money, power,... This, being still too complicated for the pop culture, is simplified further: power means money, good physical traits will give you both (at least by the end of the movie),... so, you're left with body and mind as the only two important traits (now my answer is a bit of an oversimplification, but not as much).
Another simplification is that pretty = sociable. So, those not pretty are not sociable, which is the reason why such persons become, as you rightly put it, socially awkward.
Now, if you have only two (main) traits, a person cannot have both, because (s)he would be perfect, right? Hence, smart must mean socially awkward. Tony Stark is the obvious counterexample here, but he is portrayed as an all-might human, and his flaws are just minor annoyances.
Keep in mind that your examples are more or less aiming towards comedy. Very common way to make fun of characters is to make them stupid, but you cannot make that to smart people. Hence, you make them "socially stupid", and achieve the comedy element (this is the key element of the Bones character in Bones, although most of the other - also smart - characters there are not nearly as awkward as she is, with the exception of Zack).
This kind of stereotype is also somewhat believable to "ordinary" people, since "smart" is also mysterious, in respect to what such people do. Most people do not understand advanced mathematics, programming/system administration/hacking, chemistry, etc., so people who understand this stuff must be weird, right?
For example, astronauts must also be insanely smart, but they are mostly seen as good looking guys (rarely women) who fly in rockets and walk on alien planets and moons. Or snipers, who just put a cross on someone and shoot him (in reality, it's a job that requires quite a bit of smarts; see a nice, short explanation in this Cracked article). These types are never portrayed as socially awkward, are they?
As for how strongly this is based on the real life, I'd say not at all. Sure, there are very smart people who are also socially awkward, but there are much more of them who are just as normal as the next man. On the other hand, there are loads of people who are of an average intelligence, but are for some reason socially awkward. A typical example: obsessive fans/collectors, i.e., StarWars fans, Trekkies, Tolkien fans,... some of them are certainly very smart, but you can be one without having beyond-average intelligence (I hope this doesn't get my rep in negative :-)).
To sum it up: social awkwardness is related to the interaction and share of one's interests with the "ordinary" people (read: with the majority), and is not, at least directly, related to one's intelligence, but it's an easy way for the pop culture a) to achieve comedy, and b) to emphasize smartness of a person doing something that common people have hard time understanding.