In this demonstration we're gonna focus on working with tables and databases,
that is, creating one to one and
one to many relationships between different data tables that may
hold some non-spatial data that we want associated with particular features.
Now, we're also gonna work a little bit with calculating fields, and
creating new data based on either the geographic characteristics of our features
or something else that we might want to calculate it based on.
So the first thing you should probably want to check is that you have your
data downloaded.
In the demo 7 folder that you should have extracted,
you should have an example set of a the soils for
Notre Dame that were digitized using the process in the preceding demo.
You should have a few different database files that yours may or
may not say that they're openable in some other program the way mine do.
You should have one for water measurements, one for water sites, and
one for the soil descriptions for St. Joseph county.
So, once you're in ArcMap, as usual, go ahead and load up a blank map.
We wanna add in first our soil data and the soil descriptions for
that particular data layer.
Now I'm not gonna go through the process of adding these things again.
If you don't already have your folder connected, I encourage you to look at one
of the earlier demos that'll show you how to do that.
So we want to just add the two that are for the region of northern Indiana,
where Notre Dame is.
The soils for Notre Dame here and then the soil descriptions for St. Joseph county.
So just go ahead and select those two and add them.
So what you should see right off is that we have a fully digitized
soil map right here.
And if you open the attribute table for
it, we have the soil codes filled in properly.
But that's really the only information that's in there.
And while I might be able to guess that a W is water, I really don't have any
information intuitively about what UEWA as far as the soil code is.
So if you go ahead and open up the other layer that you have here, the soil
descriptions, what you have is for each soil code you have a description of it,
the formal name, whether or not it's good farm land and that's it.
That's all we have added in here.
There's lots and lots of other data that are in the soils database that I didn't
include, and that's just because it could be kind of complicated to have them.
So what we want to do here is we want to append for
each soil code in our soils shape file, all of the information for
that soil code, from this descriptions file.
And this is really easy using a one-to-one relationship,
we're creating a join between them.
So the first thing we want to do is just simply right click on
our soils shape file.
And go down to Joins and Relates.
And you're going to choose Join.
So if you look at Remove Join, there's nothing on there yet, but
eventually that would be how you would remove something.
So go ahead and choose Join.
Now, what we want to do is join attributes from a table.
So this is correct up here.
We want to choose the field in this layer that the join will be based on.
Well, it's the soil code, the code that we entered in the preceding demo.
It already has guessed that were looking at the soil description St.
Joseph file and it's already guessed that we want to look at the MUSYM here. S
Which is the sort of symbol,
the soil code, this is kind of formally what they call them in the soils database.
You could choose another one that wouldn't work, but
in a lot of cases you may have to manually choose.
In this case it's guessed it based, because they're about the same length and
they're both text.
So we want to keep all records.
Then just go ahead and hit OK.
It's gonna tell you something about indexing.
It's fine to go ahead and say yes.
This is really only important if you have a very, very large file.
So it's going to run happily in the background.
And you probably won't even notice that it's finished,
it doesn't give you a pop up or anything like some of the other tools do.
But now, if I go open the attribute table for my soils Notre Dame, you'll
see that for each one of those codes, it's appended all of the information in there.
Even in cases where it’s it's in there twice.
So any time it found a W it's given me the description of water there.
Any time it's found a UHPC it's given me urban land hills Hillsdale-Tracy complex,
5 to 10% slopes.
So you can sorta scroll through.
If you have any blank ones, anything where it gave you a null or
something is missing, what that usually means is that you had something wrong with
the soil code when you entered it.
So either you typed it a little bit incorrectly, you have a space before it.
Anything will really kind of trigger an error there.
If that's the case, just go ahead and cancel your join.
Go back, start editing your shape file.
Maybe add in the raster from the proceeding demo.
See if you can't figure out what that code was supposed to be that you maybe wrote in
there incorrectly, typed in there incorrectly.
In this case it's all generated fine.
We don't have any nulls or anything.
So, at the moment this is a temporary record.
At the moment this is only a temporary join between them.
We can make it permanently pretty easily.
Simply by creating a copy of this temporary shape file,
it's gonna go ahead and bring in those data permanently for us.
If you simply right click on it, and go to Data > Export Data,
we have our output in this case is a little bit incorrect, so
I'm gonna send it to the right spot.
We want to put it into our Demo07 data folder.
This is one where we simply type the name in here as well.
So I'm going to call it
Soils Notre Dame with description.
Then go ahead and hit OK.
So it's gonna copy everything over to create an entirely new file for us.
And then it's gonna ask us if we want to add that in.
Now I do want to add it in for now, so go ahead and just hit Yes.
And now when you open up that attribute table, everything is in there permanently.
This, these are going to be with that file forever.
Now there's a couple things in here that we don't need as well.
It brought in both the soil code and the symbol.
We don't need both of these, they're exactly identical data.
That's how the join was done.
So we can simply right click up here on the name of
MUSYM then choose Delete Field.
It's gonna make sure that we're correct that this is what we want to do.
We do, so hit Yes, then I'm gonna do the same thing for this OID.
This is a unique identifier that it put into the soil descriptions.
It's really not necessary at this point because we have the description and
the soil code.
Those are the important information for us.
So I'm just gonna right-click and delete that field as well.
So at this point, I no longer need my Soils_NotreDame, my original shape file,
and I no longer need this table that's come in that we used for the join as well.
So I'm just gonna remove these from my map layer as well.
So just hit Remove.
Right click, Remove.
Now, one thing you may have noticed over here in the table of contents,
is as soon as we added in something that doesn't have spatial data,
it brought us to this kind of file system view as well where
it's telling us the folder that it's in and then the files that are in the map.
If we wanted to be able to move things around and put one on top of another,
we'd need to go back to the list by drawing order.
This is the one we're used to seeing.
Anytime you add a table, it's gonna move you back over to this list by source,
because the table on its own has nothing special.
So it doesn't have a drawing order associated with it.
So the next thing we wanna work on is calculating fields and creating new data
in, in the, in the attribute table for this new Description.
We have two different ways of doing this in ARC GIS,
both of which we have to first add a new field.
But then we can do what's called a field calculation.
Which is creating a new value based on pre-existing values
elsewhere in the attribute table.
But we can also do calculating geometries, where we use something about
the geometry, the geographic feature of that particular
feature to create a new mathematical field.
So, this could be something like the area, the length of the perimeter,
the X-Coordinate, the Y-Coordinate, either of a point or the center of a polygon.
So let's go ahead and
first start off doing some of this geography, calculating geography.
So if you don't already have your attribute table open,
go ahead and open it.
And I'm going to go ahead an add a new field.
And we do this in Table Options > Add Field.
Then, I'm going to call this field “area.”
We're going to calculate the area of each individual polygon.
Now, these are going to have decimals in them, so we don't
want it to be an integer like this, and we certainly don't want it to be text.
So, I'm going to go down and choose double.
This is the largest kind of number that we can have in there with decimal places.
We could probably get away with float, but let's just be a bit more,
give ourselves a little bit more room and choose double.
I'm gonna leave precision and
scale at zero, that's just gonna default to the maximum.
Go ahead and hit OK.
So now, you've probably noticed pretty quickly that we have a new field,
but it’s auto populated it all with zeros.
That's kind of what we want.
So, I'm gonna go ahead and choose to calculate the geometry of this field.
So, I'm gonna go back to the name, the top of the table of contents here,
the top of the attribute table here,
and I'm gonna right-click on Area, and choose calculate Geometry.
It's gonna tell me that I'm doing this outside of an edit session.
It's faster, but there's no way to undo it.
That's okay.
This is a new field.
There's no data in there.
It doesn't matter if I mess it up.
So I'm gonna hit yes.
Now it's going to ask me what property I want to calculate.
I want to stick with area, but I'm just showing you that you have the ability to
do perimeter, or the X coordinate of the centroid or the Y coordinate of centroid.
These would be different if you had a line shape file, or
if you had a point shape file.
If you have two different coordinate systems you could
choose which one you're doing.
In this case they're the same.
Now, one thing I'm gonna do here is I'm going to change this from square meters
to square kilometers, and that's only because
things get really big if you're dealing with square meters.
Square kilometers will keep it in kind of a manageable number of, of
numbers within each field.
So, go ahead and hit okay, once you've changed all of this area,
square kilometers.
Okay.
Again, it's gonna say the same thing about the field calculator.
So, now it's calculated everything for me.
I have my things that are really relatively big, and
then I have a lot of smaller ones in there as well.
So that's the area of each polygon that's in there.
It's not the area of each type of each soil class.
But it's the area of each individual polygon.
Now this is a useful thing to have, but let's go ahead and experiment a little bit
with doing calculating features based on other aspects.
Let's classify these areas into kind of small, medium and large.
And this is going to be multi-tiered,
multi-step calculation based on other attributes.
So the first thing we want to do, is create the field for these data to sit in.
So again I'm gonna go over to Table > Add Field.
And I'm gonna call this Size Class. Size Class
Now I want this one to be text, so I can say small, medium, and large.
So I'm gonna go down and choose text.
But I only need about ten characters.
So I'm just gonna restrict it down to ten.
So then hit Okay.
Now the way we're gonna do this is, we could create a very complicated equation
that tells us if it's below a certain area then we're gonna give it this.
But what's a little bit easier to do in this case Is,
if you remember from when we dealt with selections.
Any time we have a selection, those are going to be the only features that
anything happens on by default.
So if I just simply select to the smallest ones, then calculate that new field
as small, it's only gonna do it for those particular ones.
Same thing for medium, same thing for large.
So that's what we're going to do first.
Is we're going to go to our select by attributes right here, and
we're going to choose, area is less than.
Now this is a case where we don't want to hit Get Unique Values,
because they're numeric and it just would give us thousands and
thousands of values or 417 probably different values.
But I already know that the areas that I'm interested in here are going to be those
that are less than 0.05.
So I'm going to do Areas are less than 0.05 square kilometers.
Now I'm going to hit apply.
And you'll see what it's done is it selected all of those
ones that are smaller than .05.
So now, if I go to size class,
field calculator, I'm gonna choose,
don't warn me again now, because I'm kind of tired of this pop up showing up.
And hit yes.
If I now go and just type.
"Small".
And hit OK.
Make sure you have your double quotes in there or it won't work.
It's going to calculate just for the ones that have been selected.
So it's not calculating for anything else that hasn't been selected.
So now if I go back to my select my attributes.
If yours isn't still up, then it's this button right here.
In this case I want to do my area,just going to clear this out. I'm just going to clear this out.
My area.
This is kind of my middle one, is greater than 0.05.
Now, click and.
And this is a little complicated here.
It would be nice if you could just string together the equations here, but
this is what's called SQL, or standards query language, and
it's a little bit finicky in how it likes to have things.
So you want to do area is greater than .05,
and area is less than one.
So this is gonna give us things that are less than one square kilometer but
greater than .05.
So this is our medium class.
So then hit apply.
So you can see it's selected a lot of the ones that are in between there, but
not every single one.
So now if we go to size class, field calculator, and
we just change where it was previously small to medium.
Again make sure you have your double quotes or it'll fail.
It's just gonna calculate it for those.
So our final class, select by attributes,
area is greater than 1 square kilometer.
So these are our big ones.
So on the screen here, we only have one we can see, but
you can see down here that 12 out of 417 have been selected.
And if you just wanted to see the ones that have been selected,
you could choose this part here.
So now we could go to field calculator.
Large Double quotes, hit OK.
So now for every feature in here, we have a size class.
So we have a nice little way of seeing what is a big one, what is a small one.
And what is a medium one.
So there shouldn't be anything unclassified.
It should all be ready to go.
So if we hit clear selection, just to make it a little pretty.
I'm gonna close this down.
And then we can go into the symbology here if we double click on our layer.
Go to your Symbology tab.
And if you use size class as your value field and hit Add All Values.
Now we can sorta color those different layers.
We can see it in a slightly different ways so we can very easily pick out what
are the big ones, what are the small ones and what are the sort of mini, medium ones.
So it's a little bit silly to do this calculation of small medium large and
we can sorta see that as well.
But I put that in here to show you how to do these multi-tier selections and
calculations using the attribute table in ARC GIS.
Now, the next stage here, is we're gonna work with a point shape file of
water quality sampling stations across Indiana.
So at this point,it, we could leave the same data in here.
It wouldn't really do us any harm to leave our soils in.
We could also simply start a new map document.
If you wanna save this as sort of Demo Seven Part One,
you're welcome to, but I'm just gonna start a new blank map document.
And I'm gonna go ahead and add in my water measurements in my water sites table here.
So nothing spatial, no shape files.
Just water sites and water measurements, so add those guys in.
You notice we've gone back to our list by source tab here and
there is nothing showing up because there is nothing spatial yet.
These are just two tables.
So if we open up water sites, what you see is that we have a location county,
the station ID, the state, the latitude, the longitude, and
then this northing and easting, or our quadrant system for Indiana.
For each one of these water sampling stations from this USGS,
now, if we open up the other table here, our water measurements.
What you probably notice pretty quickly,
is that, for in each site, we might have multiple different records.
We might have different measurements, different years.
All sorta other things for these, for these individual stations.
So there's lots of different measurements all at the same locations.
So this is an example of a one to many relationship.
A situation where we would wanna set up a relate between them.
So for each site we could see all the measurements.
Or for a particular year we could see what sites have been measured.
Now the first stage of what we wanna do is convert one
of these into something spacial.
Otherwise it's not really a GIS problem it's just a database management problem.
And this is gonna be water sites, this, this is the one that we wanna deal with
because it has latitude and longitude coordinates.
It's gonna be relatively simple for us to project this into a shape file,
into a vector data model.
Now we're gonna do this by quite simply right clicking on it.
And gonna display xy data.
Now we could do this with latitude and longitude, that's fine to do.
But let's go ahead and do it with the easting, northing.
And I did misspeak a little bit earlier, those are,
this is actually UTM coordinates.
They're not, it's not an Indiana coordinate system.
So we're gonna be able to use the same UTM system that we've used in all
of the proceeding data.
So our X field should be easting, our Y field should be northing,
and then we want to set our Coordinate System to be WGS 1984 UTM, so an 18 north.
16 north.
So we're gonna go in to our Coordinate System tab here,
projected, UTM WGS 1984,
northern hemisphere.
And then simply find 16 north.
Go ahead and hit OK.
And go ahead and hit OK.
So you should have what now appears to be roughly the outline of Indiana.
You can tell that there's a lot of water sampling stations in this sorta corner of
Lake Michigan up here.
But there's also sort of following rivers.
This is the St. Joseph river right here and
a few others sorta surrounding other rivers throughout Indiana.
So at this point we have something that is temporary again.
So when it comes up and
it says water sites events that just means it's a temporary file.
So we do wanna go ahead and save this permanently.
So just choose water sites events.
Go to data, export data.
And if your folder came up correctly, I'm gonna go ahead and
put this in as water_sites_spacial.
Now if you try to give it the same name,
just water sites it's not gonna work cuz there has to be a dbf associated, so
a database file associated with the shape file as well.
So it's gonna give you an error if you don't change the name somehow.
That's why I just append spatial in here.
So go ahead and add it in.
You can see that nothing has changed other than the color.
And at this point we no longer need our temporary events layer.
And in fact, we no longer need our original water sites.
Table here.
Go ahead and remove that.
You should have water side spatial and water measurements.
The next step we wanna do is create a relationship between these.
So we wanna our one to many relationship.
We want to create our relate between them.
We do this vary simly, similarly to how we did the join.
We're gonna go to water side spatial, gonna choose join and relates, and
go down and choose relate.
It's gonna ask you first to choose the field in this
layer that the relate will be based on.
It's station, the other table is water measurements.
If you'd left water sights in there,
that might show up, but just make sure you're looking at water measurements.
And it's already a sorta auto populated station here.
You can give it a name if you had multiple ones in here, but
I think Relate1 is okay in this case.
So go ahead and hit OK.
And when you open up the attribute table for Water Sights Manager of
Water Sight Spatial, what you'll notice is that absolutely nothing has changed.
It can't append records into the shape file.
Into the attribute table for
the shape file because there's multiple records for each one of those locations.
So what's it's done instead is it's created this relate and
it sorta happens behind the scenes until I do something.
So if I choose just a couple of these things here just selecting them,
once I click related tables then send it to water measurements.
It's gonna select all the records in water measurements that are from
those sampling locations.
So you can see that there are a couple of measurements in 1979 but
that's really about it for those particular sites.
I can go back and forth however I want here so in this case I'm sitting
in my water measurements attribute table, it sort of bounced me over there, and
I'm only looking at the six records that are selected.
So if I choose over here to look at all of them and
I want to do a select by attributes.
I wanna just find those sites where a particular measurement is done.
So I'm gonna look at what parameter, what measurement was done.
Then I'm gonna use Get Unique Values cuz there's I think only four of them in here.
So I'm gonna look at all of the sites where I have a dissolved
oxygen measurement.
So there's 3,358 sites that have a dissolved oxygen measurement.
So I'm in my water measurements, I've done a selection here.
Now if I go and
do my relate back to water site spatial, it's gonna take it just a second.
Because it's going through in finding each one of those stations and selecting it.
So there's 1,055 out of the 1,900 or so sites.
If I close down the attributes table you can see that these are all of
the sites that have a water measurement or
a dissolved oxygen measurement from that particular database.
So I encourage you to go ahead and experiment with querying back and
forth on these relates.
So maybe select all the sites that have a measurement from 1980 or
from after 1980 and see, see which ones pop up that way.
So the next thing that we wanna do is we've seen a couple of different
examples of how we would use the relate to sort of see things on the slide.
But there is an interesting thing that we can do kind of working back and
forth on summarizing fields
to give ourselves a little bit more information about the data.
The first of these is working with the water sites here.
So we're going to do one quickly here.
And then we're going to go back and
just do a more brief one on the soil data as well.
So if you have the water measurements table up and
open, you'll notice that you have lots and lots of different things in here.
You have different measurements and you have different years.
So it would be useful if we water to symbolize, say,
the water sites from 1979 for a particular measurement.
We could do that pretty easily.
So, if we clear everything out,
the first thing we wanna do is create a summary table.
And we're gonna do this by selecting a particular parameter in here.
So let's go ahead and go ahead and just reselect this dissolved oxygen.
So what we're gonna end up with is an average value for dissolved oxygen.
So go ahead and hit Apply.
It'll give you all the dissolved oxygen measurements again.
Now what we wanna do is create a summary table that for
each station gives us the average value of dissolved oxygen.
So I'm gonna do that in here by going to,
I'm sorry I'm gonna do that,
right here I'm gonna click on Station and I'm gonna go to Summarize.
Why am I clicking on station, well,
that's what I want to summarize based on each station number.
I want to take all of the values and give the average of them.
So I don't need to choose parameter because I've already selected
just dissolved oxygen.
And it's only going to work off of the selected records.
But what I do want to do is go into value.
I want to get the average value, and it's probably good to get the minimum and
maximum as well.
So it's gonna go ahead and start to send this to an output table.
So let's go ahead and put it some place permanent,
I'm just going to send it over to my data Demo7 folder.
And I'm gonna call it DO by site.
If it gives you an error like this, there's one quick thing to notice in here.
And that's that it's trying to save it as a type file and
personal geo database tables.
We actually want to save it as is a D base table here.
So choose D base table.
Hit OK.
It's going to run all the math behind the scenes.
Create this summary.
We do want to add it in.
I'm just gonna close down a bunch of windows here.
Now when I open this up, for each station
I have the number of dissolved oxygen measurements that were there.
I have the minimum value, the maximum value and the average value.
Now a lot of them only have one.
But other ones we're getting different measurements here.
So some of them have 10, some of them have 8.
So what's kind of neat that I could do here is I could actually go, and
now what I have is something that has a one to one relationship.
For every station I have only one record, and
that one record is in this new table that I've made.
So now I could go through, set up a Join.
Based on Station.
Not going to water measurements but going to DO_BySite.
So now when I open my water site spacial, you'll see that for
every Station that has a dissolved oxygen measurement I'm now seeing that in there.
I'm seeing how many were done at that station,
the minimum value and the maximum value.
So now it would be relatively easy to go in and create for
the average value a sort of color based on how high that dissolved oxygen reading is.
So it's taken out all the ones with a null, and it's showing me just the ones,
it's showing me an idea of how high the dissolved oxygen,
the maximum value, the average value for that particular location is.
Okay, so I encourage you to play around a little bit with this.
Maybe try it with a different measurement, maybe try looking at it in a different
way, maybe for a particular year or something like that.
The last one I wanna show you is just going back to our soil descriptions,
our Soils_NotreDame_WithDesc.
So, just add that back in.
You don't need to create a new file at this point.
I am gonna just right-click and zoom on it because there's quite a lot of these water
sites that are well outside of the realm of where this particular layer is.
But I just want to show another example of when you might want
to create one of these summary tables.
And in this case we have the area of each polygon here,
but we don't have the total area for each type of soil.
So it'll be really easy for us to, for each soil code, create the total area.
We can do that by right-clicking on soil code, choosing summarize.
And then for area, we're gonna get the average, and let's go ahead and
get the sum as well.
So it's sending it to the right location.
I'm gonna call this Soil code by area.
Soil code areas or something like that.
So now this has been added in.
When I open it, for every soil code it's telling me how many different polygons,
how many different features there were, what the average area is, and
what the total area is.
So this tells us something different about the soils.
So we know how many different occurrences they are.
If they tend to be relatively small, if they tend to be relatively large.
So it's a good indicator of something else about the soil data
created from one of these summary tables.
Now in this case,
this isn't one where we would want to necessarily join this back on to
the main data because we've taken out a lot of the spatial information there.
So that's really about it for tables and databases.
I encourage you to experiment around with these a little bit.
Maybe try them on the sample data that you created in the preceding demos.
Feel free to email me or ask any questions through whatever source
you've been given for getting more information.
So thank you, and have a good day.