Look forward to seeing it Tom!
I think you'd be surprised though, as I said (probably earlier in this thread), the AFC wimbledon stadium which is far from ideal given the constraints we've had to work with, is on a footprint similar to that of the existing Pittodrie.
Essentially, to the west of the land there is an existing set of substations - no access, to the south and east and part of the north is part of the master plan (i.e. residential) as well as other tenancies, however the club had very little say and have had to deal with what they have been given. The planners have criticised the master plan (Sheppard Robson) as it doesn't allow the stadium enough space to breath. I am certain that we could redevelop Pittodrie, however whether the costs would allow that to happen is a whole different thing. Neighbour issues as well would play a role, but it's certainly not impossible. The above is for the 20K final solution, whereas the first phase would be 11K (1 permanent stand and 3 temporary, 1 being standing).
In terms of dealing with the public / housing frontage, the stadium will use a different set of materials which would usually be associated with a stadium for this very reason. It'll be interesting to see how it turns out after a few years.