Chennai in India is considered another success story for rainwater harvesting. It should be noted that there was much whining and hand-wringing about the costs of setting this up. But the situation got kinda bad about a decade ago, and there was an ordinance from the state government that every building must have the requisite facilities. The situation turned around within a few years, and the water situation is much much better now.
Interesting topic. The Commonwealth Scientific and Industrial Research Organisation (CSIRO), Australia has been looking into the usage of aquifers for water storage in urban areas for some time now (the most recent project was
Managed aquifer recharge and storm water http://www.csiro.au/en/Research/LWF/Areas/Resilient-cities/U... from 2010 to 2014, though I remember reading reports about some research around 15 years ago).
I think it is particularly interesting that their research is focused on urban areas, where all the concrete and bitumen results in huge runoff. This creates risk of flash flooding, wastes a useful resource and even causes problems for marine life. So if it can be stored underground in natural aquifers and utilised locally that'd be quite an improvement. Apparently the cost of treating storm water for drinking is similar to treatment of water from reservoirs. No doubt there'd be a pretty big saving infrastructure needed for distributing water over long distances as well.
Here are a few (semi-random) articles about that:
1. http://www.thealternative.in/society/i-started-to-do-rwh-in-...
2. http://www.auick.org/database/apc/apc044/apc04403.html
3. http://www.ndtv.com/india-news/tamil-nadus-water-revolution-...