This is true for many major engineering works, at least in the west. Many rail systems, dams, tunnels, etc from the 20th century would now be impossible politically or cost prohibitive.
The pyramids may not have been realistically possible any time since they were built
Carl Sagan, in his book Pale Blue Dot, has a relevant chapter called 'The Gift of Apollo". Manned missions are exciting to the public, and are valuable for this reason - but their design requirements are often at odds with scientific objectives. I think both types of exploration are important.
In addition to stimulating culture and interest in space travel, manned missions lead to engineering breakthroughs. Both unmanned, and manned missions are important. Manned missions have stagnated, but unmanned is certainly not 'trapped within LEO' !
The main thing sensor suites can't do, though, and which manned missions are (were, would be) an ongoing proof of concept of, is colonizing space. If we don't want mankind to perish with this ball of mud in just a few billion years (at the very most), we gotta get out there. We, not just our "sensors".
In terms of societal benefits, the lunar mission had an extremely high cost for little direct benefit. The moon landing was an effort in propaganda, and an effective at that, aimed to show the world that the US was not lagging behind the USSR in terms of technology. But it would have been foolish to go on and continue to spend such a massive amounts of money, and hogging the best scientists and engineers for decades more, with hardly benefits other than ideological ones.