Analytics Toolbox for Redshift

tiler

We currently provide a procedure to create aggregation tilesets, useful to generate aggregated point visualizations. We will soon support the creation of simple tilesets to visualize features individually. Visit the Overview section to learn more about tileset types.

CREATE_POINT_AGGREGATION_TILESET

Description

Generates a point aggregation tileset.

• input: VARCHAR that can either contain a table name (e.g. database.schema.tablename) or a full query (e.g.(SELECT * FROM database.schema.tablename)).
• output_table: VARCHAR of the format database.schema.tablename where the resulting tileset will be stored. The database and schema must exist and the caller needs to have permissions to create a new table in it. The process will fail if the table already exists.
• options: VARCHAR containing a valid JSON with the different options. Valid options are described in the table below.

Result

The generated tileset consists of a table with the following columns, where each row represents a tile:

• z: zoom level of the tile.
• x: X-index of the tile (0 to 2^Z-1).
• y: Y-index of the tile (0 to 2^Z-1).
• data: contents of the tile, encoded as a GeoJSON string (a feature collection). It will contain the resulting points (location of the aggregated features) and their attributes (as defined by properties).

Additionally, there is a row in the data column identified with Z=-1 which contains metadata about the tileset in JSON format. It contains the following properties:

• bounds: geographical extents of the source as a string in Xmin, Ymin, Xmax, Ymax format.
• center: center of the geographical extents as X, Y, Z, where the Z represents the zoom level where a single tile spans the whole extents size.
• zmin: minimum zoom level in the tileset.
• zmax: maximum zoom level in the tileset.
• tilestats: stats about the feature’s properties. In addition to its name (attribute) and type, it contains min, max, average and sum.

Example

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

CALL carto.CREATE_POINT_AGGREGATION_TILESET(
  'SELECT * FROM database.schema.cities_table',
  'database.schema.cities_tileset',
  '{
    "geom_column": "geom",
    "zoom_min": 0,
    "zoom_max": 12,
    "aggregation_resolution": 5,
    "aggregation_placement": "cell-centroid",
    "properties": {
      "num_cities": {
        "formula": "COUNT(*)",
        "type": "Number"
      },
      "population_sum": {
        "formula": "SUM(population)",
        "type": "Number"
      },
      "city_name": {
        "formula": "(CASE WHEN COUNT(*) <= 1 THEN ANY_VALUE(city_name) ELSE NULL END)",
        "type": "String"
      }
    },
    "metadata": {
      "name": "Population",
      "description": "Population in the cities"
    }
  }'
);

In the example above, for all features we would get a property "num_cities" with the number of points that fall in it and "population_sum" with the sum of the population in those cities. In addition to this, when there is only one point that belongs to this property (and only in that case) we will also get the column values from the source data in "city_name".

CREATE_SIMPLE_TILESET

Description

Generates a simple tileset.

• input: VARCHAR that can either contain a table name (e.g. database.schema.tablename) or a full query (e.g.‘SELECT * FROM db.schema.tablename’).
• output_table: VARCHAR of the format database.schema.tablename where the resulting tileset will be stored.
• options: VARCHAR containing a valid JSON with the different options. Valid options are described in the table below.

Example

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

CALL carto.CREATE_SIMPLE_TILESET(
  'SELECT geom, population, category FROM mypopulationtable',
  'MYDB.MYSCHEMA.population_tileset',
  '{
    "geom_column": "geom",
    "zoom_min": 0, "zoom_max": 6,
    "properties": {
      "population": "Number",
      "category": "String"
    }
  }'
);

CREATE_SPATIAL_INDEX_TILESET

Description

Creates a tileset that uses a spatial index (H3 and QUADKEYS are currently supported), aggregating data from an input table that uses that same spatial index.

Aggregated data is computed for all levels between resolution_min and resolution_max. For each resolution level, all tiles for the area covered by the source table are added, with data aggregated at level resolution + aggregation resolution.

• input: VARCHAR that can either contain a table name (e.g. database.schema.tablename) or a full query (e.g.‘SELECT * FROM db.schema.tablename’).
• output_table: VARCHAR of the format database.schema.tablename where the resulting tileset will be stored.
• options: VARCHAR containing a valid JSON with the different options. Valid options are described in the table below.

Examples

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

CALL carto.CREATE_SPATIAL_INDEX_TILESET(
  'YOUR_DATABASE.YOUR_SCHEMA.INPUT_TABLE_QUADBIN_LEVEL14',
  'YOUR_DATABASE.YOUR_SCHEMA.OUTPUT_TILESET_QUADBIN_LEVEL14',
  '{
    "spatial_index_column": "quadbin:index",
    "resolution": 14,
    "resolution_min": 0,
    "resolution_max": 8,
    "aggregation_resolution": 6,
    "properties": {
      "population": {
        "formula": "SUM(population)",
        "type": "Number"
      }
    }
  }'
);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

CALL carto.CREATE_SPATIAL_INDEX_TILESET(
  'SELECT * FROM YOUR_DATABASE.YOUR_SCHEMA.INPUT_TABLE_H3_LEVEL10',
  'YOUR_DATABASE.YOUR_SCHEMA.OUTPUT_TILESET_H3_LEVEL10',
  '{
    "spatial_index_column": "h3:index",
    "resolution": 10,
    "resolution_min": 0,
    "resolution_max": 6,
    "aggregation_resolution": 4,
    "properties": {
      "population": {
        "formula": "SUM(population)",
        "type": "Number"
      }
    }
  }'
);