ArcGis中IQueryDef接口查询

      IQueryDef用于属性查询。它支持复杂的SQL查询并且能在同一工作空间内做连表查询。但是查询后的结果不支持编辑。且不能在Shapefile 和Coverage 数据中使用，而仅仅可以使用在GeoDatabase数据库中。若要执行空间查询或编辑查询后结果可用IQueryFilter或ISpatialFilter接口。官网地址：转至官网

测试数据准备:

ABC

       

ABC_Point

 

使用QueryDef查询：

1、最普遍的查询例子 

public static void UsingIQueryDef()
{
    Type type = Type.GetTypeFromProgID("esriDataSourcesGDB.FileGDBWorkspaceFactory");
    IWorkspaceFactory pWorkspaceFactory = Activator.CreateInstance(type) as IWorkspaceFactory;
    IWorkspace pWorkspace = pWorkspaceFactory.OpenFromFile(@"F:\GIS测试数据\测试.gdb", 0);
    IFeatureWorkspace pFeatureWorkspace = pWorkspace as IFeatureWorkspace;

    //创建 QueryDef
    IQueryDef2 pQueryDef2 = (IQueryDef2)pFeatureWorkspace.CreateQueryDef();

    //指定表查询
    pQueryDef2.Tables = "ABC_Point";
    pQueryDef2.WhereClause = "OBJECTID=1";

    using (ComReleaser comReleaser = new ComReleaser())
    {
        ICursor pCursor = pQueryDef2.Evaluate();
        comReleaser.ManageLifetime(pCursor);
        //字段——索引字典
        Dictionary<string, int> dicField = new Dictionary<string, int>();
        for (int i = 0; i < pCursor.Fields.FieldCount; i++)
            dicField[pCursor.Fields.Field[i].Name] = i;

        IRow pRow = null;
        while ((pRow = pCursor.NextRow()) != null)
        {
            string str = string.Empty;
            foreach (var it in dicField)
                str += $"{it.Key}:{pRow.Value[it.Value]}   ";
            Console.WriteLine(str);
        }
    }
}

2、使用JOINS连表查询

pQueryDef2.Tables = "ABC INNER JOIN ABC_Point ON ABC.BSM =ABC_Point.BSM";
pQueryDef2.SubFields = "ABC.BSM,ABC.ADDRESS,ABC_Point.NAME";
/**
结果：
ABC.BSM:111222   ABC.ADDRESS:赵国   ABC_Point.NAME:张三
ABC.BSM:333444   ABC.ADDRESS:燕国   ABC_Point.NAME:李四
ABC.BSM:333444   ABC.ADDRESS:燕国   ABC_Point.NAME:王五
*/

3、DISTINCT

pQueryDef2.Tables = "ABC_Point";
pQueryDef2.SubFields = "bsm";
pQueryDef2.PrefixClause = "distinct";
//等同于 pQueryDef2.SubFields = "distinct bsm";

/**
结果：
bsm:555666
bsm:111222
bsm:333444
*/

4、ORDER BY  排序

pQueryDef2.Tables = "ABC_Point";
pQueryDef2.SubFields = "BSM,NAME as AAA";  //默认情况下它为“*”
pQueryDef2.PostfixClause = "ORDER BY BSM DESC";

/**
结果：
 BSM:555666   AAA:钱六
 BSM:333444   AAA:李四
 BSM:333444   AAA:王五
 BSM:111222   AAA:张三
*/

5、GROUP BY 和 HAVING

//pQueryDef2.Tables = "ABC_Point";
//pQueryDef2.SubFields = "BSM,SUM(OBJECTID)";
//pQueryDef2.PostfixClause = "GROUP BY BSM";
/**
结果：
BSM:111222    SUM( OBJECTID):1
BSM:333444    SUM( OBJECTID):6
BSM:555666    SUM( OBJECTID):3
*/

pQueryDef2.Tables = "ABC_Point";
pQueryDef2.SubFields = "BSM,SUM(OBJECTID)";
pQueryDef2.PostfixClause = "GROUP BY BSM HAVING SUM(OBJECTID)>1";
/**
结果：
BSM:333444    SUM( OBJECTID):6
BSM:555666    SUM( OBJECTID):3
*/

6、各种SQL函数

pQueryDef2.Tables = "ABC_Point";
pQueryDef2.SubFields = "BSM,CASE WHEN BSM='333444' THEN 'B' WHEN BSM='111222' THEN 'A' END AS bsm_LEVEL";

/**
结果:
BSM:111222   bsm_LEVEL:A
BSM:333444   bsm_LEVEL:B
BSM:555666   bsm_LEVEL:
BSM:333444   bsm_LEVEL:B
*/

//COUNT
//pQueryDef2.Tables = "ABC_Point";
//pQueryDef2.SubFields = "COUNT(*)";
//结果 : COUNT(*):4

QueryDef 可用于生成一个虚拟表或要素类，类似“视图”：

/// <summary>
/// QueryDef 可用于生成一个虚拟表或要素类。类似“视图”
/// </summary>
public static void UsingIQueryDefBuildTempTable()
{
    Type type = Type.GetTypeFromProgID("esriDataSourcesGDB.FileGDBWorkspaceFactory");
    IWorkspaceFactory pWorkspaceFactory = Activator.CreateInstance(type) as IWorkspaceFactory;
    IWorkspace pWorkspace = pWorkspaceFactory.OpenFromFile(@"F:\GIS测试数据\测试.gdb", 0);
    IFeatureWorkspace pFeatureWorkspace = pWorkspace as IFeatureWorkspace;

    //创建 QueryDef
    IQueryDef2 pQueryDef2 = (IQueryDef2)pFeatureWorkspace.CreateQueryDef();

    //指定表和字段查询
    pQueryDef2.Tables = $"ABC INNER JOIN ABC_Point ON ABC.BSM =ABC_Point.BSM";
    pQueryDef2.SubFields = "ABC.BSM,ABC.ADDRESS,ABC_Point.NAME";

    //构建表查询
    IQueryName2 pQueryName2 = new TableQueryNameClass();
    pQueryName2.PrimaryKey = string.Empty;
    pQueryName2.QueryDef = pQueryDef2;

    IDataset pDataset = (IDataset)pWorkspace;
    IWorkspaceName pWorkspaceName = (IWorkspaceName)pDataset.FullName;

    // 将表查询对象转为IDataset
    IDatasetName pDatasetName = (IDatasetName)pQueryName2;
    pDatasetName.WorkspaceName = pWorkspaceName;
    pDatasetName.Name = "TABLE_ABC";
    IName pName = (IName)pDatasetName;

    //打开表
    ITable pTable = (ITable)pName.Open();

    Dictionary<string, int> dicField = new Dictionary<string, int>();  //字段——索引字典

    for (int i = 0; i < pTable.Fields.FieldCount; i++)
        dicField[pTable.Fields.Field[i].Name] = i;

    using (ComReleaser comReleaser = new ComReleaser())
    {
        ICursor pCursor = pTable.Search(null, true);
        comReleaser.ManageLifetime(comReleaser);
        IRow pRow = null;
        while ((pRow = pCursor.NextRow()) != null)
        {
            string str = string.Empty;
            foreach (var it in dicField)
                str += $"{it.Key}:{pRow.Value[it.Value]}   ";
            Console.WriteLine(str);
        }
    }
}

/**
结果：
ABC.BSM:111222   ABC.ADDRESS:赵国   ABC_Point.NAME:张三
ABC.BSM:333444   ABC.ADDRESS:燕国   ABC_Point.NAME:李四
ABC.BSM:333444   ABC.ADDRESS:燕国   ABC_Point.NAME:王五
*/