There are two things involved here.
The first is that Yes, prepositions are often employed 'intransitively', without an object: for instance, the prepositions† in verbal idioms like sit down and stand up.
The second is that although where ordinarily stands for an entire preposition phrase—the store where I buy my beer is equivalent to the store at which I buy my beer—the implied preposition is often supplemented with an express preposition. Because the 'default' interpretation of where is that it designates a static location, the supplemental preposition is almost obligatory when where designates not a static location but the origin or a goal of some motion. For instance,
Where I am designates my present location, but
Where I am from adds the preposition from to indicate that where designates my origin, not my location.
But supplemental prepositions are not limited to situations like this: they are also used with ordinary locatives, sometimes with a particular idomatic sense. Back in the 60s, for instance, the phrase where it's at was a common designation for clubs and other venues regarded as especially fashionable:
They got a little place a-down the track
The name of the place is I like it like that
You take Sally and I'll take Sue
And we're gonna rock away all of our blues
Come on, come on, let me show you where it's at
The name of the place is I like it like that.
—'I Like It Like That', Chris Kenner
† Traditional grammar called them 'adverbs' and some modern grammarians call them 'particles'.