*puts on his HVAC guy hat*
What you're talking about is a geothermal heat pump system
Typically iirc, it's HDPE tubing in the ground, distributing heat to the ground in the summer , and picking up heat in the winter.
It could work for a tank system.
However, the depth of the hole is crucial. You have to get below the frost layer, or you'll run into issues in winter.
Heat always transfers from hot to cold, so a closed system would need a reservoir of tank water (the sump would work) and the geothermal pit.
The calculations required can be pretty complex.
Interested to see how this works