It is absolutely not a hideous place at all. Pics from a bridge approach do not do it justice. The Hunters Point neighborhood of LIC has one of the best waterfronts in the entire city.
From ArchPaper:
This is also LIC (photos by Stefano Ukmar of the NYTimes):
This is the studio of artist (and former Bjork partner Matthew Barney):