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
- at_wkf_geopivot_count - this counts the number of point data sources within each polygon
Note that the resulting output table names should be lower-case, this will prevent problems accessing the data from MapServer in the next step.
Stored Procedure at_wkf_geopivot_sum
at_wkf_geopivot_sum takes the following parameters:
| Parameter | Type | Description |
|---|---|---|
| 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 | 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 | 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.
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
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.
Stored Procedure at_wkf_geopivot_count
at_wkf_geopivot_count takes the following parameters:
| Parameter | Type | Description |
|---|---|---|
| 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 | 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 | 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.
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.
Click on Parameter 1. cmdtoexecute and enter the following.
alter table docs_geopivot_parish add column ogc_fid serial;