I'm the sort of mathematician who works really well with elements. I really enjoy point-set topology, and category theory tends to drive me crazy. When I was given a bunch of exercises on subjects like limits, colimits, and adjoint functors, I was able to do them, although I am sure my proofs were far longer and more laborious than they should have been. However, I felt like most of the understanding I gained from these exercises was gone within a week. I have a copy of MacLane's "Categories for the Working Mathematician," but whenever I pick it up, I can never seem to get through more than two or three pages (except in the introduction on foundations).
A couple months ago, I was trying to use the statements found in Hartshorne about glueing schemes and morphisms and realized that these statements were inadequate for my purposes. Looking more closely, I realized that Hartshorne's hypotheses are "wrong," in roughly the same way that it is "wrong" to require, in the definition of a basis for a topology that it be closed under finite intersections. (This would, for instance, exclude the set of open balls from being a basis for $\mathbb{R}^n$.) Working through it a bit more, I realized that the "right" statement was most easily expressed by saying that a certain kind of diagram in the category of schemes has a colimit. At this point, the notion of "colimit" began to seem much more manageable: a colimit is a way of gluing objects (and morphisms).
However, I cannot think of any similar intuition for the notion of "limit." Even in the case of a fibre product, a limit can be anything from an intersection to a product, and I find it intimidating to try to think of these two very different things as a special cases of the same construction. I understand how to show that they are; it just does not make intuitive sense, somehow.
For another example, I think (and correct me if I am wrong) that the sheaf condition on a presheaf can be expressed as stating that the contravariant functor takes colimits to limits. [This is not correct as stated. See Martin Brandenburg's answer below for an explanation of why not, as well as what the correct statement is.] It seems like a statement this simple should make everything clearer, but I find it much easier to understand the definition in terms of compatible local sections gluing together. I can (I think) prove that they are the same, but by the time I get to one end of the proof, I've lost track of the other end intuitively.
Thus, my question is this: Is there a nice, preferably geometric intuition for the notion of limit? If anyone can recommend a book on category theory that they think would appeal to someone like me, that would also be appreciated.
Best Answer
I pick up your remarks about sheaves. Indeed, the sheaf condition is a very good example to get a geometric idea of a limit.
Assume that $X$ is a set and $X_i$ are subsets of $X$ whose union is $X$. Then it is clear how to characterize functions on $X$: These are simply functions on the $X_i$ which agree on the overlaps $X_i \cap X_j$. This can be formulated in a fancy way: Let $J$ be the category whose objects are the indices $i$ and pairs of such indices $(i,j)$. It should be a preorder and we have the morphisms $(i,j) \to i, (i,j) \to j$. Consider the diagram $J \to Set$, which is given by $i \mapsto X_i, (i,j) \mapsto X_i \cap X_j$. What we have remarked above says exactly that $X$ is the colimit of this diagram! In a similar fashion, open coverings can be understood as colimits in the category of topological spaces, ringed spaces or schemes. It's all about gluing morphisms.
Now what about limits? I think it is important first to understand limits in the category of sets. If $F : J \to Set$ is a small diagram, then we can consider simply the set of "compatible elements in the image" of $F$, namely
$X = \{x \in \prod_j F(j) : \forall i \to j : x_j = F(i \to j)(x_i)\}$.
A short definition would be $X = Cone(*,F)$. Observe that we have projections $X \to F(j), x \mapsto x_j$ and with these $X$ is the limit of $F$. Now the Yoneda-Lemma or just the definition of a limit tells you how you can think of a limit in an arbitrary category: That $X$ is a limit of a diagram $F : J \to C$ amounts to say that elements of $X$ .. erm we don't have any elements, so let's say morphisms $Y \to X$, naturally correspond to compatible elem... erm morphisms $Y \to F(i)$. In other words, for every $Y$, $X(Y)$ is the set-theoretic limit of the diagramm $F(Y)$. I hope that this makes clear that the concept of limits in arbitrary categories is already visible in the category of sets.
Now let $X$ be a topological space and $O(X)$ the category of open subsets of $X$; it's an preorder with respect to the inclusion. Thus a presheaf is just a functor $F$ from $O(X)^{op}$ to the category of sets (or which suitable category you like). Now open coverings can be described as certain limits in $O(X)^{op}$, i.e. colimits in $O(X)$, as above. Observe that $F$ is a sheaf if and only if $F$ preserves these limits: If $U$ is covered by $U_i$, then $F(U)$ should be the limit of the $F(U_i), F(U_i \cap U_j)$ with transition maps $F(U_i) \to F(U_i \cap U_j), F(U_j) \to F(U_i \cap U_j)$, i.e. $F(U)$ consists of compatible elements of the $F(U_i)$, meaning that the elements of $F(U_i)$ and $F(U_j)$ restrict to the same element in $F(U_i \cap U_j)$. Thus we have a perfect geometric example of a limit: the set of sections on an open set is the limit of the set of sections on the open subsets of a covering.
Somehow this view takes over to the general case: Let $F : J \to Set$ be a functor. Regard it as a presheaf on $J^{op}$, and the map induced by $i \to j$ in $J^{op}$ as a restriction $F(j) \to F(i)$. Also call the elements of $F(i)$ sections on $i$. Then the limit of $F$ consists of compatible sections. Since I've been learning algebraic geometry, I almost always think of limits in this way.
Finally it is important to remember that limit is just the dual concept of colimit. And often algebra and geometry appear dually at once, for example sections and open subsets in sheaves. If $(X_i,\mathcal{O}_{X_i})$ are ringed spaces and you want to find the colimit, well you can guess that you have to do: Take the colimit of the $X_i$ and the limit of the $\mathcal{O}_{X_i}$ (pullbacked to the colimit).
This is not correct. The reason is that the index category can be rather wild and colimits in preorders don't care about that. In detail: Let $U : J \to O(X)^{op}$ be a small diagram. Then the limit is just the union $V$ of $U_j$. Thus $F$ preserves this limit iff sections on $V$ are sections on the $U_j$ which are compatible with respect to the restriction morphisms given by $U$. If $J$ is discrete and $U$ maps everything to the same open subset $V$ of $X$, then the compatible sections are $F(V)^J$, which is bigger than $F(V)$.
I think this book is still one of the best introductions into category theory. It can be hard to grasp all these abstract concepts and examples, but it gets easier as soon as you get input from other areas where category theoretic ideas are omnipresent. Your example about gluing morphisms illustrates this very well.