I was taught this too growing up in rural america. Did it myself at some land my grandparents had.

Best explanation I've heard for why it "works" is that when looking for places to first install pipes the location tends to be obvious or intuitive, so then years later when someone needs to find it again we naturally trend to the same rough area, pull out those stupid rod things and when they randomly cross there's a pipe there cause we're already standing in the general right spot. Get a high enough success rate and our brains start to think there is causation to the correlation.

It's called Dowsing

Dowsing is a type of divination employed in attempts to locate ground water, buried metals or ores, gemstones, oil, claimed radiations (radiesthesia), gravesites, malign "earth vibrations" and many other objects and materials without the use of a scientific apparatus.

Fascinating twist. Its like it was a subcultural mass delusion

It's because the water does not flow in "pipes" underground. It is nearly everywhere, and so you have "found" it most times.. You just don't know at what depth you will find it - until you ask your neighbor :)

There's a part of me that believes that magic/psyonics/spirit whatever intentionally and willfully does not respond to the scientific method.

Whatever entity is behind it refuses to be subjected to scrutiny and furthermore refuses to be turned into a machine with an on-off switch.

You can have your magic or you can have your proof that magic doesn't exist but you can't have proof that magic exists and magic at the same time.