Geopivoting

Overview

Geopivoting allows you to aggregate point-level data within polygon-level geometries. For example, given point-level data on street crime you can geopivot this to show average crime rates per parish or ward, or even county. In the following screenshot we have used some Street Crime point data and Ward polygons and then applied Thematic colouring to the layer, in this example the thematic colouring is based on drug crime.

Requirements

The requirements for geopivoting are as follows:

• Both your point data and polygon data must be in PostgreSQL. They can be loaded using a Workflow Spatial Data Transformation Task, or a Data Share Connection, in Studio.
• Both datasets must contain a geometry column called wkb_geometry. If the point dataset does not, then it must contain some form of spatial identifier such as Eastings and Northings or a Postcode.
  • If the point data has Eastings and Northings then the function at_wkf_geo_createpointsfromxy can be used in a Workflow Stored Procedure Task (from v6.0.0 this is called wkf_geo_createpointsfromxy under the -Workflow- Function filter).
  • If the point data has a Postcode, then it will be necessary to join the data to a postcode dataset containing geometries, for example Code-Point Open from the Ordnance Survey (http://www.ordnancesurvey.co.uk/oswebsite/products/code-point-open/index.html). The Workflow function at_wkf_executecmd (from v6.0.0 this is called wkf_executecmd under the -Workflow- Function filter) can be used with a select query to join these two datasets together on a common column e.g.

drop table if exists sales_geo;
   
Create table sales_geo as
select n.*, replace(lower(n."Postcode",' ','') as postcode_nospace, p.wkb_geometry, p.ogc_fid from "Sales" n LEFT JOIN codepoint p ON replace(lower(n."Postcode",' ','') = replace(lower(p."postcode",' ','');

Changing the table name as required.

• The point data must contain a column of numeric values that can be used for aggregation, such as crime totals. For geopivoting to work, this column must be of a numeric data-type, in other words “integer” (or smallint, bigint), “numeric” (or decimal), “real”, or “double precision”. The data type of the column can be seen in PgAdmin3 by navigating to the correct table in your database, and expanding the “columns” node and selecting the appropriate column e.g.

Data types of “text”, “varchar” or “char” are not numeric and will need to be converted. See topic Convert a PostgreSQL column from text to numeric on how to convert text to numeric for some information on how to convert your data if required. 

If there is no existing count column one can be added using a Studio Workflow Stored Procedure Task. This should run the at_wkf_executecmd function (or from v6.0.0 the wkf_executecmd), creating a new table, and adding an additional column called count, that contains the value 1 (as an integer) for each row in the existing table:

drop table if exists sales_geo_counted;
   
Create table sales_geo_counted as
select *, 1 as count from sales_geo;

Changing the table name as required.

• Now you need to create the geopivot tables.