The definition probably only seems fiddly if you haven't seen it (or related) definitions before. What is says is the following: a map $p: Y \to X$ is a covering map if $p$ locally looks like the projection from $X \times \text{ a discrete space} \to X.$
A little more precisely: each point $x \in X$ has a neighbourhood $U$ such that the map $p^{-1}(U) \to U$ is isomorphic to a projection $U \times \text{ a discrete space} \to U.$
This kind of property of a map --- that locally on the target it looks like the projection from a certain kind of product --- is very common in topology and geometry, and underlies the fundamental notion of a fibre bundle. Covering spaces are perhaps the simplest example, since they are fibre bundles with discrete fibres. Fibre bundles of all kinds appear everywhere, and so it is not so much a question of asking what they are useful for, but rather, of identifying a ubiquitous property and giving it a name.
Another, more global, way to describe covering spaces is as follows: if a discrete group $\Gamma$ acts on a space $Y$ in such a way that each point has a neighbourhood $U$ such that the orbits $\gamma U$ are distinct for distinct elements $\gamma \in \Gamma$, then the quotient map $Y \to Y/\Gamma$ is a covering map (i.e. $Y$ is a covering space of $Y/\Gamma$).
Since group actions on spaces are pretty ubiquitous, this gives some indiction of why covering maps might be commonly encountered in topology. (The basic example is $\Gamma = \mathbb Z$ acting by translation on $Y = \mathbb R$, with the cover being $\mathbb R/\mathbb Z$, which is a circle.)
Finally, if you begin with space $X$, in order to construct covers of $X$, you have to "unwind" certain directions in $X$. Thus investigating covering spaces of $X$ is the same as investigating the extent to which the various directions in $X$ are "wound up".
E.g. in the circle there is just one direction, and unwinding it, you get the covering space $\mathbb R$. In $SO(3)$ there is one direction which is wound up, and unwinding it gives $SU(2)$. Often this "unwinding" can be thought of in a physical way: e.g. imagine that you are walking around a stadium, and measuring the distance you have travelled as you walk. When you get all the way around, you are back where you are started (the stadium is a circle), but your distance travelled isn't at zero (it's at 400 metres, say). The numerical distance travelled "unwinds" the circle of the stadium into the line $\mathbb R$.
E.g. in $SO(3)$, the "belt trick" mentioned by Georges allows you to "unwind" a rotation to get an element of $SU(2)$. (And when you do it twice, you get back where you started --- unlike in the case of the stadium, where your distance travelled never resets to zero; so here you see the difference between a double cover, like $SU(2)$ over $SO(3)$, and an infinite cover, like $\mathbb R$ over $\mathbb Z$.)