It occurs anytime a Royal Navy ship visits London, which is once or twice a year. (In recent years allied navy ships also partake in the ceremony.)

What kind of ships usually visit London / why visit (since it’s kind of land locked)?

There is a small navy base just outside Tower Bridge. But every now and then some larger Warships moor next to the Belfast (am Museum ship). That’s quite central already and also Tower Bridge has to open for them, which is probably part of the fun.

Sometimes a cruise ship moores there as well. It seems to be popular.

Greenwich used to be a very important Royal Navy training centre, so it is not too unsurprising in that context that there might be some errands to do just a little further up the Thames.