5. Data Design Separating Data, GIS kurs, GIS Data Formats, Design, and Quality, W1 Course Overview & Data Models and ...

[ Pobierz całość w formacie PDF ]
[MUSIC] Welcome back everyone. In this lesson, we're going to cover datastructures and joins in database tables. This sounds maybe a little moretechnical than some of you thought you signed up for. But it's a really critical componentof GIS that you should understand. And it's easy to understand atthe level that you need to know it. To begin with, let's take a data tablesimilar to something that we might have in my line of work of watershed sciences. And it's going to be field data which Ihope you can easily adapt to the type of work you're doing. It's where someone goes out and collectssome information using a sensor and a GPS at some remote location and thenbrings it back and needs to analyze it. So we'll start by creating a table here that'll look just like yourdata tables in ArcGIS. And I'm going to create a column here. I'm just going to call it ID. That'll be our primary key columnlike you're used to from the datasets you've been working with. And then, if we think about it, probablythe next most basic column we need in a GIS is a location field of some sort. We're used to that beingcalled Shape in ArcGIS, but you could have your latitude and longitudecoordinates or something like that. For now,I'm going to call it Shape just so it mirrors what you'llprobably see in ArcGIS. And then, what's left isthe data we actually captured when we sent somebody out into the field. So in this case,we're going to have Velocity or for stream velocity, the measure of flow. And then we'll have Depth forthe stream depth at that location and then we may also want toknow who took that measurement. So I'm going to createa field called Observer here. And that's who was sent out in the field. That, in this case,could be something like the name. So let's create a couple records. I'm just going to give themIDs really quickly, 1, 2, 3 and Shape will just besome sort of binary data. We can't actually read butArcGIS can display it for us and that'll go all the way through there. And then for Velocity and Depth,I'm just going to make up some numbers so we can work with them. 12, 8, 7 andDepth in whatever units they're collecting in, might be 4, 17, 8. Something like that. And then Observer,give these people names. Liam, Sarah and Kelly. Okay, sowe have a really basic data table here. Records our information in a waythat seems acceptable to me and doesn't really have any special cases. We could just keep adding more and morerecords, more locations, more velocity, more depth, more observers. But where it starts to get concerning isas we grow out in specific directions, we run into potential complications wherewe want to start separating our data into multiple tables that we bring backtogether with things called joins or relates in ArcGIS. In database parlance, it tends to bejoins across table relationships, but in ArcGIS there are joins and relates,which we'll explain at some point. So, the next thing we might want todo with this data table would be to add a field indicating whenthis information was collected so that we can trackobservations across time. So I'm just going to remove the end here,So we can extend our tableout a little further. And add another field called Date. As a side note, you don't generallywant to actually call your fields Date. That's what's called the reserved word andthe database might choke on it. You might call it somethinglike observation date. But for this particular example we'llcall it Date just to keep it short. And I'll put 2016-01-14, which, it says 14, for today's date. And then we'll do 2015-12-7-07 there and then we'll do 2015-11-18 or something like that forthose observation dates. If I start adding more recordshere with more dates and more locations, this is fine. But think about whathappens if I start wanting to record multiple observations forthe same location here. I start duplicating informationabout my location and if we do that, we're going to get multiple featuresin ArcGIS at the same location. And they're going to overlap each otherand that's going to be inconvenient. Because if all the sudden I want tosay edit the name of a location or the actual location I need to correct it,then we run into issues where we have to do that across multiple recordsand that's time consuming and error-prone. So what do we do about it? We're going to use that thingcalled joins we were talking about. And we're going to create a new table andseparate it out so that we can attach it back when we need it but that the datais kind of in its own object. We have data tables that have a theme,that they can characterize a specific phenomenon and then we can bring themtogether to understand something larger. So, I'll do that right over here fornow, and we'll call this table Observations. And then I'll create a tableover here that I'll call Sites. And in the Sites table, I'm also going tohave an ID, just like in the Observations table, but it's going to be an IDthat refers to its own things. And, in that Sites table, we're basically going to take thisShape field and move it over here too. So, we'll have ID,we're going to have Shape and maybe we'll have a site name nowthat we have its own table for it. Site Name, and we'll draw those fieldsdown and I'll give it a few IDs here. 3, 4, andthen this is still binary data here. It represents featureinformation in ArcGIS. And then we have a site name. I'm going to call them Site A and Site B. And maybe this one will be likeB downstream or something. Indicating it's still near B,but it's not exactly at B. And then we'll call this one Site C. Now that I have it in this table, I canremove it from the Observations table. And we'll just strip offthis whole field in here, And we'll rewrite that in a moment. We're going to modify this table now. In programming terms, a lot of timesthat's called refactoring, when you take something you already have and you changeit to update it to your new parameters. You call that refactoring. So we're going to have two fields still, because we still need an ID field forour Observations. And if you were going to do this in place, they'd keep their same existingIDs if we were moving it out. The Sites would get this new ID and our Observations field would have theirsame IDs so we have our ID field. And then we have our Site_ID field. And what we get with that is we'regoing to reference the ID field of the Sites table in our Site_IDfield to say, this Velocity, this Depth measurement occurredat the site with this ID. And then, we're going to bring thosetogether in ArcGIS when we need to know information about the site,such as its location or its name. And in ArcGIS in particular, we'd actuallygo in the direction of starting with the sites table that has the location and attaching this informationin that direction. And a lot of database systems,you might start on this other side. So we'll reference it back andsay that this velocity and flow measurement occurredat Site with Site_ID 1. And this one occurred atSite with Site_ID 2 and this one occurred at Site with Site_ID 3. These happen to correspond right nowjust to keep it consistent with what it was before. But we can add a new recorddown here with ID 4 and let's say that this onealso happened at SiteID 2. So we have multiple recordsat the same site and now we can attach thoseback to one site over here. And 11 and 13 and let's say this one also was done by Sarah. And it occurred on some date. So, now we have additional recordsto show that these aren't unique. This ID field is a referenceto this ID field. And one way that we can keepthis straight in our heads is if you make your table names plural, soinstead of just being Site, this is Sites. This has multiple different sites inthis table and then our foreign key here which references the key field,the ID field in a foreign table. It starts with the non-plural version ofthe table name and then ends with ID. You can come up with your own system. This isn't a rule. It's just a way to keep it straight sothat if I send this to somebody, they automatically know without muchadditional documentation that the Site_ID field probably references the ID fieldin Sites and can be joined that way. Now, for a moment,just to make this a little stronger for you, let's take a moment tovisualize how that join would occur. If I have these Sites over here andthese Observations. If I have Sites in ArcGIS and I want toattach the data, I'd use the join tool, which we'll show you in a screencast. I'd use the join tool andattach this information. And how that would work is recordhere with Site_ID gets appended to the end of the record for Site_ID 1. And the record here with Site_ID 2 thatoccurred at Site_ID 2 rather goes and gets appended to Site_ID 2. And same for Site_ID 3. But then what happens, andthis is what makes this something called a one-to-many join, is this one,since it also has Site_ID of 2, in ArcGIS, we'd use a relate to connect both ofthese records with Site_ID 2 here. And get the records to show up forus in our attribute table. Where this information with the Shapeshows up with both of these records. And that way we can go query forinformation at Site_ID 2 and see everything that's happeningat Site_ID 2 in one shot. I'm going to erase these now soyou can see me again. We've covered a number of concepts in thisvideo already, so let's pause it here and we'll continue on with some moreadvanced concepts in the next video. In this video, we covered how toseparate our data into multiple tables with a type of join calleda one-to-many join. We utilized primary keys and foreign keys to reference data in theSites table from our Observations table. And add additional informationto our observation records... [ Pobierz całość w formacie PDF ]

  • zanotowane.pl
  • doc.pisz.pl
  • pdf.pisz.pl
  • styleman.xlx.pl

    		•