Well, you have to ask yourself. What actually makes a good film, what is art? Is it an intriguing story, characters with depth and great writing or is it huge budget films with mind blowing visuals? How do you define what makes a good film. It's very rare you get the two things mixed together, exceptions like you pointed out though, district 9, Terminator 2 (special effects still look good to this day as well) etc.
I can appreciate and enjoy both types, providing that the story/dialogue/character's aren't THAT BAD.
Well have you seen the new transformers? It advertises a war between humans and the decepticons, but that's not actually how it is at all. The entire second half of the film is just all out robot warfare.
The robots take up most of the second half of the film.
Off topic but speaking about films with great CGI and great characters and story. KUNG FU HUSTLE. If you haven't seen this film yet, then watch it ASAP. It's in my top 10