Actually, just out of the theatre, I said to myself "I wanna make sand too!!".
The problem was that at that time I was familiar only with 3DS Max and Maya and none of them had a particle system good enough to make this kind of effect (nParticles system was released two years later).
Browsing around I learned that sandman effect was done with Houdini... and that was the exact moment when I started studying this software! :)
Anyway, the sand simulator of Spiderman 3 is a proprietary tool developed internally by Sony Pictures Imageworks and is not available in Houdini.
Houdini has a fluid solver called "Sand Solver", based on both PIC/FLIP methods and specifically designed for sand simulations. After release 10, it was hidden from the interface because the FLIP method is (=should be) handled better by the new FLIP fluid solver. After some tests in which I basically added a matrix strain field and a GAS Sand Forces microsolver to a particle FLIP fluid, I was frustrated by liquid-like sand simulations.
So I decided to try the "obsolete" Houdini sand solver. Even if hidden, it's still embedded inside the software and unhiding it is pretty easy: all you have to do is to run the following commands in Houdini Textport:
opunhide Dop sandsolver
opunhide Dop sandobject
opunhide Dop sandconfigureobject
After that, the two digital assets become visible in the DOP context and you can use them just like any other voxel fluid. Here is my test:opunhide Dop sandconfigureobject
As you can see, the result is good but still a little "liquid".
Physically talking, the most realistic approach to sand dynamics would be with rigid bodies, since the grains of sand are nothing more than tiny rocks. In the other hand, using rigid bodies to simulate thousands or millions of grains is of course prohibitive in terms of calculation speed.
The problem is that the advection method (typical of all fluids) that is used by FLIP solver doesn't allow particles to suddenly stop after an impact or when trapped (like they were rigid bodies) because velocity is deviated from obstacles instead of being subtracted; in addition, the absence of grain-to-grain friction causes a wrong dissipation of forces. GAS sand forces microsolver turns off the motion of internal matter (trapped grains) allowing the particles to stack and tries to give an idea of friction between grains, but the resulting "sand" never stop sliding until it flattens almost completely.
Making (more than) some modifications to Houdini sand with microsolvers and adding a POP network to store the final position of grains and avoid the liquid motion, I've obtained the result shown in the video below:
(simulation time: less than 3 minutes)
The simulation is better (and still fast) but I want something more. In the meantime, I've built from scratch a new sand solver with the same behaviour but containerless (just like FLIP fluids)... anyway I'm still not satisfied.
As soon as I have time I'll try to build another custom solver. When it will be ready, I will update this post.