Understanding ArcSDE Table Relationships

good morning my name is Kel de Hedstrom and I work with the ESRI training team here in Redlands California I want to welcome you all to today's live training seminar understanding arc SD table relationships the topics we're going to be covering today include a general overview of the different ways available to you an ArcGIS to associate your tables together including joins relates relationship classes arc IMS joins and art guests de views we'll also take a look at the different options you have available to you for performing joins with your data including inner and outer joins and finally we'll look at what you can do to optimize the performance of your table relationships through the use of specifying where your joins take place building indexes and building statistics on your tables the format of the seminar will be 20 minutes of slides followed by a software demonstration and then a short review question-and-answer period effective database design includes storing data and multiple tables as a way to organize your database in order to get to the data and all of your tables links or associations can be created between your tables and these links and associations are created between common columns with between the tables you want to link together so let's take a look at the example that we have here we want to make an association between the pol block groups table and the pol census table well we can choose to link the tables together through the bkg column which is the common column to both of these tables associations can be established between multiple data formats for example I can create a link between a shapefile in an arc info table or I can create a link or an association between a feature class on a personal geo database and a table in an arc SD Enterprise database and finally these relationships can be temporary for example they can only exist within a specific arcmap document or they can be permanent and that they are available to multiple geo database users there are different ways we can create relationships between our tables in ArcGIS beginning with arcmap we can build relationships by joining our tables together relating our tables together or by creating relationship classes in arcmap there are various dialogues to help you step through the process of creating an association creating associations between your tables here we have an example of a join dialog and this is where you're going to specify which columns you want to use to create the joint upon which tables you want to use to create joins with and again the columns from the tables you want to join together you're going to specify all of this in this join dialog and we'll take a look at this in the first demo we can also use our chi ms to create associations between our tables and these associations are created using the query and spatial query tags and arc IMS axle or map configuration files and we'll take a look at these further on in the presentation as well and finally we can build relationships between our arc SD tables and our caste feature classes by creating our caste spatial and non-spatial views we will learn how to create views later on in the presentation as well as in the demo and all the relationships mentioned in this presentation can be applied to non arc ste data except for the arc SD spatial views and non spatial views but for today we're going to mainly just be discussing our caste data so let's start by examining the first type of relationship that we can create an arc map which is a joint when tables are joined in arcmap all the columns from both tables are virtually appended together into one large table this does not affect your source data however meaning that the tables are not appended together in the database but it's just a temporary Association that you can create right in your arcmap map document so let's take a look at this example here I have a parcels feature class that I want to join to my land-use attribute table well after examining both of the tables we can see that we have a common column of land-use and both of the tables so we can choose to join on this land-use column and once we create the join we get one large table called parcels that has all the columns from both of the tables placed in this parcels table and we can see that the naming convention is what is going to specify which columns come from which table so in the first column we see parcels dot parcels ID that is telling us that we're getting this parcels ID column from the parcels table if I scroll over a little bit further I'll see the land use Lu desc column and I can see that that is coming from the land use table because it says land use dot Lu desc so I'm getting this Lu desc column from my land use table joined our temporary associations that can be saved an individual map documents this is nice because it allows you to create multiple joins between one feature class and between attribute tables so here we have an example of the parcels being joined to the land use attribute table we can save this in one month map document well I could create a second map document using the same parcels feature class and this time I might want to join it to a Zoning attribute table so I can now create multiple relationships between line by tables in different map documents joins can also be applied across multiple data formats I could join a debased table to a coverage if I want to and this allows you to create joins between all of your tables regardless of the data format that they are stored in one advantage of a joint is that it can be used to symbolize or label upon when we join this parcels feature class to the land use attribute table I can now symbolize my parcels based on this land-use Lu description code or column if I'd like to or I could label it based on that without the join though I would not be able to symbolize based on this land-use description column because my data will be in two separate tables another advantage to using a join is that they can be used to complete a data set for example I have GPS data points feature class and I have a table of attributes about these points well I can create wanting to put these two tables together and then I can export this table out into a different format like a shapefile or a geodatabase feature class to make it permanent data set let's take a look at the second type of relationship you can create an arcmap which is a relate when you choose to relate your tables together a relationship is established between the tables and unlike a join the columns from the tables are not appended together the tables are viewed separately let's take a look at this example here we have the coffee feature class and a percent owner attribute table both of these tables have a common column of coffee ID which we can use to create a relationship upon but as you see once we create this relationship or this relate our tables remains separate and I can make selections from the coffee table and view these bill related records down in my % own table and to do this I have to go to the options button scroll down to the related tables and scroll over to the coffee % own relate that I've created this will open up my perséfone table for me and this will also reflect any selections that I've made in the coffee feature class so if I want to instead of looking at the Java quick you know the records that are related to the Java quick coffee shop I can select the percolator for example and then I need to go back to options again down to the related tables and refresh the relationship so now I'll be able to see the first record and the percent owned table will be highlighted to show me the relationship so now I can see the percolator is you know 100% owned by owner ID number 40 and relates just like joins or temporary association saved in the map document and they can also be a client applied across multiple data formats and is it advantageous to use relate when you have redundant information in both tables and you do not want to make one large table of repeating values relates are often used with look-up tables to maintain a relationship between the table that is all the descriptive information in the table with the corresponding values so I could have my parcels feature class with a zone code column and relate it to a zoning table that's going to have all the zoning descriptions for my parcels so I don't have to store all that redundant information my parcels feature class the third type of relationship that can be created in arcmap is a relationship class in a relationship class behaves just like a relate and that you have just created an association between your two tables however a relationship class is permanent it's a permanent connection between your tables until it's physically removed this allows you to create a persistent connection between your tables that can be access and by mini geo database users or in multiple arcmap documents and relationship classes are created in our catalog by right-clicking on the geo database that you want to create your relationship class in choosing new then choosing new relationship class and you can set it up you know give it a name and specify which tables will be participating in the relationship class and then you will see a permanent relationship the permanent relationships symbolized and stored right along with the other feature classes and tables in the geo database and unlike overlaid a relationship class is only accessible to one geo database meaning that you can only create a relationship class between tables that exists within the same geo database you cannot create a relationship class with tables that exist in two separate geo databases and you as the user have the ability to specify the rules as to how you would like your tables to relate together in the relationship class for example say that I have created a relationship class between a fire station feature class and the table of fire station employees after I create my relationship class I can set up relationship rules that allow me to specify that each fire station has to have exactly five to ten employees and then I can bring this into arcmap and this will be reinforced in arcmap for me and finally relationship classes can be used in editing data when you select a feature that is participating in a relationship class you can edit the properties of all the objects that are related to it as well as breaking up or creating the relationships between all your features now that we've taken a look at what type of relationships we can create an arcmap we'll move on to examining arc IMS and arc SD joins but first let's take a minute to look at what is actually happening when we create a join between our tables when we build joins the software is actually using SQL or structured query language statements to query the database tables to find the table records you're looking for SQL is the language that is being used to communicate with the database Tanning upon the database you're using either Microsoft sequel server Oracle db2 or Informix to go out to the tables and the database and select and join them together for you so here we have an example we have a join between a coffee feature class and an owner attribute table we have three columns from the coffee feature class and two columns from the owner table as you can see we've chosen to join the tables together based on the own ID column from each table well when we create this join in arcmap behind the scenes the SQL statement is being created and that's using this select from and where clauses this equals select Clause is used to tell the database which columns you would like returned so as you can see here we've chosen to return the name column the coffee ID column and the own ID column from the coffee feature class and we've chosen to return the own ID column and the owner name column from the owner after table the from Clause tells the database which tables you'd like the columns from this is where we specify that we'd like all the columns that I've just mentioned from a coffee feature class and the owner attribute table finally the where Clause identifies the condition that you want met or what you want to do with the columns in this example we want to join together the coffee feature class and the owner attribute table based on the own ID columns from each table and as you might have noticed each column has the name of the database and the table that is coming from in front of it separated by periods this is required by the database to alleviate confusion as to which tables and columns you're choosing from inside of the database and you can let ArcGIS software create these sequel statements for you or you can create them manually inside of the database that you're working with to select data or join your tables together okay now we have a polling question are you familiar with our SDE views please answer yes or no by clicking in the appropriate button in the polling window now that we know how the software talks to the database by using sequel statements let's take a look at how our kms joins tables together arc ims joins are created using arc XML or extensible markup language using arc XML tabular joins can be performed within the axle or map configuration file joins are performed using the spatial query or query tags the spatial query and query tags perform the same task except that the spatial query tag can handle spatial queries meaning you can return features you can return features you can buffer your features you can do any type of analysis you'd like to do with your spatial features with the spatial query tag but you cannot do this with the query tag the query tag you can only return attribute information and put to establish to join an arc IMS you need to include two attributes as well the first attribute you need to include is the SQL where clause and this where Clause establishes the common fields and both of the tables that you want to join together the second attribute you need is the join tables attribute and the join tables attribute establishes which table or tables you are joining together by specifying the name of the table or tables that you're joining to the first table let's take a look at an example of this now we have the spatial query tag and we want to join together the FIPS column from the city feature class that's in the DB database you want to join this column to the FIPS column from the school's attribute table that's also in the Divi database and we want to use the join tables attribute to specify that we want to join the school's attribute table to the city feature class and it will look the same in the query tag as well now again we're going to specify that we want to join together the parcels in the zoning table and we're going to use the zone code columns from each table to do this and then again with the join tables attribute we're going to specify that we want to join the zoning table to our parcels table when creating our chi ms joints you can only join shapefiles to DBF tables or arc ste feature classes that have spatial data to arc ste tables with non spatial data you cannot join across data formats and you cannot join across directories or databases both your shapefile and DB F table must be in the same folder or directory and both of your arc SD feature classes and tables must be in the same geo database well looks like 79% of you are not unfamiliar with our guest de views so let's go ahead and take it up take a look at them a little more depth to make sure that real understanding what they are our caste views allow you to associate multiple our caste tables together to show only eight columns that you specify from each table you can create spatial or non spatial views but views can only be made with one our caste feature class that includes spatial data or shape column at a time when creating a spatial view you must include the shape column from the our caste feature class if the shape column is not included or if the view is made between two attribute tables then the view will be non spatial views are just permanent SQL queries stored in the database a view reference is a collection of related tables and as a way to filter out or look at the subset of data from different tables views can be accessed like a table or a feature class in arcmap if the view has a spatial clone then it will behave like a feature class and that it will be able to be symbolized labeled and queried in arcmap if the view does not have a spatial calm if it's a non spatial view then it will behave just like a table in arcmap and you'll be able to join it or relate it to other tables if you'd like to you can also change the SQL statement of your view to give you different data we can go right into the database and change the select from or where clauses to pull up different columns from different tables or change how our columns have been joined together let's take a look at an example of how you're going to create a view you're going to creative using the SD table - o create view command and with this command are specific parameters you need to include to create your view the first parameter is the capital T parameter this allows you to specify the name that you want to name your view the lowercase T parameter specifies which tables you want to get your data from the lowercase C parameter specifies which columns that you want to include in your view from all of your tables or from the tables that you're pulling from to create your view the lowercase W parameter specifies how you want to join your tables together so this is where you're going to set which columns you want to join your tables together on the capital D parameter allows you to specify where your view is being created in which database the lowercase u parameter is where you specify the user who's creating the view and the lower case P parameter specifies the password or the user who's creating that view and we'll take a look at this will create one in the in the demo user read-only and then our KSD clients such as arcmap can't directly edit your views however you can change the underlying source data and this will these changes will be available to you when you refresh your views views can be created on they have to be created actually on non version or non editable tables to avoid returning inconsistent or incomplete results from your database and you have to keep track of this manually because the software will not stop you from creating a view on a version table now let's go ahead and take a look at a few of the relationship tests that we've just talked about we're going to go into arcmap to go ahead and do this and I have created a map document called arc ste table relationship demo for you and I've added three data frames in it just to kind of keep everything organized so we can step through the demo I've created for you today so the first thing we're going to do is create an arcmap join so let's take a look at this parcels feature class that I have and this zoning attribute table that I have when I look at both of my tables I see that I have a common column of zone code so I can use this zone code column to create a join between both of my tables so let's go ahead and do that before I create the join though I'm going to go ahead and clear my selection just to make sure everything's fresh for when we create our join I can do that by going up the options in choosing clear selection I'll do that for both tables then we'll go ahead and create the joint and to do this I'm going to right click on my parcels feature class go to joins and relates and choose choose join then I'm going to choose which column and my parcels feature class I want to join on and in this case it is the zone code column then I'm going to select which table I want to join to my parcels feature class which is the zoning table and then I'm going to specify which column in my zoning table I want to join to my parcels feature class which is zone code then I'm going to click OK at the bottom of the join data dialog window but this will be off of your screen but I'm pressing ok and once I do that you can see that all of the columns from the zoning attribute table have been appended up into my parcels feature class table so now you know we see that oh you have all the columns from the parcels and all the columns from the zoning tables up here together and we can tell which columns come from which tables by looking at the naming convention again here we have parcels zone code so this zone code column is coming from the parcels table if I scroll over I see the zoning dot zone code column so this zone code column is actually coming from the zoning attribute table so that's how you again you're going to tell where your columns are coming from which tables are coming from so let's go ahead and take a look at how to create a relate now see the difference but to do that I'm going to have to remove my joint so to remove my joint I'm going to go back to my parcels feature class I'm going to right click on it go back to joins and Rolaids and this time choose remove joins and then I can either remove my individual join or just choose remove all joins and we can see now the parcels feature class or table has gone back to the original how it was originally the the tables that were appended have been removed so to create my relate this time again I'm going to right click on my parcels feature class go to joins and relates and this time choose relate again I'm going to specify which column I want to make my relate on I'm going to scroll down to zone code this is in the parcels feature class I'm going to choose which table I want to relate to my parcels feature to class which is the zoning table and then I'm going to choose which column in the zoning table I want to use to relate to my my parcels feature class which again is zone code and then I'm going to give my relate and name so I'm going to call it parcels zoning relate and it's important to give you relate a unique name so that if you have multiple tables and feature classes in one of your data frames you won't get confused as to which tables are related to each other which feature classes are related to each tables so it's a good way to keep everything organized so now you can click OK and notice that nothing really happened it doesn't look like you're anything occurred because the my columns from zoning we're not appended up into my parcels feature class but there is a relationship there so let's see if we can and take a look at it tell me go ahead and select a couple of records from my parcel feature class and then I'm going to go to options down to related tables and choose that that relationship that I just created that relate so again this is why it's important to to properly name these because if I add a whole bunch of relates and they were just named relate one relate to and relate three I would have no idea of which tables I what we're referring to and with relate one so I now I can click on my relate and I can see the appropriate record has been highlighted down in my Zoning attribute table so say I want to choose a different set of records and see their relationship so I can go and switch my record choice up here in my partials feature class but as you can see that has not automatically been updated down into my zoning attribute table I have to refresh or manually update my selections so I again have to go to the options button on the Parcells feature class go to related tables and then choose that relate that I just created and this will refresh my relationship so now I can see I have the correct a selection made in my zoning attribute table so now I'm going to go ahead and again clear my selections just to clear everything out I go into options clear selection and this time we're going to go ahead and take I'm going to remove my relate first actually excuse me you know to joins and relates go down to remove relates and remove that related I just created and now we're going to take a look at a an arc SD spatial view so I'm going to activate my spatial view data frame and to create room if you recall to create a spatial view we have to use the SD table - Oh create view command so I'm going to activate my command prompt and now I'm going to type in SD table - OH create view then I'm going to enter in the rest of the parameters that we talked about in the slides so the first parameter I'm going to enter in is capital T this is where I'm going to specify the tables that I'm going to use in my view so I'm use the parcels and the zoning table or tables next I'm going to excuse me excuse me I just mix those up for you the capital T is where you specify the name of the view that you want to create so I'm going to actually type in parcel view so that's the name I want to give my relate I'm just a little bit ahead of myself so the next parameter the lowercase T is where I go I'm going to type in the actual tables that I want to use in my view so this time is when I type in parcels and Zoning the next parameter I'm going to enter in is the lowercase C parameter this is where you specify the columns that you want to include in your view so I'm going to include my parcels shape column so this is what's going to make this be a spacial view because I'm going to include that shape that geometry information from my parcels feature class and I'm also going to include my zoning description column from my zoning attribute table next I'm going to specify how I'm going to join my tables together and I'm going to join my tables together using the parcels using the zone code column from both my parcels and zoning tables then the next parameter I'm going to specify is where I you will be taking place so I'm going to be creating it in the Wilson database under the user calda and caldas password is my password okay I'm going to hit return and see that my view has been successfully created so now I can go back into arcmap I can and then I can add my view into my map document so I choose my parcel view and add and there you can see we have my view that we've just created but notice that one of the parcels is missing the parcel is missing because of the type of the join that we have just created when are used when creating this view by default when you create an arc SD View you create an inner join and we're going to discuss what an inner join is in the next topic but for now let's take a moment to review what we've learned and answer any questions that you might have up to this point so we have just taken a look at some of the different types of relationships that we can create between our tables and arcgis we've learned how to create a join which is going to virtually append your columns together from both of your tables we've learned how to create a relate which is going to keep both tables separate but it'll create relationship between your two tables and you have to refresh your relationship after you make different selections we talked about relationship classes they allow you to create a permanent relationship between different tables within the same geo database which is accessible the multiple geo database users we also learned how the software communicates with the database through sequel statements and how they are used to select and join our tables together we also talked about our chi ms now you can use the spatial query or query tags as well as the sequel where Clause attribute and the join tables attribute to join you shapefile DVF's and your arc st feature classes and tables together and finally we learned that an arc SD View is just a permanent stored sequel query as well as how to create them with the SD table - o create view command and that it is very important to make sure that you have everything spelled correctly and qualified correctly otherwise you will not be able to create your view so to answer some questions for us today we have Brenda Simmons who is a colleague of mine in the training department go ahead Brenda good morning I have Janet from Florida and she says can we create an ste view by simply using the sequel statement like creating a regular view or I think she's asking a regular view and the answer is no when you're when you're creating the ste view you're creating a spatial view so it's expecting to have one of your tables with a spatial column on it and sequel server or C programming in sequel is very powerful but it doesn't quite understand about that spatial column so you need to use the arc ste table or the ste table - o create view command to create your spatial views victor in germany asks can I import tables from Access 97 we have the ability to do joins and relates with access tables I'm not quite sure about that version 1997 but at the end of the seminar check back in a couple of weeks and calda is going to post with the publication other seminar answers to these questions so we'll make sure that she looks at that particular version to see if if it's compatible on Daisy and Tennessee asks do I have to join a table to select from it no if you have a table in your arcmap table contents in your document you can do the select by attribute and work with with your tables without doing joins it relates to any sorts of geometry Michael in Calgary asked can I create permanent relationships from non database datasets and yes you can all of our shape files debase tables the arcinfo tables if you can make a connection in our catalog to Oracle sequel server db2 or Informix using our guest EE and oledb connection or a direct connect then you have access to those tables and you can create joins and relates to other tables as well and finally Bill from Billings asks with arc ste for coverages oh I'm sorry he asks what versions of arc ste does this apply to caldas presentation is completely with eight arcgis eight arc ste 8 technology and I believe on our machine she has arc ste 8 to loaded and killed us you got a question now let's take a question from Tammy and Virginia can you symbolize on a relationship class you cannot symbolize directly on a relationship class you can bring that into arcmap and you can join create a join and then you can symbolize but you cannot symbolize directly with a relationship class you have to create that join between your tables okay let's move on to the second topic where we're going to take a look at a couple of different options you have for joining your tables together including inner and outer joins let's revisit the first demo to find out why that parcel was missing from our parcel view let's look at the the parcels and Zoning tables that we have here we created the view by joining the zone code columns from both the parcels feature class and the Zoning attribute table in the parcels feature class there's a parcel with a zone code of calm but in the zoning attribute table there is no zone code of calm and there's no zoning description of calm and when we created the inner the inner join we only returned those records that have a match in both columns so in this case because there was no match for the calm parcel it was dropped out or left out when the join was created because by default when you create an arc SDE view you use an inner join to link the tables together and again this is why our parcel dropped out in the parcel view so let's take a look at the sequel statement that is used to when you create this inner join again we have the Select clause this is going to choose the columns suit that you want to appear in the view and then this example using the star means we want to select all the columns from all that's it from both of the tables to create our view the from clause is going to specify the parcels that we want to use the parcels feature class in our view and then we're going to use the inner join clause to specify that we want to use an inner join to join the zoning and the parcels tables together then when you use the on Clause you're specifying that you want to complete the inner join using the zone code columns from both of the parcels and the Zoning attribute table and you can go into the database and change the sequel statement at any time to be a different type of join and we're also going to take a look at an example of an inner join in the next couple slides because you can also create inner joins using arcmap and to do this one you're in that join dialogue that we looked at in my first demo you're going to actually click on the advanced button which is down near the bottom of the dialog and then you can choose the keep only matching records option in arcmap will actually perform the inner join for you another type of join that can be created between our tables is an outer join and an outer join will return all records from the tables regardless if they have a match or not so to be able to perform an outer join in arcmap again you're going to press the advanced button in the join dialog and this time you're going to choose keep records and an outer join is what arcmap is going to do by default so if you don't press that advanced options dialog it's just going to create an outer join for you and unlike inner joins there are three different kinds of outer joins you can perform you can perform the left outer join a right outer join or a full outer join and we're going to take a look at exactly what these are in the next slide and with ARC st views you have a choice of using all three types of outer joins either left outer join the right outer join and a full or a full outer join however arcmap only supports the left outer join and when an outer join is created the sequel's statement looks a little different than the sequel statement from the inner join again you have the Select statement which is going to select the columns that you want from your the tables that you're working with you've got the from statement which is going to specify that you want to start with the Parcells feature class and you want to left outer join now the zoning table to the parcels feature class so this time instead of saying inner join were you going to use the clause left outer join to perform a left outer join with between our tables and then again where you use the on Clause to specify that we want to join the two tables based on the zone code from each of our tables so let's take a look at how these inner and outer joins work again arcmap supports inner joins and left out our joins only whereas our guest eview support all four types of joins so here we have an example of two source tables table 1 and table 2 let's take a look at the results at a return when we use various types of joins to put these tables together so when we create an inner join again recall that when you inner join only records that have matches in both tables are returned so that's why when we create an inner join between these two tables we only get records number two and records 3 as our results because record number 1 does not from table 1 does not have a match in table 2 and record number 4 and table 2 does not have a match in table 1 let's take a look at outer joins again when you perform an outer join all records are returned regardless if they have matches and both tables are not so when we specify that we want to create a left outer join we're going to use all the records from table 1 and join them to table 2 so we'll only get the matches from table 2 but we'll keep all the data from table number one when we specify a right outer join we're going to be doing the opposite we're going to keep all of the dáil from table number two but only keep or return those records that match from table number one so this time that's well why we have all records 2 3 & 4 from table 2 and only those that match from table 1 which is 2 & 3 and finally you can create a full outer join this is where you keep all the data from both tables regardless if they have matches or not so that is why we have records 1 2 & 3 from table 1 and records 2 3 & 4 from table 2 inner joins tend to process quickly but can end up leaving out records if they are not properly matched in both tables as we saw with the in the first demo with the partial view outer joins can be used when you want to keep historical reference of all of you data to maintain all the attributes from one table and even if they don't have a match in another table or as we saw in the demo example it'd be more appropriate to use the left outer join to maintain and make sure we keep all those parcels in addition to considering join types you have to take cardinality into account when creating table relationships cardinality describes how many records in one table are related to how many records in another table all relationships between tables must follow rules for connecting together and that they fall under specific cardinality choices so we can connect tables together that have a one-to-one relationship so we have one parcel that has one owner and an attribute table or you can use one-to-many cardinality where you have one parcel that has many owners this you could represent this in the real world when you have one powerful that has many transformers for example you can also have many to one which is many parcels having one owner or this is what we actually just looked at in the demo where we had many parcels that went to one zoning code in our zoning table and finally you can have many to many where you have many parcels that have many owners which might be more realistic of a commercial situation where you have many buildings to many owners it's very important to understand the cardinality or relationships between your data before you you create a join or late because as we'll see in the next slide this this will affect which choice you make depending upon the software you were working with either arcmap arc SD or arc ims there are different supported cardinality choices available to you there is no coincidence as to why again we've been using a many to one relationship between the parcels and Zoning feature class in the demo and that's because both arcmap joins and relates support this type of cardinality so arcmap joins only support one to one or many to one cardinality if you try to create a join with one-to-many cardinality you you will have records that will be cut off because arcmap does not support that you can also with relates you can create relate using any type of cardinality that you like one-to-one one-to-many many-to-one or many to many they're all supported in arcmap relationship classes you can create a relationship class with your one-to-one cardinality one-to-many cardinality or many to many cardinality with our guest D views you can create a view with any type of cardinality however one-to-many cardinality and many too many cardinality is not supported in arcmap same with ARCA XML or arc ims joins can create a join with any type of cardinality but those with one too many or many too many are not going to be supported in arcmap so now that we've learned what inner and outer joins are let's take a look at how to create them as well as how cardinality comes into play when creating table relationships so I'm going to go back into arcmap and begin by taking a look at the spatial view that we created in the first so let's open up the original parcels table and the zoning table and again let's look at what we have here again in this parcels table we have that calm parcel which just does not have a match down in the zoning attribute table and again by default nark st you're going to create an inner join so when we created that view that calm parcel dropped out because it doesn't have a match in the zoning attribute table so let's see if we can do something about that let's go ahead and manually change the sequel statement of the view to create an outer join between the tables so all of our parcels will be accounted for and on my machine I'm using Microsoft sequel server 2000 so to do this I'm going to open up my sequel server Enterprise Manager and I'm going to open up my Wilson database because that's where I created my view click on the views and you can see it's not here right now to be able to see it I have to refresh my views now I can see my parcel view to be able to get to the sequel statement I have to open it up or or go to the design view and now I will be able to see the sequel statement that was used in creating the view so I'm going to just go ahead and reformat it a little bit so we can see it a little easier and as you can see we have the Select statement selecting the shape column and the description column from our tables we have the from statement which is going to say we want to join from the parcels feature class and then we want to do an inner join on the zoning with the Zoning attribute table we can go ahead and change this to left outer join so now we'll be creating a left outer join between the two tables and we'll be able to see our parcel again so I'm going to go ahead and run or execute the statement and I'm going to save it and I'm going to minimize my sequel server Enterprise Manager and I'm going to refresh arcmap and as you see once I refreshed arcmap our parcel came back because we use a left outer join to join the two tables together instead of the inner join so now I can go ahead and use the identify button to identify my parcel and we can see that the description is null and again that's because there is no com description in the zoning attribute table so let's go ahead and look at how arcmap and we can create an inner join in arcmap so i'm going to switch back to my joins and relates data frame i'm going to activate it again and this time we're going to go in and make a join between our parcels and zoning layer and this time when I choose join I'm going to again to specify the zone code from the parcels that we want to join to the zoning table and we want to join on the zone code column from the parcels this time I'm going to click advanced and now I'm going to choose the keep only matching records option to create the inner join then I'm going to click OK and again I'm going to click OK which is off of your screen and then I'm going to refresh my view and as you can see the parcel dropped out again because we just created an inner join using arcmap so finally let's take a look at how cardinality plays a role in creating relationships between our tables then you go ahead and activate my cardinality data frame and let's look at the tables that we have we have the parcels table again and this time we have an owners table so to take a look at the relationship we're going to create a join or relationship based on this parcels ID column from both of our tables let's take a look here we have a parcel ID 1 2 3 4 and so on and parcels and then owners we have 1 2 3 4 4 so we must have a one-to-many relationship between our parcels and our owners attribute table so let's see what happens when we create a join and we clear my selections just to clear everything out and now I'm going to create a join on my parcels label parcels layer so we click join this time I'm gonna use that parcels ID field use the owners table and again the parcels aidid feel from the owners attribute table and I'm going to just use the the left outer join is the default option and then I'm going to click OK now we can see all the columns have been appended together into my parcels table which is what occurs when you do a join so let's go ahead and take a look at what we have here scroll over to the parcel owners let's look at number 4 all looks like a number 4 we lost our second owner because above in the owners table we have Cunningham and silver tan who owned parcel number 4 and down here in my parcels table after doing the join only Cunningham owns parcel number 4 so I've lost my my second silver tan record so again it's important to choose the correct type of cardinality when creating your joins because if you remember arcmap supports a one to one an diminutive one join only so let's clear our selections again and this time let's try creating a relate to see if this works a little better so i'm going to route remove my joint as we've seen before just going to remove join and this time i'm going to create a relate again i'm going to relate on the parcels ID column and I'm going to name my relate parcels owners relate so I know which relationship by working with which tables and again you can see the tables remain separate because when you're creating relate you create that relationship about your tables remain separate I'm going to go ahead and select parcel number four and see what we get for owners so I'm going to go to options refresh the relate that I've created and we can see that both of my owners have been selected for partial number four so that one-to-many relationship has been maintained in my relate because again relates support all four types of cardinality so let's go ahead and review what we've looked at we've looked at different options available for joining with arcmap an arc SD can either use an inner and outer join again with inner join you're going to keep only those records that match and with excusing with an inner join and keep only those records that match and with an outer join you're going to keep all records and regardless if they have a match or not and you can use a left-right or a full outer join and then again you also have to choose which type of relationship where you're going to use depending upon cardinality you have one-to-one one-to-many many-to-one or many-to-many cardinality choices for your your relationships so let's go ahead and take about two to three questions from this section go ahead Brenda here's a question from Michael in Boston and he asks how would I change the view definition if I'm using Oracle DBA Studio or sequel press first you have to create the view Michael using the ste table - they'll create once you've done that you can use the database to drop the view recoded it I use db2 I'm most familiar with db2 so I would go into the db2 command line and inside of there rewrite the query and all of the metadata that you created when you did the ste table myself create view is still there so all of the changes that you make at the database level are then applied to UM the view and you're ready to work with it again with your new parameters um Matt in Norwalk asks why would I use a relate instead of a joint what is the advantage and I knew in Raleigh as a similar question can I relate via many to many Association and that's the first thing you want to consider when you're using a joint instead of a relay is the cardinality um with a with the join you can do a one to one or many to one cardinality if you're using that many too many cardinality then you want to make sure your if you do a join and you had a many many cardinality you're going to lose data like in the demo that that Hilda did where she lost one of her parcels Hilda all right and James asked how do I symbolize honor late and you cannot symbolize on a relate you have to create a join to be able to symbolize on the table that you're creating a relationship with so let's move on to the third topic and which we will discuss how to optimize the performance of your table relationships by understanding where your relationships take place either on the client or the server building indexes and calculating statistics so first let's take a look at the relationships processed by the client for example arcmap the types of joins processed by a client include all of arcmap outer joins when you're using the keep all records option and they advance join options dialog and join dialog also when you're doing joins across connections or across different data formats when I'm creating a join between a shapefile and an arc info table arc map or the client is the one actually creating that that joint for you anytime you're doing relates or relationship classes as well the client arcmap is the one who's maintaining that relationship for you the benefits of having the client process your relationships is that this enables advanced functionality that you can join across database connections we can join different data formats together the drawback is though is that is potentially very slow especially if you're working with large data sets I can take arcmap a while to symbolize on tables that you've joined together or to work with those relationships and let's take a look at relationships process by the server or your database these types include arcmap join when you're creating an inner join we're using the keep only matching records option and it's across the same database connection so if I am doing an inner join between a personal geodatabase feature class and an arc ste table that will actually be performed by the client because in order to have the server or the database women join you have to have both of those meet both of those criteria where you're doing the inner join s across the same database connection anytime you're creating Arc IMS joins using the query or spatial query tag as well as our caste views the server or the database is the one always processing that relationship for you benefits to having the server process your relationships is that you get the best performance it's much quicker it's they're set up to handle large amounts of data you know large tables and the server or the hardware is usually much faster than an arcmap the drawbacks are though is that this is going to require advanced knowledge you have to know where to go into your database to be able to change that sequel statement as well as you have to be aware of which users have which privileges to be able to go in and change those statements or create views and so on a way to increase your performance of your relationships is by creating indexes this will allows you to quickly search through your attribute of your spatial data creating index is just like using an index in the back of the book it's much quicker to be able to run to the back of the book and look at that index to find out what you're looking for instead of having to start from page one and go through the whole entire book to look for what you're trying to find these indexes can be created with our catalog or in our caste using the SD table - Oh create index command as well as directly in the database that you're working with and this is going to depend on if you're using sequel server or Oracle you're going to create these index different ways and we recommend that when you're creating indexes you always create indexes on your join columns so in the demo we joining the parcels in the zoning table with the zone code column so we recommend creating indexes on the zone code column and both the parcels and both the zoning attribute table so you always want to index the join columns on both of your tables and finally another way increase your performance of your relationships is to build statistics on the tables and the feature classes in your database this is going to allow your database to choose the best execution plan for how to go about searching for records within your tables the database will compute how many rows are in each table as well as how many unique values are in your tables as well it just helps the database go through and perform these joins and and relationships for you the statistics are created with art catalog by right-clicking on your feature class or your table and choosing analyze then you can choose to either analyze the F table in the business table or create statistics for both of those you can also do create statistics using the arc SD SD table - Oh update DBMS statistics command and finally you can use statistics right directly in your database and again this is going to depend on how you're going to do this is it going to depend on the database that you're working with finally we suggest updating your statistics when your data changes significantly when you load data into your database or when you compress a version during editable database it's always a good idea to rebuild your statistics or recalculate them to make sure your DBMS is using the best execution plan to go through your your tables to find your data for you and then review there are a few ways in which we can optimize the performance of your table relationships this is includes creating relationships on the server one at all possible as well as creating indexes creating indexes on both of the join columns for both of your tables that you have putting together as well as creating statistics on your columns and you excuse me on your tables in your future classes I think we have time for a couple of questions so go ahead and Brenda um patty from Bowling Green asked to relationship classes effective performance and yes they do whenever possible you want build rules into your Joe database instead of using relationships that would include those pick lists the sub in the value list creating subclasses and domains and then making cardinality rules about those so yeah they do affect your performance Patricia asks the column she's putting tables together in the column definition match but she isn't getting any attributes that the columns are moving over to her table but she isn't seeing any attributes first you're getting the columns because they match so the definitions are matching during the join so you're getting them joined together but you need to look at your cardinality if your cardinality is many-to-many then you probably needed to do a relate instead of a join and that's why none of your fields you're not finding any values in your fields okay um Jeff and Wisconsin ask why do you need to add object ID to the non-spatial SD table to be able to select by attributes well the databases don't understand the the the the spatial side of your GIS data and so you have to give or add this object ID to your non spatial data so so the the system can go ahead and and manage the table properly James S is their way to make the relate static keep the relate without always having to redo the relate each time the query is made and that's by default I think somebody else was asking about parcels will my parcels automatically update the reflect the changes that are made to the ownership table and yes it will these are static relationships that your that you're creating well it looks like we're out of time on behalf of ESRI thanks for attending and we hope you'll attend our next live training seminar on November 14th using art catalog Tips & Tricks

Channel: Mahmoud Abdelrahman

Views: 16,109

Rating: 4.7419353 out of 5

Keywords: ArcSDE Table Relationships, maptitude, geographical information sytem, ESRI, kotobgis, geodatabase, arcmap, arc desktop, survaying computers tutorial

Id: 4LgWYwPV8Ns

Length: 60min 44sec (3644 seconds)

Published: Sat Apr 30 2011