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.
1. #First create a simple view
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.
ALTER VIEW SDE. VW_PARCEL_OWNERS AS
SELECT TEST.PARCELS.OBJECTID, TEST.PARCELS.Shape,
FROM TEST.PARCELS LEFT INNER JOIN
TEST.OWNERS ON TEST.PARCELS.PARCEL_ID = TEST.OWNERS.PARCEL_ID
# View Design
Add the OWNERS table and create the join; or, modify the 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.
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.
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.
5. #LEFT OUTER JOIN Result