Looks like an exciting camera! The tilt/swivel LCD with live view should make this camera the #1 macro camera in the entire Nikon line, and therefore an attractive 2nd or 3rd body even for people with a bag full of pro equipment.
Doesn't appear to replace the D40 though, which everyone thought it would. If this is priced between D60 & D90, then D40 & D60 maybe slide down in price, but still have their place.
A more interesting question is, where does this leave the D90? It's not that D90 has more features, just different features. To a traditional photographer, D90 offers a focus motor, DOF preview, exposure bracketing, etc, none of which means squat to a new buyer. The D5000 tilt/swivel display puts it in a class by itself, probably more desirable than D90, and the new AF-S Nikkor primes (which were a long time coming) really reduce the importance of the AF motor.