From a purely AE standpoint, "at" is used for a specific, defined place, "on" is used before a street name with no specific address, and "in" is used whenever there is the sense of "within" or "inside of." For example:
He lives at 34 Oxford Street.
He lives on Oxford Street.
He lives in Palace Apartments.
Notice that if the person you are talking to already knows of Palace Apartments, then it is already defined and specific enough between the speakers, so that one may say, "He lives at Palace Apartments." (If the other person does not know of Palace Apartments, he would infer that it is a specific place and probably ask, "What is Palace Apartments?" instead of "Where is Palace Apartments?")
There are always exceptions and linguistic nuances. One could say, "John lives at that restaurant" to mean "John goes to that restaurant so often that he practically lives there"; but to say "John lives in that restaurant" would imply "John physically inhabits that restaurant."
When speaking of geographic regions, one always uses "in" since we literally mean "He lives within/inside of London" and not "He lives on top of London" or "He lives at the specific spot of London."
From the perspective of a UK resident, the name "10 Downing Street" means the building we see on the news every day. It is a three-dimensional concept, not just an address.
Bill Bloggs lives at 47 Adelaide Street. He does not live on 47 Adelaide Street.
He might say he lives on Adelaide Street, or in Adelaide Street. I think that his choice of on/in depends on how big Adelaide Street is.
The kids used to play games in the street. They were not allowed to play on the road(pavement), but were told to stay on the footpath(sidewalk).
I somewhat disagree--I think one rides a bicycle in the street, but on the pavement. Which is interesting, because one rides a bicycle on the sidewalk, not in the sidewalk. And, even worse, one drives a car on Main Street. Why do I think a bicycle uses in and a car on? No idea, it's just the way I've always said it!
At any rate, it's tricky, isn't it?
Hrmm, I'm American. To me, there's a significant difference between the two.
"In the street" means literally that you are "in the middle of the street, standing on it, and waiting for a passing car to hit you.", while "on the street" can be used to describe buildings which face the street. i.e. "The factory was on Jackson Street." or a pedestrian who is walking down the street, but is on the sidewalk, not in the middle of the road.