A limit point of a set $S$ in a metric space $X$ can be either in $S$ or in $X$.
So you have this point, call it $x$. Now think of an arbitrary distance (using your distance metric, $d(x,y)$). Call it $r$. If $x$ is a limit point, then it means that for ANY $r>0$, you will be able to find some other point $y \in S$ such that $d(x,y) <r$. You can make $r$ arbitrarily small, but within the distance $r$, our point $x$ will not be alone. Its neighborhood will never be empty: a neighborhood is just the set of points that are within the distance $r$ from $x$.
This essentially means that for any neighborhood of $x$ that we select, there is an infinite number of points in the neighborhood, 'accumulated around' x. Example:
Consider the set $\mathbb{R}^1$, the real line. This is our metric space with the usual Euclidean Norm for distance. Now consider: $$ S = \{1 + \frac1n \mid n \in \mathbb{N}\}$$
This set $S$ has a limit point at $1 \in \mathbb{R}^1$ (and only at 1) because for any $r>0$, we can always find some $n \in \mathbb{N}$ such that $1+ \frac1n < 1+r$, so that there is some $s \in S$ with a distance to $1$ that is less than $r$.
Intuition: you can then expand this notion: in $\mathbb{R}^2$, we think of $r$ as a radius, and the neighborhood is a circle if you draw the set. In $\mathbb{R}^3$, $r$ becomes a sphere (ball), etc...