As others have mentioned, they're very common (in the US) in parks and libraries, but there are also some that are "just there" on the side of the street as part of the sidewalk or path (sure, often near a park but not always).

More and more the newer ones are also "dog watering stations" which is basically a foot-operated drinking fountain with a slow-drain bowl (or just a concrete bowl with no drain).

Even parks that don't have restrooms will often have a fountain.