Spatial Extension for BigQuery

Guides

Quickstart

As a CARTO Spatial Extension 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 has access to the Tiler, try running this query:

1
2

SELECT bqcarto.tiler.VERSION()
--Use bqcartoeu.tiler.VERSION() if your BigQuery account is in GCP's EU multi-region

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

The Tiler works processing data directly in BigQuery and creating a table that contains the whole tileset. Each individual tile is stored as a row in this table, with the tile coordinates and the encoded MVT stored in different columns:

Visualizing the tileset is straight-forward with Map Viewer: a new tool available from the Dashboard to visualize and publish your tileset maps.

The integration of tilesets with custom web map applications is also possible with CARTO Maps API, which will connect to BigQuery using your connection’s Service Account credentials to fetch and serve the tiles in a standard format, so they can be used with any webmapping library or desktop GIS application.

Creating a tileset

Once you are all set getting access to the tiler, the first step is to create a tileset using one of the procedures available, depending on the nature of the original data, and the type of tileset to be created:

1. tiler.CREATE_SIMPLE_TILESET

   • Use this procedure if you have a dataset with any geography type (point, line or polygon) and you want to see it at an appropriate zoom level.
   • The geographies will be represented exactly as stored in BigQuery, which means that if they are too small to be visible at a certain zoom level they won’t be included in the tiles at that zoom level.
   • The values associated with each feature are exactly the same as the ones available in the source dataset.

2. tiler.CREATE_POINT_AGGREGATION_TILESET

   • Use this procedure if you have a point dataset (or anything that can be converted to points, such as polygon centroids) and you want to see it aggregated.
   • The points will be aggregated into cells. Each feature or cell represents all the points that fall under it, so the associated properties available for visualization are generated by aggregating the values in the source dataset.
   • Values of individual points are available using single_point_properties which will only be included when a cell includes only one point. Remember that you could also get similar values with the aggregated properties using functions like ANY_VALUE or FIRST_VALUE.

Take a look at the examples and the complete reference if you need help with the SQL query specifics.

Creating a tileset is as easy as opening your BigQuery console and running the query there. In this case, we are going to create a tileset 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 bqcarto.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"
       }
  }'''
);

Visualizing a tileset

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.

Click in one the tilesets to visualize it using Map 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-drive 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

      "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.

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 on the top right corner of the screen:

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, to it can directly fetch and serve the map tiles. Use the Unpublish toggle to revoke the access.

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