I rarely go for the Manhattan Project, but when I do it's usually when I didn't focus on my victory condition enough and the AI is cruising towards culture or science victory. As far as I can tell, nuclear waste isn't a great tourist magnet. Also, not sure if it can be achieved in a war in any other way, but when the AI is a victim of a nuclear attack, they are willing to make enormous concessions to cease the conflict which is kinda useful. It's usually hard if not impossible to snatch a good amount of great works in a regular peace deal, but nuclear obliteration seems to do the trick.
Another use I have for it is blasting annoying AI back to the stone age. I keep a little "black list" every game with stuff each one did to me so I decide whom to nuke. Alexander and Tomyris seem to be consistently high on that list.
It definitely doesn't seem to work as a war deterrent though, the AI simply cannot process the state of the game or civ dynamics properly. So when you use the nukes on one and the rest find out, they might still declare war on you because they think you are a trigger-happy warmonger (which isn't terribly inaccurate).
Sidenote: I wonder whether the use of nuclear weapons will have a positive era score impact in the new expansion. That would be interesting to see.