Example: Creating a spatial view in SQL Server using SQL and registering it

Complexity: Beginner Data Requirement: Use your own data

You can use SQL to create a spatial view in an ArcSDE geodatabase by including the spatial column in the view definition. When you use SQL to create the spatial view, you must register it with ArcSDE to query the spatial view with ArcGIS and view it as a feature class.

In this example, a spatial view is created between the employees table and region feature class.

Create a view with a spatial column

Define the view to include both the spatial column and the object ID from the feature class along with the other attribute columns you want in the view.

The owner of the employees table and regions feature class is the gdb user; therefore, the user already has the necessary privileges to create the view.

USE testdb;

CREATE VIEW emp_region_vw
AS SELECT (e.emp_name,e.emp_id,r.rname,r.reg_id,r.region) 
FROM employees e JOIN region r 
ON e.emp_id = r.emp_id;

GO

The reg_id is the not null, integer column from the regions feature class that will be used as the row ID when you register the view with ArcSDE. Region is the spatial column from the regions feature class.

Grant privileges on the spatial view

Now that the view is created, grant select privileges to the ptolemy domain login. Include the WITH GRANT OPTION to allow ptolemy to grant privileges on the view to other users.

USE testdb;

GRANT SELECT 
ON emp.region.vw 
TO [ourdomain\ptolemy]
WITH GRANT OPTION;

GO

Tip:

The ptolemy domain login must be added to the SQL Server instance and the testdb database before you can grant privileges to it.

Register the view with ArcSDE as a layer

To query the spatial view with ArcGIS, you must register the view as a layer with ArcSDE. Doing so adds records to the ArcSDE SDE_layers and SDE_geometry_columns system tables.

The layer must be registered by the owner of the view; therefore, the gdb user name and password must be supplied with the –u and –p options.

sdelayer -o register -l emp_region_vw,region -e a 
-t GEOMETRY -k GEOMETRY -C reg_id,USER -R 1
-E 2.20,2.20,3.01,3.01 -u gdb -p gdb.bdg 
-i sde:sqlserver:server1\ssinstance2 -D testdb

The spatial column in the emp_region_vw uses SQL Server Geometry storage and stores polygons; therefore, GEOMETRY is specified with the –t and –k options and a (area) with the –e option. The values you provide for these and all the other options except –o will vary depending on your data.

For more information on the sdelayer command, see the Administration Command Reference.