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 tiles of two types:
simple tilesets to visualize features individually
aggregation tilesets to generate aggregated point visualizations
Visit the Tilesets section to learn more about tileset types.
CREATE_SIMPLE_TILESET
Description
Generates a simple tileset.
input:VARCHARthat can either contain a table name (e.g.database.schema.tablename) or a full query (e.g.'SELECT * FROM db.schema.tablename').output_table:VARCHARof the format'database.schema.tablename'where the resulting tileset will be stored.options:VARCHARcontaining 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.
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: 12. 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. 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, 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 * 4 = 40000 @ tile_resolution of 1. 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 * 4 = 800000 @ tile_resolution of 1. 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."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.
generate_feature_id
Default: true. A BOOLEAN used to add a unique numeric id in the GeoJSON.
calculate_geoids
Default: false. Generates an additional geoids column that contains the geoid value from each row in the input data that intersects with the tile. The input table must have a column named geoid. This option is required to use the resulting tileset as a boundary.
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.
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\_vertices/max\_tile\_features
tile\_resolution and max\_tile\_vertices/max\_tile\_featuresIn 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.
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 values set for option(s) max_tile_features or max_tile_vertices will be multiplied by 4. Even when unspecified, the default values for max_tile_features (40000) and max_tile_vertices (800000) are better thought of as the defaults of 10000 and 200000 @ tile_resolution of 0.5, multiplied by 4. For example, at tile_resolution of 1, the default max_tile_features => [default max_tile_features> @ 0.5] X 4 => 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.
Example
CREATE_POINT_AGGREGATION_TILESET
Description
Generates a point aggregation tileset.
input:VARCHARthat can either contain a table name (e.g.database.schema.tablename) or a full query (e.g.(SELECT * FROM database.schema.tablename)).output_table:VARCHARof the formatdatabase.schema.tablenamewhere 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:VARCHARcontaining 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.
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: 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 + 1 => 7 @ tile_resolution of 1. 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:
"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 Snowflake and returns the expected type. Note that every property different from Number will be casted to String.
max_tile_features
Default: 10000 * 4 => 40000 @ tile_resolution of 1. 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."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.
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 tilesets tile_resolution.
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
tile\_resolution and aggregation\_resolutionThe 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:
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
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
tile\_resolution and max\_tile\_featuresAt the default tile_resolution of 1, any values set for option(s) max_tile_features will be multiplied by 4. Even when unspecified, the default values for max_tile_features (40000) is better thought of as the default of 10000 @ tile_resolution of 0.5, multiplied by 4. Such that, max_tile_features @ tile_resolution of 1 => [default max_tile_features @ 0.5] X 4 => 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.
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 (0to2^Z-1).Y: Y-index of the tile (0to2^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 identified by 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 inXmin, Ymin, Xmax, Ymaxformat.center: center of the geographical extents asX, Y, Z, where theZrepresents 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,sumandquantilesfor numeric attributes andcategoriesfor text attributes.
Example
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
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:STRINGthat can either be a quoted qualified table name (e.g.`database.schema.tablename`) or a full query contained by parentheses (e.g.(SELECT * FROM `database.schema.tablename`)).target_table: Where the resulting table will be stored. It must be aSTRINGof the form`project-id.dataset-id.table-name`. Theproject-idcan 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:STRINGcontaining a valid JSON with the different options. Valid options are described 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.
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.
tile_resolution
Default: 1. Valid values are 0.25 (256px), 0.5 (512px), 1 (1024px), 2 (2048px) or 4 (4096px)
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 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 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. Properties are case sensitive.
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.
Examples
Snowflake treats columns uppercase by default, to set explicit lowercase use the following syntax:
Additional examples
Last updated