
Analytics Toolbox for BigQuery
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 and which procedures to use in each case.
CREATE_POINT_AGGREGATION_TILESET
Description
Generates a point aggregation tileset.
source_table
:STRING
that can either be a quoted qualified table name (e.g.projectID.dataset.tablename
) or a full query contained by parentheses (e.g.(Select * FROM `projectID.dataset.tablename`)
).target_table
: Where the resulting table will be stored. It must be aSTRING
of the formprojectID.dataset.tablename
. The projectID can be omitted (in which case the default one will be used). The dataset 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
:STRING
containing a valid JSON with the different options. Valid options are described the table below.
Option | Description |
---|---|
geom_column |
Default: "geom" . A STRING that marks the name of the geography column that will be used. It must be of type GEOGRAPHY . |
zoom_min |
Default: 0 . A NUMBER that defines the minimum zoom level for tiles. Any zoom level under this level won’t be generated. |
zoom_max |
Default: 0 . A NUMBER that defines the maximum zoom level for tiles. Any zoom level over this level won’t be generated. |
zoom_min_column |
Default: NULL . It is the column that each row could have to modify its starting zoom. It can be NULL (then zoom_min will be used). When provided, if its value is greater than zoom_min , it will take precedence and be used as the actual minimum. |
zoom_max_column |
Default: NULL . It is the column that each row could have to modify its end zoom level. It can be NULL (then zoom_max will be used). When provided, if its value is lower than zoom_max , it will be taken as the real maximum zoom level. |
target_partitions |
Default: 4000 . Max: 4000 . A NUMBER that defines the maximum amount of partitions to be used in the target table. The partition system, which uses a column named carto_partition , divides the available partitions first by zoom level and spatial locality to minimize the cost of tile read requests in web maps. Beware that this does not necessarily mean that all the partitions will be used, as a sparse dataset will leave some of these partitions unused. If you are using BigQuery BI Engine consider that it supports a maximum of 500 partitions per table. |
target_tilestats |
Default: true . A BOOLEAN to determine whether to include statistics of the properties in the metadata. These statistics are based on mapbox-tilestats and depend on the property type:
|
tile_extent |
Default: 4096 . A NUMBER defining the extent of the tile in integer coordinates as defined by the MVT spec. |
tile_buffer |
Default: 0 . A NUMBER 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 . A NUMBER specifying the approximate maximum size for a tile. |
max_tile_size_strategy |
Default: "throw_error" . A STRING that determines what to do when the maximum size of a tile is reached while it is still processing data. There are three options available:
|
max_tile_features |
Default: 0 (disabled). A NUMBER 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 features are kept, use in conjunction with tile_feature_order . |
tile_feature_order |
Default: "" (disabled). 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 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. This is an expensive operation, so it’s recommended to only use it when necessary. |
aggregation_type |
Default: "quadkey" . A STRING defining what kind of spatial aggregation is to be used. Currently only quadkey is supported. |
aggregation_resolution |
Default: 6 . A NUMBER that specifies the resolution of the spatial aggregation.For quadkey the aggregation for zoom z is done 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.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 STRING that defines what type of geometry will be used for the cells generated in the aggregation. For a quadkey aggregation, 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. |
properties |
Default: {}. A JSON object that defines the extra properties that will be included associated to each cell feature. In Point Aggregation Tilesets we have two kinds of properties : the main ones, "properties" , which are the result of an aggregate function, and "single_point_properties" , which are properties that are only applied when there is a single point in the cell, therefore, they are columns from the source data points themselves, not an aggregation.Each main property is defined by its name, type (Number, Boolean or String) and formula (any formula that uses an aggregate function supported by BigQuery and returns the expected type) to generate the properties from all the values of the points that fall under the cell. Only name and type are necessary for "single_point_properties" . Check out the examples included below. |
Examples
|
|
Here is an example of valid properties
for a Point Aggregation Tileset:
|
|
In the example above, for all features we would get a property "new_column_name"
with the number of points that fall in it, the "most_common_ethnicity"
of those rows and whether there are points whose ethnicity value matches one specific value ("has_other_ethnicities"
). 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: "name"
and "address"
.
CREATE_SIMPLE_TILESET
Description
Generates a simple tileset.
source_table
:STRING
that can either be a quoted qualified table name (e.g.`projectID.dataset.tablename`
) or a full query contained by parentheses (e.g.(SELECT * FROM `projectID.dataset.tablename`)
).target_table
: Where the resulting table will be stored. It must be aSTRING
of the form`projectID.dataset.tablename`
. The projectID can be omitted (in which case the default one will be used). The dataset 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
:STRING
containing a valid JSON with the different options. Valid options are described the table below.
Option | Description |
---|---|
geom_column |
Default: "geom" . A STRING that marks the name of the geography column that will be used. It must be of type GEOGRAPHY . |
zoom_min |
Default: 0 . A NUMBER that defines the minimum zoom level for tiles. Any zoom level under this level won’t be generated. |
zoom_max |
Default: 0 . A NUMBER that defines the minimum zoom level for tiles. Any zoom level over this level won’t be generated. |
zoom_min_column |
Default: NULL . It is the column that each row could have to modify its starting zoom. It can be NULL (then zoom_min will be used). When provided, if its value is greater than zoom_min , it will take precedence and be used as the actual minimum. |
zoom_max_column |
Default: NULL . It is the column that each row could have to modify its end zoom level. It can be NULL (then zoom_max will be used). When provided, if its value is lower than zoom_max , it will be taken as the real maximum zoom level. |
target_partitions |
Default: 4000 . Max: 4000 . A NUMBER that defines the maximum amount of partitions to be used in the target table. The partition system, which uses a column named carto_partition , divides the available partitions first by zoom level and spatial locality to minimize the cost of tile read requests in web maps. Beware that this does not necessarily mean that all the partitions will be used, as a sparse dataset will leave some of these partitions unused. If you are using BigQuery BI Engine consider that it supports a maximum of 500 partitions per table. |
target_tilestats |
Default: true . A BOOLEAN to determine whether to include statistics of the properties in the metadata. These statistics are based on mapbox-tilestats and depend on the property type:
|
tile_extent |
Default: 4096 . A NUMBER defining the extent of the tile in integer coordinates as defined by the MVT specification. |
tile_buffer |
Default: 16 . A NUMBER defining the additional buffer added around the tiles in extent units, which is useful to facilitate geometry stitching across tiles in the renderers. |
max_tile_size_kb |
Default: 1024 . Maximum allowed: 6144 . A NUMBER specifying the approximate maximum size for a tile in kilobytes. |
max_tile_size_strategy |
Default: "throw_error" . A STRING that determines what to do when the maximum size of a tile is reached while it is still processing data. There are three options available:
|
max_tile_features |
Default: 0 (disabled). A NUMBER 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 features are kept, use in conjunction with tile_feature_order . |
tile_feature_order |
Default: "" (disabled). 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 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. This is an expensive operation, so it’s recommended to only use it when necessary. |
drop_duplicates |
Default: false . A BOOLEAN to drop duplicate features in a tile. This will drop only exact matches (both the geometry and the properties are exactly equal). As this requires sorting the properties, which is expensive, it should only be used when necessary. |
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 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. |
Examples
|
|
In Simple Tilesets, the properties
are defined by the source data itself. You only have to write the name of the column (as defined in the source query or table) and its type. It doesn’t support any extra transformations or formulae since those can be applied to the source query directly.
|
|
Here is an example of a valid JSON for the options
parameter:
|
|
CREATE_SPATIAL_INDEX_TILESET
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
:STRING
that can either be a quoted qualified table name (e.g.`projectID.dataset.tablename`
) or a full query contained by parentheses (e.g.(SELECT * FROM `projectID.dataset.tablename`)
).target_table
: Where the resulting table will be stored. It must be aSTRING
of the form`projectID.dataset.tablename`
. The projectID can be omitted (in which case the default one will be used). The dataset 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
:STRING
containing a valid JSON with the different options. Valid options are described the table below.Option Description resolution_min
Default: 2
. ANUMBER
that defines the minimum resolution level for tiles. Any resolution level under this level won’t be generated.resolution_max
Default: 15
. ANUMBER
that defines the minimum resolution level for tiles. Any resolution level over this level won’t be generated.spatial_index_column
A STRING
in the formatspatial_index_type:column_name
, withspatial_index_type
being the type of spatial index used in the input table (can bequadbin
orh3
), andcolumn_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 typequadbin
) or H3 (for spatial index typeh3
).resolution
A NUMBER
defining the resolution of the tiles in the input table.aggregation_resolution
Defaults: 6
for QUADBIN tilesets,4
for H3 tilesets. ANUMBER
defining the resolution to use when aggregating data at each resolution level. For a givenresolution
, data is aggregated atresolution_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 extra_metadata
Default: {}
. A JSON object to specify the custom metadata of the tileset.per_level_metadata
Default: false
. ABOOLEAN
indicating whether or not to compute metadata tilestats separately for each computed level, or just for the full output table.
Examples
|
|
CREATE_TILESET
Description
Creates a simple tileset. It differs from carto.CREATE_SIMPLE_TILESET
in that the procedure performs a previous analysis in order to find automatically the right options for the tileset. It is done by extracting all the properties to be included within the tileset and sampling the data in order to avoid BigQuery limitations. Therefore, only source_table
and target_table
are mandatory and options
can be set to NULL
.
source_table
:STRING
that can either be a quoted qualified table name (e.g.`projectID.dataset.tablename`
) or a full query contained by parentheses (e.g.(SELECT * FROM `projectID.dataset.tablename`)
).target_table
: Where the resulting table will be stored. It must be aSTRING
of the form`projectID.dataset.tablename`
. The projectID can be omitted (in which case the default one will be used). The dataset 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
:STRUCT<name STRING, description STRING,legend STRING, zoom_min INT64, zoom_max INT64, geom_column_name STRING, zoom_min_column STRING, zoom_max_column STRING, max_tile_size_kb INT64, tile_feature_order STRING, drop_duplicates BOOL, extra_metadata STRING>|NULL
containing the different options. Valid options are described in the table below.
Option | Description |
---|---|
name |
Default: "" . A STRING that contains the name of tileset to be included in the TileJSON. |
description |
Default: "" . A STRING that contains a description for the tileset to be included in the TileJSON. |
legend |
Default: "" . A STRING that contains a legend for the tileset to be included in the TileJSON. |
zoom_min |
Default: 0 for POINTS datasets and 2 for POLYGON/LINESTRING datasets. A NUMBER that defines the minimum zoom level for tiles. Any zoom level under this level won’t be generated. |
zoom_max |
Default: 15 . A NUMBER that defines the minimum zoom level for tiles. Any zoom level over this level won’t be generated. |
geom_column_name |
Default: "geom" . A STRING that contains the name of the geography column that will be used. It must be of type GEOGRAPHY . |
zoom_min_column |
Default: NULL . It is the column that each row could have to modify its starting zoom. It can be NULL (then zoom_min will be used). It must be a positive number between zoom_min and zoom_max . |
zoom_max_column |
Default: NULL . It is the column that each row could have to modify its end zoom level. It can be NULL (then zoom_max will be used). It must be a positive number between zoom_min and zoom_max . |
max_tile_size_kb |
Default: 512 . Maximum allowed: 6144 . A NUMBER setting the approximate maximum size for a tile in kilobytes. For every zoom level, a consistent fraction of features will be dropped in every tile to make sure all generated tiles are below this maximum. |
tile_feature_order |
Default: NULL . 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 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. This is an expensive operation, so it’s recommended to only use it when necessary. If no order is provided, a custom dropping depending on the geometry type is performed. In case of POINT geometries, features are dropped randomly. In case of POLYGON geometries the features are added ordered by their area, while for LINESTRING geometries the criteria is the feature length. |
drop_duplicates |
Default: false . A BOOLEAN to drop duplicate features in a tile. This will drop only exact matches (both the geometry and the properties are exactly equal). As this requires sorting the properties, which is expensive, it should only be used when necessary. |
extra_metadata |
Default: {}. A JSON object to specify the custom metadata of the tileset. |
Examples
|
|
If any of the options introduced above are required, the remaining fields should also be provided or set to NULL
. Here is an example of a valid structure for the options
parameter (the field alias can be ignored):
|
|

This project has received funding from the European Union’s Horizon 2020 research and innovation programme under grant agreement No 960401.