First of all, there is a difference between classical guitar and fingerstyle guitar. Generally fingerstyle refers to steel string acoustic guitars played with the fingers, while classical guitar uses nylon stringed guitars with a very specific playing technique. Technically classical guitar uses a type of fingerstyle technique, but usually fingerstyle is associated with folk, blues, or country music on steel-stringed acoustic guitars. These are all generalizations, but there is a distinction between the two worth noting as you decide which path you want to take. The video you posted is indeed classical guitar played on a nylon stringed guitar. If you want to teach yourself, it would probably be easier to learn fingerstyle without the classical guitar component as it is less dependent on having the exact right technique.
Classical guitar is one instrument where your technique must be very precise so it's a little bit harder to teach yourself. I would recommend getting a good teacher as they can give you proper instruction on how to hold the guitar, how to play, hand positions, etc. If you don't want to take a lot of lessons, you can potentially just get a few lessons to get the techniques down, then transition to teaching yourself once you know the basics. But it is very hard to un-learn bad habits, and with classical guitar especially bad habits can severely limit your playing potential, so finding a quality teacher is going to be the best and most productive route.
There are a lot of good books out there, but it's difficult to know without feedback if you are doing things right so learning from a book by itself isn't easy. If you want to try a book, The Christopher Parkening Guitar Method and/or the Carcassi Classical Guitar Method are both popular choices. It would probably be useful to watch some video lessons as well, because it's much easier to explain proper technique with video examples than it is with text and pictures. There are a lot of good DVDs out there on the subject, and YouTube can be pretty useful as well. If you are going to teach yourself, you have to pay close attention to your posture and hand positions because those are crucial to proper classical guitar technique.
As for the usefulness of your prior theory knowledge, that will help, but not really in the beginning stages. When you first learn guitar the physical aspects of playing are very important, so the theory isn't as useful until you get the techniques down. With piano all you have to do is press the key and you get a note, so it's easy to transition to chords and scales very early in the learning process, but with guitar it takes more practice and effort just to make a single note, so you will have to spend a lot of time learning how to play before your knowledge of theory really becomes useful.
Finally, getting to the point where you can hear a song and instantly play it will take a lot of time and practice. I'm sure it took a while for you to get to that point in your piano playing, so it isn't much different with guitar. You will have a bit of a head start because you probably have a good ear from all your piano playing, but learning guitar technique and how to play by ear is a different skill than playing piano by ear.
