All language subtitles for 01_joining-tables.en

A common task that we want to be able to accomplish is to join

two datasets together based on the attributes and their tables.

So, I'm going to show you how to do that.

So, there's lots of reasons why you may want to

join two datasets together based on their attributes.

I'm going to show you a very common scenario

it's not the only reason why you'd want to do this,

but it's a common one which is that you want to take a non-GIS dataset,

or a table that you found somewhere,

and be able to map that data.

So, you want to have or you have to connect that data that's not in

a GIS format to a dataset that isn't

a GIS format in order to be able to map that non-GIS data.

So, this is my little scenario,

it's a very common thing.

It's like I said it's not the only reason to join tables together,

but I think if I'll walk through this you'll see both how joining tables works,

and why you may want to do this.

So, my objective here is just as an example is I'm going

to try to show the population density of each province in Canada.

So, here's my provinces dataset that I have so this is a GIS dataset.

When we look at the attribute data for this,

you'll notice that there's nothing in there about population,

there's nothing in there about area,

there's nothing in there about population density.

So I've got to start in that I have the areas that I

want to make a map of in other words I've got the provinces and territories for Canada.

But I don't have the data in there that I want to use to do my map.

So, I have to find another dataset that does have that population data,

and then I can attach that to this dataset using an attribute join.

Just a little terminology you should be aware of is

columns in a table in a database are called a field,

and rows in a database are referred to as records.

So, if you want to add a column to your table you're adding a field.

If you want to add a row to your table you're adding a record. Just so you know.

Okay, so here we have a second dataset that does

have population values for each of the provinces and territories.

The problem is is that it's not a GIS dataset,

and I've put this in here just to help indicate that

as you'll see that it has that little table icon.

So that is being stored inside a Geo-database,

you can see that there.

So, there's the Geo-database icon.

But it's not map-able,

there's no GIS map coordinates attached to that dataset it's just a table.

That table could just as easily have been in an Excel format or something else.

So, we want to take that non map-able GIS data and somehow map it.

We're going to do that by connecting it or joining it to our provinces data.

So, here's our GIS file and our non-GIS file,

and what we're going to do is connect these two things together.

So, how can we link these tables together?

I wonder if there's anything that they have in common that we

could use to cross reference the information between them.

There is, what do you now?

Okay thank goodness!

There's a code column or field that is the same in both datasets,

and all that really means is as far as we're concerned here is that,

we can get the software to go through these and say

here's the NL code for for Newfoundland.

In this table, where is the NL code here or there it is.

So, it will line up or connect the data for

those two entries so the this record here will be connected to this record here.

So, I said Newfoundland it should be Newfoundland and Labrador,

and so it will then go to the next one which is for Prince Edward Island.

It will go and find that one in this dataset over

here we will connect those two values together and so on.

So, I do think of it this way in terms of

cross-referencing are matching the data between the two,

and then it will connect those two together and create a table that looks as

though it's one big table or it can be treated as though it's one big table.

So, the term that we use for this is we're going to join the tables together

that's the term you'll see used in ArcMap.

What I'm doing here is adjoined based on attributes,

you can actually also do a join based on

geography where instead of it being based on columns in a table,

it's based on is this point inside a polygon,

but we'll save that for another time.

So, here we're just going to focus on attributes.

So, the table that we're going to join the data to is called

the target table so that's the target of our operation,

and the table that we are joining to the target is known as the join table.

I wonder how they came up with that name!

Actually I'm not really fond of that

in that it's you'd think they'd have would have called it something else.

But okay, the join table is being joined to the target table.

Sometimes that terminology comes up so I just wanted you to be aware of it.

A couple more terms that you should be aware of is the primary key and the foreign key.

This starts to get into concepts related to relational databases.

I'll try to keep it simple here.

Essentially, the idea with a primary key is that you have to have

a unique way of identifying each of the records in our target table.

In this case, each of those codes is different.

We have a unique code for each province and territory.

So, when we're trying to join data to

that table the software can tell them apart from one another.

If two of them have the same code,

then it wouldn't know which thing to connect it to and so

that would not be a useful way to try and identify them.

There's lots of examples of this in the world if you

have a student number or a social insurance number or things like

that those are identifiers that are used to

uniquely tell you apart from other people and it's exactly the same idea.

If you had two people with the same number,

you wouldn't be able to tell them apart,

and then you wouldn't be able to join data from

different data sets together and so the whole thing would break down.

So, the essential component for us to remember for no,

w with a primary key is that there has to be something that's

unique that's able to tell those records apart from one another.

The foreign key is the same column in

our join table that's going to be used to join to the primary key.

It's not a requirement with a foreign key that they all be unique to one another.

There's various methods or ways of joining things together.

You could actually have more than one entry or

record in a join table that's joined to a target table.

I'm not going to get into that right now,

I just want to you to kind of get the general idea of this.

But essentially, the idea is that the primary key has to be unique,

the foreign key does not have to be unique,

but there has to be something in that foreign key that matches

up with what's in the primary key in order

for it to be able to join those two things together.

All right, so let's see how this all works.

In order to accomplish a join we just right click

on the target table in our table of contents.

This will bring up a dialog box here that says join data,

and we're going to join atributtes from a table

and it says choose the field in this layer that the joint will be

based on we're going to use the code column.

By the way I try to keep things simple here by having

the same name for the fields in

both tables they're both called code that does not matter.

I could have called them anything I want as long as I know that they are

the correct ones and I know which one is the primary key and foreign key, that's okay.

So, don't feel like they have to have

the same name they don't as long as you know what it is.

So, here I'm telling it that I'm going to use code,

choose the table to join to this layer it's going to be called

prov-pop so that's the population data for my provinces and territories.

It just so happens that it's also the same name code

here and I'm going to ask you to keep all the records okay.

So, you just fill this in and I like the way that they set up this dialog box.

It's just kind of a natural language way of

saying what do you want to do and you're walks you through the steps,

you fill it in, and then you're ready to go.

So, here we have our joined tables.

So, you'll notice that it actually looks like this is

the provinces that attribute table because

that's the target table that's what we were joining it to.

But in actual fact,

this is the province data provinces and territories data from that table,

and this is the data from the prov-pop table.

So, this was the non-GIS,

non-map-able data that's now been joined to it.

So the idea is that it's pretending or treating

these two tables now as though they are all one big table.

The whole point being that now the software has access to

that non-GIS data and we can use it in our mapping operations.

I've mentioned in here that the join is dynamic.

That's an important point.

So, what this means is that this has not created a new dataset,

it hasn't changed anything in either of these tables in their files,

nothing has been written to those files.

All that's happening is that when I created this join, it says,

"I've got one dataset here and another one there,

he wants me to connect them this way, okay,

I'm going to join those two together". Now they're joined.

If I didn't save my map document,

and I closed out of ArcMap.

Then I came back later and open it again that joint would be gone.

Because that's something that I asked it to do for me in real time.

If I did save the map document,

then it would be there the next time,

because that join the fact that I'm

asking to join those tables together is being stored in that map document.

However, if I open those tables in a different map document,

they would no longer be joined,

that have to do that again.

So, that's one thing to keep in mind.

The other is that it's dynamic,

meaning that when I open a map document and that join is being done,

so I opened it up, the join has been stored,

and the software says, "Oh he wants me to join this table that table".

If anything has changed in that, one of those tables,

that will automatically be reflected in the joined version of those tables.

So, if there's a change to one of the tables, it's dynamically updated.

It's not static. It's not creating a new set of values.

It's saying, every time that join is done,

it's looking for the fresh current version of that data, and that's what it's using.

That's what I mean by dynamic.

So, the join is stored in the map document.

I said that already. I Just want to make sure that's clear.

All of this is to say that now I can go into the Symbology tab in ArcMap,

and I can access the population data from my non GIS table.

Because now that I joined it,

I can access it in the drop-down here, and I can use it.

So, here's the attribute table here.

So, this is the prof pop data,

but it's now being joined to the other one.

So, I can take those population values in here and that's what's being used

here to create a choropleth map of those population values.

So, this is the results.

I've mapped my population data from a non-GIS dataset.

All I had to do was do an attribute join so that I could access that.

Now I've got a population map for Canada.

This is really the fundamental idea behind a relational database.

Essentially, the idea is just that you have two or more tables that are separate.

They're stored separately, they're organized separately for

whatever reason often it makes more sense to do that.

For example, I was thinking if you are a university or college or whatever.

If you have a student number,

that's the primary key that's used to identify you,

and there might be one table about what books you took out of the library.

There might be another table about what courses you're taking.

There might be another table about what fees you've paid.

So, there might be different groups within

the organization that are keeping track of those separately.

But because they're all using the same primary key,

your student number, they can relate those datasets together if they need to.

So, they can say what do we know what this person?

Or what's the current situation with this person?

If they are able to access those datasets

based on permissions and privacy of course and everything else.

But the idea is with relational database is that you can have

those separate tables and they can be connected to each other in various ways.

It doesn't always have to be by one primary key.

As long as any two tables within the larger set of tables,

as long as any two of them have a primary key and a foreign key,

those two can be joined together.

So, it's a very flexible system.

Relational database architecture is very old.

It's been around for a long time.

But the idea is that it's pretty straightforward.

It's pretty simple to use in theory,

and I think it's just popular because it works well.

It's flexible and it's easy for people to understand.

Now one thing about this map,

by the way is my original objective was to map population densities.

So, I'm going to show you how to do that in a separate video.

But I didn't forget about that. I just want you to know.

But one thing that I want to point out as well is that when you're joining tables,

do not join them based on the object ID column.

This is a common mistake that newbies make.

I just want to make sure this is clear is that

I said you have to have a primary key and a foreign key.

If you're trying to join two datasets together,

especially if they don't have

a primary key and foreign key that are common to one another,

it's really tempting to say look,

there's these object ID columns.

They both have values from one to 13. I'll just use those.

Don't. The reason for that is that,

and I'm not even sure why Ezri shows you

these columns because there really of no use to you as a user,

unless under some really unusual circumstances.

But essentially, that's used to keep track of the features that are being mapped,

and the coordinates that are being used,

and things like that behind the scenes that the software is using.

These are not useful to you.

So, the numbers that are being assigned as

object IDs are assigned in the order in which those objects are created.

Okay? So, for example,

Alberta was created first.

So, it has an object ID of one.

British Columbia was second, so,

it has an object ID of two, and so on.

Right? That's in the provinces' table.

In the prof pop table,

the first entry or record that was created was for Newfoundland and Labrador.

So, it gets an object ID of one.

The second one that was created was for Prince Edward Island.

So, it gets a value of two.

So, in other words, if you tried to join these together,

so without me drawing all over, this is what it's showing here,

what we should be doing is that this should

be object one if this was actually going to work but they don't.

So, they're not going to line up correctly.

What will actually end up happening if you try to join them based

on this is that the data for Alberta will

be joined with the data for Newfoundland and Labrador

because they both have the same object ID of one,

which of course would be incorrect.

So, just pay attention to that.

You may at first glance,

looks at the object IDs and see if they look like they're lining up correctly.

If they do, it's just purely by chance, it's a fluke.

You never know especially if it's a big dataset,

somewhere down the line, maybe they don't line up correctly.

So, it's not something I would ever rely on.

So, this is a long explanation for just saying that it's tempting

to want to use object IDs for joining tables but it's a really bad idea.