Example: Creating a spatial view in SQL Server using SQL

Complexity:

Beginner

Data Requirement:

Use your own data

You can use SQL to create a view and include the spatial column in the view definition. You would do this so you can view the features in ArcMap. You might also create a spatial view to allow you to use a table that contains more than one spatial column; your spatial view would only include one spatial column so you can use it with ArcGIS.

In this example, a spatial view is created to join data from the employees table and the region feature class.

Create a view with a spatial column

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

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

Tip:

To create a view, the user must have at least SELECT privileges on each table or feature class included in the view, in addition to the CREATE VIEW privilege in the database.

USE testdb;

CREATE VIEW emp_region_view
 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 ObjectID column from the region feature class. Region is the spatial column from the region feature class. The tables are joined based on the emp_id column.

Grant privileges on the spatial view

Now that the view exists, grant SELECT privileges to the ptolemy domain login.

USE testdb;

GRANT SELECT 
 ON emp.region.view 
 TO [ourdomain\ptolemy];

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.