如何在ArcSDE中创建视图（转+自己分析）

对于在Oracle或是SQL Server等数据库中，用视图(View)来提高的表间的查询检索效率的方法会经常用到。在GIS系统中，我们也会经常需要进行表间的查询，如：从空间位置查找该空间地物相关的属性信息表。我们常规的做法一般是先通过空间查询，再用关键字段进行关联查询。但在有些情况，如：进行插值时，需要空间表的空间位置，但需要另外一张表的某个属性作为插值字段，就无法实现,必须通过视图的方式来实现。

    在ArcSDE中，可通过sdetable命令来创建视图，该视图可为一般的没有空间信息的，也可为有空间位置的。

     sdetable命令的格式为：

     sdetable -o create_view  -T -t -c  [-a ] [-w <"where_clause">] [-i ] [-s ] [-D ]
[-u ] [-p ] [-N] [-q]

其中：

    -T 为视图名，创建完成后，在arccatalog中浏览与普通的table或是featureclass相同；

    -t 为参与的表名，各表间用“，”隔开，另外，各表名前需加上所属用户，如：sde.tablename；

    -c 为目标视图列，一般格为：用户.表名.字段名，如：sde.sampletable.samplefield。可用.*代表所指定表的所有字段。若参与的table有featureclass(空间表），则若指了shape字段，则结果视图将带有空间信息，与featureclass相似，若没有指定shape字段，则为普通表，与table类似。

    [-a ]为可选项，一般不用设置；

    [-w <"where_clause">]为视图的逻辑条件，为一般的SQL语句where 后面的语句，注意：该语句需用双引号；

    [-i ] [-s ] [-D ]为指定ArcSDE所在服务器，数据库名以及服务名，若是在本机上操作，则这些参数可省；

    [-u ]为用户名，该用户名为SDE用户名；

    [-p ]为SDE密码，即登陆SDE数据库的密码。

以下为创建一个带有空间信息的视图例子：

sdetable -o create_view -T dcpntView -t sde.dc_pnt_pc, sde.gz_pc_soilfield -c sde.dc_pnt_pc.Shape, sde.gz_pc_soilfield.* -w "sde.dc_pnt_pc.FieldID = sde.gz_pc_soilfield.FieldID"  -u sde -p sde

   其中：视图名为dcpntView；参加的表有：sde.dc_pnt_pc和sde.gz_pc_soilfield；目标列为：dc_pnt_pc中的空间位置字段(shape)和gz_pc_soilfield中的所有字段；关联条件为：sde.dc_pnt_pc.FieldID =sde.gz_pc_soilfield.FieldID；用户为：sde，密码：sde。

  具体创建时，需要dos的命令行窗口键入以上命令即可，若无法创建成功，则在DOS窗口中会有相应提示信息。但在创建时，即使创建成功，也会出现以下的错误提示，可不用理会，在数据库内部已创建成功：    

 

   视图的用法与普通的Table和Featureclass用法相同，不用做任何特殊处理，在此不在详述。

作者备注：

注意：创建之前需要给网络数据集注册

sdetable -o create_mv_view -T mv_rqpt -t rqpt -i esri_sde -s 192.168.5.78 -u zhgwpipesde -p zhgwpipesde

sdetable -o create_mv_view -T mv_rqlk -t rqlk -i esri_sde -s 192.168.5.78 -u zhgwpipesde -p zhgwpipesde

错误解答见英文部分：

Creating a geographic view in SDE

There are two ways to create a view :

1. Through ArcSDE command line commands.
2. A toolbox tool in ArcCatalog. In order to use it, open the toolbox in ArcCatalog -> Data Management -> Layers and Table views -> Make Table View, and follow the wizard (for more information, check out the ArcGIS 9.2 Desktop Help).

In order to create the view, you need to use the command sdetable :

sdetable -o create_view -T <view name> -t <table list separated by comma> -c <column names from the tables separated by comma> -w <where condition> -u <user name / schema name> -p <password>

For example :

We have a layer named street_lights_g which has a foreign key to a table called streets. We need a view to contain the name of the street for each street light (so it can be displayed when you perform identification. In order to create the view we will run this command :

sdetable -o create_view -T street_lights_v -t street_lights_g,streets -c street_lights_g.shape,street_lights_g.objectid,streets.name -w "street_lights_g.street_code = streets.code"-u myusername -p ...

After running the command the view will be seen in the desktop tools like a regular layer. You can add it like a regular layer to a mxd, perform queries etc...

I recommend, to check out the spatial index of the view, to see if it exists (right click on the view in ArcCatalog -> properties -> indexes). If all the grid values are 0 then you need to recalculate the index - using the recalculate button, or enter the grid values manually with the Edit button.

Troubleshooting :

Problem :

When running the command the following error message is received :

Error : DBMS view exists (-238). Error : Unable to create view <view name>

Cause :

The schema in which you tried to create the view, already contains a view with the same name.

Solution :

Delete the view from the schema using ArcCatalog. Why ? When a geographic view is created it will be registered in the sde.layers table. If the existing view is a geographic view, and you delete it from the database via console, the table will still stay registered, and you will not be able to recreate the view.

Problem :

When running the command the following error message is received :

Error: Table not registered (-220). Error: Unable to fetch registration for table <view name>.

Cause :

Geographic view was deleted from db console, and is still registered in the sde.layers table.

Solution :

• Create a view with the same name via DB console (pl/SQL developer, SQL server management console).
• Delete the view with ArcCatalog.
• Rerun the command.