CSCI 1210

From the Earth to the Moon

We now leave the world of baseball and consider the problem of how to travel in space. This is represented as trivial in science fiction movies such as Star Wars and Star Trek, but in the real world it's quite a challenge.
To the best of my knowledge, the first person to propose a physically feasible method of travel to outer space was Jules Verne. In his 1865 novel From the Earth to the Moon, he proposed firing three astronauts and their two dogs from a giant cannon to the Moon.  Interestingly, Verne chose Cape Canaveral, Florida, as his lauch point.
Let's think about how we would model this scenario. It should be clear that this problem has many elements in common with the batted-ball model. There are also the following differences:

• Since the projectile is fired straight up, there is a y variable but no x variable. In other words this is a one-dimensional problem, as opposed to the batted ball problem which is two-dimensional.
• As the projectile gets very far above the Earth, the attraction of gravity will decrease.
• Air resistance depends on the density of the atmosphere. As the projectile rises, the density of the atmosphere will decrease.

Gravitation:

        A = k/Distance^2

where A is the acceleration, Distance is the distance of the object from the center of the Earth, and k is some constant of proportionality. The "caret" operator ^ is used in Excel to represent raising a number to a power. We really don't care about the numerical value of k, but we will solve for it so we can find the acceleration of gravity at any distance above the Earth. We use the known facts that the radius of the Earth is 3960 miles and the acceleration of gravity at the Earth's surface is 32.2 feet per second per second. Thus

    32.2 = k/3960^2

Solving for k we get

    k = 3960^2/32.2

If our projectile is at a distance y miles above the surface of the Earth, then its distance from Earth center is 3960 + y. Putting this back into the equations above we get
 
    A = k/(3960+y)^2

plugging in the value we found for k and doing a little algebra we get

    A = 32.2 * (3960^2)/(3960+y)^2

If you look at this equation you should notice the following reassuring facts:

1. When y is zero, A will be 32.2
2. When y is more than zero, A will be less than 32.2.

Air resistance:

   d = exp(-0.169y)        if y<6,
         1.6 exp (-0.252y) if  6<= y <= 40,
          0                         if y>40

Here the air density d is defined to be 1.00 at Earth's surface, and y is once again the altitude in miles. Exp is the exponential function from mathematics, which we will see in many different contexts during this course. We'll be learning more about the properties of this function shortly, but for now it's enough to know that Exp is one of Excel's many built-in functions.
Air resistance itself is now defined as

    Air resistance = k*d* v^2

where v is the velocity of the projectile, d is the air density as calculated above, and k is another constant of proportionality called the drag coefficient. You must get used to the fact that the symbol k is re-used to represent different constants of proportionality in various contexts. For the current context, we will use the drag coefficient of a typical artillery shell, which is 0.00012.

Implementation details:

• We now have two new variables which were previously constants: acceleration of gravity and air density. We will need two new columns to represent these variables.
• We also have new parameters: the Earth's radius and the drag coefficient. (The drag coefficient replaces a similar parameter in the batted ball problem). These parameters will be added to the parameters area at the top of the worksheet.
• A new Excel feature, table lookup, will be used to calculate the air density at each point of the simulation. This involves making a table of values of air density at different altitudes, which will be placed in a different area from our main simulation table.

• Because the table has thousands of rows, it is inconvenient to make a chart of the complete table. On a separate "summary" sheet, we extract the position of the projectile at intervals of every 20 rows of the main simulation (2 seconds of simulation time). This is done using the Excel built-in  INDEX() function.
• How fast must the projectile be fired? There is a special velocity called escape velocity, which is the speed which a space vehicle must attain to break free of Earth's gravity. The projectile must still have this speed at the altitude where it has left the earth's atmosphere and no longer loses energy to air resistance. We use an Excel feature called goal seek to search for the correct initial velocity to satisfy this requirement. Goal seek is found on the Excel tools menu.