Quite true, however, I think we should care about the metric for gameplay as well as realism. When we look at a game screen, we intuitively judge distances based on the Euclidean metric, and thus it goes against our intuition to be able to travel in the same amount of time from the bottom-left corner to the top right as to the top-left.
This shows up in other gameplay idiosyncrasies as well. For example, city cultural boundaries are clearly low-resolution approximations of a circle. Since the culture boundary determines the city's LOS, the city tends to have less warning of units approaching on a diagonal.
I agree that it's a bit unintuitive that traveling diagonally lets you move 'further' for the same cost; but I on the small movement scales I think the approximation works fine. Other effects, such as different terrain costs, play a much larger role most of the time in Civ4.
In a different game that I've played, moving diagonally costs 1.5, and it rounds up. So, for example, if you have 2 movement points then you can move east then north-east, but you can't move north-east and then east. If you have 3 movement points then you can move north-east twice, or east twice and then north-east.. and so on. I think that system is pretty fair, and it worked well for that particular game, but I wouldn't like to have such a system in Civ4 because I think it would be a bit fiddly - it would require more micromanagement and such, and so it would just be a distraction from the core game-play. In that sense, I think the simplicity of the cheap diagonal moves in Civ4 is good.
You're right about the impact cultural borders thing. It take fewer moves in enemy territory to approach a city diagonally rather than horizontally or vertically; and that's a bit weird. I guess that comes about because the cultural borders don't follow the same distance rules (ie. diagonal isn't cheap for culture like it is for movement; otherwise we'd get square cultural borders instead of round cultural borders).
Hexes don't have any of these diagonal cost issues, so I suppose they are superior in that sense. But I still find it easier to read the map with squares; maybe just because that's what I'm use to. With the square grid, I find it easier to see how close things are, and what the quickest path through the terrain is. With hexes, when even a direct path on flat land is a wiggly line, I find it a bit harder to read the map. -- In my mind, all of these issues are of pretty low importance in a Civ4. I don't have a strong preference for hexes or squares.
In an octagonal grid, intuitively you'd think movement in all directions is equal, but it isn't...diagonal moves between octagons are separated by a square.
So what if we made the squares represent a kind of penalty for units passing through them? Would a percentage of unit damage be too harsh?
An octagonal grid is effectively the same as the square grid with diagonal movement. If you want, you could have that 1% penalty for diagonal movement on a square grid; and you could chop the corners off the tiles so that they looked like octagons... I think I'd prefer to just leave them as squares.