What is the shortest distance from a point to a line? A spreadsheet solution

Once or twice a year, if I'm lucky, the Red Arrows will fly near my house.  They'll be flying en route to or from an airshow, or heading back to their home airbase.  I check their flightpaths on various websites (Military Airshows is my favourite, since it provides maps of the flightpaths) and then see if they'll be anywhere near me.  

Then comes the question - where's the best place to go and see them fly over?  Ignoring the lie of the land (I live near the top of a hill, with valleys and hills on almost all sides), where is the point that is the shortest distance from my house?

And, being a maths student, I generalised:  what's the shortest distance between a point and a line?

To start with, we need to understand that the shortest distance from a point and a line is the length of the perpendicular drawn from the point to the line.  In the diagrams below, A represents the point (my house), and the flight path goes from B to C.  D is the point at which the line is closest to point A.  The angle is 90 degrees, and a circle centred on A would form a tangent to the line BC at point D.

Fortunately, the waypoints for the Red Arrows flights are given as longitudes and latitudes, and I know the same for my own home.  But let's simplify to x and y co-ordinates.  We can transfer the flightpath to a straight line of the form y=mx+c, and start with some simple numbers.  For example, let's take point B above as the point (0,1) and the point C as (4,3).  Point A (my house) is (1,3).  Point D is not necessarily the midpoint of B and C.

We know (and this is maths I'm going to use without proving) that if the line BC has the slope m (in the form y = mx+c), then the slope of the line AD is -1/m because the lines are perpendicular.

The strategy breaks down into four separate sections:

1. Determine the equation of the line BC in the form y= mx + c by first determining m and then c.

2. Determine the equation of the line AD, also in the form y = mx + c.  We will know m for this line, and can use this and the values of x,y for A to determine c.

3.  Equate the expressions for y in 1. and 2. as simultaneous equations, to get the x,y values for point D.
4.  Use Pythagoras to determine the distance AD.

1.  Determine the equation for the line BC.

y = mx + c where m = (y₂ - y₁)/(x₂ - x₁).

In our example, m = (3-1)/(4-0) = 2/4 = 0.5

Substituting this value into the coordinates for point B will give us the value of c.  B = (0,1) so if y=-0.5x + c then c = y - 2x = 1 - 0 = 1.

So the line has the formula y = 0.5 x + 1.

2. Determine the equation for the line AD.

Since AD is perpendicular to BC, we know m = -1/0.5 = -2.

We have point A on this line, so we know we have (1,3)
y = mx + c

3 = (-2 * 1) + c

5 = c

And hence the formula for the 'radius' from A to D is y = -2x + 5

3.  Equate the two lines, and solve the simultaneous equation to find the nearest point

The lines are:
y = 0.5x + 1 (the flightpath)

y = -2x + 5 (the path from my house to the flightpath's nearest point)

0.5x + 1 = -2x + 5

2.5x = 4

x = 1.6

And by substitution, y = 0.5x  + 1  so y = 1.8

So the nearest point to the line, point D, is (1.6, 1.8)

4.  Use Pythagoras to determine the straight-line distance from A to D

A = (1,3)

D = (1.6,1.8)

Distance = SQRT((1.6-1)² + (1.8-3)²)

Distance = 1.34

And next, to replicate this in a spreadsheet.  All that this requires is to translate our step-by-step thinking into spreadsheet formulae:

The key steps here are in finding the slope of BC, and then using the reciprocal to find the slope of AD.  The two constants, c, are found by substitution (i.e. rearrange y = mx + c with known y, m and x to determine c).
Then, for clarity, spell out the formulae of the two lines, and use the values of m and c to determine the co-ordinates of point D - first x, then y.

Then use Pythagoras to determine the distance from A to D.

No, it's not entirely efficient, or tidy, but a spreadsheet like this shows the entire process from end to end (and makes you think about how you actually do geometry and algebra, instead of just punching in numbers).