Creating a View in a Corporate schema V1.5

Open Studio
- Create a Workflow Stored Procedure Task and give it a Name e.g. Create Planning View
- Select the Spatial Data Warehouse Connection, Click on the Workflow radio button and select the Function at_wkf_executecmd.
- Select the cmdtoexecute parameter and enter the CREATE VIEW statement into the Edit parameter 'comdtoexecute' box e.g.

Example CREATE VIEW statement

CREATE VIEW planning.current_apps AS SELECT * FROM planning.all_apps WHERE decision_date IS NULL;

In order to open a view in QGIS you will need a primary key column, ideally as the first column in the view which QGIS will select by default.

- Run the task. Click OK

Now we need to grant privileges to this view.
- Create a Workflow Stored Procedure Task and give it a Name e.g. Grant Privileges to Planning View
- Select the Spatial Data Warehouse Connection, Click on the Astun radio button and select the Function at_sdw_update_schema_ownership_and_privileges.
- Select the schemaname parameter and enter the name of the schema where you created the view into the Edit parameter 'schemaname' box e.g.

- Run the task. Click OK

In PostGIS 1.5 a row will also need to be inserted into the geometry_columns table via an INSERT statement before GIS clients such as QGIS and MapInfo will be able to see the view.
- Create a Workflow Stored Procedure Task and give it a Name e.g. Insert into Geometry Columns in Planning View
- Select the Spatial Data Warehouse Connection, Click on the Workflow radio button and select the Function at_wkf_executecmd.
- Select the cmdtoexecute parameter and enter the following INSERT statement into the Edit parameter 'comdtoexecute' box e.g.

Example INSERT Statement

INSERT INTO geometry_columns(
            f_table_catalog, f_table_schema, f_table_name, f_geometry_column, 
            coord_dimension, srid, "type")
    VALUES ('', 'schema_name', 'view_name', 'wkb_geometry',2, 27700, 'GEOMETRY');

Replace 'schema_name' with the name of the schema where you created the view and 'view_name' with the name of the view. The "type" could also be POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION.

- Run the task. Click OK

Open Studio
- Create a Workflow Stored Procedure Task and give it a Name e.g. Create Planning View
- Select the Spatial Data Warehouse Connection, Click on the Workflow radio button and select the Function at_wkf_executecmd.
- Select the cmdtoexecute parameter and enter the CREATE VIEW statement into the Edit parameter 'comdtoexecute' box e.g.

Example CREATE VIEW statement

CREATE VIEW planning.current_apps AS SELECT * FROM planning.all_apps WHERE decision_date IS NULL;

In order to open a view in QGIS you will need a primary key column, ideally as the first column in the view which QGIS will select by default.

- Run the task. Click OK

Now we need to grant privileges to this view.
- Create a Workflow Stored Procedure Task and give it a Name e.g. Grant Privileges to Planning View
- Select the Spatial Data Warehouse Connection, Click on the Astun radio button and select the Function at_sdw_update_schema_ownership_and_privileges.
- Select the schemaname parameter and enter the name of the schema where you created the view into the Edit parameter 'schemaname' box e.g.

- Run the task. Click OK

In PostGIS 1.5 a row will also need to be inserted into the geometry_columns table via an INSERT statement before GIS clients such as QGIS and MapInfo will be able to see the view.
- Create a Workflow Stored Procedure Task and give it a Name e.g. Insert into Geometry Columns in Planning View
- Select the Spatial Data Warehouse Connection, Click on the Workflow radio button and select the Function at_wkf_executecmd.
- Select the cmdtoexecute parameter and enter the following INSERT statement into the Edit parameter 'comdtoexecute' box e.g.

Example INSERT Statement

INSERT INTO geometry_columns(
            f_table_catalog, f_table_schema, f_table_name, f_geometry_column, 
            coord_dimension, srid, "type")
    VALUES ('', 'schema_name', 'view_name', 'wkb_geometry',2, 27700, 'GEOMETRY');

Replace 'schema_name' with the name of the schema where you created the view and 'view_name' with the name of the view. The "type" could also be POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION.

- Run the task. Click OK