Aggregating Vector Data

Preface

Aggregation data is the main presence for dashboard, reports, and statistics. By using kala server we can leverage the power of aggregation includes

• Group by multiple columns
• Aggregating numbers using functions like SUM, AVG, MIN, MAX
• Aggregating geometry like calculating the total area or length of geometries and wonderfully it can transform to the defined EPSG code
• Filtering data using CQL 2 Filter
• Sort by muliple columns
• Limiting the result

Request Structure

Aggregate payload consist of this fields:

• by : (array of string) list of column names to group by
• type: (string) aggregation type, can be one of the following
  • SUM
  • AVG
  • MIN
  • MAX
• target: (object) target column to aggregate, consist of
  • kind: (string) target column kind, can be one of the following
    • COLUMN : for non geometry column
    • AREA : for geometry column area calculation
    • LENGTH : for geometry column length calculation
  • args: (list of string) this is to feed the aggregation function
    • if kind is COLUMN, args consist of single item which is the column name to aggregate
    • if kind is AREA or LENGTH, args consist of two items or None if you want to use the native geometry EPSG code and the default geometry column
      • first item is the geometry column name
      • second item is the target EPSG code to transform before calculation
• sortBy: (array of object) Optional, list of column to sort by, consist of
  • target: (string) column name to sort by
  • order: (string) Optional, sort order, can be ASC or DESC but default to ASC
• filter: (object) Optional, CQL 2 Filter to filter the data before aggregation, for now only support textual CQL 2 filter maybe in unforseeable future we will support JSON CQL 2 Filter
• limit: (number) Optional, limit the result count

Response structure

The response will be a simple columns and values like this

{
    "data": [
        {
            "column": ["Klaten"],
            "value": 12345
        },
        {
            "column": ["Jakarta"],
            "value": 12345
        }
    ]
}

It may looks odd because the column is an array of string, this is to accommodate multiple group by columns.

Example

For the sake of quick writing, because of the limited time we have, so here we go the example directly.

Let’s say we have a PostGIS datastore called postgis_sample and a collection called city which has the following columns

• name (string)
• population (number)
• area (number)
• province (string)
• geom (geometry)

Simple Aggregation

POST /api/data/datastore/postgis_sample/collection/countries/aggregate
Content-Type: application/json

{
  "by": ["province"],
  "type": "SUM",
  "target": {
    "kind": "AREA",
  }
}

Aggregation with filter

POST /api/data/datastore/postgis_sample/collection/countries/aggregate
Content-Type: application/json

{
  "by": ["continent"],
  "type": "SUM",
  "target": {
    "kind": "AREA",
  },
  "filter": "name LIKE 'KLA%'"
}