Overview
ArcSDE Spatial Views are a useful tool for organizing information from multiple feature classes and geodatabase tables into a single “virtual feature class” or table at the database level. Similar to a database view, they allow administrators to join commonly used information so that the users do not need to perform the same actions repeatedly in a client (in this case, ArcMap).
Spatial Views are created with the “sdetable –o create_view …” command. When creating a Spatial View in this manner, the default join type is INNER JOIN. This fact cannot be altered via the command line syntax. INNER JOIN is the most restrictive join type and only records that match on both sides of the join will be in the resulting view. But once a default INNER JOIN Spatial View has been created, the join type can be changed after the fact either with the ALTER VIEW SQL statement, or through a database administration tool.
This article demonstrates how to create a Spatial View and then change the join type using SQL Server Management Studio. The same principles can be applied to other databases using whatever management tool you have available. [more]
Discussion
Step 1: Create the Spatial View
To create the Spatial View, use the sdetable –o create_view command. The syntax for this command 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]
The important parameters that we will be working with in this example are:
- -T : The name of the view to create
- -t : The list of tables to include in the view
- -c : The list of columns to include in the view
- -a : The list of field alias as they will appear in the view
- -w : A where clause to define the view
- -u : The name of a user with ArcSDE administration privileges
- -p : The password for the aforementioned user
Here’s an example that joins a geodatabase table to a feature class on a common ID field called Join_ID:
sdetable –o create_view –T MyView –t “MyLayer, MyTable”
-c “MyLayer.OBJECTID, MyLayer.Shape, MyLayer.Join_ID, MyTable.Join_ID, MyTable.Field1”
-a “OBJECTID, Shape, Layer_Join_ID, Table_Join_ID, Table_Field1”
-w “MyLayer.Join_ID = MyTable.Join_ID”
-u ****** –p ******
One thing that’s a little odd about this syntax is that the join information is given in the WHERE clause. In a typical SQL CREATE VIEW statement, the join information would be found in the FROM clause.
Also note that if you want the resulting view to be a virtual feature class that can be used with ArcGIS Desktop, you will need to include the OBJECTID and Shape fields from the original feature class in the list of columns to include.
For reference, the sample data in MyLayer and MyTable looks like so:
Notice that there is no record with a Join_ID of 5 in MyTable, and no record with a Join_ID of 7 in MyLayer.
Step 2: Modify the View Definition in SQL Server Management Studio
As was mentioned in the overview, the default behavior of “sdetable –o create_view” creates a view with an INNER JOIN. If we add the newly created view to ArcMap, only the 5 matching records are shown in the view:
To modify the view definition so that it uses an outer join, open SQL Server Management Studio and drill down to the Views folder under the sde database. Next, right click on the view you just created and choose Modify.
Change the phrase “INNER JOIN” in the FROM clause to “LEFT OUTER JOIN” and save the changes.
Now if we look at the View in ArcMap again, we will see all records from MyLayer, with null values for any records with a Join_ID in MyLayer that was unmatched in MyTable:
You can use other join styles such as FULL OUTER JOIN, but keep in mind that if the primary OBJECTID (or Row ID) column contains multiple null values, client behavior will become unpredictable.
Wrap Up
When creating ArcSDE Spatial Views using the “sdetable –o create_view” command, the default join type is the most restrictive INNER JOIN. But you can change the view definition to use other join types using the ALTER VIEW SQL command or your database’s management tool. The article showed an example of how to make such a change with SQL Server Management Studio.
I hope you find this helpful!
Resources
