Yes.
You get Antiquities and mythic items which include some of the best furnishings and playstyle-altering set pieces in the game. As for skill points somewhere around 30 I think for scrying and excavating.
To add to what @sharquez said, there are some builds that almost require mythics to reach their full potential. Arcanists and the Velothi amulet are a perfect example. You could make similar arguments for Death Dealers Fete or Markyn Ring for PVP, Oakensoul for PVE (and PVP?), and Spaulder of Ruin for trial groups just to name a few.
The time it takes to be able to scry gold level items for these mythics are long, so be prepared to do a lot of grinding.
Finally, you can immediately notice a chest when in a dungeon. Rushers have narrower vision and don't care about goodies - with maxed out scrying, you can easily spot chests, loot them and chase the runners.
Is it worth it? Yes, 100%. There is tons of gear, furniture, and collectables you can unlock just by playing passively or hunting for them.
Do you need all the skill passives? No.
Refer to the video, this is considered Master, and with the passives I purchased it becomes so trivial a monkey could do it. Quite a shame because it's actually fun at first when you need to solve the puzzles, but like overland content, ZOS likes to balance for the lowest common denominator
The antiquities system, which includes scrying and excavation, is so worth it I maxed both skill lines on two toons. Others have already mentioned mythics, furnishings and the keen eye buff for chests, so to these I'll add the following: rare motifs, a nice dance emote that doesn't even require farming to obtain, some cosmetic wearables (hats, a bog blight mask, etc) a non-combat pet and a mount.
Some infuriating grinding aside (I'm looking at you leads from treasure map chests), antiquities are some of the best additions devs made to the game.
Imperial DK and Necro tank. PC/NA
"Nothing is so bad that it can't get any worse." (Brazilian saying)