Analytics Toolbox for BigQuery

Analytics Toolbox for BigQuery

Creating and visualizing tilesets

Creating a tileset

From the CARTO Workspace

The CARTO Workspace offers a user interface that you can use to create simple tilesets. The option Create tileset is available from the Data Explorer for those tables that are too big to be visualized directly and therefore require the creation of a tileset.

Create tileset button available from the Data Explorer

Clicking on the Create tileset button will trigger a tileset creation wizard that you can follow along to configure your tileset. For step-by-step instructions, please visit this guide.

Create tileset wizard from the Data Explorer

From the BigQuery console or client

As a CARTO Analytics Toolbox module, the Tiler’s capabilities will be available as SQL procedures that can be executed directly from your BigQuery console or client of choice after connecting your CARTO account to BigQuery.

To check that your Google account or service account has access to the Tiler, try running this query:

1
2
SELECT `carto-un`.tiler.VERSION()
--Use `carto-un-eu`.tiler.VERSION() if your data is in GCP's EU multi-region

Check the Getting Access section if you run into any errors when running the query above.

Once you are all set getting access to the Tiler, creating a tileset is as easy as opening your BigQuery console or client and running a query. In this case, we are going to create a simple tileset (see Tileset procedures) from a couple of joined tables: one containing demographic information for the US at the blockgroup level, the other containing the geometries of the blockgroups.

The result will be a tileset with the geometry and total population per blockgroup:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
CALL `carto-un`.tiler.CREATE_SIMPLE_TILESET(
  R'''
  (
    SELECT
      d.geoid,
      d.total_pop,
      g.geom 
    FROM `carto-do-public-data.usa_acs.demographics_sociodemographics_usa_blockgroup_2015_5yrs_20142018` d
    JOIN `carto-do-public-data.carto.geography_usa_blockgroup_2015` g
      ON d.geoid = g.geoid
  ) _input
  ''',
  R'''`cartobq.maps.blockgroup_pop`''',
  R'''
  {
    "zoom_min": 0,
    "zoom_max": 14,
    "max_tile_size_kb": 3072,
    "properties":{
      "geoid": "String",
      "total_pop": "Number"
    }
  }'''
);

Creating a tileset by means of tiler.CREATE_SIMPLE_TILESET can sometimes be cumbersome due to the large amount of parameters that users have to manage. In order to relieve them of this responsibility, we provide a wrapper function in which the tiler configuration is automatically set by performing a previous analysis of the input data. This analysis also serves as a validation step to avoid BigQuery limitations. As a result, the above generated tileset can also be obtained by executing:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
CALL `carto-un`.tiler.CREATE_TILESET(
  R'''
  (
    SELECT
      d.geoid,
      d.total_pop,
      g.geom 
    FROM `carto-do-public-data.usa_acs.demographics_sociodemographics_usa_blockgroup_2015_5yrs_20142018` d
    JOIN `carto-do-public-data.carto.geography_usa_blockgroup_2015` g
      ON d.geoid = g.geoid
  ) _input
  ''',
  R'''`cartobq.maps.blockgroup_pop`''',
  null
);

or by defining explicitly the options if they are required:

 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
28
29
CALL `carto-un`.tiler.CREATE_TILESET(
  R'''
  (
    SELECT
      d.geoid,
      d.total_pop,
      g.geom 
    FROM `carto-do-public-data.usa_acs.demographics_sociodemographics_usa_blockgroup_2015_5yrs_20142018` d
    JOIN `carto-do-public-data.carto.geography_usa_blockgroup_2015` g
      ON d.geoid = g.geoid
  ) _input
  ''',
  R'''`cartobq.maps.blockgroup_pop`''',
  STRUCT
  (
    null AS name,
    null AS description,
    null AS legend,
    0 AS zoom_min,
    14 AS zoom_max,
    null AS geom_column_name,
    null AS zoom_min_column,
    null AS zoom_max_column,
    3072 AS max_tile_size_kb,
    null AS tile_feature_order,
    null AS drop_duplicates,
    null AS extra_metadata
  )
);

Visualizing a tileset

From the CARTO Workspace

The CARTO Workspace offers access to the Data Explorer, where you will be able to preview your tilesets, and Builder, CARTO’s state-of-the-art map making tool, where you will be able to style them, include them in your visualizations and share them.

Previewing tilesets from the Data Explorer

The Data Explorer offers a preview of your tilesets and displays their associated details and metadata, such as their size, number of records and statistics regarding the tile sizes per zoom level. Please refer to this page for more information regarding the Data Explorer.

Tileset preview from the Data Explorer
Creating maps with tilesets using Builder

You can include tilesets as layers in your maps created with Builder. To do so, you have two options:

  • use the Create map option from the tileset preview page in the Data Explorer (see previous screenshot). This action will create a new map with your tileset as a its only layer.
  • adding a layer to an existing map.

For the latter option, you simply need to follow these simple steps:

  1. Click on the Add source from button in Builder, that can be found at the bottom left of the screen.
Choosing connection to add tileset from
  1. Choose the BigQuery connection from where your tileset is accessible.
  2. Browse your projects and datasets until you find your tileset in the data explorer tree.
Choosing tileset to add as layer
  1. Select your tileset. Your tileset will then be added as a layer.
Tileset added as layer
  1. Style your tileset like any other layer in Builder. For more details on how to style your layers, please visit this page.
Tileset added as layer and styled

From the CARTO Dashboard

After connecting your CARTO account to BigQuery, a new Your Tilesets tab will appear in the Data section of your Dashboard. This new tab shows the tilesets available to your account in a specific BigQuery project and dataset and some useful metadata.

Your Tilesets

Click in one of the tilesets to visualize it using Map Viewer.

Tileset Viewer

Map Viewer uses CARTO for deck.gl’s declarative styling language, which makes it easier to create data-driven visualizations.

Creating color ramps for data-driven visualizations is straight-forward, using helper functions for different types of classifications. Take a look at the documentation for more information.

Let’s create a binned ramp visualization with the colorBins() helper function:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
"getFillColor": {
  "@@function": "colorBins",
  "attr": "total_pop",
  "domain": [
    729,
    937,
    1154,
    1394,
    1712,
    2235
  ],
  "colors": "Emrld"
}
  • attr: name of the data attribute in your tileset.
  • domain: manual classification breaks. Click on Open TileJSON and find the quantiles section, which gives you the breaks for different quantile classifications.
  • colors: Use an array of RGBA colors [ [r, g, b, [a]] ] , or just pick a CARTOcolors ramp and use its name.

Tileset Viewer II

Using Google Maps as a basemap is also possible with this tool. Add "google": true in the Map Style section, or just use the basemap selector in the top right corner of the screen:

Tileset Viewer III

Sharing a visualization

Tilesets can be used as data layers with many web mapping libraries. Take a look at the Development tools section in our documentation to learn about different options.

For quick sharing and publishing on the web, use the options from the Share menu in Map Viewer.

Clicking on Publish will grant permission in BigQuery to the CARTO Maps API service account, so it can directly fetch and serve the map tiles. Use the Unpublish toggle to revoke the access.

Tileset Viewer sharing menu

Copy the link or the embed code to share or publish the visualization.

Map Viewer

Map Viewer is a new tool for visualizing tilesets directly from the Dashboard. To open Map Viewer, just click on a tileset from ‘Your Tilesets’ tab in the ‘Data’ section of the Dashboard.

Map Viewer provides some defaults to help you explore your data, but it’s possible to customize the cartography using the deck.gl styling language.

For this guide, we will use a tileset that contains every European river. It’s available in the CARTO Data Observatory public data project in BigQuery. To visualize it, just type the bqcartodemos in the project selector and find the tilesets dataset.

eurivers tileset

Map Style

By clicking on the icon on the left bar, the Map Style panel will appear, showing a text editor with a predefined style.

As mentioned before, Map Viewer uses deck.gl’s style language. For more detailed information about the language’s capabilities, see the complete reference here.

Map Style

Basemaps

Map Viewer offers different basemaps for your visualizations. Click on the icon in the top-left corner to show the basemap selector.

Positron, Dark Matter, and Voyager CARTO basemaps are optimized for data visualization and will make your data layer stand out.

You can also use Google Maps as basemaps, selecting Roads or Satellite

Map Style basemap selector

Basic styles

You can quickly change some of the properties, like getFillColor to modify the fill colors of points and polygons, or getLineColor for lines. The property expects a color defined as an [r,g,b,[a]] array. Simple visualization properties are detailed here.

Color ramps

Basic styles might be a good option for the most basic maps, but creating more sophisticated, data-driven visualizations is also possible using helper functions for three different types of visualization:

  • colorBins assigns different colors to different buckets in the data range. This is useful for a choropleth visualization where you need to define the breaks of the data and define a color palette.
  • colorCategories assigns colors to specific values. This is useful for visualizing categorical data.
  • colorContinuous assigns a blended color based on a linear interpolation of values. This is useful for different types of visualizations. This is the one we are going to use for our example.

Replace the getLineColor property with the following block to style the rivers depending on their bearing.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
"getLineColor": {
  "@@function": "colorContinuous",
  "attr": "bearing",
  "domain": [
    0,
    60,
    120,
    180,
    240,
    300,
    360
  ],
  "colors": "Earth"
}

Let’s explain a bit how this function works:

  • "attr": Select a property from your tileset that contains the values that you want to use for the styling. In this case, it’s bearing, which contains the direction in degrees relative to the North in which each river flows.
  • "domain": In this case, it contains the values that will be used for the interpolation.
  • "colors": This value can be an array of [r,g,b,[a]] colors that will be mapped with the values in the domain. The intermediate colors will be assigned based on the linear interpolation of values. Another option is using a CARTOcolors palette, by just indicating its name.

Take a look at the result. Can you appreciate how the hydrographic basins stand out, just by assigning different colors to each river’s bearing?

Map Style ramp

Open TileJSON

At the bottom of the Map Style section, you will find an Open TileJSON button. It will open a new tab with a TileJSON request. The response contains a description of the tileset in TileJSON format, with metadata about:

  • The URL pattern to retrieve the tiles.
  • Zoom range, bounds, and center of the features in the tileset.
  • Information about the layers contained in the tileset.
  • Tilestats with statistic information such as maximum, minimum, average, count, and sum. It also includes a section called quantiles that contains the quantile breaks for the properties included, as well as the top 10 categories and their frequency.
Copy XYZ URL

Also at the bottom of the Map Style section there is a Copy XYZ URL that copies directly in your clipboard the tiles URL following the XYZ convention:

This is most useful for loading the tileset with any web-mapping library or desktop application, like QGIS.

Share

The Share section allows the publishing of a tileset. By publishing, we grant CARTO BigQuery Data Viewer (bigquery/dataViewer) permissions to the associated tileset. By doing so, this map becomes public on the web, and anybody with the URL will be able to see it.

By unpublishing the tileset, we will revoke the permission mentioned above and disable the sharing links.

Once published, you will find different options to publish the map URL on social networks, as well as the HTML code to embed the map on a website.

CLI tool

We provide a Python Command Line tool called carto-bq-tiler. Think of it as a supplement to the bq command-line tool provided by Google, but with some specific functionality to work with Tilesets. It allows you to:

  • Create, list, delete, and manage Tilesets in your BigQuery project
  • Visualize privately, using your authentication, your Tilesets
  • Upload Tilesets generated using other tools in MBTiles format
  • Download a tileset from BigQuery into a set of vector files or an MBTiles file to host your Tilesets somewhere else
Installation

You need to have the Google bq command-line tool already installed and working. So check if this command works for you:

1
bq query "SELECT 1"

If you get something like this:

Working CLI

you are good to go and you should install carto-bq-tiler like this:

1
pip3 install carto-bq-tiler

Finally, to check that the tool is working just type:

1
carto-bq-tiler --help
Authentication

carto-bq-tiler uses the credentials created by the bq command-line tool, and it will use the default project configured for it. If you want to use another project you can use the -p (--project) option, for example for listing the tilests:

1
carto-bq-tiler -p PROJECT list

Also, if you have a service account JSON file you can use it instead with -c (--credentials):

1
carto-bq-tiler -c CREDENTIALS_JSON_PATH list
List your tilesets

List the Tilesets in your Google Cloud project with:

1
carto-bq-tiler list
Upload a tileset

You can upload MBTiles files that contain tiles in MVT format. The only constraint is that the features must have an id integer property.

1
carto-bq-tiler load MBTILES_PATH TILESET_NAME

TILESET_NAME is the tileset destination in BigQuery, and it’s composed by the dataset and the table as dataset.table.

Delete a tileset

You can simply delete a dataset from BigQuery with:

1
carto-bq-tileset remove TILESET_NAME
Export a tileset

Tilesets can be exported to your computer in two formats:

MBTiles files:

1
carto-bq-tiler export-mbtiles TILESET_NAME

Directory tree:

1
carto-bq-tiler export-tiles TILESET_NAME
View a tileset

Tilesets can be viewed and explored in multiple ways:

A downloaded directory tree:

1
carto-bq-tiler view-local TILESET_DIRECTORY

A tileset in BigQuery:

1
carto-bq-tiler view TILESET_NAME

A tileset in BigQuery in comparative mode:

1
carto-bq-tiler view TILESET_NAME -c

An empty viewer:

1
carto-bq-tiler view -e

You can also modify the port of the viewer with the --port option.