"Somebody didn't do the research on that," Tyson told the talk show host, making the case that if you pound your fist into a sand dune, it wouldn't actually produce a thumping sound the way it does in the film. "You can't thump sand."
Oh, this is easy. Neil, the thumping isn't for the sand its for the spice in the sand which is a near-magical substance that is tied biologically to the sandworms and when consumed by humans in large quantities lets you see into the future. Are you going to try and tell me a substance which clearly grants its user the ability to see through space-time can't be excited mechanically with thumping it on the ground?
Which means Neil is actually upset with how much scientific world building Frank Herbert did, since it confuses people like him who haven't studied sand dunes for decades.
This doesn't mention anything about it working with any kind of large impact, though. It's all about higher frequency vibrations from layers of sand moving around. It's an interesting phenomenon, but jot what is being talked about.
The resonance depends on the size of the cavity. It's conceivable that with different sand structure you could get different size resonating chambers. Plus even though a piano is tuned for higher frequency vibrations, it'll still ring when you thump on it. I'd imagine that'd be the same with these chambers.
Well, it's more than that. I think this is even mentioned in the new movies, but there's a phenomenon in Dune called "drum sand" that is a section of sand that somehow amplifies vibrations. Obviously it doesn't matter how any of this works. It's a story where, if you get high enough, you can predict possible futures. No shit it isn't realistic. No one cares.
With what Spice does to people, and the general weirdness of the spice/worm/maker life cycle, suggesting that the worms are partially fungal in nature actually makes a lot of sense!