I just did a couple of very rough mental calculations regarding wood requirements, based upon one of my favourite tank sizes of 4 x 2 footprint. If I were doing a traditional MFK-style stand, I would be using 4 legs each consisting of 3 sections of 2x4 laminated together, and would easily do the stand using 7 pieces of 2x4 each 10feet long. This would leave me enough shorter scraps to add a couple of diagonal braces...very desirable if the stand is not affixed to the wall. This would be at height of 3 feet, with a bit of wiggle room in that dimension, up or down as desired.
Building a similar-sized stand according to your original photo would utilize approximately twice as much wood, to produce a stand which in the interest of efficiency would be about 1/4-inch narrower front-to-back than the true 24 inches. This would not matter if you are planning to put a sheet of plywood on top as mentioned.
Don't worry about splitting; if you simply pre-drill all screw holes you won't have a problem. A vertical column in each corner as you describe...it would be much easier to just use another 2x4 or even 2x2 rather than going all the way to 4x4...really isn't needed, although it might make the actual assembly process go a bit faster and smoother.
Your design would not look nearly as utilitarian as the "normal"...and it wouldn't be, because without major modifications you would be losing all the storage space underneath. But it would be easy to finish it in various ways with paint or stain, maybe in multiple contrasting colours, to get a really unique look. I'm very much a form-follows-function guy, but I've seen your fluorescent plastic plants and radioactive gravel, so I'm sure you have plenty of ideas regarding dressing it up.
I'm starting to hope you build this; I'd love to see how it goes. I might even try it myself, but only if I decide to set up a "display" tank in a finished portion of the house. Otherwise, I just couldn't abide taking up that much space and getting no storage room in return.
If you build this...please, please,
please...buy yourself a good quality metal framing square of decent size, and then take good care of it...and use it constantly throughout construction. This will protect you from frustration-induced suicide halfway through the job.