Is 视死如归 seen as "See death as returning home"(Respect to someone who died) or "Not afraid of death"(arrogance)
Both definitions are correct.
多功能成语词典 explains:

解释 视:看待;归:回家｡把死看作像回家一样｡形容不怕死｡多指为了正义事业而勇于牺牲自己的生命｡

视: treat ;归: returning home. to treat death the same as returning home. To describe being fearless in the face of death. Refers mostly to bravely sacrificing oneself for a righteous cause.
They also give an example sentence:

例句 面对敌人的屠刀,革命先烈视死如归,大义凛然｡

A, more literal, translation would be something like:
Facing the butchers knives of the enemy, the martyrs to the revolution treated death as returning home, righteousness abounds.
Another translation you can have a look at from A Chinese-English Dictionary:

刘胡兰临刑时, 大义凛然, 视死如归。
Liu Hulan faced the executioner's ax with a fortitude that commanded admiration and respect.

This phrase is mostly seen in connection with martyrdom and warfare.
Some other definitions that might help:
ABC

face death unflinchingly

Oxford

meet one's death like a hero

CC-CEDICT

1 to view death as a return home
2 to not be afraid of dying
3 to face death with equanimity (idiom)

I doubt you'd ever use this phrase to console anybody, even if you did it'd be strange unless they were dying for some big cause or fighting in a war.
There is an antonym: 贪生怕死 which CC-CEDICT defines as:

1 greedy for life, afraid of death (idiom); craven and cowardly
2 clinging abjectly to life
3 only interested in saving one's neck