In this topic
In ArcGIS, there are several methods to join data depending on the data sources and the cardinality of the data. This article discusses the following:
QueryTables allow you to build virtual tables representing queries that can involve one or more tables from the same geodatabase or Object Linking and Embedding database (OLE DB) data source.
When building a QueryTable, you can manufacture an object ID column based on a primary key. This ability allows you to perform one to many joins where the output has an object ID column and therefore supports selections.
When the QueryTable is created, it is returned as a read-only table or feature class depending on whether or not a shape column is involved. It can then be added to ArcMap as a layer or used as any other read-only table or feature class. The query can be persisted in a layer file or a map document. If changes are made to the tables involved in the query, they are reflected in the QueryTable since it is a virtual table.
To use a QueryTable to perform a join, you must first create a QueryDef object, which defines the join. The IQueryDef.evaluate method can then be called to get a cursor of the join results. The following code example shows how to join a streets feature class (using the OBJECTID field as the primary key) and an altname table, which holds alternative names for streets (using the JOINID field as the foreign key):
[Java]
static void viewAlternateStreetNames(IFeatureWorkspace featureWorkspace)throws
Exception{
// Create the query definition.
IQueryDef queryDef = featureWorkspace.createQueryDef();
// Provide a list of tables to join.
queryDef.setTables("streets, altname");
// Set the subfields and the where clause (the join condition in this case).
queryDef.setSubFields(
"streets.NAME, streets.TYPE, altname.ST_NAME, altname.ST_TYPE");
queryDef.setWhereClause("streets.OBJECTID = altname.JOINID");
// Get a cursor of the results and find the indexes of the fields to display.
ICursor cursor = queryDef.evaluate();
int streetsNameIndex = cursor.findField("streets.NAME");
int streetsTypeIndex = cursor.findField("streets.TYPE");
int altnameNameIndex = cursor.findField("altname.ST_NAME");
int altnameTypeIndex = cursor.findField("altname.ST_TYPE");
// Use the cursor to step through the results, displaying the names and altnames of each street.
IRow row = null;
while ((row = cursor.nextRow()) != null){
System.out.println("Street name: " + row.getValue(streetsNameIndex) + " " +
row.getValue(streetsTypeIndex) + " - Alt. name: " + row.getValue
(altnameNameIndex) + " " + row.getValue(altnameTypeIndex));
}
}
Once the QueryDef is defined, you can use the TableQueryName class to create the QueryTable. The following code example shows a function that takes a QueryDef, the geodatabase workspace containing the tables, and the name of the new QueryTable. It also takes an argument for the comma-delimited list of key fields to use to manufacture the object IDs. If this is not an option, you can set the makeCopy parameter to true and provide an empty string for the key fields.
[Java]
static ITable makeTableQuery(IQueryDef queryDef, String keyFields, boolean makeCopy,
IWorkspaceName workspaceName, String tableName)throws Exception{
// Make the new TableQueryName.
IQueryName2 queryName2 = new TableQueryName();
queryName2.setQueryDef(queryDef);
queryName2.setPrimaryKey(keyFields);
queryName2.setCopyLocally(makeCopy);
// Set the workspace and name of the new QueryTable.
IDatasetName datasetName = (IDatasetName)queryName2;
datasetName.setWorkspaceNameByRef(workspaceName);
datasetName.setName(tableName);
// Open and return the table.
IName name = (IName)queryName2;
ITable table = new ITableProxy(name.open());
return table;
}
The RelQueryTable class allows you to join tables from different data sources. For example, you can join a dBASE table to a file geodatabase feature class.
To create a RelQueryTable, you need two tables and a relationship class. The relationship class can be persisted in a geodatabase or generated in memory as a MemoryRelationship class. When creating a RelQueryTable where all the data is from the same source geodatabase or OLE DB connection, it is recommended that you use the inner join option. RelQueryTables also do not support one-to-many joins.
RelQueryTables, like QueryTables, are read-only and virtual in that changes made to the base tables are reflected in the RelQueryTable.
The following code example takes the tables to join and the fields on which to base the join and returns a RelQueryTable:
[Java]
// For example, targetTable is the streets feature class
// joinTable is the altname table
// fromField = "OBJECTID" (primary key)
// toField = "JOINID" (foreign key)
static ITable createRelQueryTable(ITable targetTable, ITable joinTable, String
fromField, String toField)throws Exception{
// Build a memory relationship class.
IMemoryRelationshipClassFactory memRelClassFactory = new
MemoryRelationshipClassFactory();
//Create object class for the tables since table cannot be cast to object class
IObjectClass targetClass = new IObjectClassProxy(targetTable);
IObjectClass joinClass = new IObjectClassProxy(joinTable);
IRelationshipClass relationshipClass = memRelClassFactory.open("MemRelClass",
targetClass, fromField, joinClass, toField, "forward", "backward",
esriRelCardinality.esriRelCardinalityOneToOne);
//Open the RelQueryTable as a feature class.
IRelQueryTableFactory rqtFactory = new RelQueryTableFactory();
ITable relQueryTable = (ITable)rqtFactory.open(relationshipClass, true, null,
null, "", false, false);
return relQueryTable;
}
| Development licensing | Deployment licensing |
|---|---|
| ArcView | ArcView |
| ArcEditor | ArcEditor |
| ArcInfo | ArcInfo |
| Engine Developer Kit | Engine Runtime |