I believe that English speakers use in and at based on what sounds best to them or what they are used to hearing. We say, "The desk is in the office." "I met my friend at the office." (in the USA) "The children were playing in the street." means the children were playing on the actual street and would be able to be hit by a car driving on that street. We say, "The house was located on Main Street." here the house is actually located adjacent to the actual street. If the house was "in the street," the house would be blocking the street. I met my friend "in/at the cafe" are virtually the same. As Randy has mentioned, "at the cafe could also mean in the vicinity of the cafe ( just outside the cafe).

Given different contexts, all these combinations could be correct. "In" often conveys the idea of inside a closed space. "I arrived at the cafe late." "We had drinks in the cafe." "Homeless people live in the street." "Rumors on the street suggest a merger." "In/At the office we have our own gymnasium."