It's basically the same as your other question and its answer.
In all your examples here, at indicates an instantaneous event (not related to anything leading up to that event) had an effect; from indicates that the effect stemmed from an existing or long-standing condition.
I became hungry at the smell of the cookies.
The smell of the cookies reached you and you became hungry (instantaneous).
I was exhausted from working all day.
Working all day is a long-standing condition, not instantaneous.
I got excited at the sight of her face.
You saw her face and in an instant became excited.
The last one is more interesting:
I'm broke from having spent all my money at the bar.
Although you could have spent all your money in one transaction, so that you instantaneously became penniless, the use of the continuous having spent means that the state of penury has continued since that time. Consequently you use from.
You could even make a more ambiguous case with
I'm broke from all the expenditure I incurred.
But the same analysis applies: you incurred all the expenditure, and an hour later you had still incurred all that expenditure. It's not the same as suddenly seeing someone or smelling cookies.
What is it called when the preposition is omitted? It's called an indirect object.
That's what an indirect object is. An indirect object is licensed and governed by a verb. When a preposition is involved, the object is a prepositional object or an oblique object.
Oddly enough, the cited dictionary page doesn't manage to offer a clear example of this verb's ditransitive use. The example "he was awarded the Military Cross" is cast in the passive voice. The ditransitive construction is more obvious in the active voice: "They awarded him the Military Cross." The example "a 3.5 per cent pay rise was awarded to staff" is not a ditransitive example, even when cast in the active. "Management awarded a 3.5% raise to the staff" includes a direct object and a prepositional object, but there is no indirect object in sight.
You don't seem to be confused by the ditransitive use of this verb in the active voice. However, passive-voice participles do seem to confuse you.
Such confusion is easy to understand. For most verbs in English, the past-tense forms and the so-called past participle forms are identical. Even the "past participle" label is confusing, given that participles have no tense.
"The Academy awarded the Oscar" looks quite similar to "the actor awarded the Oscar". Without further context, they both can be parsed as clauses in the active voice and past tense. However, "the actor awarded the Oscar has refused to accept it" requires a different parsing. Here, "awarded" is a participle, "awarded the Oscar" is a participial phrase which modifies "actor", and "the actor awarded the Oscar" serves as the complete subject of "has refused to accept it".
You're not the only one experiencing such confusion. It's nearly universal. Sentences like "the actor awarded the Oscar has refused to accept it" are garden-path sentences. It is easy for anyone to assume that "awarded" is a finite verb until the phrase "has refused" is encountered. Since "has refused" is finite and must have a subject, we have no option but to re-parse "awarded" as non-finite and without subject.
The examples that you provided are not garden-path sentences. Each one contains a clue to the participial nature of the verb in question before it is encountered:
The phrase "on the plaque awarded her" has "on", a preposition which licenses an object. The phrase "the plaque" cannot serve as the subject of "awarded" since it serves as (a part of) the object of "on". A similar explanation serves for the prepositional phrases "about the Nobel Prize awarded Ernest Hemingway" and "by the Oscar awarded Michael Radford's Il postino . . .". In "who refused the Oscar awarded him", "the Oscar" is (a part of) the direct object of "refused". In the absence of a subject, there is no clause and there is no reason to assume that the verb's form is finite.
Best Answer
When something simply collapses, it falls more or less straight down. While it was standing, it occupied a certain amount of space on the ground. When the collapse is complete, the thing is much shorter, but takes up more space on the ground, because it spread out or tilted over as it fell.
If you picture a person standing, they occupy only about a square foot of ground space; when they collapse, their knees would buckle and the start of the fall would be straight downward, but not everything bends in the same way and the person would end up lying on the ground, probably taking 5 or 6 square feet of space.
When something collapses in on itself, it falls more or less straight down, and it ends up taking no more ground space when fallen than it did while standing. Obviously a person can't really do this, but a building can be made to do it. (check out wikipedia's page on Building Implosions for some images and videos.)
Both prepositions are necessary; you can't collapse on yourself because the on, when used alone with collapse, is describing where you land. (You might "collapse on the floor", or "collapse on the bed"; but "collapse on yourself" sounds like you end up floating in mid-air since you didn't actually land on anything else.) And you can't collapse in yourself because in, when used alone with collapse, describes either the general manner in which you end up, or something that you do while collapsing. (You might "collapse in a heap", or "collapse in a flood of tears".) When you combine both prepositions, though, in describes the direction of your collapse (as being not a normal "spreading-out-as-you-fall" collapse) and on now specifies correctly where you (or the parts of you that did not spread out as normally expected) landed.