Air essentially behaves like liquid. Without pressure, it will slow down significantly through constricted spaces. You need static pressure fans when you have to push a lot of air through tight space or things that offer a lot of resistance such as heatsink, hard disk cage or water cooler radiator. Air flow is needed when you just need to push a lot of unrestricted air.
Therefore, use static pressure fans as intake fans, radiator fans and heatsink fans whereas use regular airflow fans as exhaust fans especially in a positive pressure case (where you have more intake than exhaust). If you have a tight case grill with small drilled holes, use high static pressure fan. If you have a wide spaced case grill (or you can cut it out completely), use high air flow fan.
How to differentiate between a static pressure fan and regular air flow fans?
Static pressure fan has lesser but bigger fan blade compared to regular air flow fans. The fan blades on static pressure fan has less gaps in between each other whereas regular ones has bigger gap in total. Usually static pressure fans would have 9 or lesser fans blades while the regular airflow fans would have 9 to 11 fan blades.