I think there are two elements at play: one, whethere the sound is going to be loud (or piercing) enough to be heard a few meters ahead of your bike, with parks, traffic, and countryside having different levels of noise in each environment; and two, the nature of the sound the horn emits, with car-like horns being more likely to elicit the right behavior (get out of the way), and bike-like horns being more likely to be ignored (just a small, slow-speed vehicle coming).

I think the challenge is to find a device that has different sound levels (e.g., countryside, park, traffic), and different tones (regular bell, car horn).

The frustrating thing is that the technology is all there: there are electronic bell that simulate a siren, an emergency vehicle horn, a cell phone beep, etc. No manufacturer seems to have put the right tones and noise levels together in one device yet.