Analytics Toolbox for Snowflake

tiler

We currently provide procedures to create two types of tilesets: simple and aggregation tilesets, the former to visualize features individually and the latter to generate aggregated point visualizations. 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.

Option	Description
`geom_column`	Default: `"GEOM"`. A `VARCHAR` that indicates the name of the geography column that will be used. The geography column must be of type `GEOGRAPHY` and contain only points. The capitalization (uppercase/lowercase letters) of the name must match exactly the column name; note that Snowflake by default converts names to uppercase.
`zoom_min`	Default: `0`. An `INTEGER` that defines the minimum zoom level at which tiles will be generated. Any zoom level under this level won’t be generated.
`zoom_max`	Default: `15`; maximum: `20`. An `INTEGER` that defines the maximum zoom level at which tiles will be generated. Any zoom level over this level won’t be generated.
`aggregation_resolution`	Default: `6`. An `INTEGER` that specifies the resolution of the spatial aggregation. Aggregation for zoom `z` is based on quadkey cells at `z + resolution level`. For example, with resolution `6`, the `z0` tile will be divided into cells that match the `z6` tiles, or the cells contained in the `z10` tile will be the boundaries of the `z16` tiles within them. In other words, each tile is subdivided into `4^resolution` cells, which is the maximum number of resulting features (aggregated) that the tiles will contain. Note that adding more granularity necessarily means heavier tiles which take longer to be transmitted and processed in the final client, and you are more likely to hit the internal memory limits.
`aggregation_placement`	Default: `"cell-centroid"`. A `VARCHAR` that defines what type of geometry will be used to represent the cells generated in the aggregation, which will be the features of the resulting tileset. There are currently four options: `"cell-centroid"`: Each feature will be defined as the centroid of the cell, that is, all points that are aggregated together into the cell will be represented in the tile by a single point positioned at the centroid of the cell. `"cell"`: Each feature will be defined as the entire cell’s polygon, thus the final representation in the tile will be a polygon. This provides more precise coordinates but takes more space in the tile and requires more CPU to process it in the renderer. `"features-any"`: The aggregation cell will be represented by any random point from the source data contained within it. That is, if 10 points fall inside a cell, the procedure will randomly choose the location of one of them to represent the aggregation cell. `"features-centroid"`: The feature will be defined as the centroid (point) of the collection of points within the cell.
`metadata`	Default: {}. A JSON object to specify the associated metadata of the tileset. Use this to set the `name`, `description` and `legend` to be included in the TileJSON.
`properties`	Default: {}. A JSON object that defines the properties that will be included associated with each cell feature. Each `property` is defined by its name, type (Number, Boolean, String, etc.) and formula to be applied to the values of the points that fall under the cell. This formula can be any SQL formula that uses an aggregate function supported by Redshift and returns the expected type. Please note that every property different from Number will be casted to String.
`max_tile_features`	Default: `10000`. A `NUMBER` that sets the maximum number of features (points) a tile can contain. When this maximum is reached, the procedure will drop features according to the chosen `max_tile_size_strategy`. You can configure in which order the features are kept by setting the `tile_feature_order` property. Any value lower than `4^aggregation_resolution` will be ineffective, therefore the default of 10000 only applies if `aggregation_resolution` is higher than 6.
`tile_feature_order`	Default: `RANDOM()`. A `STRING` defining the order in which features are added to a tile. This expects the SQL `ORDER BY` keyword definition, such as `"aggregated_total DESC"`. The `"ORDER BY"` part must not be included. You can use any source column even if it is not included in the tileset as a property.
`max_tile_size_strategy`	Default: `"throw_error"`. A `STRING` that specifies how to apply the limit defined by `max_tile_features`. There are four options available: `"drop_features"`: In each tile the features that would exceed the limit are dropped. Different fractions of the total features may be dropped in each tile, which on a map can appear as noticeable differences in feature density between tiles. `"drop_fraction_as_needed"`: For every zoom level, this process will drop a consistent fraction of features in every tile to make sure all generated tiles are below the limit. Since a constant fraction of the features is dropped for all tiles of a given zoom level, this will in general drop more features in less populated tiles than the the `"drop_features"` strategy. `"return_null"`: A row with a NULL DATA column will be produced for all tiles that exceed the limit. `"throw_error"`: The procedure execution will be aborted if any tile exceeds the limit. . For the `drop_` strategies, features will be retained according to the `tile_feature_order` specified.

The value of aggregation_resolution sets an upper bound to how many features can be present in a tile. For a value of n, a maximum of 4^n (4 raised to n) features can be present in a tile. For example, for an aggregation resolution of 8, the maximum number of features (points) will be 65536 per tile. This value can be too high and produce tiles that are too large when either the aggregation resolution is high or many properties are included. In that case, to improve the performance of the map visualizations, the max_tile_features should be used to limit the size of the tiles to about 1MB.

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 identified with Z=-1 which contains metadata about the tileset in the DATA column 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, sum and quantiles for numeric attributes and categories for text attributes.

Example

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


CALL carto.CREATE_POINT_AGGREGATION_TILESET(
  'SELECT geom, population FROM mypopulationtable',
  'MYDB.MYSCHEMA.population_tileset',
  '{
    "geom_column": "GEOM",
    "zoom_min": 0, "zoom_max": 6,
    "aggregation_resolution": 5,
    "aggregation_placement": "features-centroid",
    "properties": {
      "population_sum": { "formula": "SUM(POPULATION)", "type": "Number" }
    }
  }'
)

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.

Option	Description
`geom_column`	Default: `"GEOM"`. A `VARCHAR` that specifies the name of the geography column that will be used. It must be of type `GEOGRAPHY`. The capitalization (uppercase/lowercase letters) of the name must match exactly the column name; note that Snowflake by default will use only uppercase letters for the column names, but this can be altered if column names are quoted in their definition. Do not use quotes here, just mutch the capitalization.
`zoom_min`	Default: `0`. A `NUMBER` that defines the minimum zoom level at which tiles will be generated. Any zoom level under this level won’t be generated.
`zoom_max`	Default: `10`. A `NUMBER` that defines the maximum zoom level at which tiles will be generated. Any zoom level over this level won’t be generated.
`metadata`	Default: {}. A JSON object to specify the associated metadata of the tileset. Use this to set the `name`, `description` and `legend` to be included in the TileJSON.
`properties`	Default: {}. A JSON object that defines the properties that will be included associated with each cell feature. Each property is defined by its name and type (Number, String, etc.). Please note that every property different from Number will be casted to String. Property names must correspond to column names in the input and match it’s capitalization exactly; note by default Snowflake will use only uppercase letters for the column names but this can altered if column names where quoted in their definitions. Do not use quotes here, just mutch the capitalization. The properties will appear in the GeoJSON data with the same capitalization as the column names.
`max_tile_features`	Default: `10000`. A `NUMBER` that sets the maximum number of features a tile can contain. This limit only applies when the input geometries are points. When this limit is reached, the procedure will stop adding features into the tile. You can configure in which order the features are kept by setting the `tile_feature_order` property.
`max_tile_vertices`	Default: `200000`. A `NUMBER` that sets the maximum number of vertices a tile can contain. This limit only applies when the input geometries are lines or polygons. When this maximum is reached, the procedure will drop features according to the chosen `max_tile_size_strategy`. You can configure in which order the features are kept by setting the `tile_feature_order` property.
`tile_feature_order`	Default: `RANDOM()` for points, `ST_AREA() DESC` for polygons, `ST_LENGTH() DESC` for lines. A `STRING` defining the order in which properties are added to a tile. This expects the SQL `ORDER BY` keyword definition, such as `"aggregated_total DESC"`. The `"ORDER BY"` part must not be included. You can use any source column even if it is not included in the tileset as a property.
`max_tile_size_strategy`	Default: `"throw_error"`. A `STRING` that specifies how to apply the limit defined by `max_tile_features` or `max_tile_vertices`. There are four options available: `"drop_features"`: In each tile the features that exceed the limit are dropped. Different fractions of the total features may be dropped in each tile, which on a map can appear as noticeable differences in feature density between tiles. `"drop_fraction_as_needed"`: For every zoom level, this process will drop a consistent fraction of features in every tile to make sure all generated tiles are below the limit. Since a constant fraction of the features is dropped for all tiles of a given zoom level, this will in general drop more features in less populated tiles than the the `"drop_features"` strategy. `"return_null"`: A row with a NULL data column will be produced for all tiles that exceed the limit. `"throw_error"`: The procedure execution will be aborted if any tile exceeds the limit. . For the `drop_` strategies, features will be retained according to the `tile_feature_order` specified.

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"
    }
  }'
)