This isn't quite what happened with the Moon landing at all. Even throughout the Apollo program, it was relatively unpopular. A large minority were exceptionally happy about it, but you have to remember this was also the era of Vietnam, race politics, and more. And that led to a lot of opposition to things like spending billions of dollars on space that could be spent on [x]. Like today. But what happened is that after Nixon defacto cancelled the space program in 1972, support for space began to grow dramatically. It turns out that people looking back didn't quite realize what they had, until it was gone.
I'm sure there was also the gradual realization, conscious or not, that space is also one of the very few ventures where you get out of it what you put into it. Spend $10 billion on SpaceX, and we'll have ships headed to Mars to establish the first infrastructure of human civilization there. By contrast you can't just buy your way to a good education system, or spend away social problems. Spend it on most other things and you're going to see a relatively negligible positive impact. Spend it on our favorite way to waste money (and with orders of magnitude larger sums), geopolitics, and you'll likely see an overall negative impact.
I don't really see NASA playing a meaningful role in the future of space. Their role in the present, outside of funding, is already quite negligible. They lack consistent leadership/direction, and are driven by congress - who is driven by graft. The SLS (NASA's new rocket - made by Boeing/Lockheed) alone has hoovered up tens of billions of dollars, to one day hope to construct a rocket that already isn't competitive against a Falcon Heavy. At the same time SpaceX is constructing a ship which will revolutionize space, again. China has already designed, built, and launched a new space station into space (as ours continues to deteriorate), and so on. The systems and motivations which drove NASA to epic success in the 60s simply no longer exist in our public sector.
---
The other things sound reasonable, but seem largely driven by a lack of knowledge - which I am not directing at you, but the authors in the podcast you linked. For instance you don't need to live deep underground to protect from radiation. One of the best shields against radiation is hydrogen. So one of the ways we'll likely protect against space weather on the way to Mars is by using the water storage on the ship as a shield. On Mars igloos, as odd as it sound, would make beasts of cheaply constructible radiation proofed housing, for but one example. You'd also conveniently be right next to basically infinite sources of water.
I'm happy to get into the other stuff as well, but this post is already turning into a novel!