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 a STRING 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.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

CALL `carto-un`.carto.CREATE_POINT_AGGREGATION_TILESET(
  R'''(
    SELECT ST_CENTROID(geometry) as geom
    FROM `bigquery-public-data.geo_openstreetmap.planet_features`
    WHERE 'building' IN (SELECT key FROM UNNEST(all_tags)) AND geometry IS NOT NULL
  )''',
  '`your-project.your-dataset.osm_buildings_14_7`',
  R'''
    {
      "zoom_min": 0,
      "zoom_max": 14,
      "aggregation_type": "quadkey",
      "aggregation_resolution": 7,
      "aggregation_placement": "cell-centroid",
      "properties":{
        "aggregated_total": {
          "formula":"count(*)",
          "type":"Number"
        }
      }
    }
  ''');

Here is an example of valid properties for a Point Aggregation Tileset:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

R'''
{
    "properties": {
        "new_column_name": {
            "formula":"count(*)",
            "type":"Number"
        },
        "most_common_ethnicity": {
            "formula":"APPROX_TOP_COUNT(ethnicity, 1)[OFFSET(0)].value",
            "type":"String"
        },
        "has_other_ethnicities": {
            "formula":"countif(ethnicity = 'other_race') > 0",
            "type":"Boolean"
        } 
    },
    "single_point_properties": {
        "name":"String",
        "address":"String"
    }
}
'''

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 a STRING 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.

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

CALL `carto-un`.carto.CREATE_SIMPLE_TILESET(
  R'''(
    SELECT geom, type
    FROM `carto-do-public-data.natural_earth.geography_glo_roads_410`
  ) _input
  ''',
  R'''`cartobq.maps.natural_earth_roads`''',
  R'''
  {
    "properties":{
      "type": "String"
    }
  }'''
);

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.

1
2
3
4
5
6
7
8

R'''
{
    "properties": {
        "source_column_name": "Number",
        "source_column_name_2": "String"
    }
}
'''

Here is an example of a valid JSON for the options parameter:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

R'''
{
    "geom_column": "geom",

    "zoom_min": 0,
    "zoom_max": 0,

    "tile_extent": 4096,
    "tile_buffer": 0,

    "max_tile_size_kb": 1024,
    "max_tile_size_strategy": "return_null",
    "max_tile_features": 10000,
    "tile_feature_order": "total_pop DESC",

    "target_partitions": 4000,
    "target_tilestats" : true,

    "drop_duplicates": true,
    "properties": {
        "geoid": "String",
        "total_pop": "Number"
    }
}
'''

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 a STRING 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. A NUMBER that defines the minimum resolution level for tiles. Any resolution level under this level won’t be generated.
  resolution_max	Default: 15. A NUMBER 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 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
  extra_metadata	Default: {}. A JSON object to specify the custom metadata of the tileset.
  per_level_metadata	Default: false. A BOOLEAN indicating whether or not to compute metadata tilestats separately for each computed level, or just for the full output table.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

CALL `carto-un`.carto.CREATE_SPATIAL_INDEX_TILESET`(
  "your-project.your-dataset.quadbin_test_dataset_level15",
  "your-project.your-dataset.spatial_index_tileset_test_output",
  R'''{
  "resolution_min": 4,
  "resolution_max": 8,
  "spatial_index_column": 'quadbin:geoid',
  "resolution": 15,
  "aggregation_resolution": 4,
  "properties": {
      "pop": {
          "formula":"sum(population)",
          "type":"Number"
      }
  }
  '''
);

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 a STRING 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.

1
2
3
4
5
6
7
8
9

CALL `carto-un`.carto.CREATE_TILESET(
  R'''(
    SELECT geom, type
    FROM `carto-do-public-data.natural_earth.geography_glo_roads_410`
  )
  ''',
  R'''`cartobq.maps.natural_earth_roads`''',
  NULL
);

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):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

CALL `carto-un`.carto.CREATE_TILESET(
  R'''(
    SELECT geom, type
    FROM `carto-do-public-data.natural_earth.geography_glo_roads_410`
  )
  ''',
  R'''`cartobq.maps.natural_earth_roads`''',
  STRUCT(
    "Tileset name" AS name,
    "Tileset description" AS description,
    NULL AS legend,
    0 AS zoom_min,
    10 AS zoom_max,
    "geom" AS geom_column_name,
    NULL AS zoom_min_column,
    NULL AS zoom_max_column,
    1024 AS max_tile_size_kb,
    "RAND() DESC" AS tile_feature_order,
    true AS drop_duplicates,
    R'''
      "custom_metadata": {
        "version": "1.0.0",
        "layer": "layer1"
      }
    ''' AS extra_metadata
  )
);

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