In ye olde times, back when we actually raised taxes to have money to build things, we built aesthetically pleasing radio towers [1] [2] (my grandpa actually worked in construction of the latter as a crane operator) that have become a beloved part of our city landscapes. No one complained about RF despite the old analog emitters running multiple megawatts of power.
Nowadays? It's usually eyesores on already ugly roofs in urban areas or metal towers looking about as pleasing as a high-voltage transmission lines.
My two cents: Maybe if we would actually invest money into making things look good, we'd get less opposition. And that is valid for a looooot of other NIMBY-plagued infrastructure as well.
[1] https://de.wikipedia.org/wiki/Fernmeldeturm_N%C3%BCrnberg