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.

Add ogc_fid column

alter table docs_geopivot_parish add column ogc_fid serial;

Browser not supported