All language subtitles for 03_field-calculations-in-tables.en

Often when you're working with tables,

there's some operations you want to do like adding

a column or doing some field calculations.

So, I'm just going to walk through some of the basic ones that are more popular,

that you probably want to use more often,

and just show you how they work.

Here I have a map of population values for provinces and territories in Canada.

Mapping population counts for a Choropleth map is not really

recommended because you end up with biases based on areas.

So, we prefer to want to normalize that data or standardize it in

some way by dividing by area in order to get a population density values.

So, let's see how we could use a Field Calculations to help us do that.

Here we have an attribute table for that data set.

So, we have population values but we don't have anything about area or

population density in order to be able to make a density Choropleth map.

So, we can add a column for area and we

can do calculations for density in order to fix that.

It's easy to add a field,

you can just do that by selecting up in this little dropdown here,

you can select Add a Field and so here I've given it the name of Area,

you can give it whatever name you want,

it doesn't have to match what you're doing.

Then, there'll be a dropdown here for the type of field that you want to add.

These are the choices that are available to you.

So, there's Short Integer, Long Integer,

Float, Double, Text and so on.

If you're not familiar with these,

it's worth having some idea of

what the definitions are for each of them so you know which of those to pick.

So, here are the more common field data types.

We have Short Integer and Long Integer which do not have decimal values,

and then we have Float and Double which do have decimal values,

and then we have Text that is for non-numerical values like characters,

and then we have Date for literally dates.

So, I don't know about you but I've never

taken the time to memorize these particular numbers,

I wouldn't expect you to do that either.

But, I do think it's useful to kind of know so well

why for example is there a Short Integer and a Long Integer.

The idea is just that when we're creating a column in a database, when we add a field,

we have to tell it how big that field should

be because then the software is actually setting

aside that storage space for that field whether we have numbers in it or not,

and we always want to be as efficient as we can and

only set aside as much space as we really need.

So, why would we set aside a huge amount of space for

really big numbers when all we plan on doing is storing small numbers?

So, that's the thinking behind the idea of having a Short Integer.

If you just have values between roughly negative 32,000 and positive 32,000,

then you can just store them with short integers.

But if you're going to store much larger numbers like this,

then you would want to use the Long Integer.

I won't go through all of these in a whole lot of detail but with Float and Double,

essentially it's that you can now store

decimal places as opposed to the Integer which you can't,

and it's a matter of how many decimals or significant digits you can store.

Text, the main thing I wanted to point out there is that it saves characters,

but you can actually have numbers in a Text field.

But the important thing here is that the software will not see them as numbers,

it will just see them as characters. Why is that important?

Well, for example, if you try to do a calculation on those,

it would say well, I can't do a calculation on them because it's text,

even if they are numbers.

So, when will this come up?

For example, if you have some kind of ID number,

like a student number, or social insurance number for somebody.

Really, that's not something that you plan on doing calculations on,

so it may end up being stored in a Text field.

That's one example.

But sometimes inadvertently,

you have values that you do want to do a calculation on that are stored in a Text field.

So, you can't do that if it's in

a Text column instead of in an Integer column or something like that.

Just while I'm thinking of it, another situation where this is relevant is

if you're trying to join two tables together based on numbers.

If in one table they're stored as numbers and the others they are stored as text,

you won't be able to join those together

even if they actually look like the same numbers.

Because as far as the software is concerned,

they are different things, they are not the same field.

You have to have the same field type in order to be able to join them together.

So, it's just important that you understand what these things are,

I think it's always important.

I always encourage people when there are faced with a decision like this,

is to make a conscious educated informed decision

instead of just kind of crossing your fingers going,

"Well, this one kind of looks right."

So, that's all I wanted to say there is,

just be familiar with them.

Know which one to choose and why, and let's move on.

So now, I've got my Area column or Field that's been added,

but you'll notice that it's filled with zeros.

Is that the software is not smart enough to know that,

"Oh, he called it area.

So maybe, I should calculate the areas for all of the polygons that he has."

No, it doesn't work that way.

So, all I've done now is I've created the field,

I've set aside the storage space, if you want,

for the areas that I'm going to then calculate.

All I have to do is right-click on that field and select Calculate Geometry.

As it says here, I'm not going to read this exactly,

but this will give you options for calculating things like areas and perimeters.

So, when you get to the dialog box you can say,

"Okay, what do I want to calculate?"

In this case, it's going to be area.

You have to tell it what's the coordinate system that you're going to use for that.

It's important that it be a coordinate system where areas are maintained.

So, in this case, it's an Equal Area Conic projection.

Therefore, the areas that I'm going to

calculate are going to be correct as opposed to if I'd say,

for example, used the conformal projection,

then they would not be correct.

So, it's important that you know what the coordinate system is that you're using in

the projection related to that and that the calculations are being done correctly.

So, I know that these are equal area,

so that's going to work.

I've chosen the units to be square kilometers,

I think that makes the most sense.

For Areas the size we're talking about things that are provinces and territories.

Depending on the areas that you're working with,

you might want to use something else like square miles or square meters,

but here square kilometers makes the most sense.

So, now you'll see that once I've closed that dialog box,

it does the area calculations and populates that field with areas in square kilometers.

So, I now have areas available for me

to be able to use for my population density mapping.

There's two ways that I can then create

a Choropleth map of population density and I'll show you both of them.

One is to do normalization in the symbology.

What do I mean by that?

Well, remember, all the normalization is,

is that we're going to remove the bias in our values

to make our Choropleth map more true to what it is that we're trying to interpret.

In other words, here we want to divide

the population by the area in order to calculate density.

That's known as normalization.

So, here I've selected the value to be

population and the normalization to be the area, and when I do that,

I can then choose whatever classification method I want,

the number of classes,

and we'll end up with our classification scheme down here and so on.

But the important thing is that this is being done on the fly so to speak.

That's like nerd language for saying that there's nothing about

the actual density values that are calculated that's going to be stored in the dataset.

You won't see density values in our table anywhere.

It's just going through that.

As soon as I click this dialog box, it'll say,

"Okay, population value, area,

divide the two get the density.

How would that look on the map? Let's show it on the map."

So, that's literally doing it on the fly.

So, let's see what happens. So, here's our population density map for Canada,

for provinces and territories.

As you might imagine, the territories are fairly low in population density.

Ontario is the highest and then we have some that are in between.

So, this has accomplished the task that I was trying to do,

which is make a Choropleth map of population density.

The only thing here is that there's nothing in

my dataset that's storing those density values.

So, if I want to do that then I have to go and calculate those density values.

So, let's see how that works.

So, I went ahead and added a field for Density

and I'm now going to use the Field Calculator.

So, if I right-click on that field,

I just select Field Calculator,

and then you get this dialog box here where

you can select the field that you want to use.

So, in this case, I have Area and Population,

and you'll notice that these are,

I might as well mention this now is that you will see that some of them say

Provinces.Something and some of them say Prov_pop.Something.

What I did here is I have joined these tables together.

I actually have two different tables that I'm using at the same time,

but it's treating them all as though all

of these fields is other part of the same big table.

So, now I can divide Population by Area.

Just so happens that the population is coming from

the Prov_pop table and the Area data is coming from the Provinces' table.

Just so that's clear,

because whenever you see stuff like that, I want you to,

instead of just glossing over your eyes,

guys glazing over and going, "I don't what I'm looking out there."

It's really not that complicated,

it's just these are in square brackets,

so that this is sort of one package,

this is another little package.

We're dividing one by the other,

that's what the dividing symbol is.

So, I'm dividing population by area,

it's just telling me the dots in one table and that's in another.

So, that's all there is to it. Population divided by Area.

So, then when I close that dialog box,

now I have Density values in my table.

So, I can then use those to create my Choropleth map if I want.

So, I have my area data,

I've got my population data,

and both of those were used together to make my density data.

So, now if I go into the symbology,

I can use density as my value.

Again, this is something I find some people get confused by.

If the density value is already been calculated,

then you do not need to normalize it in

the symbology dialog box because you've already done that normalization,

because the density value has already been calculated.

That's what that means. Otherwise, you basically go through

the same classification method and classes that you would with anything else,

and this gives you your color scheme just like you would with anything.

So, now, you end up with a choropleth map of population density.

So, visually, this looks exactly the same as if I had done it with symbology alone,

and done the normalization there,

and done the calculation for density on the fly.

But now, if it's important to me or if I need to do that,

I can use the Field Calculator to

calculate the density values to keep those in my attribute dataset.

So, you can use either method.

It completely depends on what you're using it for,

whether it's important to you to have those in there.

But I thought it'd be useful to show you how to do those calculations,

how to add a field,

do a field calculation,

do a calculate geometry if you want to measure things like areas or perimeters,

and then show you a typical example of one that will be useful.