The vast majority of written science fiction doesn't need a lot of special effects.

There are almost uncountably many stories that could be transferred to television or film at no more cost than any other drama. Take Clifford Simak's Way Station for instance, most of Ray Bradbury's stories, J. G. Ballard, and dozens or hundreds more. Even stories with a grand idea and scope like Clarke and Baxter's Light of Other Days hardly need much in the way of complex sets or difficult special effects.

Why is it that science fiction in video form always has to be space opera, alien invasions and so on?

If we are going to go for special effects let's not waste them on more space cowboys. Let's have Charlie Stross's Accelerando and Saturn's Children or Ken McLeod's Sky Road, etc.