ArcGIS Server Banner

Using database views

Using database views

Release 9.3 E-mail This TopicPrintable VersionGive Us feedback

NOTE: Applies to geodatabases created with an ArcGIS Server Enterprise license only

Essentially, views are stored queries that select data from specified tables. The difference between a view and a regular query executed by a user or client application is that views are stored in the database, and the underlying tables can be indexed to improve performance.

Since views are stored in the database, view functionality varies from one database management system (DBMS) product to another. These differences are described in the DBMS-specific topics listed here:

With ArcSDE, it is possible to define views against a single table, between two feature classes, or between a feature class and a table, or you can create more complex views containing subqueries or spanning databases. ArcSDE views are created with the sdetable –o create_view command. A spatial ArcSDE view includes the spatial column of a feature class. This view appears as a feature class to ArcSDE clients.

NOTE: When you create a view on a versioned dataset, you will only see the business table and not the edits in the delta tables.

Views can be created using the sdetable administration command. The syntax of sdetable –o create_view is

sdetable –o create_view  
–T <view_name> 
–t <table1,table2...tablen>     
–c <table_col1,table_col2...table_coln
[–a <view_col1,view_col2...view_coln>] 
[–w <"where_clause">]
[–i <service>] 
[–s <server_name>] 
[–D <database>]
–u <DB_User_name> [–p <DB_User_password>] [–N] [–q]

NOTE: You must have proper permissions in the database to create a view to execute this command.

You must list the columns (–c) you want in the view as well as a WHERE clause (–w) within the sdetable command. To create a spatial view, be sure to specify the spatial column with the –c option column list.

To remove a view, you can use the sdetable –o delete command.

Each DBMS product also provides ways to create database views, usually by issuing SQL commands. Since this is DBMS specific, consult the documentation provided with your DBMS product. Views created outside ArcSDE need to be registered with the geodatabase before they can be used by an ArcGIS client.

Specific permissions are needed to allow a user to create a view; for example, the user must be granted CREATE VIEW privileges in Oracle or SQL Server databases. Also, with some DBMSs, if you create a view on a feature class or table for which you are not the owner, you will not be able to grant other users rights to the view unless the owner of the underlying tables has granted you permission to grant privileges to other users.

Tips

Spatial views

Spatial views contain a single spatial column. You can create spatial views in the same way you create other views; just be sure to include the geometry column in the view definition. The table containing the spatial column must be the same table that provides the row ID (also called the ObjectID) column for the view. Spatial views cannot be edited through an ArcSDE client.

A spatial view can only reference one table that contains a spatial column. If you want to include attribute information from another table containing a spatial column, you must first create a nonspatial view of that second table. You can then create your spatial view using one table with a spatial column and attribute information from the nonspatial view. The following is an example of this:

To create a spatial view containing the site address and shape columns from the ADDRESS feature class and the site address and owner name from the PARCEL feature class, you could first create a simple view of the PARCEL business table:

sdetable –o create_view –T view_parcel_owner –t parcel 
–c owner,site_address –u manager –p fl

Next, create a spatial view using attributes from the ADDRESS feature class and the simple view, VIEW_PARCEL_OWNER:


sdetable –o create_view –T view_adrs_owner –t address,view_parcel_owner  
–c address.objectid,address.site_address,address.shape, view_parcel_owner.owner,
view_parcel_owner.site_address 
–w "address.site_address = view_parcel_owner.site_address" –u manager –p fl

Spatial views should only be created on nonversioned data; otherwise, they may return inaccurate results. Since the software does not prevent you from creating a spatial view on a multiversioned table, you must adopt this practice as part of your workflow.

To create views that can be used throughout ArcGIS Desktop applications, you should use the sdetable –o create_view command to create the view, being sure to include the ObjectID field from the feature class in the view's fields. Be sure to include the feature class's spatial column to make the view spatial.

When you create a spatial ArcSDE view on a dataset stored in the ArcSDE compressed binary type or Open Geospatial Consortium well-known binary (OGCWKB) representation (available in geodatabases stored in Oracle or SQL Server), three views are created: one each for the business table, the feature table, and the spatial index table. Creating a nonspatial ArcSDE view only creates a single view.

For details on how to use the sdetable command, consult the ArcSDE Administration Command Reference.

Some users create registered views with sdetable –o create_view and alter the view definition using SQL. This is done to avoid complex sdetable –o create_view command line syntax (especially the –c option) or to include a more complex or extended view definition such as one that uses connections to external databases. Although altering a view can solve some problems, it is also possible to create a view that does not work well with ArcSDE or ArcSDE clients such as ArcGIS Desktop.

When you create a view using ArcSDE tools or by other means, you must take into consideration the appropriateness of the new view. You must determine, for example, whether the specified columns actually exist in the specified tables and if there are indexes on the columns to speed query execution. If you choose to replace views created by ArcSDE by altering the view afterward, you are taking responsibility for not only the query's correctness and efficiency but also its relevance and suitability for use with ArcSDE clients. For example, creating one-to-many views involving feature classes results in duplicate ObjectIDs. This leads to unexpected behavior in applications such as ArcMap and ArcCatalog, because the ObjectID is supposed to contain unique values. See About ObjectID fields for a definition of ObjectIDs.

See Also

  • An overview of tuning an ArcSDE geodatabase