If you look at more data, you see that even in California (https://www.countyhealthrankings.org/app/california/2021/mea...), SF is not at the top (Lake, Mendocino and Inyo rank higher). I'm not saying drug use is not a problem in SF. I'm just saying it's not the primary reason why we have a big homeless problem.
That said, compare with Ohio (https://www.countyhealthrankings.org/app/ohio/2021/measure/f...) where there are dozens of counties that more than double SF's overdose rate. And still, their homelessness rate is a fraction of California's.