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