I'm on rainwater here, our whole house is supplied by water collected off the roof and stored in a big plastic tank.
As for using it for your aquarium, the main consideration is the complete lack of mineral hardness. Depending on the fish you keep this could be a huge benefit, or something you need to remidy by dosing. For the South American fish I keep, the low mineral content is ideal. I use a bit of crushed shell in the substrate to help buffer the water against the large amount of driftwood in the tank, which keeps the pH stable around 7 and the TDS around 50-60ppm. If you are keeping fish from hard water areas liek central american or rift lake cichlids you will need to add minerals (calcium, epsom salts etc) to raise the KH/GH and TDS to a more suitable level.
The only other concern is the quality of the water coming off your roof. If your area has clean air and you keep the tank/gutters fairly clean you shouldn't have any problems.
