You badly discount both the Manhattan Project and the Apollo Project.
Although both were led and funded by government, they had very special characteristics. Both projects brought together the best and the brightest of the engineering and physics communities, and animated them to prodigious achievement in quest of a difficult goal.
Neither project was just a simple matter of engineering. Both required the vision to attempt what was not possible at the time – to expect and create technological breakthroughs in a variety of fields in order to meet the goals. Both projects were amazing accomplishments. Of course, since it did involve government, my father, an Apollo researcher, commented that NASA had set a record in how quickly it could build a complex and unresponsive bureaucracy 
As for Apollo, it’s downfall wasn’t its lack of economic value. The problem was the lack of follow-on. Once we (Americans) made it to the moon, which was exciting to all of us at the time, we lost interest. The economy was sick, Vietnam had split the nation, and the excitement was gone form the enterprise and the whole country.
While I am not a fan of socializing problem, sometimes government can do good. The DARPA Urban Challenge generated far more creativity (in autonomous vehicles) than it cost [see links]. But what a surprise, DARPA (ARPA) created the internet, also. The NSA was responsible for significant improvements in computing technology. The integrated circuit was developed because it was needed for the Minuteman guidance system. Velcro was but one of many results of Apollo.
John McCain’s battery prize is in the right spirit – it targets one of the most critical issues in alternative energy for transportation. Offer a guarantee of a whole lot of money if someone can achieve an important breakthrough. A big prize for the first man on Mars might likewise be an efficient government expenditure. Furthermore, such a prize is a win-win situation (done right). If the development doesn’t happen, no money is awarded.
