After Effects looks like the best approach.
Can I create a 3D terrain in After Effects? I heard someone said Photoshop can create 3D object, which one would be better in doing the 3D job?
Must have misunderstood you.
creating a 3D terrain in AutoCAD, then process the AutoCAD file in a Adobe software.
I get that as using AutoCAD to create the 3D terrain. Don't know if that is the most obvious choice though. For terrain creation I would personally look at Vue. See http://www.e-onsoftware.com/ and specifically http://www.e-onsoftware.com/products/vue/vue_11_infinite/
But virtual cameras, titling etc. are best done in AE, because you said:
I want to create a video
and then Photoshop is out, since it is a photo application, not a video application.