Semantically the closest specific tag would be historic=memorial, since it's more or less a memorial to the good deed of supplying drinking water.

In OSM terms it probably wants to be a tourist attraction, though that's a tall ask for what it is :)

You can see what it is here: https://archives.bristol.gov.uk/records/17563/1/493

No, it is a historical item and local point of interest. It serves no purpose as a water supply.