Most 3d game houses have a staff of texture artists and 3D modelers (the number of people will, of course, vary with company and game budget). The art of hand-painting textures is very much alive. While there may not be a whole lot of oils or acrylics in the studio, there are a whole lot of Photshop, Painter, and similar tools. In addition to the huge number of filters and tools available in 3D modelers such as Maya and 2D painters such as Photoshop, many companies have tool makers on staff who can write little bits of software to help get a precise visual effect. Plus, there are more grunge brushes and the like out there than most people realize.

Modern game engines have a huge array of tricks that they can use to get the right visual cues in place to make an image look more real such as fog, multiple lights, depth of field blurs, and so on. My first impression on viewing the images was that they were in-game screen shots, perhaps from a higher-resolution PC version of the game in question. The Robin Hood one looks like a 3D castle that is (possibly) in front of a hand-painted matte. The Easthaven map looks 100% like an in-game shot. What was a offline rendering activity just a few years ago (depth of field blurs, dynamic fluid flow computations, antialiasing, soft shadows, volumetric fog/smoke/dust, atmospheric effects) can now be computed directly on the video card in real time. And it's usually not just simple texturing these days; there are normal maps, gloss maps, glow maps, and potentially many other "texture" layers that can be aplied to a surface depending on the complexity of your engine.

Doing this sort of stuff yourself will mostly require familiarity with 3D modeling software and a good hand at making/painting textures to go on the models. In my experience, the third most important part of getting consistently good results is artistic ability, the second most important is recognizing when you have good results, and the most important part of getting good results is sheer bloody-minded determination to put in the endless hours of practice needed to be consistent. I can draw a box with someone else's texture applied to it and that's about the level of my competence. I am pretty familiar with the technical aspects of the process, but I lack both the talent and the persistence to be any good at it. If you love the task, you'll put in the work and call it play.