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?
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?
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
/// <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 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?
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(); }