Unless the spell text specifies, that is left to a DM ruling.
A question like this highlights how integral to this edition of the game the "rulings > rules" philosophy is in aiding a DM, or a whole table, in resolving such puzzles.
We had an extended discussion about this on the GiTP forums regarding casting Leomund's Tiny Hut on a ship. The consensus we arrived at was, to avoid absurdities, that the DM needs to pick a frame of reference. I recommend reading the whole thing, as painful as that might be.
Why is that? Because trying to shoehorn RL physics into a game where magic exists, and which isn't built as a physics emulator, opens up a can of worms that need not be opened. Poster Shining Wrath summarized it like this (LTH = Leomunds Tiny Hut):
This is a classic "rulings, not rules" situation. Where you can and can't put a LTH is up to your DM.
...
If LTH is immobile with respect to things like ships, a wizard could destroy the strongest ship ever built by teleporting into the hold, casting LTH, and then teleporting back out, if there was a strong wind and the ship was under sail. {The "immobile" hut ends up putting a hole in the bottom of the hull, and down goes the ship}
If LTH is mobile with respect to things like wagons, then armies would use them as tanks.
Therefore, neither of those things work, because it's magic and it doesn't have to make sense. A LTH on the deck of a ship moves with the ship, a LTH on a large wagon slides off. {The point he was making was "Make a ruling that makes sense"}
For a spell like fog cloud, it makes sense to anchor it to a local frame of reference. You cast it at a point you can see:
I see the ship's wheel, I cast fog cloud at that point! (My aim is to confuse or confound the ships' helmsman, and perhaps a few archers in the rigging).
The fog cloud should remain centered over the ship's wheel. But ...
You create a 20-foot-radius sphere of fog centered on a point within
range. ... It lasts for the duration or until a wind of moderate of
greater speed (at least 10 mph) disperses it. (PHB. p. 242)
... if the wind is strong enough that day, that won't work since the wind disperses it. (Not due to relative motion). Rather than seeking a general rule, you need to look at the specifics of a given spell and go from there.
Select a frame of reference
As jason_c_o pointed out in a comment:
Just apply relativity. The ship (and everything on it) is stationary in space, and it's everything else that is moving {relative to the ship}.
This approach fits the game's basic framework, in that the local area where the players are interacting with monsters and creatures and weather and the environment is a local frame of reference. During table top play, that frame of reference is frequently portrayed as a small grid or battle map (be they squares or hexes).
Alternately, you can overthink this and get into arguments like the ones at the linked forum discussion.
The choice, Willow, is yours.
You may be pleased to know that D&D 3.5 and Pathfinder end up using a similar approach: make a ruling. Jeremy Crawford has offered rulings on related questions regarding casting teleportation circle on a moving surface here, and also here. Thanks to @HeyICanChan and @V2Blast for their assistance and comments.
