>> For one, it is sad to know that our closest celestial body will never be much more than a tourist curiosity and/or commercial mining operation
Well, hold on there. A free surface atmosphere is not necessarily an absolute requirement for even quite large scale settlement of the Moon. Because it is so close, we may be able to ultimately have millions of people living there. It's so easy to get to, cosmically speaking, that it remains quite a good candidate for becoming a decent colony. We would have to put lots of enclosed structures up, either on or buried into the surface, but the list of possible productive activities available on the Moon is probably bigger than you think.
For a start, the lack of atmosphere makes the Moon an attractive place to build observatories. Not as good as deep space, but vastly better than Earth, with its' thick soupy, light-polluted gunk. Low gravity may well lead to novel manufacturing processes, especially for pharmaceuticals and materials. No doubt several branches of the sciences will want to take advantage of the unique research opportunities the Moon will present. It is possible that facilities on the Moons surface could become the major manufacturing centres for spaceship production (once we've actually gone unequivocally into space), although that would probably require a Beanstalk on the Moon for getting raw materials down, and finished components up to space-based construction yards.
Anywhere else in the Universe, such an unprepossessing lump of airless rock would be of little interest to us, but right there...