Turrets are useless in any situation other than MAYBE dropping a dozen of them around a starbase if you're TEC or Advent. There's also a trick that allows you to colonize super fast with Vasari, by colonizing a planet and quickly ordering a turret and a repair bay built on the other end of the gravwell. That way you can send your Egg to another planet while the turret handles the handful of rebels still alive.
Turrets are an essential component of quick early expansion of any race in a reasonably spacious map, combined with a colony cap and a large fleet of combat scouts.
Outside of colonization, well-placed turrets have also actually decided the game for me a few times (as well as being the prime cause for at least one of my losses). If the enemy isn't spamming LRF or is Advent, placing a couple of turrets around your frigate factory(ies) can be extremely useful if the enemy attacks - turrets are quite durable and do a very large amount of damage, especially to light frigates and capital ships. If you have a turret or two around your frigate factory and force the enemy to focus on them instead of your factory, that can give you just the time that you need. In particular, defending with turrets combined with carriers can be extremely effective against those who simply love their Heavy Cruisers. (cough Sire cough)
Now, most players don't play Advent and focus on LRF spam, so turrets aren't often that viable except as a time-waster and damage-soak, but against some opponents, they are quite effective.
Turrets are also very useful for protecting your asteroid planets. A couple of turrets can cover the bombing range, meaning that any enemy capital ship attempting to bomb will take very severe damage (or die) while bombing your asteroid from 1000 HP to 0. Against an enemy player who goes cap-heavy, this can be a very effective deterrent / time-waster while you build up your own fleet or move it into position. While it's of course possible for a TEC or Vasari player to send along a good number of LRF to safely destroy the turrets, that will take some time, especially if their resources are already invested in a couple of capital ships.
In addition to being able to defend against short-ranged enemy ships, turrets are also surprisingly effective against starbases (Orkuluses). If I had an Orkulus, I would definitely think twice before moving my starbase into the range of just two turrets. Even if my Orkulus survived, it would probably take very heavy damage if it hadn't been upgraded.
It's also possible to build turrets close to the edge of a gravity well while an enemy Orkulus is building to do serious damage to it while it's in progress and to scare it from moving in that direction once it's built. The counter to this is to jump in your Migrator farther back, outside of the range of potential turrets (you can only build turrets up to a certain radius from the planet).
Play Point Blank a few times against good opponents. That's what taught me the utility of turrets.
Now, it's true that noobs to Sins are very prone to overbuilding turrets in useless positions, leading many to think that those who build turrets often have no idea what they're doing - but that's not right. In the hands of someone who knows how to use turrets effectively, turrets can be quite useful and well worth their cost.
But in player vs player fights, Hangars with their super-useful abilities and repair bays are always the preferable method of wasting your tactical slots, not turrets.
A couple of hangars with bombers are quite useful if you're trying to defend a particular world from attack by an enemy Orkulus in the early game... and strike-craft can be very effective at taking out enemy scouts and LRF if the enemy has no flak... but turrets excel in area defense and in buying you time before the enemy can attack your frigate factory or asteroid planet.
Both turrets and hangars have their place.
