I for one am always excited to dive new places locally. Sure, we have a lot of lakes and rivers with poor visibility, but not all. I think we tend to gravitate towards warm water with good visibility for ease of diving, comfort and the variety of aquatic life that it offers. Prepared artificial reefs with swim throughs are hard to pass up.
However, local diving, depending on where you are from, can be a rewarding experience. I dove Lake Winola and there is still a lot yet to be discovered there. There are bottles over 100 years old along with artifacts from that time too. You will find propellers, old anchors, huge sunken timber and tons of lures. Yes, local diving can be cold and can have limited visibility but that is often what makes it challenging and gives you the opportunity to find things of the past. In our area, Lake Wallenpaupack has great potential for artifacts to be found as it is a man made reservoir, Harveys Lake is the largest natural lake in PA, Chapman Lake is spring fed and although very cold, has great visibility and good history, the Delaware River is loaded with things to find, Crystal Lake has a great history, etc, etc. I can go on and on.
So I would start looking right in your own back yard because you never know what you will find.
If we can just get the lakefront property owners to allow us more access without being worried about liability, more local diving would be possible.