What is a foliation and why should I care?
Without any disrespect, let me say that I find it incredible that someone naturally cares about non-commutative geometry but needs convincing about actual geometry (this just goes to highlight that there is a wide variety of ways of thinking in mathematics). I would need convincing the other way around (e.g. How are C* algebras relevant in foliation theory from the geometric point of view?).
From the point of view of someone interested in geometry, foliations appear naturally in many ways.
The most basic way is when you consider the level sets of a function. If the function is a submersion you get a non-singular foliation, but this is rare. However every manifold admits a Morse function and the theory of Morse functions (which can be used for example to classify surfaces, and to prove the high dimensional case of the generalized Poincaré conjecture) can be seen as a special (or maybe as the most important) case of the theory of singular foliations (where the singularities are pretty simple).
Another natural type of foliation is the partition of a manifold into the orbits of the flow determined by a vector field. Again the simplest case, in which the vector field has no zeros, is rare but yields a non-singular foliation (with one-dimensional leaves). However, already in this case one can see that the leaves of a foliation can be recurrent (i.e. accumulate on themselves) in non-trivial ways (the typical example is the partition of the flat torus $\mathbb{R}^2/\mathbb{Z}^2$ into lines of a given irrational slope).
A notable fact generalizing the above case (the result is in papers of Sussmann and Stefan from the early 70s) is the following: Consider $n$ vector fields on a manifold. For each point $x$, consider the set of points you can reach using arbitrary finite compositions of the flows of these vector fields. The partition of the manifold into these "accessibility classes" is a singular foliation (in particular each accessibility class is a submanifold).
Hence foliations appear naturally in several types of "control problems" where one has several valid directions of movement and wishes to understand what states are achievable from a given state. This point of view also gives a nice insight into Hörmander's theorem on why certain differential operators have smooth kernels (there's a nice article by Hairer explaining Malliavin's proof of this theorem). Essentially the Hormander bracket condition means that Brownian motion can go anywhere it wants (i.e. a certain foliation associated to the operator is trivial).
Another way to obtain motivation is to look at history (I remember reading a nice survey which I think was written by Haefliger). In my (unreliable) view, the first geometric results (so I'm skipping Frobenius's theorem) in foliation theory are the Poincaré-Benedixon and Poincaré-Hopf theorems both of which can be used to show that every one-dimensional foliation of the two-dimensional sphere has singularities.
Hopf then asked in the 1930's if there exists a foliation of the three dimensional sphere using only surfaces. The first observation, due to Reeb and Ehresman is that if one of the surfaces is a sphere then you cannot complete the foliation without singularities. They also constructed the famous Reeb foliation and answered the question in the affirmative.
Since then there has been a whole line of research dedicated to the question of which manifolds admit non-singular foliations. In this regard, the main Theorem is due to Thurston who (in the words of an expert in the theory) came around and "foliated everything that could be foliated".
But there are other lines of research. For example, I know that there is a certain subset of algebraic geometry dedicated to trying to understand the foliations of complex projective space which are determined by the level sets of rational functions of a certain degree.
Also, whenever you have an action of the fundamental group of a manifold there is a natural "suspension" foliation attached (suspensions are considered the "local model" for a general foliation and are hence very important in the theory). This point of view sometimes has given results in the current area of research known as higher-Teichmüller theory (where basically they study linear actions of the fundamental group of a surface).
And of course, when one has an Anosov, or hyperbolic, diffeomorphism or flow (for example the geodesic flow of a hyperbolic surface), there are the stable and unstable foliations which play a role for example in the famous Hopf (not the same Hopf as before) argument for establishing ergodicity.
Oh, and I haven't even mentioned the special place that foliations occupy in the theory of 3-dimensional manifolds. Here there are many results which I can't say much about (but I've heard the book by Calegari is quite nice). Maybe a basic one is Novikov's theorem which basically proves that the existence of Reeb components is forced for foliations on many 3-manifolds.
And (I couldn't resist adding one last example), there are also foliations by Brouwer lines, which have recently been used (by LeCalvez and others) to prove interesting results about the dynamics of surface homeomorphisms.
TLDR: Foliations occur naturally in many contexts in geometry and dynamical systems. There may not be a very unified "Theory of foliations" but several special types have been studied in depth for different reasons and have yielded insight or participate in the proof of important results such as the Poincaré conjecture and Hörmander's bracket theorem. For this reason mathematicians have taken notice and singled out foliations as a basic object in geometry (there have even been significant efforts in producing a couple of nice treaties trying to give the grand tour, for example the books by Candel and Conlon).
Probably there are many reasons why people care about foliations, but for someone coming from operator algebras one of the main reasons is the connection to von Neumann algebra theory. In brief, every foliation of a smooth manifold has an associated von Neumann algebra and interesting properties of the von Neumann algebra are reflected in geometric properties of the foliation. The von Neumann algebra is a factor if and only if the foliation is ergodic, for example. You can get examples of factors of all types by this construction and the geometric aspect of foliations is especially helpful in understanding the type III case. The modular automorphism group has a straightforward geometric interpretation, and so on. A good reference is "Operator algebras and the index theorem on foliated manifolds" by H. Moriyoshi.
Here is another reason about why people care about foliations: If you care about dynamical systems, you should care about foliations.
For instance, if you have a nowhere singular vector field on a closed manifold, it defines a 1-dimensional foliation, the leaves are the orbits of the flow associated to the vector field. Studying 1-dimensional foliations is (almost, modulo orientation issues) the same as studying nonsingular vector fields. The only thing is that when you think in terms of foliations, you forget the parametrization of the orbits.
If you have a locally free action of a connected Lie group on a closed manifold, again you have an associated foliation by the orbits. Here locally free means that the stabilizer of any point in the manifold is a discrete subgroup of the Lie group. The dimension of the leaves will be the dimension of the Lie group, this generalizes example 1. There are already interesting things going on for actions of the Lie group $R^2$ (i.e., pairs of commuting vector fields).
Even if you are only interested in flows or diffeomorphisms, there are sometimes natural foliations associated to them. For instance, the stable/unstable foliations of an Anosov flow or diffeomorphism.
As for the mental picture of what a foliation is, well there are probably plenty of pictures online. You should think of a partition of your manifold which is locally nice (like a "mille-feuille" or like a product $R^{k}\times R^{n-k}$) but globally complicated (leaves could be dense or their closure could be transversally a Cantor set, etc.).