Well, as a player I never considered defining a trade route or "Go" unit's command as an automation... These are like the basic interface stuff.
Yes quantity of MM can cause headaches. Again back to Civ2 in the early game it was nothing to manually manage 20 workers. But in the later game when you could have a hundred workers it was an absolute nightmare. Choosing one of the automation commands is what I'm talking about with "optional automation". In the end game you want to focus on anything but workers, so you set and forget about them. (Note here: the quality of the automation should not detract from the actual point of automation, specially in Civ's worker case haha). As a GOOD example of optional automation, look at Colonization's (the original) custom house and wagon links. The player manually transports good via wagon and ship from the New World to Europe. You can set a trade link for a wagon which automatically moves goods from one place to another (ie: from inland to a harbor). Then using a custom house the harbor automatically sells those goods to Europe. This is GOOD optional automation which eliminates mundane boring MM.
Maybe let's divide it like that:
- automated carrying an order WITHOUT any decisions involved - pathfining for a unit, and all forms of setting "source" and "destination" for a unit/transport (colonization's wagon links)
- delegating to AI tasks that involve decisions - city governor, automated worker
If we talk about automating things that do not involve decisions of any kind I fully agree. These are always good, no exceptions.
If we talk about giving to the hands of AI any real decisions, then it's a different story. I would divide these in 2 kinds. Trivial decisions, like worker automation, well, it's the necessary evil, I don't like it, it's a poor design but it does not bother me too much. The second is strategic decisions, like what to build in a city. That's the pure evil. It takes away the decisions I, the player, should make. It means there is something really wrong with the game, it starts playing the game for me. The second one is very hard for me to accept.
As for Civ's workers, I agree with what you said about Civ2, but there is an interesting thing they did with these in Civ4. First, they gave workers movement of 2, which was a brilliant decision. Now you need much fewer workers because you were not wasting time for movement (most of the time workers move on undeveloped high movement cost terrain or need to cover long distances). It made it both faster, more efficient and less troublesome because you could always move and issue an order in the same turn. But even more genious decision is what they did with the terrain improvements. They made making improvements FUN In Civ 1-3 you were just building roads and irigation on flatlands and mines in mountains. Not any real decision I would say, just a chore... But in Civ 4 it looks completely different. You actually think what to build on what terrain (well, maybe it's not the most important strategic level decision and maybe it's not that hard decision, but it's still a real and valid decision to make). You click on the city, check what it has, what it needs, on what it specializes and then make a decision. Then once you made up your mind you recall that in 4 turns you will have technology ready that will allow making a certain improvements better, so you go and check techs screen and start thinking if it's worth switching to another improvement type. And finally, once you confirmed and really are ready to go you recall you planned to change an economic policy to "planned economy" which will change yet another improvement, so you go to the internal politics screen and reconsider once again
They did a great thing here, they didn't focus on making better automation for workers, they focused on making the decisions involved FUN and meaningful so the player won't WANT to use any automation at all. They haven't cured the symptoms, they stroke in the core of the problem.