Javier de la Torre Chad W. Jennings

and

How Google Cloud & CARTO power geospatial analysis at scale

As the volume of data with a location component continues to grow exponentially, both geospatial analysts and data scientists are looking to scale their analytical workflows. Accessing massive spatial datasets securely and seamlessly running analysis on this data are key to driving more efficient business decisions.

Almost all data analytics workflows can be broken down into four primary components: Ingestion, Storage, Compute and Visualization. With the support of geospatial datatypes and functions in BigQuery, Google unblocked geospatial workloads on the first three components.

CARTO’s BigQuery Tiler completes that effort by allowing visualization over datasets at scale. CARTO’s BigQuery Tiler is now available in General Availability as part of the CARTO Spatial Extension for BigQuery. The extension enhances the geospatial capabilities already supported by BigQuery using simple SQL.

Together CARTO and BigQuery support the components of an analytics workflow at scales impossible on any other system.

  1. Ingestion & Streaming: Users can stream geospatial datasets directly into a BigQuery table at speeds up to 10GB/sec per table using any number of geospatial data formats, such as shapefiles, KML, GeoJSON, etc. For batch loads, customers routinely load in excess of petabytes per day.
  2. Storage: BigQuery's storage engine can automatically scale to support petabytes or exabytes of customer data containing spatial elements.
  3. Compute: BigQuery's native support for geospatial data types and ST_* functions allow rapid queries over terabytes or petabytes of stored data.
  4. Visualization: CARTO’s BigQuery Tiler releases geospatial visualization from the memory limitations of client-side applications. Users can now interactively visualize and analyze massive datasets right inside BigQuery.

Using BigQuery Tiler, the Intermx Data Enablement Platform® can eliminate complex batch data updates and reduce user downtime. Being able to rapidly process and analyze large population datasets empowers our customers through a fuller and more cohesive user experience.
Matthew Martimo, Founder at Intermx.

Analyzing Global Internet Connectivity

To demonstrate how BigQuery can support key stages in the geospatial workflow and also the visualization capabilities of BigQuery Tiler, we have sourced a very large, openly available dataset of global fixed broadband network performance data from Ookla®.

Ookla’s mission is to make the internet better, faster and more accessible for everyone. Their Ookla for Good™ initiative provides data, analysis and content to organizations that are seeking to improve people’s lives through internet accessibility. Hundreds of millions of people worldwide use their Speedtest® platform.


A noticeable difference in connectivity in rural areas vs. urban area across the US

How to visualize this data with BigQuery Tiler

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

The Tiler works by processing data directly in BigQuery and creating a table that contains the complete 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 a tileset is straight-forward with a Map Viewer, which will connect to BigQuery using your Service Account credentials in order to fetch and serve the tiles.

The CARTO API serves maps in a TileJSON standard format, so they can be easily integrated with any web mapping library or desktop GIS application.


Internet speed comparisons across Western Europe

Creating the tileset from Ookla data

The source Ookla data is aggregated into quadkeys for zoom level 16 and contains more than 7 million points. This means that we’ll need to aggregate this data to be represented in lower zoom levels.

For that, we’ll use the tiler.CREATE_POINT_AGGREGATION_TILESET procedure.

Take a look at these examples and the complete reference to get an idea of the different options available.

Creating a tileset is as easy as opening your BigQuery console and running the query there. Users have permission to call the Tiler’s functions with their Google account once they have:

  1. Signed up in CARTO, using a Google account.
  2. Added a new BigQuery connection from your CARTO Dashboard.
CALL cartobq.tiler.CreatePointAggregationTileset(
  R'''
(
  SELECT ST_CENTROID(geom) as geom, avg_d_kbps FROM `carto-project.ookla.performance_fixed_2020q1_with_geom`
) _input
  ''',
  R'''`cartodb-on-gcp-pm-team.demo.ookla_r8_z16_avg_d_speed`''',
  R'''
  {
      "zoom_min": 0,
      "zoom_max": 16,
      "max_tile_size_kb": 1024,
      "type":"quadkey",
      "placement":"features-any",
      "resolution":8,
      "properties":{
          "agg_avg_d_kbps": {
            "formula": "avg(avg_d_kbps)",
            "type":"Number"
            }
       },
       "single_point_properties":{
           "avg_d_kbps": "Number"
        }
  }'''
);
 
 
`cartodb-on-gcp-pm-team.demo.ookla_r8_z16_avg_d_speed` 

By running the query above, a new table is created, containing our map’s tiles and the average internet download speed from the Ookla data that we’ll use to style the map.

Visualizing the Ookla data as a tileset

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

Click on one of the tilesets to access the Map Viewer: a new tool to visualize and publish your tileset maps.

Screenshot of the Map Viewer


Map Viewer uses CARTO for deck.gl’s style language, which makes it easier to create data-driven visualizations. Take a look at the documentation to start using it.

In addition, creating color ramps for data-driven visualizations is straight forward, using helper functions for different types of classification.

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


Thailand’s internet connectivity is much faster than neighboring countries

Sharing map visualizations

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, we can use the options from the Share menu in the 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 this access.

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

Why try BigQuery Tiler?

BigQuery Tiler is a powerful tool that can be used to bring massive enterprise datasets to life and enhance our spatial understanding of complex analytical use cases, such as cellular network deployment, IoT Analytics, or mobility analysis, through stunning map visualizations. To find out more about our Location Intelligence solution, contact hello@carto.com.


Concentration of populations and the demographic deserts of the Middle East

Want to discover more?

Click for more info on BigQuery Tiler
About the author
Javier de la Torre

Javier de la Torre is founder and Chief Strategy Officer of CARTO. One of the pioneers of location intelligence, Javier founded the company with a vision to democratize data analysis and visualization. Under his leadership, CARTO has grown from a groundbreaking idea into one of the fastest growing geospatial companies in the world.
In 2007, he founded Vizzuality, a renowned geospatial company dedicated to bridging the gap between science and policy making by the better use of data.

More posts from Javier de la Torre
About the author
Chad W. Jennings

Product Manager Data Analytics and Geospatial Lead, Google Cloud

More posts from Chad W. Jennings

Related Posts

Ready to optimize your territories with Location Intelligence?

Close circle icon

Contact us

Please fill out the below form and we'll be in touch real soon.