I enjoyed the second part of this story a great deal more than the first, with a couple exceptions.
Reporting
I chose to create a map and look at what the water rates are across the state. I’ve said for a long time that I think McIntosh has the cheapest rates in the state — as far as I can tell. I think this remains true. I called or searched out at least one county offices, city and town halls and utilities from every county in Florida. I thought for sure some of the smaller towns in the panhandle would be lower, but I still have not found cheaper water rates elsewhere.
I also found it was fun to talk to other town clerks about their water. It stopped being fun in McIntosh a long time ago. It reminded me of how reporting on McIntosh USED to be when I first started doing it. I targeted small towns and cities and utilities for the rate comparison. I was surprised that some small towns have gone as far as requesting the water management districts do a study on how much they should raise their rates to accommodate future demand and shortages. In McIntosh, the council just raised the rates in October for the first time since 1991, and they went for the most they thought they could get away with. Nothing scientific in that decision.
On the other hand, I actually talked to staff at one town office who thought their rates were 2 cents per gallon. I had to call them back when I figured the rates. A bill for 23,000 gallons of water would have come to $420. The first woman I talked to was really angry when I questioned her about it. She transfered me to the second who confirmed that the rates were 2 cents a gallon. So, I chatted her up in another direction. It turns out they don’t really KNOW their water rates. Jimmy — the man who set up the formula in her computer — knows it. It’s .002 per gallon, by the way. And even they are higher than McIntosh.
As for the rates, I think where McIntosh loses money compared to other water providers is in their tiers and the amount included in the base rate. For instance, for $9 you can get 5,000 gallons of water in McIntosh. Other places include anywhere from zero to 3,000. The other trend that they town is behind on is charging high volume users more. Across the state, you can see in the rates that anything above 20,000 gallons a month is excessive.
McIntosh has some serious volume users. I chose May for a month to pull an average from for this graphic. It gave me a month where the residential average hit the low 20’s. It wasn’t unfair — choosing a summer month when people water a lot. I also realized why December is such a low water use month two weekends ago when the residents started dragging out their Christmas lights. No one wants to get electrocuted, so they stop watering. McIntosh’s highest residential water customer went from 151,000 gallons down to 14,000 in December. It should be noted that she and her husband won their division for the Christmas decoration contest. (It’d be totally OK to think Griswald’s, here.)
Resources
Ironically, a number of McIntosh’s high volume customers are current and former town officials. I posted a yearly usage report online without names on it and referenced it in my resources for the map. I have another one with names that I’ve used to separate the residential users from commercial users — but I can’t post it without angering the masses. Officials were unhappy that I have a copy of the report altogether. Imagine that.
The other problem I ran into with resources was how to cite the number of sources I referenced to get the water rates I used. I called the offices I couldn’t find online. But still there were 69 different water providers included in my graphic. There was no way that info would fit into the space underneath the map for me to link their websites to…
So, I thought I should put them in the graphic. Talk about easier said than done. You have to be a genius to get links to show up in a Google map graphic sourced from a Google spreadsheet sourced from an Excel spreadsheet. And I am so not a genius.
I had to use code like this in the Excel file: =CONCATENATE(”<a href=”, “http://www.clayutility.org/myhome/current_rates.aspx”, ” target=”,”_blank”, “> See rates.<a/>”)
And then loop it through the directions cell like this: =CONCATENATE(”In “, C13, “, 23,000 gallons of water would cost <br/> a single-family residence $”, “<b>”, F13, “<b/>”, “.”, B13)
Then - transfer all that up to Google docs, then into Google Maps.
Some of the trickier problems was how to handle quotation marks within HTML code in a href tags (to make links) and target_blank tags (to open the links in a new window.) I made a discovery that anything I linked opened the water sites within the window of my project rather than a new window. Anyway. The above works. But it took some playing around.
API
So all was well until I tried to load this thing live. I’d had happy times with my push pins using local drives. But when I loaded the beast live today, the Google map crashed — because live it needed an API key. (Something I had to sign up for through Google, eventually, using my Google account and giving them the addy I am running the map from.)
Then, Google crashed this afternoon. No fooling. I kinda freaked at the prospect of doing this whole thing over in another kind of map. But then the whole of Google crashed. Good times. No support. N0 blogs. No nuthin’. We’re in trouble folks, if we’re this dependent on a search engine. Well, I am, anyway.
I found some documentation that said I didn’t need an API key to use a Google map, however this wasn’t true when it came to loading a Google spreadsheet into a map. How did I get it to work?
Truth be told, I’m not sure because I was trying alot of things. I went here first to set up an API.
What I can’t tell you is whether or not I used the API key or the URL I registered in the map generator. I tried both more than several times before it shook loose and worked for me. The Google crash probably didn’t help matters.