You've lit upon one of the most confusing grammar issues here. There seem to be rules, but the number of exceptions is astronomical, and the rules seem to be changing all the time (historically speaking).
We all know that Sherlock Holmes (to pick a name AT random) lives ON Baker Street. Which makes perfect sense until you begin to realize that he used to live IN Baker Street.
You arrived IN Paris yesterday. Two hundred years ago you would have arrived AT Paris, believe it or not.
At work, at the office, at the supermarket, at the ballroom, at the train station and even at home all seem to indicate that the person (or, sometimes, an object) is AT (ha, ha) someplace where he or she or it is not expected to remain permanently. (At the crossroads, at odds, at the half-way point, etc).
But, but ... "I know not seems."
At might sound general while in is more specific ... more concrete ...
The phrase "She is in the supermarket" would indicate that the speaker and the listener are in the immediate vicinity of said supermarket, while "She's at the supermarket" would suggest that neither the speaker nor the listener are anywhere near that supermarket.
"He's in the office" would suggest that the speaker and the listener are currently positioned very close to that office, definitely in the same building, probably on the same floor.
ON the bus, yes, but IN the car.
So far as islands are concerned, Americans favor living ON them, while the Brits are more often inclined to live IN them:
"I've lived ON Long Island all my life," says a Huntington resident. (The same Huntington resident will also tell you that he or she was shopping IN Manhattan the other day).
Somerset Maugham, a British author, has this to say in one of his books:
It was absurd that she should continue to bury her beauty, her wit,
her social grace IN an island in the corner of the Mediterranean.
But no one seems to live at an island. You can't live at Rome, BUT who's going to stop you from living at some distance from it?
To summarize. The correct choice is nearly always intuitive and depends on where the speaker lives (and also when he or she lives, historically speaking).