Creating a geographic view in SDE

There are two ways to create a view:
Either via ArcSDE command line commands or ArcCatalog toolbox tool in ArcCatalog.

ArcCatalog

Using ArcCatalog is pretty straightforward:
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.

ArcSDE

To create a view, use the sdetable command:

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>

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.
To do so, we use 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: DBMS view exists

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: Table not registered

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.

Yossi Shmueli

Technologist, enjoy solving technical challenges for real world applications. Keeping it Green since 1995

comments powered by Disqus