How To Create an ArcSDE Spatial View With an Outer Join

by James Richards July 16, 2009


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]


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!



Tags: ,

ArcSDE | How To | Planet GS


8/13/2009 5:52:37 AM #


To make it even easier you could write the view with straight SQL and then just register it with sde using 'sdetable -o register...'.  

shane United States |

8/14/2009 1:58:32 AM #


        Trackback from Are You Spatial?

        How To Create an ArcSDE Spatial View With an Outer Join

Are You Spatial? |

2/11/2010 6:18:06 AM #

Dan Brumm

        I have always wanted to put together a site just like this. I just happened to stumble across yours looking for arcsde spatial view instruction and I must say it looks pretty good.

        Thanks for the info and keep up the good work.

        Dan Brumm
        City of Midland, Mi

Dan Brumm United States |

2/13/2010 4:13:06 PM #


Dan, Thanks for the feedback. I'm glad you found the post useful!

james |

3/8/2012 2:04:58 PM #


Pingback from

Spatial View and Outter Join « badarcgis |

3/30/2014 12:44:16 PM #


Pingback from

SDE Views Not Showing Values In Joined Table | Click & Find Answer ! |

12/28/2014 9:14:48 AM #


Pingback from

SDE views not showing values in joined table | CL-UAT |

Comments are closed

Powered by BlogEngine.NET
Theme by Mads Kristensen | Modified by Mooglegiant
Creative Commons License This work is licensed under a Creative Commons Attribution 3.0 United States License.


James Richards

Hi, I'm James Richards the CTO and co-founder of Artisan Global LLC. We make location-aware mobile apps with maps. I'm the author of QuakeFeed and I helped launch Zaarly at LASW Feb 2011. I also enjoy surfing, snowboarding, golfing, yoga, and music. I love my family: Linda, Sequoya and our cats Remy and Twiggy. Thanks for stopping by, I hope you find something helpful here.

Subscribe by RSS   Follow me on Twitter   Connect on Facebook   View my profile on LinkedIn

Amazon Associates

Some of my posts may contain Amazon Associates links. I only include these links when it makes sense within the context of the article. If you are in the market for one of these items, please consider clicking on an affiliate link to make your purchase. You still get the same great deal from Amazon and it will help me pay for hosting and bandwidth. Thanks!