Tilesets

OSM buildings (aggregation)

We want are going to create a Point Aggregation Tileset to visualise all the features tagged as ‘building' in the OSM BigQuery Dataset. Since this dataset has different types of geometries for the buildings, we are going to use ST_CENTROID to ensure they all are points.

The extra column, aggregated_total, is adding a count of the number of buildings that are aggregated into a cell, which in this case are quadkeys made of z + resolution tiles, that is, each tile will be subdivided into 4^7 (16384) cells.

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

This process will take the over 300M buildings in the source table, aggregate them into cells and generate a table containing more than 4M tiles around the world.

New York City trees (aggregation)

In this case, we want to visualize an aggregation of the tree census of NYC. Since the table doesn’t have a geography column, we are going to create it on the fly using the latitude and longitude columns.

We also want to have access to the status and health of each aggregated cell, so we add some extra properties around that. Finally, as it is a more localized dataset, we want to generate higher zoom levels (16) and when we see individual points we want access to both their official id and their address.

 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
CALL bqcarto.tiler.CREATE_POINT_AGGREGATION_TILESET(
  R'''(
        SELECT
            ST_GEOGPOINT(longitude, latitude) as geom,
            status, health, tree_id, address
        FROM `bigquery-public-data.new_york_trees.tree_census_2015`
    )''',
  '`your-project.your-dataset.test_tilesets.nyc_trees_16_7`',
  R'''
    {
      "zoom_max": 16,
      "aggregation_type": "quadkey",
      "aggregation_resolution": 7,
      "aggregation_placement": "features-centroid",
      "properties":{
        "aggregated_total": {
          "formula": "count(status)",
          "type": "Number"
        },
        "alive_total": {
          "formula": "countif(status = 'Alive')",
          "type": "Number"
        },
        "ok_health": {
          "formula": "countif(health = 'Good' OR health = 'Fair')",
          "type": "Number"        
        }
      },
      "single_point_properties": {
           "tree_id": "Number",
           "address": "String"
      }
    }
  ''');

Then we can style our visualization using the properties that we have added:

NYC happy trees example

2020 world population (aggregation)

For this example, we are going to use a dataset from CARTO’s public Data Observatory to visualize the 2020 world population. We are going to use the already aggregated 1km * 1km grid cells:

 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_POINT_AGGREGATION_TILESET(
  R'''(
        SELECT ST_Centroid(b.geom) as geom, population
        FROM
          `carto-do-public-data.worldpop.demographics_population_glo_grid1km_v1_yearly_2020` a
        INNER JOIN
          `carto-do-public-data.worldpop.geography_glo_grid1km_v1` b
        ON (a.geoid = b.geoid)
    )''',
  '`your-project.your-dataset.wpop_2020_1km_cell`',
  R'''
    {
      "zoom_max": 6,
      "aggregation_type": "quadkey",
      "aggregation_resolution": 7,
      "aggregation_placement": "cell",
      "properties":{
        "population": {
          "formula": "sum(population)",
          "type": "Number"
        }
      }
    }
  ''');

Note that since this dataset contains already aggregated data, it doesn’t make sense to visualize it at very high zoom levels, but visualize the data at a country scale.

2020 worldpop

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. We are going to use the already aggregated 1km * 1km grids cells:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
CALL bqcarto.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 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"
       }
  }'''
);

Check 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 is 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 bqcarto.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"
        }
    }
''');