The game of Civilization IS a game about details. The key is that the details matter more at the beginning, and less at the end.
For example, in the Civ franchise as it's always existed, there's nothing to stop a player from deciding each and every tile that their population heads work in the city-radius. But most players don't. And the players who do, they only do so at the beginning of the game, or to speed up an important wonder, and so forth. Why not all the time? Because there's no reason to -- at a certain point, automation works just as well. And if they DO micromanage those population heads, they're probably not the kind to complain about it ANYWAY.
NOW let's talk about workers and improving your tiles.
The only time you're especially concerned with the "global needs" of tile improvements is at the beginning of the game. Early in the game, you might be able to look at your 6 or 7 cities and determine that it's most important to build a road between two of them, instead of just letting the AI automatically go through its priorities.
The key is that after you're done with the basics, "where to go" becomes arbitrary -- and automation becomes roughly equivelent to doing it yourself. After scanning 100 tiles, players would quickly realize this, and move to a higher level of visionary work. If they don't absolutely automate, they might put 80% of their workers on auto-irrigation, and 20% on auto-roads. One thing they won't do is scan the entire landscape looking for the 5 perfect squares to work on, because there are no wrong answers. (And if they DO scan everything, then they must be having a good time, and why should we stop them?)
The problem with the current system is manifold compared to this more abstracted system. But one important deficit with the old system compared to the new one: you can't stop when you're not having a good time, because you lose too much by automating, and waste valuable time and energy on the actual movement of workers if you don't automate. It's a catch 22 with no way out.