Query related tables

Starting at version 10.2.4 of ArcGIS Runtime SDK for Java, you can find related tables and query for related features in a local GeodatabaseFeatureTable or an online GeodatabaseFeatureServiceTable. For online services, you can also use the QueryTask to query for related features.

What are related feature tables?

Relationships in the geodatabase manage the associations between features in one class (feature class or table) and features in another. Throughout this topic feature table means both a table containing features with geometry and a table containing features without geometry (that is, simple records). A related feature table is any table associated with another feature table. Individual features within a feature table may or may not be related to features in the related feature table. For example, a building may be associated with a parcel, a parcel of land may be associated with an owner, or a parcel owner may be assigned a tax code.

Relationships can be used to help enforce referential integrity between related features. That means, a relationship can be set up so that when you modify a feature, related features update automatically. For example, you could set up a relationship so that whenever you move a utility pole, attached transformers and other equipment move with it. By setting rules, a relationship class can restrict the type of relations that are valid between individual features. For example, a pole may support a maximum of three transformers.

ArcGIS Runtime supports finding relationships in which a feature table participates, querying for features related to a feature, and editing related features. How to achieve this is described in the sections below.

Types of relationships and relationship properties are briefly discussed below. For more information about relationships, see Relationship class properties.

Find related tables

With a local GeodatabaseFeatureTable or an initialized GeodatabaseFeatureServiceTable connected to a service, the following method call returns relationships in which the feature table participates:

List<Relationship> relationships = featureTable.getRelationships();

Some of the properties of a relationship are:

  • ID—Relationship identifier that is unique across the geodatabase.
  • key field—Field containing a value that identifies features in this table.
  • related table ID—Identifier of the feature table at the other side of this relationship; this identifier is unique across the geodatabase.
  • role—Either origin or destination. In most relationships, the value of the key field for a feature from the origin table is stored in a field called the foreign key field for the related feature in the destination table.
  • cardinality, which is either:
    • One-to-one—One origin feature can relate to only one destination feature. For example, a parcel may have only one legal description.
    • One-to-many—One origin feature can relate to multiple destination features. For example, a parcel may have many buildings. In a one-to-many relationship, the one side must be the origin class and the many side must be the destination class.
    • Many-to-many—Many origin features can relate to multiple destination features. For example, a given property may have many owners and an owner may have many properties. Many-to-many relationships require the use of an intermediate table to map the associations; they are sometimes called attributed relationships.
  • is composite—If the origin table is logically composed of the destination table, the destination table's features will be deleted when their origin table's features are deleted. Composite relationships are always one-to-many when you create them but can be constrained to be one-to-one with relationship rules.

The terms one and many can be misleading. One is really zero-to-one, and many is really zero-to-many. So when you create a one-to-many relationship between parcels and buildings, for example, the relationship permits all of the following:

  • A parcel with no buildings
  • A building with no parcel
  • A parcel with any number of buildings

Query related features

With a local geodatabase, you can work with a feature table that participates in a relationship as if it were a stand-alone feature table. For example, if a pole supports many transformers in an composite relationship, you can query the poles feature table and edit a pole even if the transformers feature table does not exist in your local geodatabase. If a pole (origin feature) is deleted and that edit is synced to the server, the transformers (destination features) will be deleted as part of a cascade delete on the server. However, to query locally in the geodatabase for related features, you must first have downloaded related tables from the service. That is, to query for or edit transformers related to poles, you must create your geodatabase using the LayerQuery class, as shown below:

static int POLE_LAYER_ID = 0;
static int TRANSFORMER_LAYER_ID = 1;
LayerQueries queries = new LayerQueries();
// poles that require inspection and intersect the envelope below
queries.add(LayerQuery.createUsingFilter(POLE_LAYER_ID, "requires_inspection = Yes", true));
// all transformers related to poles that require inspection
queries.add(LayerQuery.createRelatedFeatures(TRANSFORMER_LAYER_ID));

// set up the parameters to generate a geodatabase with layer queries
GenerateGeodatabaseParameters params = new GenerateGeodatabaseParameters(
    new int[]{POLE_LAYER_ID, TRANSFORMER_LAYER_ID}, 
    queries, 
    envelope, 
    spatialReference,
    true, 
    SyncModel.GEODATABASE, 
    null);
// use params to generate the geodatabase
...

The above GenerateGeodatabaseParameters are then passed to the GeodatabaseSyncTask. An overview of this process is given in Create an offline map.

To query for related features in both a local GeodatabaseFeatureTable and an online GeodatabaseFeatureServiceTable from an online service, create an instance of the RelatedQueryParameters class. At a minimum, you must populate an instance of the RelatedQueryParameters class with:

  • The relationship identifier
  • The feature IDs for which to search related features

Some other parameters are:

  • definition expression—This allows you to further filter the related features using this criteria.
  • output fields—Can be used to limit fields and hence the amount of data returned by services. This parameter has no effect on a local geodatabase; you will get all fields returned.
  • geometry—Can be used to ensure geometries are not returned and hence limit the amount of data returned by services. This parameter has no effect on a local geodatabase; you will get geometries returned.

With a local GeodatabaseFeatureTable or GeodatabaseFeatureServiceTable, the following code returns and iterates related features:

// get all transformers on a pole with rating of transformer < 10
RelatedQueryParameters relatedQuery = new RelatedQueryParameters();
relatedQuery.setObjectIds(new long[] {10}); // pole id = 10
relatedQuery.setRelationshipId(3); // relationship id from pole to transformer = 0
relatedQuery.setDefinitionExpression("RATING < 10");
Map<Long, FeatureResult> result = featureTable.queryRelated(relatedQuery, null).get();
for (Map.Entry<Long, FeatureResult> entry : result.entrySet()) {
  long id = entry.getKey(); // pole id
  Feature feature = (Feature) entry.getValue(); // transformer
  Map<String, Object> attrs = feature.getAttributes();
}

Notice above that a Feature is returned to represent the related feature. You can edit this related feature like any other feature and commit the edit (that is, sync edits in the case of the local GeodatabaseFeatureTable or apply edits in the case of a GeodatabaseFeatureServiceTable).

Note:

Currently editing the relationship between features is not supported for GeodatabaseFeatureServiceTable. That means you can add a new stand-alone feature or edit an existing feature if you do not change its relationships, but you cannot add new related pairs of features.

For GeodatabaseFeatureServiceTable, querying related features always queries the service (not a local cache) and initializes the related feature service table if it is not already initialized. Feature results of the query are imported into the related table. Depending on the mode of the related table, either:

  • existing features (including edited features) are deleted before the feature results are imported into the related table, or
  • feature results are appended to the feature table and any pre-existing features are updated.