Creating simple tilesets

We provide a set of examples that showcase how to easily create simple tilesets allowing you to process and visualize very large spatial datasets stored in BigQuery. You should use it if you have a dataset with any geography type (point, line, or polygon) and you want to visualize it at an appropriate zoom level.

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:

carto-un

CALL `carto-un`.carto.CREATE_TILESET(
    "cartobq.maps.covid19_vaccinated_usa_blockgroups",
    "`cartobq.maps.covid19_vaccination_usa_tileset`",
    null
);

carto-un-eu

CALL `carto-un-eu`.carto.CREATE_TILESET(
    "cartobq.maps.covid19_vaccinated_usa_blockgroups",
    "`cartobq.maps.covid19_vaccination_usa_tileset`",
    null
);

manual

CALL carto.CREATE_TILESET(
    "cartobq.maps.covid19_vaccinated_usa_blockgroups",
    "`cartobq.maps.covid19_vaccination_usa_tileset`",
    null
);

The CREATE_TILESET procedure implements smart memory management techniques that sample the data when needed in order to avoid hitting BigQuery’s memory limits.

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:

carto-un

CALL `carto-un`.carto.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
);

carto-un-eu

CALL `carto-un-eu`.carto.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
);

manual

CALL carto.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.

carto-un

CALL `carto-un`.carto.CREATE_TILESET(
    R'''
    (   SELECT geometry AS geom, YearBuilt
        FROM cartobq.maps.pluto_nyc
        WHERE YearBuilt > 0
    )
    ''',
    "`cartobq.maps.nyc_footprints_tileset`",
    null
);

carto-un-eu

CALL `carto-un-eu`.carto.CREATE_TILESET(
    R'''
    (   SELECT geometry AS geom, YearBuilt
        FROM cartobq.maps.pluto_nyc
        WHERE YearBuilt > 0
    )
    ''',
    "`cartobq.maps.nyc_footprints_tileset`",
    null
);

manual

CALL carto.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.

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.

WARNING

This example uses the CREATE_SIMPLE_TILESET procedure. We strongly recommend to use CREATE_TILESET instead. Learn more here about the difference between the two procedures.

carto-un

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'''
  {
      "zoom_min": 0,
      "zoom_max": 10,
      "max_tile_size_kb": 3072,
      "properties":{
          "type": "String"
       }
  }'''
);

carto-un-eu

CALL `carto-un-eu`.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'''
  {
      "zoom_min": 0,
      "zoom_max": 10,
      "max_tile_size_kb": 3072,
      "properties":{
          "type": "String"
       }
  }'''
);

manual

CALL 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'''
  {
      "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.

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.

This example uses the CREATE_SIMPLE_TILESET procedure. We strongly recommend to use CREATE_TILESET instead. Learn more here about the difference between the two procedures.

carto-un

CALL `carto-un`.carto.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"
       }
  }'''
);

carto-un-eu

CALL `carto-un-eu`.carto.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"
       }
  }'''
);

manual

CALL carto.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:

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.

This example uses the CREATE_SIMPLE_TILESET procedure. We strongly recommend to use CREATE_TILESET instead. Learn more here about the difference between the two procedures.

carto-un

CALL `carto-un`.carto.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"
       }
  }'''
);

carto-un-eu

CALL `carto-un-eu`.carto.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"
       }
  }'''
);

manual

CALL `carto.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"
       }
  }'''
);

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