First off, I live in Phoenix, AZ. This is desert here. I live in an area where people mostly have natural desert landscaping rather than lawns.
Now, this will sound really odd to folks who live elsewhere, but the house I'm in now does not get "cold" water in the summer. I can literally shut off my hot water heater and take a shower just from the cold water coming in from the city water line. The so-called "cold" water temp is somewhere over 110 for most of the summer. It's fine for most of the rest of the year, just not from May thru Sept.
I've lived in different parts of the city throughout my life, and this is the hottest "cold" water I've ever had to deal with. I think a big part of it is because the ground in this area is quite rocky and sandy, not much solid dirt. And it's dry as a bone.
Where you've got a water main running under yards that have lawns that are watered regularly, the water seeps into the ground and cools the ground and whatever pipes are there. Hardly anybody in this area has lawns, certainly nobody along the route where my water main comes from.
Short of putting a 50 gallon water cooler into a fridge (the opposite of a water heater), I'm wondering if anybody has any ideas about how I might rig up something that could cool the water coming into the house by 20-30 degrees?