Twin Peaks has a breath-taking view of San Francisco. The night we drove up there, the fog was so heavy. We got lost on the way up and stopped for directions. The locals laughed at us for thinking we were going up there for a view. Boy, were they wrong!
Similar to the Coit Tower, local legend suggests that the Twin Peaks represent portions of a woman's anatomy. There are lots of photos here.
Here's the wikipedia page.
Here's Windows Live Local Bird's Eye View. You may have to work around a little to get a clear view of both peaks.