tiler

We currently provide procedures to create the following kind of tilesets:

Spatial index tiles (aggregates spatial indexes into tiles at specific resolutions)
Geometry-based MVT tiles of two types:
- simple tilesets to visualize features individually
- aggregation tilesets to generate aggregated point visualizations

CREATE_SIMPLE_TILESET

carto.CREATE_SIMPLE_TILESET(input, output_table, options)

Description

Create a simple tileset from a table, with feature dropping.

input: TEXT 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: TEXT 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: TEXT containing a valid JSON with the different options. Valid options are described in the table below.

warning

If a query is passed in input, it might be evaluated multiple times to generate the tileset. Thus, non-deterministic functions, such as [ROW_NUMBER] should be avoided. If such a function is needed, the query should be saved into a table first and then passed as input, to avoid inconsistent results.

warning

In case of receiving the next error: ERROR: transform: tolerance condition error (-20) (SQLSTATE XX000). The geom used as geom_column inside the input query or table might be containing latitudes around ±90°. This procedure performs reprojections to the Web Mercator Projection (SRID 3857) which is not able to show data near poles. If this is your case you might have to discard those geoms or clip them to latitudes around ±85°.

Option Description

Option	Description
`geom_column`	Default: `"geom"`. A `STRING` that marks the name of the geometry column that will be used. It must be of type `GEOMETRY`.
`zoom_min`	Default: `0`. An `INTEGER` that defines the minimum zoom level for tiles. Any zoom level under this level won't be generated.
`zoom_max`	Default: `12`. An `INTEGER` that defines the maximum zoom level for tiles. Any zoom level over this level won't be generated. If not provided, the appropriate maximum zoom level is inferred from the size of the features.
`tile_extent`	Default: `4096`. An `INTEGER` defining the extent of the tile in integer coordinates as defined by the MVT spec.
`tile_buffer`	Default: `16`. An `INTEGER` defining the additional buffer added around the tiles in extent units, which is useful to facilitate geometry stitching across tiles in the renderers. In aggregation tilesets, this property is currently not available and always `0` as no geometries go across tile boundaries.
`max_tile_size_kb`	Default: `1024` * 4 = `4096` @ `tile_resolution` of `1`. An `INTEGER` defining the approximate maximum size for a tile in kilobytes, before compression.
`max_tile_features`	Default: `0` (disabled). An `INTEGER` that sets the maximum number of features a tile might contain. This limit is applied before `max_tile_size_kb`, i.e., the tiler will first drop as many features as needed to keep this amount, and then continue with the size limits (if required). To configure in which order are features kept, use in conjunction with `tile_feature_order`. The provided value scales by `max_tile_features * 4 * tile_resolution^2`. See section below.
`tile_feature_order`	Default: `""`. A `TEXT` 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 isn't necessary. Note that in aggregation tilesets you can only use columns defined as properties, but in simple feature tilesets you can use any source column no matter if it's included in the tile as property or not. As suggestion, we recommend to use `RANDOM()` for points datasets, `ST_LENGTH()` from lines and `ST_AREA()` for polygons.
`max_tile_size_strategy`	Default: `"throw_error"`. A `STRING` that specifies how to apply the limit defined by `max_tile_features`. There are three options available: `"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. For this strategy, features will be retained according to the `tile_feature_order` specified. `"throw_error"`: The procedure execution will be aborted if any tile exceeds the limit.
`generate_feature_id`	Default: `true`. A `BOOLEAN` used to add a unique numeric id in the MVT tile.
`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. Other fields will be included in the object extra_metadata.
`properties`	Default: `{}`. A JSON object that defines the extra properties that will be included associated to each cell feature. Each property is defined by its name and type (Number, Boolean or String). Check out the examples included below.
`tile_resolution`	Default: `1`. A `FLOAT` which determines final tile resolution. Valid values are 0.25, 0.5, 1, 2 and 4 which corrospond to tile sizes of 256px, 512px, 1024px, 2048px and 4096px respectively.
In web map tilesets, each additional zoom level has 4 times the amount of tiles as the previous zoom. Level 0 has 1 tile, level 1 has 4, level 2 has 16, etc.

geom_column

Default: "geom". A STRING that marks the name of the geometry column that will be used. It must be of type GEOMETRY.

zoom_min

Default: 0. An INTEGER that defines the minimum zoom level for tiles. Any zoom level under this level won't be generated.

zoom_max

Default: 12. An INTEGER that defines the maximum zoom level for tiles. Any zoom level over this level won't be generated. If not provided, the appropriate maximum zoom level is inferred from the size of the features.

tile_extent

Default: 4096. An INTEGER defining the extent of the tile in integer coordinates as defined by the MVT spec.

tile_buffer

Default: 16. An INTEGER defining the additional buffer added around the tiles in extent units, which is useful to facilitate geometry stitching across tiles in the renderers. In aggregation tilesets, this property is currently not available and always 0 as no geometries go across tile boundaries.

max_tile_size_kb

Default: 1024 * 4 = 4096 @ tile_resolution of 1. An INTEGER defining the approximate maximum size for a tile in kilobytes, before compression.

max_tile_features

Default: 0 (disabled). An INTEGER that sets the maximum number of features a tile might contain. This limit is applied before max_tile_size_kb, i.e., the tiler will first drop as many features as needed to keep this amount, and then continue with the size limits (if required). To configure in which order are features kept, use in conjunction with tile_feature_order. The provided value scales by max_tile_features * 4 * tile_resolution^2. See section below.

tile_feature_order

Default: "". A TEXT 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 isn't necessary. Note that in aggregation tilesets you can only use columns defined as properties, but in simple feature tilesets you can use any source column no matter if it's included in the tile as property or not. As suggestion, we recommend to use RANDOM() for points datasets, ST_LENGTH() from lines and ST_AREA() for polygons.

max_tile_size_strategy

Default: "throw_error". A STRING that specifies how to apply the limit defined by max_tile_features. There are three options available:

"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. For this strategy, features will be retained according to the tile_feature_order specified.
"throw_error": The procedure execution will be aborted if any tile exceeds the limit.

generate_feature_id

Default: true. A BOOLEAN used to add a unique numeric id in the MVT tile.

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. Other fields will be included in the object extra_metadata.

properties

Default: {}. A JSON object that defines the extra properties that will be included associated to each cell feature. Each property is defined by its name and type (Number, Boolean or String). Check out the examples included below.

tile_resolution

Default: 1. A FLOAT which determines final tile resolution. Valid values are 0.25, 0.5, 1, 2 and 4 which corrospond to tile sizes of 256px, 512px, 1024px, 2048px and 4096px respectively.

In web map tilesets, each additional zoom level has 4 times the amount of tiles as the previous zoom. Level 0 has 1 tile, level 1 has 4, level 2 has 16, etc.

For the map viewer, tile_resolution is really a way of using an offset zoom level to load 'large' but few tiles , or 'small' but many tiles. For example, at zoom level 2 we might have to load 16 tiles to fill our screen. By increasing tile_resolution one step (eg. 0.5 to 1), we artificially use one z-level less (zoom level of 1) to load 4 (larger) tiles. Or we could increase tile_resolution two steps (eg. from 0.5 to 2), to artificially use two z-levels less (zoom level of 0) and thus load just one (even larger) tile. Here is a table which illustrates the real requested Z levels based on a tileset's tile_resolution.

Map Zoom	TR 0.25 (256px)	TR 0.5 (512px)	TR 1 (1024px)	TR 2 (2048px)	TR 4 (4096px)
1	2	1	0	0	0
2	3	2	1	0	0
3	4	3	2	1	0
4	5	4	3	2	1

As shown, tile_resolution of 0.5 is where the tileset zoom level and map zoom levels match, so we use 0.5 as our baseline even though the default tile_resolution is 1. Other tile_resolution values (eg, 1, 2, 4) will use offset z-levels when loaded on the map.

Relationship between `tile\_resolution` and `max\_tile\_features`/`max\_tile\_size\_kb`

In the web map viewer, tile_resolution is really a way of using an offset zoom level to load 'larger' but less tiles. For example, at zoom level 3 we might have to load 16 tiles to fill our screen. By increasing tile_resolution one step (eg. 1 to 2), we artificially use one z-level less (zoom level of 2) to load 4 (larger) tiles. Or we could increase tile_resolution two steps (eg. from 1 to 4), we artificially use two z-levels less (zoom level of 1) to load just one (even larger) tile. Here is a table which illustrates the real requested Z levels based on a tilesets tile_resolution.

UI Zoom	TR 0.25 (256px)	TR 0.5 (512px)	TR 1 (1024px)	TR 2 (2048px)	TR 4 (4096px)
1	2	1	0	0	0
2	3	2	1	0	0
3	4	3	2	1	0
4	5	4	3	2	1

As shown, tile_resolution of 0.5 is where the tileset zoom level and map zoom levels match. Other tile_resolution values will need offset z-levels.

At the default tile_resolution of 1, any value set for max_tile_features or max_tile_size_kb will be multiplied by 4. For example, at tile_resolution of 1, a specified value for max_tile_features of 10000 x 4 = 40000. This factor increases to 16 for tile_resolution of 2 and 64 for tile_resolution of 4. Likewise, it decreases to 1 (ie. no change) at tile_resolution 0.5, and 0.25 at tile_resolution of 0.25 (ie. divide by 4).

Although this is somewhat unintuitive, the offset ensures that tilesets generated using the same options (but different tile_resolutions) will always appear the same on the map.

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 in MVT format. It will contain the resulting features and their attributes (as defined by properties).

Additionally, there is a row in the tileset, identified by Z=-1, in which the data column contains metadata about the tileset in JSON format. It includes 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

CALL carto.CREATE_SIMPLE_TILESET(
  'select * from MYDB.MYSCHEMA.carto_geography_usa_censustract',
  'MYDB.MYSCHEMA.carto_geography_usa_censustract_tileset',
  '{
    "zoom_min":0,
    "zoom_max":5,
    "metadata": {
      "name": "censustract_tileset",
      "description": "A description"
    },
    "properties":{
      "geoid":"String",
      "do_perimeter":"Number",
      "do_label":"String"
    }
  }'
);

CREATE_POINT_AGGREGATION_TILESET

carto.CREATE_POINT_AGGREGATION_TILESET(input, output_table, options)

Description

Generates a point aggregation tileset.

input: TEXT 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: TEXT 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: TEXT containing a valid JSON with the different options. Valid options are described in the table below.

warning

Option Description

Option	Description
`geom_column`	Default: `"geom"`. A `STRING` that marks the name of the geometry column that will be used. It must be of type `GEOMETRY`.
`zoom_min`	Default: `0`. An `INTEGER` that defines the minimum zoom level for tiles. Any zoom level under this level won't be generated.
`zoom_max`	Default: `12`. An `INTEGER` that defines the maximum zoom level for tiles. 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 quadgrid 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 `TEXT` 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. Other fields will be included in the object extra_metadata.
`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 Postgres and returns the expected type. Note that every property different from Number will be casted to String.
`tile_extent`	Default: `4096`. An `INTEGER` defining the extent of the tile in integer coordinates as defined by the MVT spec.
`tile_buffer`	Default: `16`. An `INTEGER` defining the additional buffer added around the tiles in extent units, which is useful to facilitate geometry stitching across tiles in the renderers. In aggregation tilesets, this property is currently not available and always `0` as no geometries go across tile boundaries.
`max_tile_size_kb`	Default: `1024` * 4 = `4096` @ `tile_resolution` of `1`. An `INTEGER` defining the approximate maximum size for a tile in kilobytes, before compression.
`max_tile_features`	Default: `0` (disabled). An `INTEGER` that sets the maximum number of features a tile might contain. This limit is applied before `max_tile_size_kb`, i.e., the tiler will first drop as many features as needed to keep this amount, and then continue with the size limits (if required). To configure in which order are features kept, use in conjunction with `tile_feature_order`.The provided value scales by `max_tile_features * 4 * tile_resolution^2`. See section below.
`tile_feature_order`	Default: `""`. A `TEXT` 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 isn't necessary. Note that in aggregation tilesets you can only use columns defined as properties, but in simple feature tilesets you can use any source column no matter if it's included in the tile as property or not.
`max_tile_size_strategy`	Default: `"throw_error"`. A `STRING` that specifies how to apply the limit defined by `max_tile_features`. There are three options available: `"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. For this strategy, features will be retained according to the `tile_feature_order` specified. `"throw_error"`: The procedure execution will be aborted if any tile exceeds the limit.
`tile_resolution`	Default: `1`. A `FLOAT` which determines final tile resolution. Valid values are 0.25, 0.5, 1, 2 and 4 which corrospond to tile sizes of 256px, 512px, 1024px, 2048px and 4096px respectively.

geom_column

Default: "geom". A STRING that marks the name of the geometry column that will be used. It must be of type GEOMETRY.

zoom_min

Default: 0. An INTEGER that defines the minimum zoom level for tiles. Any zoom level under this level won't be generated.

zoom_max

Default: 12. An INTEGER that defines the maximum zoom level for tiles. 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 quadgrid 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 TEXT 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

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 Postgres and returns the expected type. Note that every property different from Number will be casted to String.

tile_extent

Default: 4096. An INTEGER defining the extent of the tile in integer coordinates as defined by the MVT spec.

tile_buffer

max_tile_size_kb

Default: 1024 * 4 = 4096 @ tile_resolution of 1. An INTEGER defining the approximate maximum size for a tile in kilobytes, before compression.

max_tile_features

Default: 0 (disabled). An INTEGER that sets the maximum number of features a tile might contain. This limit is applied before max_tile_size_kb, i.e., the tiler will first drop as many features as needed to keep this amount, and then continue with the size limits (if required). To configure in which order are features kept, use in conjunction with tile_feature_order.The provided value scales by max_tile_features * 4 * tile_resolution^2. See section below.

tile_feature_order

max_tile_size_strategy

Default: "throw_error". A STRING that specifies how to apply the limit defined by max_tile_features. There are three options available:

"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. For this strategy, features will be retained according to the tile_feature_order specified.
"throw_error": The procedure execution will be aborted if any tile exceeds the limit.

tile_resolution

Default: 1. A FLOAT which determines final tile resolution. Valid values are 0.25, 0.5, 1, 2 and 4 which corrospond to tile sizes of 256px, 512px, 1024px, 2048px and 4096px respectively.

In web map tilesets, each additional zoom level has 4 times the amount of tiles as the previous zoom. Level 0 has 1 tile, level 1 has 4, level 2 has 16, etc.

Map Zoom	TR 0.25 (256px)	TR 0.5 (512px)	TR 1 (1024px)	TR 2 (2048px)	TR 4 (4096px)
1	2	1	0	0	0
2	3	2	1	0	0
3	4	3	2	1	0
4	5	4	3	2	1

As shown, tile_resolution of 0.5 is where the tileset zoom level and map zoom levels match, so we use 0.5 as our baseline even thoug the default tile_resolution is 1. Other tile_resolution values (eg, 1, 2, 4) will use offset z-levels when loaded on the map.

Relationship between `tile\_resolution` and `aggregation\_resolution`

The value of aggregation_resolution will be adjusted based on tile_resolution. Although the default tile_resolution is 1, we use 0.5 as the baseline. So by default, aggregation_resolution gets adjusted. Its value (whether default or user-specified) will be decreased/increased as outlined below:

tile_resolution	Adjustment	Default	User-supplied Eg. 8
0.25	-1	6 - 1 = 5	8 - 1 = 7
0.5	0	6 + 0 = 6	8 + 0 = 8
1	+1	6 + 1 = 7	8 + 1 = 9
2	+2	6 + 2 = 8	8 + 2 = 10
4	+3	6 + 3 = 9	8 + 3 = 11

tile_resolution

Adjustment

Default

User-supplied Eg. 8

0.25

-1

6 - 1 = 5

8 - 1 = 7

0.5

6 + 0 = 6

8 + 0 = 8

6 + 1 = 7

8 + 1 = 9

6 + 2 = 8

8 + 2 = 10

6 + 3 = 9

8 + 3 = 11

Such that, 7, the default aggregation_level @ tile_resolution of 1, is better thought of as 6 + 1 = 7. Likewise, if a user specified an aggregation_resolution of 8, and tile_resolution of 4, the generated tile will actually use 8 + 3 = 11. But, when rendered on the map, the z-levels are offset by -3 for tile_resolution 4 so the tiles are geographically larger but otherwise look the same as those generated with another tile_resolution value.

Relationship between `tile\_resolution` and `max\_tile\_features`/`max\_tile\_size\_kb`

UI Zoom	TR 0.25 (256px)	TR 0.5 (512px)	TR 1 (1024px)	TR 2 (2048px)	TR 4 (4096px)
1	2	1	0	0	0
2	3	2	1	0	0
3	4	3	2	1	0
4	5	4	3	2	1

As shown, tile_resolution of 0.5 is where the tileset zoom level and map zoom levels match. Other tile_resolution values will need offset z-levels.

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 in MVT format. It will contain the resulting points or cell (location of the aggregated features according to aggregation_placement) and their attributes (as defined by properties).

Additionally, there is a row in the tileset, identified by Z=-1, in which the data column contains metadata about the tileset in JSON format. It includes 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

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 MIN(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_SPATIAL_INDEX_TILESET

CREATE_SPATIAL_INDEX_TILESET(source_table, target_table, options)

Description

Creates a tileset that uses a spatial index (H3 and QUADBIN 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.

source_table: TEXT that can either be a table name (e.g. schema.tablename or a full query (e.g.SELECT * FROM database.schema.tablename).
target_table: Where the resulting table will be stored. It must be a TEXT of the form database.schema.tablename. The database can be omitted (in which case the current one will be used). The schema can also be omitted and the first on the search_path will be used. The schema must exist and the caller needs to have permissions to create a new table on it. The process will fail if the target table already exists.
options: TEXT containing a valid JSON with the different options. Valid options are described the table below.

warning

Option Description

Option	Description
`resolution_min`	Default: `0`. An `NUMBER` that defines the minimum resolution level for tiles. Any resolution level under this level won't be generated.
`resolution_max`	Default: `12` for QUADBIN tilesets, `6` for H3 tilesets. A `NUMBER` that defines the maximum resolution level for tiles. Any resolution level over this level won't be generated.
`spatial_index_column`	A `STRING` in the format `spatial_index_type:column_name`, with `spatial_index_type` being the type of spatial index used in the input table (can only be `quadbin` at the moment), and `column_name` being the name of the column in that input table that contains the tile ids. Notice that the spatial index name is case-sensitive. The type of spatial index also defines the type used in the output table, which will be QUADBIN (for spatial index type `quadbin`).
`resolution`	A `NUMBER` defining the resolution of the tiles in the input table.
`aggregation_resolution`	Defaults: `6` for QUADBIN tilesets, `4` for H3 tilesets. A `NUMBER` defining the resolution to use when aggregating data at each resolution level. For a given `resolution`, data is aggregated at `resolution_level + aggregation resolution`.
`properties`	A JSON object containing the aggregated properties to add to each tile in the output table. It cannot be empty, since at least one property is needed for aggregating the original values
`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. Other fields will be included in the object extra_metadata.
`tile_resolution`	Default: `1`. A `FLOAT` which determines final tile resolution. Valid values are 0.25, 0.5, 1, 2 and 4 which corrospond to tile sizes of 256px, 512px, 1024px, 2048px and 4096px respectively.

resolution_min

Default: 0. An NUMBER that defines the minimum resolution level for tiles. Any resolution level under this level won't be generated.

resolution_max

Default: 12 for QUADBIN tilesets, 6 for H3 tilesets. A NUMBER that defines the maximum resolution level for tiles. Any resolution level over this level won't be generated.

spatial_index_column

A STRING in the format spatial_index_type:column_name, with spatial_index_type being the type of spatial index used in the input table (can only be quadbin at the moment), and column_name being the name of the column in that input table that contains the tile ids. Notice that the spatial index name is case-sensitive. The type of spatial index also defines the type used in the output table, which will be QUADBIN (for spatial index type quadbin).

resolution

A NUMBER defining the resolution of the tiles in the input table.

aggregation_resolution

Defaults: 6 for QUADBIN tilesets, 4 for H3 tilesets. A NUMBER defining the resolution to use when aggregating data at each resolution level. For a given resolution, data is aggregated at resolution_level + aggregation resolution.

properties

A JSON object containing the aggregated properties to add to each tile in the output table. It cannot be empty, since at least one property is needed for aggregating the original values

metadata

tile_resolution

Default: 1. A FLOAT which determines final tile resolution. Valid values are 0.25, 0.5, 1, 2 and 4 which corrospond to tile sizes of 256px, 512px, 1024px, 2048px and 4096px respectively.

tip

Any option left as NULL will take its default value if available.

Examples

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

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

Previousquadbin NextGuides

Last updated 1 month ago