This is a topic that we should definitely hash out.
My own view is that moderators should almost never override the community. The reasoning behind that is that, although moderators were presumably chosen because of their (our) ability to exercise good judgment on administrative matters, a moderator is still just one user. In general, I don't see any justification for the opinion of one user to override the opinion of five others who have expressed the opposite view.
This is a community-driven website, and most of our decisions should be made by consensus. So when there is a clear disagreement between two groups of people, both with the ability to undo each others' actions (e.g. one moderator and five other users), we need to put things on hold, allow both sides to make their case, and determine which view has greater support. Once we reach a decision, everyone then needs to abide by it, but especially the moderators, since we have that unilateral power.
(In this case, that would entail starting a separate question on meta in which we decide whether the original question should be open or closed. Noldorin can present his case for closing and other people who think the question should be open can present their case for that. Perhaps I'll start that other question shortly.)
Now, there may be certain times in which it would be appropriate for a moderator to override a community close vote. I can't really think of a good example, but I just want to acknowledge the possibility that it may happen. This is probably not as much of an issue here as on Stack Overflow, though, where there are so many 10k+ users that a question can get 5 close votes even if a minority of people actually think it should be closed.
Feel free to downvote this if you don't agree.