I was thinking about this a bit over the last few days and I kinda think our dislike for police is fundamentally tied to the service they provide in our society. When your neighbor is being too loud having a party you don't want to be 'that guy' who walks up and ruins everyones good time so you send the cops instead. They do the uncomfortable deed for you and they get to be the bad guy. A large portion of the services they provide are in this direction.
I do feel you on their lack of usefulness. Even if there was some instance of violence the cops take time to get there to protect. I can not name a time they have helped me past giving me a police report number for an insurance claim after my truck was broken into; while I can name many times I have had negative interactions and even a few weeks ago felt they were using legal excuses to take money from me.
One critical part someone brought up to me years ago is that nobody has a problem with cops when they are protecting people from other people. Any instance where they are preventing someone from being attacked, robbed, or raped is a positive thing. But as a society we have tons of laws passed that seem to be focused on protecting people from themselves which I believe to be unethical. Should I wear my seatbelt? Sure. But be the fucking dick bag who writes me a ticket for not wearing it and I will disrespect you because who the fuck are you to steal my autonomy from me. I believe I have the fundamental human right to be an idiot and take risks with my safety when it does not hurt anyone.
If you think about how any society sorts itself there are norms, taboos and laws. The laws are suppose to be punishments to enforce extreme taboos but our society no longer follows that. Things like smoking pot are extremely widely accepted yet 'illegal' and also follows the unethical model of 'protecting people from themselves.' Combine those two factors and you basically have cops forcing people to do things nobody has a problem with and that do not harm others which makes their function in society hated. The strange thing though, is we blame them although they do not make these laws. Politicians do. I wonder if people thought about the situation differently the hatred cops receive would be appropriately displaced onto politicians.
There is also the issue that its one of those jobs where doing it right goes unnoticed but you are crucified when its done wrong. Its thankless, dangerous work that gets shit pay so I do have some measure of respect for the people who do it. While my interactions with the police have been negative I have yet to get to know a cop off duty and not gotten along well with them. That might just be a veteran thing though.