...
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.
- 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.
Code Block |
---|
title | Example CREATE VIEW statement |
---|
|
CREATE VIEW planning.current_apps AS SELECT * FROM planning.all_apps WHERE decision_date IS NULL; |
Note |
---|
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. |
Image Added
- 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.
Image Added
- 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.
Code Block |
---|
title | 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.
Image Added