Creating the Geopivot tables
- Kim Stimpson
The following procedure outlines the process for creating the geopivot tables:
Once your datasets are loaded into PostgreSQL, Studio Workflow Stored Procedure Tasks can be used to run the geopivot functions. There are two:
- at_wkf_geopivot_sum - this sums the point data sources within each polygon (from v6.0.0 this is called wkf_geopivot_sum under the -Workflow- Function filter)
- at_wkf_geopivot_count - this counts the number of point data sources within each polygon (from v6.0.0 this is called wkf_geopivot_count under the -Workflow- Function filter)
Note that the resulting output table names should be lower-case, this will prevent problems accessing the data from MapServer in the next step.
Sum Stored Procedure
This counts of all records for each distinct entry in a category column from one table for each area in another table.
at_wkf_geopivot_sum (wkf_geopivot_sum) takes the following parameters:
| Parameter | Type | Description |
|---|---|---|
| geometry_table_schema | character varying | The schema where the geometry_table will be created - from v6.0.0 |
| geometry_table | character varying | The table of polygons to use for the data aggregation, all the columns in this table will be replicated in the output table. |
| geometry_id_column | character varying | A column in geometry_table that uniquely identifies the records. |
| data_table_schema | character varying | The schema where the data_table will be created - from v6.0.0 |
| data_table | character varying | The table containing records with point geometry and category. |
| category_column | character varying | The column in data_table to be used to categorize the data. |
| value_column | character varying | The column in data_table containing the values to be summed. |
| output_table_schema | character varying | The schema where the output_table will be created - from v6.0.0 |
| output_table | character varying | The table to create with the summed and pivoted data, if it exists it will be dropped first. |
Create a Stored Procedure Task in Studio and select the Function at_wkf_geopivot_sum (from v6.0.0 select wkf_geopivot_sum under the -Workflow- Function filter).
Here is a snapshot of the street crime data that we are using:
Now select each parameter in turn to enter the values in the Edit parameter area e.g. for the Street Crime against Ward we will use the crime_type as the category to sum.
If successful, the following is returned:
at_sys_geopivot_sum finished: table [output_table] created
The resulting table in this scenario looks like this:
Once the table has been created this can be added as a Layer in Studio e.g.
Now a Thematic can be applied and here we have selected to show how drugs crime is split across the Wards e.g.
Count Stored Procedure
This counts of all records for each distinct entry in a category column from one table for each area in another table.
at_wkf_geopivot_count (wkf_geopivot_count ) takes the following parameters:
| Parameter | Type | Description |
|---|---|---|
| geometry_table_schema | character varying | The schema where the geometry_table will be created - from v6.0.0 |
| geometry_table | character varying | The table of polygons to use for the data aggregation, all the columns in this table will be replicated in the output table. |
| geometry_id_column | character varying | A column in geometry_table that uniquely identifies the records. |
| data_table_schema | character varying | The schema where the data_table will be created - from v6.0.0 |
| data_table | character varying | The table containing records with point geometry and category. |
| category_column | character varying | The column in data_table to be used to categorize the data. |
| output_table_schema | character varying | The schema where the output_table will be created - from v6.0.0 |
| output_table | character varying | The table to create with the counted and pivoted data, if it exists it will be dropped first. |
Create a Stored Procedure Task in Studio and select the Function at_wkf_geopivot_count (from v6.0.0 select wkf_geopivot_count under the -Workflow- Function filter).
As with at_wkf_geopivot_sum select each parameter in turn to enter the values in the Edit parameter area.
If successful, at_wkf_geopivot_count returns the following:
at_sys_geopivot_count finished: table [output_table] created
Add Unique Idenfifier
Your final table must contain a unique identifier, of data type SERIAL. This is generally called “ogc_fid”. If not already present, this can be added using another Stored Procedure Task, selecting the Workflow radio button and then select the at_wkf_executecmd entry from the list (from v6.0.0 this is called wkf_executecmd under the -Workflow- Function filter).
Click on Parameter 1. cmdtoexecute and enter the following.
alter table docs_geopivot_parish add column ogc_fid serial;