Example: Creating a spatial view in PostgreSQL using SQL and registering it
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.
psql testdb gdb Enter password for user gdb: CREATE VIEW emp_region_vw AS SELECT ( employees.emp_name,employees.emp_id, hbear.regions.rname, hbear.regions.reg_id, hbear.regions.region) FROM employees, hbear.regions WHERE employees.emp_id = hbear.regions.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 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 sde_layers and sde_geometry_columns system tables.
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 -i sde:postgresql -s mypgdbserver -D testdb -u gdb -p gdb.bdg
The region spatial column in the view uses ST_GEOMETRY storage and stores polygons; therefore, ST_GEOMETRY is specified with the –t option and a (area) is specified with the –e option. These, along with all the other options except the –o option, will vary depending on your data.
For more information on the sdelayer command, see the Administration Command Reference.