# BITC / NBD protocol - 10b

0 (0 Likes / 0 Dislikes)

OK everybody,
in the previous clip of this video protocol
we began the process of calculating E-distances.
And I walked you through right here.
We attached environmental values and G-distances
to 5000 random points from across the study region.
We pasted that table into Excel.
Now, we'll work through this next section.
And, of course, our end point is a map of environmental distances.
We were in QGIS.
We took this table and moved it over to Excel.
And pasted it into Excel.
Get rid of the first column.
That leaves us with point ID for each point in the random shapefile,
temperature, precipitation, and geographic distance.
You see there are some 0 geographic distance values in here.
Those are points falling in well-known areas.
That's the crucial set of reference points that allow us to calculate E-distances.
I'm going to sort this so that all of those 0 distances are at the end.
That will make our lives a little more convenient in a moment.
Copy this.
Move it to another sheet.
Now we need minimum, maximum, and range.
And, so, I'm going to calculate the minimum.
I'm going to calculate the maximum.
Then, you know that range equals maximum minus minimum.
We can copy those formulas to the next column.
You also need to remember that we need to standardize.
We're standardizing by the range of temperature and precipitation values.
I'm going to create these new variable columns.
Temp2 will be the observed temperature minus the minimum
divided by the range of temperatures.
We're calculating how far above the minimum value our observed value is relative to the range.
We'll do the exact same thing for precipitation.
Copy those and propagate the formulas all the way down the table.
I've clearly got some problems with my propagation
because I need to fix this row and this row.
Now, all of those missing values will disappear.
We can test to be sure that the formula is correct.
The minimum values relative to the range should give us 0's.
They do.
And, the maximum values relative to the range should give us 1's.
They do.
So, our formula is correct.
What I did just now, was to take our formulas and place them up here,
which applied them to the minimum and maximum values.
Now, we need these standardized temperature and precipitation values for the points with Gdist=0.
Those are the reference points with a 0 distance to a well-known area.
All of these points here.
Take those;
and, paste them.
Paste them transposed so as to create a distance matrix.
So, transpose; and, I want just the values <i>not</i> the formulas.
You need to remember that the Temp2 column is going here.
So, this is Temp2.
Precipitation is both in column F and in row 5.
Temperature is in column E and row 4.
Just to make it clear, I'm going to highlight these different colors.
This will help reduce confusion.
Just to remind you, all 5000 points are in orange.
And, the ~600 points that are within well-known areas are yellow.
Now, we need to develop the distance formula.
That will be:
Temp2 (orange) - Temp2 (yellow) quantity squared
plus Precip2 (yellow) - Precip2 (orange) quantity squared.
Then, raise the sum of the two squared differences to the ½ power.
Arturo insisted on doing that with a square root function, though it should be the same thing.
And, so, I get my first distance.
That's good.
But, if I propagate that, these squares will shift.
I need column E to always remain in column E.
And, I need column F to always remain in column F.
That's these two.
And, values in column G need to always be taken from rows 4 and 5.
Now, our formula should propagate well.
Let's make sure by copying it out only a little bit.
You can see the formulas referring to the appropriate columns and rows.
Now, I will propagate this all the way to the end of the row,
across all 600 columns.
All the way out to here.
Delete the test block.
And, I'm going to take the full set of values and propagate it all the way down the table.
That's going to take awhile.
So, where are we?
We should now have a distance matrix that,
for every point in our 5000 random point dataset,
gives us the distance to each of ~600 points within well-known areas.
What we're interested in is the nearest neighbor.
That will be the shortest distance in the 659 distances for this point.
And, similarly for this point, we're looking for the minimum in all of those distances.
How do we do that?
Create a new column at the end of the table: "Edist".
"Edist" is the minimum of this set of values.
All 659 of the distances to well-known areas.
Propagate that down all 5000 points.
Edist=0 for the last 659 points.
That's because those last 659 points are in well-known cells.
Copy this entire table.
Then, set up a new table in which to paste the values only.
These things take a little while when it's a 5000 by 659 table.
It looks like it's ready.
Be patient.
Again, what we've done is standardized our precipitation and temperature values to the range.
Then, we calculated minimum E-distances from
every point in our dataset to every well-known point in our dataset.
I'm going to clean-up the table a bit.
We'll have the final table in just a moment.
I'd better save this.
Oops, you can already see I made a mistake.
I didn't paste as values.
That's why all those cells have error messages.
So, I'll end this video segment, and I'll be back having fixed that problem.