Finding the right thing to start with is probably the biggest hurdle to overcome in mapping. When I was still into mapping, it took me weeks to figure out WHAT I wanted to build. And then it took me days to figure out what made a level realistic and funny
I guess the problem with mapping these days is that the complex, realistic nature of current engines means you need to put in extreme effort to make your map look convincing, or otherwise it will look like a simple blocky Quake II world. Thinking of all these details you need to put in can be hard. But the guys who do it right can achieve terrific results with the tools we have today.
I'm still thinking about getting the SDK, remembering all my mapping skills from ages ago, and get to work on a single-player map. I'm just afraid I won't be creative enough to even start!