Analytics Toolbox for BigQuery

Analytics Toolbox for BigQuery

Creating simple tilesets

COVID-19 vaccination progress in the USA (points)

In this example we are creating a tileset in which every inhabitant in the US is represented by means of a point. Each point is tagged with a vaccinated (blue) or non-vaccinated (purple) tag. This visualization enables us to depict at a glance which parts of the country are progressing better with the vaccination rollout.

The query used to produce the tileset is the following:

1
2
3
4
5
CALL `carto-un`.tiler.CREATE_TILESET(
    "cartobq.maps.covid19_vaccinated_usa_blockgroups",
    "`cartobq.maps.covid19_vaccination_usa_tileset`",
    null
)

Check out this blogpost to learn how we created this dataset and this visualization using the Analytics Toolbox and a custom application using CARTO for React.

United States roads by type (lines)

In this example we use a BigQuery public dataset from the United States Census Bureau to visualize all of the national roads in the US. The visualization is styled by the RTTYP route type code.

This dataset can be produced in a very straightforward manner by executing the next procedure:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
CALL `carto-un`.tiler.CREATE_TILESET(
    R'''
    (   SELECT road_geom AS geom, route_type
        FROM `bigquery-public-data.geo_us_roads.us_national_roads`
        WHERE route_type IS NOT NULL
    )
    ''',
    "`cartobq.maps.usa_roads_tileset`",
    null
);

NYC urban growth (polygons)

This example shows in a very effective manner the historical growth of New York City by means of the year of construction of its more than 800K buildings. The dataset has been obtained from the MapPLUTO repository of the NYC Department of City planning.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
CALL `carto-un`.tiler.CREATE_TILESET(
    R'''
    (   SELECT geometry AS geom, YearBuilt 
        FROM cartobq.maps.pluto_nyc 
        WHERE YearBuilt > 0
    )
    ''',
    "`cartobq.maps.nyc_footprints_tileset`",
    null
);

The visualization represents older buildings with lighter footprints and more recent ones with darker footprints.

NYC footprints

Checkout this blogpost to learn more about this visualization.

World’s road network (lines)

We are going to use a dataset from CARTO’s public Data Observatory to visualize the world’s road network.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
CALL `carto-un`.tiler.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'''
  {
      "zoom_min": 0,
      "zoom_max": 10,
      "max_tile_size_kb": 3072,
      "properties":{
          "type": "String"
       }
  }'''
);

The result is a worldwide map with the requested tiles, including the type of each road.

Natural Earth Roads

US block groups (polygons)

We are going to use a dataset from CARTO’s public Data Observatory to visualize the block groups of the US including its population.

 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"
       }
  }'''
);

Checkout the result:

US Blockgroup population

Zoom-dependant tileset for USA administrative units

You can create a tileset that uses different data sources depending on the zoom level. In this example, we are making use of the Data Observatory’s public datasets offering to create a visualization of the different administrative units in the US: the higher the zoom level, the higher the granularity of the administrative unit being shown.

 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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
CALL `carto-un`.tiler.CREATE_SIMPLE_TILESET(
R'''(
    SELECT
    14 as zoom_min,
    15 as zoom_max,
    geoid,
    geom
    FROM `carto-do-public-data.carto.geography_usa_block_2019`
    UNION ALL
    SELECT
    13 as zoom_min,
    13 as zoom_max,
    geoid,
    geom
    FROM `carto-do-public-data.carto.geography_usa_blockgroup_2019`
    UNION ALL
    SELECT
    12 as zoom_min,
    12 as zoom_max,
    geoid,
    geom
    FROM `carto-do-public-data.carto.geography_usa_censustract_2019`
    UNION ALL
    SELECT
    10 as zoom_min,
    11 as zoom_max,
    geoid,
    geom
    FROM `carto-do-public-data.carto.geography_usa_zcta5_2019`
    UNION ALL
    SELECT
    6 as zoom_min,
    9 as zoom_max,
    geoid,
    geom
    FROM `carto-do-public-data.carto.geography_usa_county_2019`
    UNION ALL
    SELECT
    0 as zoom_min,
    5 as zoom_max,
    geoid,
    geom
    FROM `carto-do-public-data.carto.geography_usa_state_2019`
) _a''',
R'''`bqcartodemos.tilesets.usa_acs_multisource_example`''',
'''
    {
        "zoom_min": 0,
        "zoom_max": 15,
        "zoom_min_column": "zoom_min",
        "zoom_max_column": "zoom_max",
        "max_tile_size_kb": 2048,
        "skip_validation" : true,
        "properties":
        {
            "geoid": "String"
        }
    }
''');