Sub Query for One to Many Relationship using Query Filter in ArcObjects

Feature Level Data to Metadata

Often times the data that I work with has a one to many (1:M) cardinality. This presents some interesting challenges when working with data-sets (Especially as it relates to an ESRI and GIS environment).  In the past, I’ve worked with some fairly large data-sets for feature class level data (tables with spatial attributes) and their relationships to the feature level metadata. I was recently tasked with cleaning up some of the processes in how we do this procedure, in realizing that we weren’t necessarily obtaining all joins for these feature records.

Sub Queries in ArcGIS and Oracle

Below is an example of a query that was used to get the number of records from a table we create which we create and store in an SDE Geodatabase intermittently for storing all of the primary keys for each feature in the geospatial feature class. Basically, we are performing an ETL and in that process need to update the metadata tables appropriately, so that at the end of the process all records are able to be maintained. Previously we were not building this table correctly and were including some but not all features and as such our feature to metadata tables for the extraction process was incomplete.

SELECT COUNT(*) FROM NHD.FeatureToMetadata FTM WHERE FTM.PERMANENT_IDENTIFIER IN
(SELECT relateTable.PERMANENT_IDENTIFIER PID FROM PID INNER JOIN  FTM
ON  PID.PERMANENT_IDENTIFIER = FTM.PERMANENT_IDENTIFIER);

The C# and ArcObjects Method Used

Below is the final code snippet implemented using the subquery. I am using an inner join, as I only want the records that relate to both tables. Using the subquery allows for the one to many cardinality rules to be followed.

public static void PopulateFeatureToMetadata(ProductMetadataTables metaTable, IFeatureWorkspace tempFWS, IFeatureWorkspace extractWS, string pidTableName)
        {
                //using statement to relinquish use of comReleaser when complete.
                using (ComReleaser comReleaser = new ComReleaser())
                {
                    ITable srcFeatureMetadataTable = extractWS.OpenTable(metaTable.Owner + "." + metaTable.Source_Meta_table);
                    ITable pidTable = extractWS.OpenTable(pidTableName);
 
                    ITable outFeatMetaDataTable = tempFWS.OpenTable(metaTable.Source_Meta_table);
                    //using these to get the field index for the cursor to search on.
                    int tempIndePermId = outFeatMetaDataTable.FindField(metaTable.Meta_field);
                    int tempIndexMetaId = outFeatMetaDataTable.FindField(metaTable.Join_field);
 
                    //Out Cursor/Rowbuffer
                    ICursor outFeatMetaDataCursor = outFeatMetaDataTable.Insert(true);
 
                    IRowBuffer outFeat2MDataBuffer = outFeatMetaDataTable.CreateRowBuffer();
                    int indexPermId = srcFeatureMetadataTable.FindField(metaTable.Meta_field);
                    int indexMetaId = srcFeatureMetadataTable.FindField( metaTable.Join_field);
 
                    //perform the join on the relQueryTable
                    IQueryFilter2 pFilter = new QueryFilterClass();
                    //using the subquery as commented out below for inner join on Permanent_Identifier
                    //1:M relationship
                    pFilter.WhereClause = metaTable.Owner + "." + metaTable.Source_Meta_table + "." + metaTable.Meta_field +
                        " in (SELECT " + pidTableName + "." + metaTable.Meta_field + " FROM " + pidTableName +
                        " INNER JOIN " + metaTable.Owner + "." + metaTable.Source_Meta_table +
                        " ON " + pidTableName + "." + metaTable.Meta_field + " = " + metaTable.Owner + "." + metaTable.Source_Meta_table 
                        + "." + metaTable.Meta_field + ")";
 
                    //select * from NHD.FeatureToMetadata FTM where FTM.PERMANENT_IDENTIFIER in
                    //(SELECT relateTable.PERMANENT_IDENTIFIER PID FROM PID INNER JOIN  FTM
                    //ON PID.PERMANENT_IDENTIFIER = FTM.PERMANENT_IDENTIFIER);
 
                    ICursor cursor = srcFeatureMetadataTable.Search(pFilter, false);
 
                    IRow row = cursor.NextRow();
                    while (row != null)
                    {
                        outFeat2MDataBuffer.set_Value(tempIndePermId, row.get_Value(indexPermId));
                        outFeat2MDataBuffer.set_Value(tempIndexMetaId, row.get_Value(indexMetaId));
                        outFeatMetaDataCursor.InsertRow(outFeat2MDataBuffer);
                        row = cursor.NextRow();
                        outFeatMetaDataCursor.Flush();
                    }
 
                    //Let comReleaser deal with GC
                    comReleaser.ManageLifetime(pFilter);
                    comReleaser.ManageLifetime(outFeatMetaDataCursor);
                    comReleaser.ManageLifetime(cursor);
                }    
        }

You’ll notice the using statement and the comReleaser object. I worked heavily in an Oracle/SDE Geospatial database environment where not properly disposing of Com Objects such as cursors, can be troublesome, for both database connections and run-time memory allocation on application processes.