I was lucky enough to see STS 124 last May -
YouTube - STS 124 Launch - May 31, 2008
It was very impressive. If I had to do it again, I would not have videotaped it though. We were on the causeway about 6.5 miles out. I was hoping to be able to feel the launch, but believe it or not, you get blasted away harder at an NHRA event.
But keeping in mind that the shuttle was 6.5 miles out it was an impressive sound. It sounded like a long, somewhat loud rolling thunder with some popping and banging explosions that lasts about 30 seconds.
We got lucky, planned about a month out. Arrived in FL the night before the launch. Got up at 6ish to catch the tour bus in Titusville. Spent the day at the space center checking things out. Headed out to the causeway a few hours before the launch. Countdown was perfect, launched first time right on time, no issues. But then--
The way out was a nightmare! We got unlucky in that our tour bus was just about the last on to get on the causeway, therefore one of the last ones to leave. We got stuck at the visitor center for about 20 mins waiting for some jokes to catch the bus. It took 2 solid hours in traffic to leave the NASA complex. Then another solid two hours to get back to cocoa beach. The whole coast gets packed to the gills with people.
It was worth it, but I wouldn't do it again unless I could get closer then 6 miles. Man that woulda sucked if it would have gotten scrubbed-- theres some rules with the tickets that if you get out to the causeway and theres no launch, you lose the tix. I would not have come back for the second attempt.