tiler
ADVANCED
We currently provide procedures to create the following kind of tilesets:
- Spatial index tiles (aggregates spatial indexes into tiles at specific resolutions)
- Geometry-based tiles of two types:
- simple tilesets to visualize features individually
- aggregation tilesets to generate aggregated point visualizations
CREATE_SIMPLE_TILESET(input, output_table, options)
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 formatdatabase.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 geometry column that will be used. This column must be of type GEOMETRY with an SRID 4326. |
zoom_min | Default: 0 . A 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: 12 . A INTEGER 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. 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 and type (Number, Boolean, String, etc.). 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 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 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. |
tile_feature_order | Default: RANDOM() for points, ST_AREA() DESC for polygons, ST_LENGTH() DESC for lines. A VARCHAR 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 VARCHAR that specifies how to apply the limit defined by max_tile_features or max_tile_vertices . There are four options available:
. For the drop_ strategies, features will be retained according to the tile_feature_order specified. |
max_simplification_zoom | Default: 11. A NUMBER that specifies the maximum zoom level in which the simplification will be carried out. |
coordinates_precision | Default: 8. A NUMBER that indicates the geometry coordinates precision stored in the final GeoJSON tiles. This parameter should be carefully choosen in order to obtain a good trade-off between the tile size and the geometry precision, since an excessive low precision can lead to geometry collapse and excessive high precision can hit some Redshift size limits (64 KB for GeoJSON). |
generate_feature_id |
Example
1
CALL carto.CREATE_SIMPLE_TILESET(
2
'SELECT geom, population, category FROM mypopulationtable',
3
'MYDB.MYSCHEMA.population_tileset',
4
'{
5
"geom_column": "geom",
6
"zoom_min": 0, "zoom_max": 6,
7
"properties": {
8
"population": "Number",
9
"category": "String"
10
}
11
}'
12
);
CREATE_POINT_AGGREGATION_TILESET(input, output_table, options)
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 formatdatabase.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 geometry column that will be used. This column must be of type GEOMETRY with an SRID 4326 and contain only points. |
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: 12 ; 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 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 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:
|
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 Redshift and returns the expected type. Note that every property different from Number will be casted to String. |
max_tile_features | Default: 100000 . 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 100000 only applies if aggregation_resolution is higher than 8. |
tile_feature_order | Default: "" (disabled). A VARCHAR 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. Please note that the default behavior will add features to the tile according to the order in which they appear in the input table or query. |
max_tile_size_strategy | Default: "throw_error" . A VARCHAR that specifies how to apply the limit defined by max_tile_features . There are two options available:
. For the drop_ strategies, features will be retained according to the tile_feature_order specified. |
FEATURES PER TILE LIMITS
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
to2^Z-1
).y
: Y-index of the tile (0
to2^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 byproperties
).
Additionally, there is a row in the
data
column identified by 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 inXmin, Ymin, Xmax, Ymax
format.center
: center of the geographical extents asX, Y, Z
, where theZ
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
) andtype
, it containsmin
,max
,average
andsum
.
Example
1
CALL carto.CREATE_POINT_AGGREGATION_TILESET(
2
'SELECT * FROM database.schema.cities_table',
3
'database.schema.cities_tileset',
4
'{
5
"geom_column": "geom",
6
"zoom_min": 0,
7
"zoom_max": 12,
8
"aggregation_resolution": 5,
9
"aggregation_placement": "cell-centroid",
10
"properties": {
11
"num_cities": {
12
"formula": "COUNT(*)",
13
"type": "Number"
14
},
15
"population_sum": {
16
"formula": "SUM(population)",
17
"type": "Number"
18
},
19
"city_name": {
20
"formula": "(CASE WHEN COUNT(*) <= 1 THEN ANY_VALUE(city_name) ELSE NULL END)",
21
"type": "String"
22
}
23
},
24
"metadata": {
25
"name": "Population",
26
"description": "Population in the cities"
27
}
28
}'
29
);
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(input, output_table, options)
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 formatdatabase.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 |
---|---|
resolution_min | Default: 0 . A 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 VARCHAR in the format spatial_index_type:column_name , with spatial_index_type being the type of spatial index used in the input table (can be quadbin or h3 ), 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 ) or H3 (for spatial index type h3 ). |
resolution | A NUMBER defining the resolution of the tiles in the input table. |
aggregation_resolution | Defaults: 6 for QUADKEY 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 . |
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 and type (Number, Boolean, String, etc.). Please note that every property different from Number will be casted to String. |
Examples
1
CALL carto.CREATE_SPATIAL_INDEX_TILESET(
2
'YOUR_DATABASE.YOUR_SCHEMA.INPUT_TABLE_QUADBIN_LEVEL14',
3
'YOUR_DATABASE.YOUR_SCHEMA.OUTPUT_TILESET_QUADBIN_LEVEL14',
4
'{
5
"spatial_index_column": "quadbin:index",
6
"resolution": 14,
7
"resolution_min": 0,
8
"resolution_max": 8,
9
"aggregation_resolution": 6,
10
"properties": {
11
"population": {
12
"formula": "SUM(population)",
13
"type": "Number"
14
}
15
}
16
}'
17
);
1
CALL carto.CREATE_SPATIAL_INDEX_TILESET(
2
'SELECT * FROM YOUR_DATABASE.YOUR_SCHEMA.INPUT_TABLE_H3_LEVEL10',
3
'YOUR_DATABASE.YOUR_SCHEMA.OUTPUT_TILESET_H3_LEVEL10',
4
'{
5
"spatial_index_column": "h3:index",
6
"resolution": 10,
7
"resolution_min": 0,
8
"resolution_max": 6,
9
"aggregation_resolution": 4,
10
"properties": {
11
"population": {
12
"formula": "SUM(population)",
13
"type": "Number"
14
}
15
}
16
}'
17
);
Additional examples
Last modified 3mo ago