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. |
These parameters should be listed in comma-delimited form in Studio e.g.
If successful, the following is returned:
at_sys_geopivot_sum finished: table [output_table] created
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. |
As with at_wkf_geopivot_sum these parameters should be listed in comma-delimited form in Studio.
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 a Studio Workflow Stored Procedure Task that calls the function at_sys_add_column e.g.
This stored procedure takes the following parameters as shown in the above screenshot:
| Parameter | Type | Description |
|---|---|---|
| tablename | text | The name of the table to which you wish to add the column |
| columnname | text | The name of the column you wish to add, in this case ‘ogc_fid’ |
| datatype | text | The data type for the column, in this case ‘serial’ |