Colma Bart station - you sit outside waiting for you bus in cold wind and fog - there is no shelter - there is poor connection from Bus to Rail - if your bus is late like mine always is, you have to run and jump over the turnstyles and  run down stairs to jump into the train before "the doors are closing" 

Seattle - 3rd Ave between Pike and Pine. There is a McDonalds, check cashing place, smoke shop, and 'urban wear' shop on the block. It is also in the Seattle ride free zone. This becomes a place for people to buy, sell, and smoke their crack, etc...

I generally like German Hauptbahnhofs (main train stations, in particular Munich, Stuttgart, Frankfurt, Ulm)  as they often have a fair amount of retail, spills out into a multi-modal transit hub that is pedestrian friendly and also anchors one end of the main pedestrian commercial corridor, leading to a central plaza (usually the main church plaza).

I also enjoyed the minneapolis lightrail stations - each one is different and tries to play up the local highlights in an artistic manner.

I second the stop that mdler mentioned

The Damen Ave Blue line station on the Chicago EL  The platform spans a very interesting intersection of three streets, has views of the down town skyline and is the box seats to the urban street life dramas taking place below.  It is not a modern station or accessible but it is nestled into the urban fabric and the views from the platform are dramatic. The station cuts diagonally across two major thoroughfares Damen and North Avenue and you can watch TV in the adjoining apartment if you chose to linger on the landing. The connection to the street is awesome and gives something interesting to look at while your train approaches.

http://www.chicago-l.org/stations/damen-ohare.html

Thanks the responses guys, really helpful.

Peter, what a coincidence, I was just in Chicago for the first time last week and just happened to be at the station for something. I took a picture at the time thinking it was really interesting, so its really good to get the extra information you have on it!

all-time favorite: the karlsplatz station by wagner in vienna, sharing the karlsplatz with the karlskirche. an amazing little jewel in an amazing urban space: http://en.wikipedia.org/wiki/Karlsplatz_Stadtbahn_Station

would-be all-time favorite, if i could visit it: the never-functioning city hall station in lower manhattan. 

for less rockstar level but very very good transit experience though: the light rail stations along norfolk va's new line are very well-considered, varied, and make the most of the unique situation around each stop. really a good system, albeit small (so far). 

i also remember really enjoying the elevated station just off of the alexanderplatz in berlin in 1990. it wasn't just a train station: doubled as an impromptu disco!   

http://goo.gl/maps/J3b3a

Typical bus stop in my former neck-of-the-woods.

steven, did greg lynn do a thing at your vienna station?  it looks familiar but i'm afraid my mind could be getting fuzzy in my old age

the Harry Weese designed DC Metro is beautiful