Is overfitting always a bad thing? DNN can be used to recognize pictures. Great. For that usage, it's better if they are somewhat flexible so as to recognize as cats even cats that are not on the pictures on which they trained (i.e. avoid overfitting). Agreed.

But when one uses NN as a replacement for numerical tables in an Air Collision Avoidance System (ACAS), it is primarily to reduce the "required storage space by a factor of 1000". For this usage, what we want from the NN is to say "take a slight left turn" or "turn right hard" if another ship comes slightly close on the right or rapidly close on the left, respectively. 

For this usage, where the answer is much simpler than recognizing a cat, isn't overfitting a good thing? What would overfitting "look like" in this case and why would it be bad ?

This question somewhat relates to this one, where a general idea seems to be "Machine Learning is used for intractable things, you don't need ML for tractable things". And while it is quite correct that ACAS can be implemented without NN, I wouldn't call NN "useless" for ACAS, because a factor 1000 reduction in required space will always come in handy.
Typically the ramification of overfitting is poor performance on unseen data. If you're confident that overfitting on your dataset will not cause problems for situations not described by the dataset, or the dataset contains every possible scenario then overfitting may be good for the performance of the NN.