How To: Create an ArcSDE Spatial View


Spatial views are database views that contain a single spatial column. They are useful in organising your spatial data with other attribute data in a predefined way, in the same database or another database. They are also READ-ONLY, hence spatial views can not be edited via ArcMap. If you are currently still at ArcSDE 10.0 and prior versions, the trick to get this working is to follow these steps:

This blog addresses how to create a spatial view using SQL Server.

In this example, we have some Parcel data and some Owner information in a table, and would like to join the OWNER_NAME from the Owners table to the Parcel spatial data on PARCEL_ID, which exists in both tables.

Sample data:

Parcel_Owner_tables

1. #First create a simple view

For example:
sdetable -o create_view -T VW_PARCEL_OWNERS -t PARCELS -c objectid,shape -i sde:sqlserver:server_name -D testdb -u test -p xxxx

2. #Alter or Modify the view in SQL Server Management Studio

It is much easier to alter the view in SQL Server using SQL tools, than specifying all the tables, fields and query definition (where clause) in the “sdetable –o create_view” command.

For example:

#Query Design:
ALTER VIEW SDE. VW_PARCEL_OWNERS AS
SELECT TEST.PARCELS.OBJECTID, TEST.PARCELS.Shape,
TEST.PARCELS.PARCEL_ID, TEST.OWNERS.OWNER_NAME
FROM TEST.PARCELS LEFT INNER JOIN
TEST.OWNERS ON TEST.PARCELS.PARCEL_ID = TEST.OWNERS.PARCEL_ID

Or,

# View Design

View_Design

Add the OWNERS table and create the join; or, modify the Select statement.

select_statement

3. #Grant TEST permission to external table in SQL Server (Optional).

If the OWNERS table is owned by DBO or another database user account (other than TEST, in this exercise), or in another database, the following permissions would need to be granted.

For example:
GRANT ‘Select’ permission for TEST to the non-spatial table.
– In SQL Server Management Studio, Right-click OWNERS table, …Properties, …Permissions, …add TEST user, …grant ‘Select’. Click OK.

To allow other users to access the view, grant privileges on the view to other users.

4. #Result

result_inner_join

By default, the join type for all views created by the sdetable command is INNER JOIN.

If you needed to include all Parcel data even if no matches were found in the OWNERS table, change the View so that it uses an outer join, and Save the View. In this case, we have changed it to a LEFT OUTER JOIN.

select_outer_join

5. #LEFT OUTER JOIN Result

result_outer_join

Tania T.

12 thoughts on “How To: Create an ArcSDE Spatial View

  1. B Bjornsson

    I have the same question as T Konzel above, what is the best way to safely delete a spatial view so that no residual records or registries are left behind in the SDE geodatabase system tables?

    Cheers!

    Reply
  2. B Bjornsson

    Thanks for the reply, I have another question since this thread is active. Is there any special reason or benefit from using the “sdetable -o create_view” command vs. just creating a regular view in SQL server Management studio that includes the shape column?

    I have found that views created like this in SQL Server are easily read as spatial views by ArcGIS 10.1 as long as the spatial column is included and it is properly formed.

    Cheers!

    Reply
    1. Vaishali

      HI Tania,
      I would like to know if we can create spatial viewer in SDE Binary as we have recentely moved all the data to SDEBINARY to help increase performance and without using the Command promt. We are using ArcGIS10.2 and Sql database.

      Reply
  3. Miguel Paredes

    Greetings Tania,
    I was wondering if you have tried enabling the WFS capability on the service based on Spatial View, created as you describe in this article, successfully? I am having an issue with the layer created from this view. The Analize error is 00059.
    Many thanks in advance.

    Miguel.

    P.S. Great article, by the way.

    Reply

Got something to say?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s