Example: Creating a spatial view in Informix 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.

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

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 dispatch1 user. Include WITH GRANT OPTION to allow dispatch1 to grant privileges on the view to other users.

GRANT SELECT 
ON emp_region_vw 
TO USER dispatch1 WITH GRANT OPTION;

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 layers and 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 ST_GEOMETRY -C reg_id,USER -E 2.20,2.20,3.01,3.01 -R 1 
-s myidsserver -i sde:informix -D testdb -u gdb -p gdb.bdg

The values you provide for the options, except –o, will vary depending on your data.

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