It might be interesting to describe the standard embedding in the following way. Consider the irreducible 5-dimensional representation of $SO(3)$, given by the harmonic quadratics, and restrict this action to $S^4$. Then an explicit computation shows that a generic orbit is $SO(3)/V_4$, where $V_4$ is the diagonal subgroup of $SO(3)$, and if $\pi: SU(2) \to SO(3)$ is the projection, $\pi^{-1}(V_4) = Q_8$, to use Steve D's notation. Therefore a generic orbit is $S^3/Q_8$.
The two exceptional orbits are $SO(3)/O(2) = \Bbb{RP}^2$, again this follows from explicit computation. It is altogether a pleasant exercise. One of these orbits is the image of the standard embedding.
Now it follows from general theory (this is in Bredon's book on transformation groups, if I recall) that if $M$ is a $G$-manifold where the generic orbit is codimension 1, then $M/G$ is a 1-manifold, possibly with boundary; the boundary points then correspond to the exceptional orbits, and the neighborhood $(1-\epsilon, 1]$ of a boundary point corresponds to a tubular neighborhood of the exceptional orbit.
Because $S^4$ is compact and certainly is not a fiber bundle $SO(3) \to S^4 \to S^1$, we see that $S^4/SO(3) \cong [0,1]$, with two exceptional orbits; the neighborhood of either exceptional orbit has boundary isomorphic to the generic orbit $S^3/Q_8$.
To say a little more...
The neighborhoods of these exceptional orbits are diffeomorphic to a rank 2 bundle over $E \to \Bbb{RP}^2$ with $\det(E) \cong \det(T\Bbb{RP}^2)$, so that the total space of the bundle and its subset of norm 1 are both canonically oriented; these are classified by elements of $H^2(\Bbb{RP}^2; \Bbb Z_w) \cong H_0(\Bbb{RP}^2;\Bbb Z) = \Bbb Z$ (cohomology with twisted coefficients, I am following Poincare duality here). Now it happens to be the case that $k \in \Bbb Z$ here represents the bundle that pulls back to an oriented bundle $E' \to S^2$ with $e(E') = k$.
So one (not very explicit...) way to describe this neighborhood is as the bundle with twisted Euler class $4$. An alternate way to describe it is as the quotient of the bundle $E \to S^2$ with Euler class 4 by a lift of the antipodal action.
That is everything I know about this $SO(3)$-space.