topic Re: Define a query for joining data in ArcObjects SDK Questions

Define a query for joining data

CarstenSchumann — Wed, 25 Jun 2014 10:46:13 GMT

Hey around,

I´m trying to build a query that provides access to features within a FC that intersect a given geometry AND that apply to an attribute-based query from another table by a join. For the last one I may use the IQueryDef-interface as it allows joining data based on an attribute-based query, but I need both - a spatial and a attribute-component. Is there any better way then creating one of the filters first, looping the results and checking every feature for the second condition? Maybe by building a temporary table using IQueryTable from the QueryDef-object and than perform a spatial query on it?

Thanks in advance 😄

Re: Define a query for joining data

DuncanHornby — Wed, 25 Jun 2014 13:29:27 GMT

Carsten,

The ISpatialFilter allows you to specify a spatial query AND an attribute query through the WhereClause.

If you have joined your tables you should be able to take advantage of this?

Duncan

Re: Define a query for joining data

CarstenSchumann — Wed, 25 Jun 2014 14:04:49 GMT

Thanks for your replies,

If you have joined your tables [...]?

The question is on how to do this on a query in a non-persistent way. That was why I suggested using IQueryDef in combination with TableQueryName to build a virtual (joined) table and afterwards query that table spatially. Is this the way or is there another one to achieve this?

Btw.: Although it won´t matter that much, I´m writing with C# .NET 3.5

Re: Define a query for joining data

AhmedEl-Sisi — Wed, 25 Jun 2014 18:29:38 GMT

Hi Carsten,
you can't apply spatial filter on IQueryDef so your approach to use TableQueryNameClass is a good idea if your tables are in the same geodatabase, use it with query definition and then use a spatial filter.
http://resources.arcgis.com/en/help/arcobjects-net/conceptualhelp/index.html#/d/000100000146000000.htm

Re: Define a query for joining data

CarstenSchumann — Thu, 26 Jun 2014 07:39:16 GMT

Thanks for your further replies,

Why not just perform a spatial query and then an attribute query to further refine your search results. I guess I'm not reading it right or just misunderstanding what you are trying to do?

No, you understand correctly. Honestly this was my first approach and it works, but I looked for a faster way because looping every feature and checking it for the second condition (be it spatial or not) does not seem convenient to me.

It can be helpful to some posters, to know what language they are using so that others can provide samples that match.

Btw.: Although it won´t matter that much, I´m writing with C# .NET 3.5

@CC4Ever: I have only one further issue on using the TableQueryNameClass on my SDE. I do not understand exactly what the copyLocally-param is for, I suppose I do not have write-access for the destination-machine (I get a really "meaningful" SEHException when calling Open on the IName-instance) in order to build the table locally or where exactly is this table created? Because of this error I left out the parameter and it works but for testing-purposes I´d like to see the data within the table also. So is this even possible when accessing a non-locale SDE?

Thanks for all your hints so far

Re: Define a query for joining data

AhmedEl-Sisi — Thu, 26 Jun 2014 08:02:31 GMT

IQueryName2.CopyLocally Property will copy your table into the scratch workspace in your system temp directory to assign ObjectID column to your result table.
The ObjectID column is used for selections.

For more information check IQueryName2 Interface documentation.

Re: Define a query for joining data

CarstenSchumann — Sat, 11 Dec 2021 20:09:50 GMT

For some reason I get the SEHException -2147467259, no matter if CopyLocally is set to true or false so its no issue on right-access.
Do I have to consider a special syntax for the whereClause on an SDE which may differ from Table.Field?

Currently this is the code:

/// <summary>
/// Virtually joins two tables
/// </summary>
/// <param name="objectTable">the businesTable</param>
/// <param name="geometryTable">the geometry-table where the shapes are stored</param>
/// <param name="whereString">an additional whereString to further specify the query</param>
/// <param name="uuidField">the identifier-field within the geometry-table (usually OFID) used for joining the tables</param>
/// <returns>a FeatureClass that contains the the Objektart, the Modellart and the geometries of the joined tables</returns>
/// <remarks>
/// The join is performed by evaluating the UUID-field of the object-table and the UUID-field of the geometry-table 
/// (while the former is already "UUID" the latter may vary).<br> />
/// The returned featureClass contains the fields Modellart (MAT) and Objektart (OBA) from the objectTable and all fields of the geometry-table. 
/// </remarks>
 
private IFeatureClass joinTables(string objectTable, string geometryTable, string whereString, string uuidField)
{
 // queryDef to perform a join on the object- and the geometry-table
 IQueryDef queryDef = ((IFeatureWorkspace)this.Workspace).CreateQueryDef();
 // select the Objektart (OBA), the Modellart (MAT) and all columns from the geometry-table (where the ESRI-feature is stored)
 queryDef.SubFields = objectTable + ".OBA," + objectTable + ".MAT," + geometryTable + ".*";
 queryDef.Tables = objectTable + "," + geometryTable;
 queryDef.WhereClause = objectTable + ".UUID = " + geometryTable + "." + uuidField; // the join-criterion

 IQueryName2 queryName2 = (IQueryName2)new FeatureQueryNameClass();
 queryName2.QueryDef = queryDef;
 //queryName2.PrimaryKey = geometryTable + "." + uuidField; // the ID-field of the FeatureClass
 //queryName2.CopyLocally = true;

 // Set the workspace and name of the new QueryTable.
 IDatasetName datasetName = (IDatasetName)queryName2;
 datasetName.WorkspaceName = (IWorkspaceName)(((IDataset)this.Workspace).FullName);
 datasetName.Name = "tmpTable";

 // Open the virtual table.
 ESRI.ArcGIS.esriSystem.IName name = (ESRI.ArcGIS.esriSystem.IName)queryName2;
 return (IFeatureClass)name.Open();
}

Re: Define a query for joining data

CarstenSchumann — Tue, 01 Jul 2014 08:32:49 GMT

I have found that SQL queries directly to the SQL tables are much faster anyway, so often I will query directly from the SQL tables and extract unique IDs from the returned record set loading them into an array, then in a little loop build a selection string from that. I found it to sometimes be faster doing it that way?

That´s more or less why I do at the moment because the alternative way by using IQueryDef won´t work (I already pointed the exception out in previous post). Actually I do a spatial query on my FeatureClass and select only the IDs of the features. Afterwards I put the list of the so obtained IDs into one (or more, depending on the length and the DBMS) whereClause for the attribute-query on the other table. Ofc. doing this by plain SQL might be much faster, but also much more complicated because of archieve-issues and I´d like to take the ArcGIS-way 🙂

However I consider it more a workaround then the actual solution, so I´m still looking for the reason of this irritating exception. Does anyone have any similar problem on opening a virtual/temporal table?

Re: Define a query for joining data

AhmedEl-Sisi — Sat, 11 Dec 2021 20:09:52 GMT

/// <summary>
/// Virtually joins two tables
/// </summary>
/// <param name="objectTable">the businesTable</param>
/// <param name="geometryTable">the geometry-table where the shapes are stored</param>
/// <param name="whereString">an additional whereString to further specify the query</param>
/// <param name="uuidField">the identifier-field within the geometry-table (usually OFID) used for joining the tables</param>
/// <returns>a FeatureClass that contains the the Objektart, the Modellart and the geometries of the joined tables</returns>
/// <remarks>
/// The join is performed by evaluating the UUID-field of the object-table and the UUID-field of the geometry-table 
/// (while the former is already "UUID" the latter may vary).<br> />
/// The returned featureClass contains the fields Modellart (MAT) and Objektart (OBA) from the objectTable and all fields of the geometry-table. 
/// </remarks>
 
private IFeatureClass joinTables(string objectTable, string geometryTable, string whereString, string uuidField)
{
 // queryDef to perform a join on the object- and the geometry-table
 IQueryDef queryDef = ((IFeatureWorkspace)this.Workspace).CreateQueryDef();
 // select the Objektart (OBA), the Modellart (MAT) and all columns from the geometry-table (where the ESRI-feature is stored)
 queryDef.SubFields = objectTable + ".OBA," + objectTable + ".MAT," + geometryTable + ".*";
 queryDef.Tables = objectTable + "," + geometryTable;
 queryDef.WhereClause = objectTable + ".UUID = " + geometryTable + "." + uuidField; // the join-criterion

 IQueryName2 queryName2 = (IQueryName2)new FeatureQueryNameClass();
 queryName2.QueryDef = queryDef;
 //queryName2.PrimaryKey = geometryTable + "." + uuidField; // the ID-field of the FeatureClass
 //queryName2.CopyLocally = true;

 // Set the workspace and name of the new QueryTable.
 IDatasetName datasetName = (IDatasetName)queryName2;
 datasetName.WorkspaceName = (IWorkspaceName)(((IDataset)this.Workspace).FullName);
 datasetName.Name = "tmpTable";

 // Open the virtual table.
 ESRI.ArcGIS.esriSystem.IName name = (ESRI.ArcGIS.esriSystem.IName)queryName2;
 return (IFeatureClass)name.Open();
}

I tested this sample using SDE workspace and also GDB workspace and it works fine.
I used ArcObjects 10.1 +VS 2010 on windows 7 64-bit.
Similar exception found here:
http://forums.arcgis.com/threads/22016-ArcMap-C-Integration-sometimes-gives-SEHException