I saw the last Columbia launch from the California Grill observation deck (I believe that since it was a shuttle launch, they were VERY lenient on people going up to the non-restaurant part of the deck) and it was amazing....
I saw a launch from the closest civilians can be, and they scrubbed it 2 times before they could launch it......so we trekked out there 3 nights in a row, at 2am.....
but really, any high place.....like a parking garage or balcony would be perfect....