There have been plenty of overdoses at InSite but not a single person has died, which is pretty amazing given that it's been open for over ten years. This just goes to show that opioid overdoses would be a hell of a lot less deadly if people had easy access to naloxone.
I only really know the scene in Canada, but Vancouver's Downtown Eastside is regularly described as having the highest concentration of drug addicts in North America and the heroin is cheap and easy to find. I was there recently and was offered heroin and methamphetamine while just walking through. Montreal has good dope but it's expensive, and at the main open-air corner you're more likely to find hydromorphone pills. I've heard mixed opinions about Toronto... it seems that opioids are not that prevalent per capita, but it's a huge city and I'm sure you can find anything you're looking for there if you know where to look.