Platforms which deal with spatial data - like CARTO - are able to translate encoded location data into a geographic location on a map, allowing you to visualize and analyze data based on location. This includes mapping where something is, and the space it occupies.

There are two main ways that "location" is encoded.

1. Geographic Coordinates (Geography): Geographic coordinates, also known as geographic or unprojected coordinates, use latitude and longitude to specify a location on the Earth's curved surface. Geographic coordinates are based on a spherical or ellipsoidal model of the Earth and provide a global reference system.

2. Projected Coordinates (Geometry): Projected coordinates, also referred to as geometriesy or projected coordinates, utilize a two-dimensional Cartesian coordinate system to represent locations on a flat surface, such as a map or a plane. Projected coordinates result from applying a mathematical transformation to geographic coordinates, projecting them onto a flat surface. This projection aims to minimize distortion and provide accurate distance, direction, and area measurements within a specific geographic region or map projection.

The choice between geographic or projected coordinates depends on the purpose and scale of the analysis. Geographic coordinates are commonly used for global or large-scale analysis, while projected coordinates are more suitable for local or regional analysis where accurate distance, area, and shape measurements are required. Furthermore, web mapping systems may often require your data to be a geography, as these systems often use a global, geographic coordinate system.

This section of CARTO Academy explores the essential foundations of handling spatial data in the modern geospatial tech stack.

Spatial data encompasses a wide range of information that is associated with geographical locations. This data can represent anything from points on a map to complex geographic features, and it plays a central role in a multitude of applications.

• What is location data?

• Types of location data

In this webinar we showcase how to leverage the ML Generate Text component in Workflows to optimize and help us understand the results of a spatial analysis.

In this webinar we showcase how to run scalable routing analysis directly inside your cloud data warehouse by building a workflow that leverages our support for calling external routing services with the Create Routes component.

Identify best billboards to target a specific audience

This workflow example computes an index in order to analyze what are the best billboards to target a specific audience, then it filters the top 100 best billboards.

Download example

In the past few years, geospatial technology has fundamentally changed. Data is getting bigger, faster, and more complex. User needs are changing too, with an increasing number of organizations and business functions adopting data-centric decision making, leading to a broader range of users undertaking this kind of work. Geospatial cannot any longer be left on a silo.

In this rapidly evolving landscape, the traditional desktop-based Geographic Information Systems (GIS) of the past have given way to a new way of doing spatial analysis, focused on openness and scalability over proprietary software and desktop analytics. This new way of working with geospatial data is supported by a suite of cloud-native tools and technologies designed to handle the demands of contemporary data workflows - this is what we call the modern geospatial analysis stack.

This shift to more open and scalable geospatial technology offers a range of benefits for analysts, data scientists and the organizations they work for:

• Interoperability between different data analysis teams working on a single source of truth database in the cloud.

• Scalability to analyze and visualize very large datasets.

• Data security backed by the leading cloud platforms.

• Democratization & Collaboration with tools that have been esigned to lower the skills barrier for spatial analysis.

However, while the modern geospatial analysis stack excels in offering scalable and advanced analytical and visualization capabilities for your geospatial big data, there are some data management tasks - like geometry editing over georeferenced images - for which traditional open-source desktop GIS tools are great solutions for.

This section of the CARTO Academy will share how you can complement your modern geospatial analysis stack - based on CARTO and your cloud data warehouse of choice - with other GIS tools to ensure all your geospatial needs and use-cases are covered, from geometry editing to advanced spatial analytics and app development.

Enhance your sharing and collaborating skills with Builder through our detailed guides. Each tutorial, equipped with demo data from the CARTO Data Warehouse, showcases how Builder facilitates the sharing and collaboration of insights, ensuring ease of understanding and effective communication in your maps.

Explore a range of tutorials in this section, each designed to guide you through solving various geospatial use-cases with Builder and the wider CARTO Platform. These tutorials leverage available demo data from the CARTO Data Warehouse connection, enabling you to dive straight into map creation right from the start.

In this webinar we leverage Spatial Indexes along with human mobility and spend data to optimize locations for OOH billboards in a low-code environment thanks to CARTO Workflows. While this example focuses on OOH, the approach could be utilized in other sectors such as CPG, retail and telecoms.

Estimate population around top performant retail stores

This example demonstrates how to use workflow to filter out the top retail stores that belong to a specific category and computes the population living around them.

Commercial Hotspot Analysis. Identifying Optimal Locations for a New Pizza Place

Identifying an optimal location for a new store is not always an easy task, and we often do not have enough data at our disposal to build a solid model to predict potential revenues across an entire territory. In these cases, managers rely on different business criteria in order to make a sound decision for their expansion strategy. For example, they rely on defining their target market and segmenting population groups accordingly in order to locate the store closer to where the target market lives (e.g. areas with a great presence of youngsters).

In this example, we are going to use the component to explore good locations to open a new Pizza Hut restaurant in Honolulu, Hawaii. We will use H3 as our geographic support and population and distance to existing Pizza Hut stores as our criteria to identify hotspots. For a detailed description of this use case read .

In this webinar we showcase how to implement with workflows geomarketing techniques to help businesses target sportsfans & sportswear consumers.

CARTO's is a set of UDFs and Stored Procedures to unlock Spatial Analytics. It is organized in a set of modules based on the functionality they offer. Visit the to see the full list of available modules and functions. In order to get access to the Analytics Toolbox functionality in your BigQuery please read about the different in our documentation.

CARTO AI Agents provide a powerful conversational interface that allows anyone, regardless of technical expertise, to ask questions in natural language and receive instant, actionable insights. This marks a fundamental shift beyond dashboards to a dynamic, intuitive way of exploring your geospatial data.

You can create agents directly in Builder and link them to your maps. This transforms static maps into interactive experiences where end-users can ask questions, explore data, and extract insights through conversation.

What is an AI Agent?

An AI Agent is a system powered by a large language model (LLM) that can interact with your data and tools to answer questions. Unlike a simple chatbot, agents can decide which tools to use, analyze results, and take multiple steps to solve a problem.

Agents have three components:

• Instructions: Define what the agent does, what it knows, and how it should behave. This is where you specify its purpose and expertise.

• Tools: The agent can access CARTO's built-in geospatial tools and your custom MCP tools for connecting to other systems.

• Model: The LLM that powers the agent's ability to understand questions and decide which tools to use.

Enable AI Agents in your organization

AI Agents are disabled by default. To enable them, navigate to Settings > CARTO AI and toggle Enable CARTO AI to enable them for your whole organization. Once enabled, any Editor user can create AI Agents in Builder maps.

Set up an AI Agent in Builder

Once AI Agents are available in your organization, you can start the creation of Agents directly in Builder. To start, create a new map or open an existing one. Then navigate to the AI Agents tab on the left pane and click Create agent. This will open the agent configuration dialog.

The Use Case field is required. Use it to explain what the map is for and what questions users will ask. For example, "This map shows retail locations. Users want to find stores near them and compare performance across regions." This helps the agent deliver relevant, accurate answers. Once you've filled in the Use Case, you're ready to test your agent. Everything else is optional.

You can also add custom instructions for more specific guidance. This is optional but recommended. Use it to add domain knowledge, define response style, or set boundaries on what the agent should and shouldn't do.

To enhance the user experience, you can set a welcome message that greets users when they open the agent, and add conversation starters—preset questions users can click to begin interacting with the agent.

Once you're ready, click Create Agent. This makes it available to Editors in your organization. To make it available for Viewers, toggle the setting in Map settings for viewers on the top banner. This will also make the agent available to anyone with the map link if the map is public.

So, you've decided to start scaling your analysis using Spatial Indexes - great! When using these grid systems, some common spatial processing tasks require a slightly different approach to when using geometries.

To help you get started, we've created a reference guide below for how you can use Spatial Indexes to complete common geoprocessing tasks - from buffers to clips. Once you're up and running, you'll be amazed at how much more quickly - and cheaply - these operations can run! Remember - you can always revert back to geometries if needed.

All of these tasks are undertaken with CARTO Workflows - our low-code tool for automating spatial analyses. Find more tutorials on using Workflows here.

Buffer

The humble buffer is one of the most basic - but most useful - forms of spatial analysis. It's used to create a fixed-distance ring around an input feature.

• With geometries... use the ST Buffer tool.

• With Spatial Indexes... convert the input geometry to a Spatial Index, then use a H3/Quadbin K-Ring component to approximate a buffer. Lookup H3 resolutions here and Quadbin resolutions here to work out the K-Ring size needed.

Clip/intersect

Where does geometry A overlap with geometry B? It’s one of the most common spatial tasks, but heavy geometries can make this straightforward task a pain.

• With geometries... use the ST Intersection tool. This may look like a simple process, but it can be incredibly computationally expensive.

• With Spatial Indexes... convert both input geometries to a Spatial Index, then use a Join (inner) to keep only cells which can be found in both inputs.

Difference

For a “difference” process, we want the result to be the opposite of the previous intersection, retaining all areas which do not intersect.

• With geometries... use the ST Difference tool. Again, while this may look straightforward, it can be slow and computationally expensive.

• With Spatial Indexes... again convert both input geometries to a Spatial Index, this time using a full outer Join. A Where component can then be used to filter only "different" cells (where h3 IS null AND h3_joined IS not null) - at a fraction of the calculation size.

Spatial Join

Spatial Joins are the "bread and butter" of spatial analysis. They can be used to answer questions like "how many people live within a 10-minute drive of store X?" or "what is the total property value in this flooded area?"

Our Analytics Toolbox provides a series of Enrichment tools which make these types of analyses easy. Enrichment tools for both geometries and Spatial Indexes are available - but we've estimated the latter of these are up to 98% faster!

• With geometries... use the Enrich Polygons component.

• With Spatial Indexes... use the Enrich H3 / Quadbin Grid component.

Check out the full guide to enriching Spatial Indexes here.

Aggregate within a distance

Say you wanted to know the population within 30 miles of

For instance, in the example below we want to create a new column holding the number of stores in a 1km radius.

• With Geometries... create a Buffer, run a Spatial Join and then use Group by to aggregate the results.

• With Spatial Indexes... have the inputs stored as a H3 grid with both the source and target features in the same table. Like in the earlier Buffer example, use the H3 K-Ring component to create your "search area." Now, you can use the Group by component - grouping by the newly created H3 K-Ring ID - to sum the number of stores within the search area.

This is a fairly simple example, but let's imagine something more complex - say you wanted to calculate the population within 30 miles of a series of input features. Creating and enriching buffers of this size - particularly when you have tens of thousands of inputs - will be incredibly slow, particularly when your input data is very detailed. This type of calculation could take hours - or even days - without Spatial Indexes.

Next up...

In this section you can find a set of tutorials with step-by-step instructions on how to solve a series of geospatial use-cases with the help of Agentic GIS.

Create a classification model

This example shows how to create a pipeline to train a classification model using Snowflake ML, evaluate the model and use it for prediction. In particular, we will create a classification model to estimate customer churn for a telecom company in California.

This example workflow will help you see how telco companies can detect high-risk customers, uncover the reasons behind customer departures, and develop targeted strategies to boost retention and satisfaction by training a classification model.

Download this example

Create a forecasting model

This template shows how to create a forecast model using Snowflake ML through the extension package for Workflows. There are three main stages involved:

• Training a model, using some input data and adjusting to the desired parameters,

• Evaluating and understanding the model and its performance,

• Forecasting and saving the results.

Download this example

When you begin a new map in CARTO Builder, the left panel is your starting point, providing the tools to add data sources that will be visualized as layers on your map. In Builder, each data source creates a direct connection to your data warehouse, allowing you to access your data without the need to move or copy it. This cloud-native approach ensures efficient and seamless integration of large datasets.

Once a data source is added, CARTO's advanced technology renders a map layer that visually represents your data, offering smooth and scalable visualization, even with extensive datasets.

In this section, we'll take you through the various data source formats that CARTO Builder supports. We'll also explore the different types of map layers that can be rendered in Builder, enhancing your understanding of how to effectively visualize and interact with your geospatial data.

Data sources

Builder data sources can be differentiated in the following geospatial data types:

• Simple features: These are unaggregated features using standard geometry (point, line or polygon) and attributes, ready for use in Builder. These spatial and non-spatial attributes are ready to be used in Builder.

• Aggregated features based on Spatial Indexes: These data sources are aggregated for improved performance or specific use cases. The properties of these features are aggregated according to the chosen aggregation type in Builder. CARTO currently supports two different types of utilize a spatial indexes, Quadbin and H3.

• Pre-generated tilesets: These are tilesets that have been previously pre-generated using CARTO Analytics Toolbox procedure and stored directly in your data warehouse. Ideal for handling very large, static datasets, these tilesets ensure efficient and high-performance visualizations.

• Raster: Raster sources uploaded to your data warehouse using CARTO raster-loader, allowing both analytics and visualization capabilities.

Adding sources to Builder

In Builder, you can add data sources either as table sources, by connecting to a materialized table in your data warehouse, or through custom SQL queries. These queries execute directly in your data warehouse, fetching the necessary properties for your map.

Table sources

You can directly connect to your data warehouse table by navigating through the mini data explorer. Once your connection is set, the data source is added as a map layer to your map.

SQL query sources

You can perform a custom SQL query source that will act as your input source. Here you can select the precise columns for better performance and customize your analyses according to your need.

Map layers

Once a data source is added to Builder, a layer is automatically added for that data source. The spatial definition of the source linked to a layer specifies the layer visualization type and additional visualization and styling options. The different layer visualization types supported in Buider are:

• Point: Displays as point geometries. Point data can be dynamically aggregated to the following types: grid, h3, heatmap and cluster.

• Polygon: Displays as polygon geometries.

• Line: Displays as line geometries.

• H3: Displays features as hexagon cells.

• Grid: Displays features as grid cells.

• Raster: Displays data as grid of pixels.

In this section, you can explore our step-by-step guides designed to enhance your data analysis skills using Builder. Each tutorial features demo data from the CARTO Data Warehouse connection, allowing you to jump directly into creating and analyzing maps.

MCP Tools let you expose Workflows as tools that AI Agents can use. This means you can build custom geospatial operations in Workflows and make them available to any MCP-compliant agent.

For example, you could create a Workflow that finds optimal delivery routes, then expose it as an MCP Tool. An agent like Gemini CLI could then call that tool automatically when someone asks a routing question.

The CARTO MCP Server enables AI Agents to use geospatial tools built with Workflows. By exposing workflows as MCP Tools, GIS teams can empower agents to answer spatial questions with organization-specific logic. Each tool follows the MCP specification, including a description, input parameters, and output, making them accessible to any MCP-compliant agentic application.

How it works:

1. Build a Workflow that solves a specific problem

2. Configure it as an MCP Tool (add descriptions, inputs, and outputs)

3. Connect an agent to your CARTO MCP Server

4. The agent can now use your custom tools

Step 1: Create a Workflow

Each MCP Tool needs a Workflow behind it. Design workflows that solve the specific questions you want agents to answer. For detailed instructions on building Workflows as MCP Tools, see the .

Step 2: Create an API Access Token

The MCP Server uses API tokens for authentication.

1. In the CARTO Workspace, navigate to Developers > Credentials and create a new API Access Token

2. Under Allowed APIs, select the MCP Server permission

3. Copy the token and save it securely

You'll need this token to connect agents to your MCP Server.

Step 3: Connect an Agent

Once your workflow and token are ready, connect your agent to the CARTO MCP Server. Here's an example using Gemini CLI:

Best Practices

Write clear tool descriptions Explain what the tool does and when to use it. This helps agents choose the right tool for each question.

Define inputs precisely Use descriptive parameter names and types. Vague labels confuse agents.

Test workflows first Run workflows manually before exposing them as tools. Verify the outputs match what you expect.

Choose the right output mode Use Sync for quick queries. Use Async for long-running operations. Keep in mind that Async requires the agent to poll for status and fetch results when complete, which may need additional prompt engineering.

Keep tools updated When you modify a workflow, sync it promptly. Let users know if tool behavior changes.

Monitor usage Review how tools are used and check for errors. Use this to refine workflows or improve descriptions.

MCP Tools in CARTO are built from Workflows. Each tool you create defines how to solve a specific spatial problem, what inputs it needs, and what results it returns.

This guide shows you how to configure a Workflow as an MCP Tool.

Step 1: Create a Workflow

Build a Workflow that solves the specific problem you want agents to handle. For example, if you want agents to find nearby stores, create a workflow that performs that spatial query.

Step 2: Add an MCP Tool Output

Add the MCP Tool Output component to your workflow. This defines what the tool returns.

Choose the output mode:

• Sync: Returns results immediately (use for fast queries)

• Async: Returns results after processing completes (use for long-running operations)

Step 3: Configure the tool

Click the three-dot menu in the top-right corner and select the MCP Tool settings.

When the dialog opens up, write a clear description explaining what the tool does. Make sure to also. Define all input parameters with descriptive names and descriptions and enable the tool to make it available through the MCP Server.

Example description: "Finds the 5 nearest retail stores to a given location and returns their addresses and distances."

Step 4: Sync changes

When you update a workflow, click Sync to propagate changes to the MCP Tool. This ensures agents always use the latest version.

Step 5: Use your own tools

Once your workflow is configured as an MCP Tool, you can:

• Connect external agents (like Gemini CLI) to your CARTO MCP Server. See Using MCP Tools with CARTO.

• Give CARTO AI Agents access to the tool. See Adding MCP Tools to AI Agents.

Territory Balancing

In this template, we’ll explore how to optimize work distribution across teams by analyzing sales territory data to identify imbalances and redesign territories.

Focusing on a beverage brand in Milan, we’ll evenly assign Point of Sale (POS) locations to sales representatives by dividing a market (a geographic area) into a set of continuous territories. This ensures fair workloads, improves customer coverage, and boosts operational efficiency by aligning territories with demand.

Location Allocation - Maximize Coverage

Managing a modern telecom network requires balancing cost, coverage, and operational efficiency. Every network node—a set of cell towers—represents demand that must be effectively served by strategically placed facilities.

In this tutorial, we’ll explore how network planners can determine the optimal locations for Rapid Response Hubs, ensuring that each area of the network is monitored and maintained efficiently through Location Allocation. More specifically, we aim to maximize network coverage so that whenever an emergency occurs (i.e. outages, equipment failures, or natural disaster impacts), the nearest facility can quickly respond and restore service.

Location Allocation - Minimize Total Cost

Managing a modern telecom network requires balancing cost, coverage, and operational efficiency. Every network node—a set of cell towers—represents demand that must be effectively served by strategically placed facilities.

In this example, we’ll explore how network planners can determine the optimal locations for Maintenance Hubs, ensuring that each area of the network is monitored and maintained efficiently through Location Allocation. More specifically, we aim to minimize total operational costs for ongoing inspections and servicing, respecting resource capacities, and ensuring that routine maintenance is delivered cost-effectively. Our goal will be to expand our existing facilities by adding one selected site per county in Connecticut to serve rising network demand.

The CARTO Analytics Toolbox is a suite of functions and procedures to easily enhance the geospatial capabilities available in the different leading cloud data warehouses.

It is currently available for Google BigQuery, Snowflake, Redshift, Databricks and PostgreSQL.

The Analytics Toolbox contains more than 100 advanced spatial functions, grouped in different modules. For most data warehouses, a core set of functions are distributed as , while the most advanced functions (including vertical-specific modules such as retail) are distributed only to CARTO customers.

How does it work

The CARTO Analytics Toolbox is a set of SQL UDFs and Stored Procedures that run natively within each data warehouse, leveraging their computational power and scalability and avoiding the need for time consuming ETL processes.

The functions can be executed directly from the CARTO Workspace or in your cloud data warehouse console and APIs, using SQL commands.

Here’s an example of a query that returns the compact H3 cells for a given region, using Analytics Toolbox functions such as H3_POLYFILL() or H3_COMPACT() from our H3 module.

Check the for each data warehouse (listed below) for a complete SQL reference, guides, and examples as well as instructions in order to install the Analytics Toolbox in your data warehouse.

• How Spatial Indexes are supported in CARTO

• Spatial Indexes & our Analytics Toolbox

• Visualizing Spatial Indexes in CARTO Builder

How Spatial Indexes are supported in CARTO

As mentioned in the Introduction to Spatial Indexes, Spatial Indexes like H3 and Quadbin have their location encoded with a short reference string or number. CARTO is able to "read" that string as a geographic identifier, allowing Spatial Index features to be plotted on a map and used for spatial analysis.

Spatial Indexes & our Analytics Toolbox

CARTO's Analytics Toolbox is where you can find all of the tools and functions you need to turn data into insights - and Spatial indexes are an important part of this. Whether you are using CARTO Workflows for low-code analytics, or working directly with SQL, some of the most relevant modules include:

• H3 or Quadbin modules for creating Spatial Indexes and working with unique spatial properties (e.g. conversion to/from geometries, K-rings).

• Data module for enriching Spatial Indexes with geometry-based data .

• Statistics module for leveraging Spatial Indexes to employ Spatial Data Science techniques such as Local Moran's I, Getis Ord and Geographically Weighted Regression.

• Tiler module for generating tilesets from Spatial Indexes, enabling massive-scale visualizations.

Support for Spatial Indexes may differ depending on which cloud data warehouse you use - please refer to our documentation (links below) for details.

• Google BigQuery Analytics Toolbox

• Snowflake Analytics Toolbox

• AWS Redshift Analytics Toolbox

• Databricks Analytics Toolbox

Visualizing Spatial Indexes in CARTO Builder

CARTO Builder provides a lot of functionality to allow you to craft powerful visualizations with Spatial Indexes.

The most important thing to know is that Spatial Index layers are always loaded by aggregation. This means that if you want to use a Spatial Index variable to control the color or 3D extrusion of your layer, you must select an aggregation method such as sum or average. Similarly, the properties for widgets and pop-ups are also aggregated. Because of this, all property selectors will let you select an aggregation operation for each property.

Let's explore the other aspects of visualizing Spatial Indexes!

Visualizing point data as Quadbins

If you add a small point geometry table (<30K rows or 30MB depending on your cloud data warehouse - more information here) to CARTO Builder, you can visualize it as a Quadbin Spatial Index without requiring any processing! By doing this, you can visualize aggregated properties, such as the point count or the sum of numeric variables.

Zoom-based rendering

One of the most powerful features of visualizing Spatial Indexes with CARTO is zoom-based rendering. As the user zooms in further to a map, more detail is revealed. This is incredibly useful for visualizing data at a scale which is appropriate and easy to understand.

Try exploring this functionality on the map below!

Note the maximum, most detailed resolution that can be rendered is the "native" resolution of the Spatial Index table.

Controlling the resolution

With Spatial Index data layers, you can control the granularity of the aggregation by specifying what resolution the Spatial Index should be rendered at. The higher the resolution, the higher the granularity of your grid for each zoom level. This is helpful for controlling the amount of information the user sees.

Note the maximum, most detailed resolution you can visualize is the "native" resolution of the table.

Learn more about styling your maps in our documentation.

In this example we use CARTO Workflows to ingest data from a remote file containing temperature forecasts in the US together with weather risk data from NOAA, and data with the location of our stores; we will identify which of the stores are located in areas with weather risks or strong deviations in temperature.

To start creating the workflow, please click on "+ New workflow" in the main page of the Workflows section. If it will be your first workflow, click on "Create your first workflow".

Choose the data warehouse connection that you want to use. In this case, please select the CARTO Data Warehouse connection to find the data sources used in this example.

Now you can drag and drop the data sources and components that you want to use from the explorer on the left side of the screen into the Workflow canvas that is located at the center of the interface.

Now, let's add the noaa_warnings data table into our workflow from the demo_tables dataset available in the CARTO Data Warehouse connection.

After that, let’s add the retail_stores data table from the demo_tables dataset, also available in the CARTO Data Warehouse connection.

Now let's use the SPATIAL_JOIN component to know which of our retail_stores are in the warning areas.

At that point we already have our stores within a NOAA Weather Warning and, if we deem it appropriate, we can send an email to share this warnings to anyone interested in this information using the SEND_BY_EMAIL component.

After that, we can use the IMPORT_FROM_URL component to import the temperature forecast from Climate Prediction Center using this URL in particular to take the latest temperature forecast in a Shapefile: https://ftp.cpc.ncep.noaa.gov/GIS/us_tempprcpfcst/610temp_latest.zip. These data will be consulted again with each execution of the workflow. It means that the results of the workflow will change if the data has been updated.

Now, we are going to drop the geom_joined column to keep only one geom column in order to avoid confusions.

We will proceed to make a new SPATIAL_JOIN in order to have the temperature forecast associated to the stores.

Finally, we conclude with this example saving the outcome in a new table using SAVE_AS_TABLE component. Remember that you should specify the fully qualified name of the new dataset in the field of this component.

We can use the "Create map" button in the map section of the Results panel to create a new Builder map and analyze the results in a map.

Spatial Indexes - sometimes referred to as Data Cubes or Discrete Global Grid Systems (DGGs) - are global grid systems which tessellate the world into regular, evenly-shaped grid cells to encode location. They are available at multiple resolutions and are hierarchical, with resolutions ranging from feet to miles, and with direct relationships between “parent”, “child” and “neighbor” cells.

They are gaining in popularity as a support geography as they are designed for extremely fast and performant analysis of big data. This is because they are geolocated by a short reference string, rather than a long geometry description which is much larger to store and slower to analyze.

To learn more about Spatial Indexes you can get a copy of our free ebook Spatial Indexes 101.

Already a Spatial Indexes expert?

Skip ahead to the tutorials and boost your Spatial Index expertise to the next level!

Spatial Indexes: the fundamentals

• The advantages of working with Spatial Indexes

• Choosing an index type

• Choosing a resolution

The advantages of working with Spatial Indexes

Choosing an index type

So far, we’ve spoken about Spatial Indexes as a general term. However, within this there are a number of index types. In this section, will cover three main types of Spatial Indexes:

• H3

• Quadbin

• S2

• Which Spatial Index should I use?

H3

H3 is a hexagonal Spatial Index, availaIble at 16 different resolutions, with the smallest covering an average area of 0.9m2, reaching up to and 4.3 million km2 at the largest resolution. Unlike standard hexagonal grids, H3 maps the spherical earth rather than being limited to a smaller plan of an area.

H3 has a number of advantages for spatial analysis over other Spatial Indexes, primarily due to its hexagonal shape - which is the closest of the three to a circle:

• The distance between the centroid of a hexagon to all neighboring centroids is the same in all directions.

• The lack of acute angles in a regular hexagon means that no areas of the shape are outliers in any direction.

• All neighboring hexagons have the same spatial relationship with the central hexagon, making spatial querying and joining a more straightforward process.

• Unlike square-based grids, the geometry of hexagons is well-structured to represent curves of geographic features which are rarely perpendicular in shape, such as rivers and roads.

• The “softer” shape of a hexagon compared to a square means it performs better at representing gradual spatial changes and movement in particular.

Moreover, the widespread adoption of H3 is making it a great choice for collaboration.

However, there may be some cases where an alternative approach is optimal.

Quadbin

Quadbin is an encoding format for Quadkey, and is a square-based hierarchy with 26 resolutions.

At the most coarse level, the world is split into four quadkey cells, each with an index reference such as “48a2d06affffffff.” At the next level down, each of these is further reaching the most detailed resolution which measures less than 1m2 at the equator. This system is known as a quadtree key. The rectangular nature of the Quadbin system makes it particularly suited for modeling perpendicular geographies, such as gridded street systems.

S2

Finally, we have S2; a hierarchy of quadrilaterals ranging from 0 to 30, the smallest of which has a resolution of just 1cm2. The key differentiator of S2 is that it represents data on a three-dimensional sphere. In contrast, both H3 and Quadbin represent data using the Mercator coordinate system which is a cylindrical coordinate system. The cylindrical technique is a way of representing the bumpy and spherical (ish!) world on a 2D computer screen as if a sheet of paper were wrapped around the earth in a cylinder. This means that there is less distortion in S2 (compared to H3 and Quadbin) around the extreme latitudes. S2 is also not affected by the “break” at 180° longitude.

Which Spatial Index should I use?

As we mentioned earlier, H3 has a number of advantages over the other index types and because of this, it is fairly ubiquitous. However, before you decide to move ahead with H3, it’s important to ask yourself the following questions which may affect your decision.

• What is the geography of what I’m modeling? This is particularly pertinent if you’re modeling networks. In some cases, the geometry of hexagons is less appropriate for modeling perpendicular grids, particularly where lines are perpendicular with longitude as there is no “flat” horizontal line. If this sounds like your use case, consider using Quadbin or S2.

• Where are you modeling? As mentioned earlier, due to being based on a cylindrical coordinate system, both H3 and Quadbin cells experience greater area distortion at more extreme latitudes. However, H3 does have the lowest shape-based distortion at different latitudes. If you are undertaking analytics near the poles, consider instead working with the S2 index which does not suffer from this. Similarly, if your analysis needs to cross the International date Line (180° longitude) then you should also consider working with S2, as both H3 and Quadbin “break” here.

• What index type are your collaborators using? It’s worth researching which index your data providers, partners, and clients are using to ensure smooth data sharing, transparency and alignment of results.

Choosing a resolution

The resolution that you work with should be linked to the spatial problems that you’re trying to solve. You can’t answer neighborhood-level questions with cells a few feet wide, and you can’t deal with hyperlocal issues if your cells are a mile across.

For example, if you are investigating what might be causing food delivery delays, you probably need a resolution with cells of around 100-200 yards/meters wide in order to identify problem infrastructure or services.

It’s also important to consider the scale of your source data when making this decision. For example, if you want to know the total population within each index cell but you only have this data available at county level, then transforming this to a grid with a resolution 100 yards wide isn’t going to be very illuminating or representative.

Just remember - the whole point of Spatial Indexes is that it’s easy to convert between resolutions. If in doubt, go for a more detailed resolution than you think you need. It’s easier to move “up” a resolution level and take away detail than it is to move “down” and add detail in.

Learn more about working with Spatial Index "parent" and "children" resolutions in these tutorials.

Keep learning...

Continue your Spatial Indexes journey with the resources below 👇

Context

The basemap is the foundational component of a map. It provides context, geographic features, and brand identity for your creations. Every organization is unique, and CARTO allows you to bring your own basemaps to fit your specific needs.

In this tutorial, you'll learn to customize your visualizations in Builder by using tailor-made basemaps. Don't have a custom basemap already? We'll start with the creation of a custom basemap using Maputnik, a free and open-source visual editor.

Prerequisites: You need to be an Admin user to add custom basemaps to your CARTO organization.

In this guide, we'll walk you through:

1. Creating a Style JSON in Maputnik

2. Hosting a Style JSON using Github Gist

3. Adding your custom basemaps to CARTO

4. Creating a map using your custom basemap

Creating a Style JSON using Maputnik

1. Access the online version of Maputnik at https://maplibre.org/maputnik/. Then, click "Open" and select "Zoomstack Night." Zoomstack Night is an open vector basemap provided by Ordnance Survey's OS Open Zoomstack, showcasing coverage of Great Britain.

You might get overwhelmed by all the options available in the UI, but using it is simpler than it seems. To make it easier to recognize the different items you can update in the style, simply click on the map, and Maputnik will display the layers you can customize.

Now that you're more familiar with this tool, let's start customizing the look and feel of this map.

1. Set the "buildings" layer to blue using this hex color code #4887BD.

1. For the green spaces, set the "greenspaces" layer to #09927A and "woodland" to #145C42.

1. To highlight the visualization of both "greenspace names" and "woodland names" labels, increase the size using the below JSON code and set the fill color to white.

1. Once you're done, export the Style JSON and save it. You'll need this for the next section. Note depending on which style you have used as a template, you may need to include an access token at this point, such as from MapTiler.

Hosting a Style JSON in Github

In this section, we'll showcase how you can host Style JSON files using GitHub to consume them in your CARTO organization. We'll be using a feature called gist, which allows you to host files. Here’s how to do it:

1. Ensure you have access to GitHub and your own repository and create a new gist. To do so:

   • Go to GitHub and create a new gist.

   • Drag your exported Style JSON into the gist.

   • Make sure the gist is public.

   • Create the public gist.

1. Now we'll get the raw URL of the hosted Style JSON, to do so:

   • Access the raw version of the gist.

   • Copy the URL of the raw file. This URL will be used to consume the custom basemap in CARTO.

Adding custom basemaps to your organization

Note: You need to be the Admin of your organization to have the rights to add custom basemaps to your CARTO organization.

1. Go to Organization > Settings > Customizations > Basemaps

1. Click on "New basemap" to add your custom basemap, completing the following parameters:

   • URL: Enter the gist raw URL of the hosted Style JSON.

   • Name: The name you'd like to provide to your basemap

   • Attribution: Automatically filled but you can edit this if required.

2. Once the basemap URL has been validated, you can use the interactive map to navigate to the desired basemap extent.

1. Activate the custom basemap type in the "Enabled basemaps in Builder" section. Doing so, you'll enable all Editors of the organization to access all added custom basemaps.

Creating a map using your custom basemap

1. Navigate to the Maps section and click on "New map".

1. Provide the map with a title "Using custom basemaps" and load Bristol traffic accidents source. To do so:

   • Click on "Add sources from..."

   • Navigate to CARTO Data Warehouse > demo data > demo_tables.

   • Select "bristol_traffic_accidents" table.

   • Click "Add source".

The source and related layer is added to the map.

1. Rename the newly added layer "Traffic Accidents".

2. Go to the Basemap tab and choose your recently uploaded custom basemap.

1. Style the "Traffic Accidents" layer:

   • In the Fill Color section, set the color to light yellow.

   • Configure the Size to 4.

1. Now, you're done with your map creation and ready to share it with others!

This tutorial leverages the Spatial Index H3 to visualize origin and destination trip patterns in a clear, digestible way. We'll be transforming 2.5 million origin and destination locations into one H3 frequency grid, allowing us to easily compare the spatial distribution of pick up and drop off locations. This kind of analysis is crucial for resource planning in any industry where you expect your origins to have a different geography to your destinations.

You can use any table which contains origin and destination data - we'll be using the NYC Taxi Rides demo table which you can find in the CARTO Data Warehouse (BigQuery) or the CARTO Academy Data listing on the Snowflake Marketplace.

Step-by-Step tutorial

Creating a Workflow

1. In the CARTO Workspace, head to Workflows and Create a Workflow, using the connection where your data is stored.

2. Under Sources, locate NYC Taxi Rides (or whichever input dataset you're using) and drag it onto the workflow canvas).

#1 Filtering trips to a specific time period

When running origin-destination analysis, it's important to think about not only spatial but temporal patterns. We can expect to see different trends at different times of the day and we don't want to miss any nuances here.

1. Connect NYC Taxi Rides to a Spatial Filter component.

2. Set the filter condition to PART_OF_DAY = morning (see screenshot above). You can pick any time period you'd like; if you select the NYC Taxi Rides source, open the Data preview and view Column Stats (histogram icon) for the PART_OF_DAY variable, you can preview all of the available time periods.

Note we've started grouping sections of the workflow together with annotation boxes to help keep things organized.

#2 Convert origins and destinations to a H3 frequency grid

The 2.5 million trips - totalling 5 million origin and destination geometries - is a huge amount of data to work with, so let's get it converted to a Spatial Index to make it easier to work with! We'll be applying the straightforward approach from the Convert points to a Spatial Index tutorial.

1. Connect the match output of the Simple Filter to a H3 from GeoPoint component and change the points column to PICKUP_GEOM; which will create a H3 cell for each input geometry. We're looking for junction and street level insights here, so change the resolution to 11.

2. Connect the output of this to a Group by component. Set the Group by column to H3 and the aggregation column to H3 (COUNT). This will count the number of duplicate H3 IDs, i.e. the number of points which fall within each cell.

3. Repeat steps 1 & 2, this time setting the initial points column to DROPOFF_GEOM.

4. Add a Join component and connect the results of your two Group by components to this. Set the join type to Full Outer; this will retain all cells, even where they don't match (so we will retain a H3 cell that has pickups, but no dropoffs - for instance).

Now we have a H3 grid with count columns for the number of pick ups and drop offs, but if you look in the data preview, things are getting a little messy - so let's clean them up!

#3 Data cleaning

1. Create Column: at the moment our H3 index IDs are contained in two separate columns; H3 and H3_JOINED. We want just one single column containing all IDS, so let's create a column called H3_FULL and use the following CASE statement to combine the two: CASE WHEN H3 IS NULL THEN H3_JOINED ELSE H3 END.

2. Drop Columns: now we can drop both H3 and H3_JOINED to avoid any confusion.

3. Rename Column: now, let's rename H3_COUNT as pickup_count and H3_COUNT_JOINED as dropoff_count to keep things clear.

Now, you should have a table with the fields H3_FULL, pickup_count and dropoff_count, just like in the preview above!

#4 Normalize & Compare

Now, we can compare the spatial distribution of pickups and dropoffs:

1. Connect two subsequent Normalize components, first normalizing pickup_count, and then dropoff_count. This will convert the raw counts into scores from 0 to 1, making a relative comparison possible.

2. Add a Create Column component, and calculate the difference between the two normalized fields (pickup_count_norm - dropoff_count_norm). The result of this will be a score ranging from -1 (relatively more dropoffs) to 1 (relatively more pickups).

You can see the full workflow below.

Check out the results below!

Do you notice any patterns here? We can see more drop offs in the business district of Midtown - particularly along Park Avenue - and more pick ups in the more residential areas such as the Upper East and West Side, clearly reflecting the morning commute!

More Workflows Tutorials 👉

In this example we will see how we can identify customers potentially affected by an active fire in California using CARTO Workflows. This approach is one of the building blocks of spatial analysis and can be easily adapted to any use case where you need to know which features are within a distance of another feature.

All of the data that you need can be found in the CARTO Data Warehouse (instructions below).

• To begin, click on "+ New workflow" in the main page of the Workflows section. If it will be your first workflow, click on "Create your first workflow".

• To begin, click on "+ New workflow" in the main page of the Workflows section. If it will be your first workflow, you will instead see the option to "Create your first workflow".

From here, you can drag and drop data sources and analytical components that you want to use from the explorer on the left side of the screen into the Workflow canvas that is located at the center of the interface.

• Let's add the usa_states_boundaries data table into our workflow from the demo_tables dataset available in the CARTO Data Warehouse connection. You can find this under Sources > Connection > demo data > demo_tables.

• Then filter only the boundary of the state of California using the Simple Filter component; set the column as name, the operator as equal to and the value as California.

• Run your workflow!

You can run the workflow at any point in this tutorial - only new or edited components will be run, not the entire workflow. You can also just wait to run until the end.

Next, let's explore fires in this study area.

• From the same location that you added usa_states_boundaries, add fires_worldwide to the canvas. For ease later, you'll want to drop it just above the Simple Filter component from the previous step.

• Next, add a Spatial Filter component to filter only the fires that fall inside the digital boundary of the state of California. Connect fires_worldwide to the top input and Simple Filter to the bottom. Specify both geo columns as "geom" and the spatial predicate as intersect (meaning the filter will apply to all features where any part of their shape intersects California).

• To keep your workflow well organized, use the Add a note (Aa) tool at the top of the window to draw a box around this section of the workflow. You can use any markdown syntax to format this box - our example uses ## Fires in California.

• Now, use the ST Buffer component to generate a 5 km radius buffer around each of the active fires in California.

• Next, add third data source with a sample of customer data from an illustrative CRM system. You can find it as customers_geocoded in demo_tables inside your CARTO Data Warehouse.

• Now let’s add another Spatial Filter component to know which of our customers live within the 5 km buffer around the active fires and thus could potentially be affected.

• You'll notice we now have a couple of instances of duplicated records where these intersect multiple buffers. We can easily remove these with a Remove duplicated component. Now is also a great time to add a second note box to your workflow, this time called ## Filter customers.

You can explore the results of this analysis at the bottom panel of the window, via both the Data and Map tabs. From the map tab, you can select Create map to automatically create a map in CARTO Builder.

Head to the Data visualization section of the Academy next to explore tutorials for building impactful maps!

Create a classification model

This example shows how to create a pipeline to train a classification model using BigQuery ML, evaluate the model and use it for prediction. In particular, we will create a classification model to estimate customer churn for a telecom company in California.

This example workflow will help you see how telco companies can detect high-risk customers, uncover the reasons behind customer departures, and develop targeted strategies to boost retention and satisfaction by training a classification model.

Download example

Create a regression model

This example shows how to create a pipeline to train a regression model using BigQuery ML, evaluate the model and use it for prediction. In particular, we will create a regression model to predict the average network speed in the LA area.

This example workflow will help you see how telco companies can improve network planning use by training a regression model to estimate the network speed in areas where no measurements are available.

Download example

Forecast

This template shows how to create a forecast model using the BigQuery ML extension package for Workflows. There are three main stages involved:

• Training a model, using some input data and adjusting to the desired parameters,

• Evaluating and understanding the model and its performance,

• Predicting to a given horizon and saving the results.

Download example

Import a model

This example shows how to create a pipeline to import a pre-trained model using BigQuery ML and use it for prediction. In particular, we will import a regression model to predict the ration of crime counts per 1000 population in the Chicago area.

Download example

Spatio-temporal analysis is crucial in extracting meaningful insights from data that possess both spatial and temporal components. By incorporating spatial information, such as geographic coordinates, with temporal data, such as timestamps, spatio-temporal analysis unveils dynamic behaviors and dependencies across various domains. This applies to different industries and use cases like car sharing and micromobility planning, urban planning, transportation optimization, and more.

In this example, we will perform a hotspot analysis to identify space-time clusters and classify them according to their behavior over time. We will use the location and time of accidents in London in 2021 and 2022, provided by Transport for London. This tutorial builds upon this previous one, where we explained how to use the spacetime Getis-Ord functionality to identify traffic accident hotspots.

Data

The source data we will use has two years of weekly aggregated data into an H3 grid, counting the number of collisions per cell. The data is available at cartobq.docs.spacetime_collisions_weekly_h3 and it can be explored in the map below.

Spacetime Getis-Ord

We start by performing a spacetime hotspot analysis to identify hot and cold spots over time and space. We can use the following call to the Analytics Toolbox to run the procedure:

CALL `carto-un`.carto.GETIS_ORD_SPACETIME_H3_TABLE(
 'cartobq.docs.spacetime_collisions_weekly_h3',
 'cartobq.docs.spacetime_collisions_weekly_h3_gi',
 'h3',
 'week',
 'n_collisions',
 3,
 'WEEK',
 1,
 'gaussian',
 'gaussian'
);

CALL `carto-un-eu`.carto.GETIS_ORD_SPACETIME_H3_TABLE(
 'cartobq.docs.spacetime_collisions_weekly_h3',
 'cartobq.docs.spacetime_collisions_weekly_h3_gi',
 'h3',
 'week',
 'n_collisions',
 3,
 'WEEK',
 1,
 'gaussian',
 'gaussian'
);

CALL carto.GETIS_ORD_SPACETIME_H3_TABLE(
 'cartobq.docs.spacetime_collisions_weekly_h3',
 'cartobq.docs.spacetime_collisions_weekly_h3_gi',
 'h3',
 'week',
 'n_collisions',
 3,
 'WEEK',
 1,
 'gaussian',
 'gaussian'
);

For further detail on the spacetime Getis-Ord check out the documentation and this tutorial.

By performing this analysis, we can check how different parts of the city become “hotter” or “colder” as time progresses.

Understanding hot and cold spots

Once we have identified hot and cold spots, we can classify them into a set of predefined categories so that the results are easier to digest. For more information about the categories considered and the specific criteria, please check the SQL reference in the documentation.

We can run the analysis by calling the SPACETIME_HOTSPOTS_CLASSIFICATION procedure using the previously obtained Getis-Ord results.

CALL `carto-un`.carto.SPACETIME_HOTSPOTS_CLASSIFICATION(
 'cartobq.docs.spacetime_collisions_weekly_h3_gi',
 'cartobq.docs.spacetime_collisions_hotspot_classification',
 'index',
 'date',
 'gi',
 'p_value',
 '{"threshold": 0.05, "algorithm": "mmk"}'
);

CALL `carto-un-eu`.carto.SPACETIME_HOTSPOTS_CLASSIFICATION(
 'cartobq.docs.spacetime_collisions_weekly_h3_gi',
 'cartobq.docs.spacetime_collisions_hotspot_classification',
 'index',
 'date',
 'gi',
 'p_value',
 '{"threshold": 0.05, "algorithm": "mmk"}'
);

CALL carto.SPACETIME_HOTSPOTS_CLASSIFICATION(
 'cartobq.docs.spacetime_collisions_weekly_h3_gi',
 'cartobq.docs.spacetime_collisions_hotspot_classification',
 'index',
 'date',
 'gi',
 'p_value',
 '{"threshold": 0.05, "algorithm": "mmk"}'
);

We can see how now we have different types of behaviors at a glance in a single map. There are several insights we can extract from this map:

• There is an amplifying hotspot in the city center that shows an upward trend in collisions.

• The surroundings of that amplifying hotspot are mostly occasional.

• The periphery of the city is mostly cold spots, but most of them are fluctuating or even declining.

The two primary spatial data types are raster and vector - but what’s the difference?

Raster data

Raster data is represented as a grid of cells or pixels, with each cell containing a value or attribute. It has a grid-based structure and represents continuous values such as elevation, temperature, or satellite imagery.

Common raster file types

Common file types for raster data include:

• GeoTIFF: a popular raster file format with embedded georeferencing.

• JPEG, PNG & BMP: ubiquitous image files which can be georeferenced with a World or TAB file. PNG supports lossless compression and transparency, making it particularly useful for spatial visualization.

• ASCII: stores gridded data in ASCII text format. Each cell value is represented as a text string in a structured grid format, making it easy to read and manipulate.

You may also encounter: ERDAS, NetCDF, HDF, ENVI, xyz.

Vector data

Vector data represents geographic features as discrete points, lines, and polygons.It has a geometry-based structure in which each element in vector data represents a discrete geographic object, such as roads, buildings, or administrative boundaries. Vector data is scalable without loss of quality and can be easily modified or updated.

Vector data is useful for spatial analysis operations such as overlaying, buffering, and network analysis, facilitating advanced geospatial studies. Vector data formats are also well-suited for data editing, updates, and maintenance, making them ideal for workflows that require frequent changes.

Common vector file types

Shapefiles

Shapefiles are a format developed by ESRI. They have been widely adopted across the spatial industry, but their drawbacks see them losing popularity. These drawbacks include:

1. Shareability: They consist of multiple files (.shp, .shx, .dbf, etc.) that comprise one shapefile, which can make them tricky for non-experts to share and use.

2. Limited Attribute Capacity: Shapefiles are limited to a maximum of 255 attributes.

3. Lack of Native Support for Unicode Characters: This can cause issues when working with datasets that contain non-Latin characters or multilingual attributes.

4. Lack of Topology Information: Shapefiles do not inherently support topological relationships, such as adjacency, connectivity, or overlap between features.

5. No Native Support for Time Dimension: No native time field type.

6. Lack of Direct Data Compression: Shapefiles do not provide built-in compression options, which can result in larger file sizes.

Limited File Size Limitations: Shapefile size is limited to 2 GB.

Other vector file types

1. GeoJSON (Geographic JavaScript Object Notation): GeoJSON is an open standard file format based on JSON (JavaScript Object Notation). It allows for the storage and exchange of geographic data in a human-readable and machine-parseable format.

2. KML/KMZ (Keyhole Markup Language): KML is an XML-based file format used for representing geographic data and annotations. It was originally developed for Google Earth but has since become widely supported by various GIS software. KMZ is a compressed version of KML, bundling multiple files together.

3. GPKG (Geopackage): GPKG is an open standard vector file format developed by the Open Geospatial Consortium (OGC). It is a SQLite database that can store multiple layers of vector data along with their attributes, styling, and metadata. GPKG is designed to be platform-independent and self-contained.

4. FGDB (File Geodatabase): FGDB is a proprietary vector file format developed by Esri as part of the Esri Geodatabase system.

5. GML (Geography Markup Language): GML is an XML-based file format developed by the .

Everything in-between

There is a small area in between raster and vector data types, with Spatial Indexes being one of the most ubiquitous data types here.

Spatial Indexes are global grids - in that sense, they are a lot like raster data. However, they render a lot like vector data; each "cell" in the grid is an individual feature which can be interrogated. They can be used for both vector-based analysis (like running intersections and spatial joins) and raster-based analysis (like slope or hotspot analysis).

But where they really excel is in their size, and subsequent processing and analysis speeds. Spatial Indexes are "geolocated" through a reference string, not a long geometry description (like vector data). This makes them small, and quick. So many organizations are now taking advantage of Spatial Indexes to enable highly performant analysis of truly big spatial data. Find out more about these in the ebook

Featured resources

These resources have been designed to get you started. They offer an end-to-end tutorial for creating, enriching and analyzing Spatial Indexes using data freely available on the CARTO platform.

Spatial Statistics

For your use case

Not sure where to start? Check out our recommended learning path !

Working with geospatial data

Building interactive maps

Creating workflows

Advanced spatial analytics

Get help

Context

In this tutorial, you’ll learn how to use CARTO Builder to create an interactive dashboard for visualizing and analyzing retail store performance across the USA. We’ll create two types of layers; one displaying stores in their original geometry using bubbles and another using point geometry aggregated to Spatial Indexes, all easily managed through the CARTO UI.

Thanks to this interactive map, you’ll effortlessly identify performance trends and pinpoint the most successful stores where revenue is inversely correlated with surface area. Are you ready to transform your data visualization and analysis skills? Let's dive in!

Step-by-Step Guide:

1. Access the Maps from your CARTO Workspace using the Navigation menu and create a "New map".

1. Let's add retail stores as the first data source.

   • Select the Add source from button at the bottom left on the page.

   • Click on the CARTO Data Warehouse connection.

   • Select Type your own query.

   • Click on the Add Source button.

The SQL Editor panel will be opened.

To add retail stores source, run the query below:

1. Change the layer name to "Retail Stores". Click over the layer card to start styling the layer.

1. Access more Options in the Fill Color section and apply “Color based on" using Size_m2 column. Pick a gradient palette (versus one for a categorical variable), and set the gradient steps to 4.

1. Now click on the options for the Radius configuration and in the section “Radius Based On” pick the column Revenue. Play with the minimum/maximum size to style the layer as you like.

Now that you have styled "Retail stores" layer, you should have a map similar to the below.

1. Go to Widget tab, click on New Widget button and select your SQL Query data source.

1. First, we create a for the Total Revenue. Select the SUM operation on the revenue field, adjusting the output value format to currency. Add a note to indicate we are calculating revenue shown in the viewport. Rename to “Total Revenue”:

1. Next, we will create a widget to filter by store type. Select , choose COUNT operation from the list and select the column storetype. Make the widget collapsible and rename it to “Type of store”.

1. Then, we create a third widget, a to filter stores by revenue. Set the buckets to 10, formatting to currency, and make widget collapsible. Rename to “Stores by revenue”.

1. Now let’s configure the tooltip. Go to Interactions tab, activate the tooltip and select the field Storetype, Address, City, State, Revenue and Size_m2.

1. Let’s also change our basemap. Go to Basemaps tab and select “Voyager” from CARTO.

1. Now, we will upload the same data source using SQL Query type and this time we will dynamically aggregate it to Quadbin Spatial Indexes using the UI. To do so, run the following query:

1. The new layer will appear. Rename the layer to "Retail stores (H3)" and using the Layer panel, aggregate it to Quadbin.

1. Change the order of your layer by dragging it after your point "Retail store" layer. In the layer panel, set the Spatial Index resolution to 7 and style it based on Revenue using SUM as the aggregation.

1. Finally, set the height of the hexagons to be based on Size_m2, multiplying by 20 using Linear es the color scale. Then, set the map view to 3D to analyze the results.

1. Enable map dual view. On the left map disable the "Revenue store (H3)" grid layer, on the right map disable the "Retail stores" layer.

As we can see, in metro areas in the west coast we have more stores of lower surface area, yet their revenues are much higher than rural areas, where we have stores with higher surface areas.

1. Switch back to single map view mode. Hide the "Retail stores (H3)" layer. Rename the map to “Monitor retail store performance” and add a rich description using .

1. We can make the map public and share it online with our colleagues. For more details, see .

1. Finally, we can visualize the result.

In this tutorial, we’ll explore how to optimize work distribution across teams by analyzing sales territory data to identify imbalances and redesign territories using .

Focusing on a beverage brand in Milan, we’ll use the component, a feature available in the , to evenly assign Point of Sale (POS) locations to sales representatives by dividing a market (a geographic area) into a set of continuous territories. This ensures fair workloads, improves customer coverage, and boosts operational efficiency by aligning territories with demand.

Setting up your workflow

1. Sign in to CARTO at

2. Head to the Workflows tab and click on Create new workflow

3. Choose the CARTO Data Warehouse connection or any connection to your Google BigQuery project.

4. the extension package if you do not have it already.

Now, let’s dive into the step-by-step process of creating a workflow to balance territories, ensuring each sales representative is assigned a manageable and strategically optimized area. You can access the full template.

Imagine a field sales team responsible for visiting hundreds of restaurants across the greater Milan metropolitan area to promote and distribute a soft drink. When Points of Sale (POS) are assigned manually, territories often become unbalanced—some sales representatives end up with too many accounts to manage effectively, while others are left underutilized. This imbalance may lead to inconsistent store visits and missed sales opportunities. By balancing the POS to visit, Territory Balancing ensures that each sales rep is responsible for a fair and strategically valuable territory.

Loading the POS data

For this use case, we will consider restaurant point of sales (POS) in the city center of Milan from , together with popularity and sentiment scores. The full dataset is available on demand in , but we have prepared a sample for you to easily follow this tutorial.

Once in your workflow, go to the section, and drag-and-drop the component. Then, type in the table’s Fully Qualified Name (FQN): cartobq.docs.territory_balancing_milan_pos.

Using H3 Spatial Indexes

The Territory Balancing component uses a graph partitioning algorithm that splits a gridified area into a set of optimal, continuous territories, ensuring balance according to a specified metric while maintaining internal similarity within each territory. To account for spatial dependencies when defining the graph, we rely on spatial indexes for efficiency and scalability: each node in our graph is defined by an H3 or Quadbin cell (white dots), with edges or connections (white lines) defined by their direct first-degree neighbours.

We'll map each restaurant to its corresponding H3 cell at resolution 9 and aggregate the data by cell. This will allow us to calculate the number of points of sale (POS) within each cell, along with the average popularity and sentiment scores. Use the and the components to do so.

Balancing territories based on POS presence

Lastly, we will use the component to obtain 9 continuous areas of equal (with a tolerance) number of restaurants so that workload is distributed evenly. We will select geom_count (the number of restaurants) as the business KPI to be balanced, the so-called demand. We will also consider the average popularity and sentiment as similarity features, so that H3 cells within the same territory are similar in these metrics, while these averages differ across territories.

In the following map, we can see a comparison between a manual assignment based on administrative regions (Nuclei di Identità Locale, NIL) and an automatic one using territory balancing. The former is very unbalanced, with some territories being highly overloaded while others being assigned just a few restaurants. Using territory balancing techniques, it can be seen how territories are optimally assigned with balanced workload.

In this section you can find step-by-step guides focused on bringing your data visualization to life with Builder. Each tutorial utilizes available demo data from the CARTO Data Warehouse connection, enabling you to dive straight into map creation right from the start.

Builder enhances data interaction and analysis through two key features: and . Widgets, linked to individual data sources, provide insights from map-rendered data and offer data filtering capabilities. This functionality not only showcases important information but also enhances user interactivity, allowing for deeper exploration into specific features.

Meanwhile, SQL Parameters act as flexible query placeholders. They enable users to modify underlying data, which is crucial for updated analysis or filtering specific subsets of data.

Widgets

Widgets, linked to individual data sources, provide insights from map-rendered data and offer data filtering capabilities. This functionality not only showcases important information but also enhances user interactivity, allowing for deeper exploration into specific features.

Adding a Widget

Add a widget to Builder by clicking "New Widget" and select your data source.

Then, select a widget type from the menu: Formula, Category, Histogram, Range, Time Series or Table.

Configuring a Widget

Once you have selected the widget type of your preference, you are ready to configure your Widget.

Widget Data

In the Data section of the Widget configuration, choose an aggregation operation COUNT, AVG, MAX, MIN or SUM and, if relevant, specify the column on which to perform the aggregation.

Widget Display

Using the Formatting option, you can auto-format data, ensuring enhanced clarity. For instance, you can apply automatic rounding, comma-separations, or percentage displays.

You can use Notes to supplement your Widgets with descriptive annotations which support , allowing to add text formatting, ordered lists, links, etc.

Widget Behavior

Widgets in Builder automatically operate in viewport mode, updating data with changes in the viewport. You can also configure them for global mode to display data for the entire source.

Furthermore, Widgets can be set as collapsible for convenient hiding. Some widgets have the capability to filter not only themselves but also related widgets and connected layers. This filtering capability can be easily enable or disable for each widget using the cross-filtering icon.

SQL Parameters

SQL Parameters serve as placeholders in your SQL Query data sources, allowing viewer users to input specific values that dynamically replace these placeholders. This allows users to interactively customize and analyze the data displayed on their maps.

SQL Parameters are categorized based on the data format of the values expected to be received, ensuring flexibility and ease of use. Below are the current type of SQL Parameters:

• : Ideal for handling date values, date parameters allow users to input a specific date range, enabling data analysis over precise time periods. For example, analyzing sales data for a specific month or quarter.

• : Tailored for text values, users can input or select a specific category to obtain precise insights. For instance, filtering Points of Interest (POI) types like "Supermarket" or "Restaurant".

• : Designed for numeric values, users can input specific numerical criteria to filter data or perform analysis based on their preferences. For example, updating the radius size of a geofence to update an analysis result.

Using SQL Parameters

SQL Parameters can be used in many different ways. One of the most common is allowing viewers to interact with the data in a controlled manner. Let's cover a simple use case step by step:

Add a SQL Query data source

The option to create a new SQL Parameter will be available once there is at least one data source of type Query:

So, let's create a SQL Query data source with a table that contains information about fires all over the world:

• On a new map, click on 'Add source from...' and select 'Custom query (SQL)' .

• Select CARTO Data Warehouse as connection.

• Use the following query

Create and configure a text parameter

Once we have the data rendered in the map, we'll add a that helps us select between fires that happened during the day or the night.

• Click on 'Create a SQL Parameter'

• Select 'Text Parameter'

• In the 'Values' section, click on 'Add from source'. Select your data source and pick the daynight column

• In the 'Naming' section, pick a display name, like 'Day/Night'. The SQL name gets automatically generated as {{day_night}}

• After the parameter has been created, open the SQL panel and add it to your query:

You can now use the control UI to add/remove values and check how the map changes.

Create and configure a date parameter

Now, let's add a to filter fires by its date:

• Click on 'Create a SQL parameter'

• Select 'Date parameter'

• Type or select from a calendar the range of dates that are going to be available from the control UI.

• Give it a display name, like 'Date'. The SQL names gets automatically generated as {{date_from}} and {{date_to}}

Open the SQL Panel and add the parameters to your query, like:

The parameters {{date_from}} and {{date_to}} will be replaced by the dates selected in the calendar.

Create and configure a numeric parameter

Next, we'll incorporate a range slider to introduce a . It will allow users to focus on fires based on their brightness temperature to identify the most intense fires.

• Click on 'Create a SQL parameter'

• Select 'Numeric parameter'

• In the 'Values' section, select Range Slider and enter the 'Min Value' and 'Max Value' within the range a user will be able to select.

• Give it a display name, like 'Bright Temp'. The SQL names gets automatically generated as {{bright_temp_from}} and {{bright_temp_to}}

Open the SQL Panel and add the parameters to your query, like:

It's not uncommon for geospatial datasets to be larger than their non-geospatial counterparts, and geospatial operations are sometimes slow or resource-demanding — but that's not a surprise: representing things and events on Earth and then computing their relationships is not an easy task.

With CARTO, you will unlock a way to do spatial analytics at scale, combining the huge computational power of your data warehouse with our expertise and tools, for millions or billions of data points. And we'll try to make it easy for you!.

In this guide we'll help you prepare your data so that it is optimized for spatial analysis with CARTO.

Benefits of using optimized data

Having clean, optimized data at the source (your data warehouse) will:

• Improve the performance of all analysis, apps, and visualizations made with CARTO

• Reduce the computing costs associated in your data warehouse

General tips and rules

Before we start diving into the specific optimizations and tricks available in your data warehouse, there are some typical data optimization patterns that apply to all data warehouses:

Data warehouse specific optimizations

The techniques to optimize your spatial data are slightly different for each data warehouse provider, so we've prepared specific guides for each of them. Check the ones that apply to you to learn more:

• Optimizing your Google BigQuery data

• Optimizing your Snowflake data

• Optimizing your Amazon Redshift data

• Optimizing your Databricks data

• Optimizing your PostgreSQL data

• Optimizing your CARTO Data Warehouse data

Optimizing your Google BigQuery data

• Make sure your data is clustered by your geometry or spatial index column.

Optimizing your Snowflake data

• If your data is points/polygons: make sure Search Optimization is enabled on your geometry column

• If your data is based on spatial indexes: make sure it is clustered by your spatial index column.

Optimizing your Amazon Redshift data

• If your data is points/polygons: make sure the SRID is set to EPSG:4326

• If your data is based on spatial indexes: make sure you're using your spatial index column as the sort key.

Optimizing your Databricks data

• Make sure your data uses your H3 column as the z-order.

Optimizing your PostgreSQL data

• Make sure your data is indexed by your geometry or spatial index column.

• If your data is points/polygons: make sure the SRID is set to EPSG:3857

Optimizing your CARTO Data Warehouse data

• Make sure your data is clustered by your geometry or spatial index column.

How CARTO helps you apply these optimizations

As you've seen through this guide, we try our best to automatically optimize the performance and the costs of all analysis, apps, and visualizations made using CARTO. We also provide tools like CARTO Workflows or our Data Explorer UI-assisted optimizations to help you succeed.

Context

URL parameters allow you to share multiple versions of the same map, without having to rebuild it depending on different user requirements. This tutorial will guide you through embedding a Builder map in a low code tool that can be controlled using URL parameters to update the map's view based on users input. Through these steps, you'll learn to make your embedded maps more engaging and responsive, providing users with a seamless and interactive experience.

Resources for this tutorial:

In this tutorial, we're providing you with an existing Builder map as a hands-on example to guide you through the process. This example map highlights historic weather events. If you're interested in creating a similar map, this tutorial is for you.

• Public map URL: https://clausa.app.carto.com/map/5d942679-411f-4ab7-afb7-0f6061c9af63

• Embed code:

  Copy

  <iframe width="640px" height="360px" src="https://clausa.app.carto.com/map/5d942679-411f-4ab7-afb7-0f6061c9af63"></iframe>

Step-by-Step Guide:

In this guide, we'll walk you through:

• Accessing your map URL and embed code

• Dynamically updating URL parameters in Builder

• Updating an embedding map using URL parameters

Accessing your map URL and embed code

To access your map's URL and/or embed code, first ensure that your map has been shared — either within your organization, with specific groups, or publicly. After sharing the map, you can proceed with the following steps:

1. Map Link: This direct URL to your map can be quickly obtained in two ways:

   • Through a quick action from the 'Share' button.

   • Within the sharing modal in the left bottom corner.

2. Embed code: This is specifically available within the sharing modal:

   • Navigate to the sharing settings of your map.

   • Look for the "Developers and embedding" section. Here, the embed code is provided, allowing you to copy and paste it into the HTML of your site or application for seamless embedding.

Dynamically updating URL parameters in Builder

Leveraging URL parameters with Builder maps enables dynamic customization for specific audience views without creating multiple map versions. This method simplifies sharing tailored map experiences by adjusting URL parameters, offering a personalized viewing experience with minimal effort.

In the viewer mode of a Builder map, any modifications you make are instantly updated in the URL. For example, if you zoom to a specific level in the loaded Builder map, the zoom level gets added to the URL. Here's how it looks:

https://clausa.app.carto.com/map/5d942679-411f-4ab7-afb7-0f6061c9af63?zoom=4

Below you can see how when you interact with the map: navigating, filtering widgets, parameters, etc. automatically change the URL displaying that specific map view.

Updating an embedding map using URL parameters

You also have the option to manually insert URL parameters to customize your map's viewer mode further. This option is particularly useful for tailoring map content to specific user queries or interests when the map is embedded, making the application more engaging and interactive.

In this section we'll illustrate how to integrate a Builder map in a custom application using Retool, a development platform for creating apps rapidly.

1. Begin by inserting an iFrame component in your Retool application. In the URL section, use your map URL. You can use the provided map URL: https://clausa.app.carto.com/map/5d942679-411f-4ab7-afb7-0f6061c9af63.

1. Add a container in Retool to neatly organize UI components that will interact with your map.

2. Implement UI elements to enable users to filter the map view based on criteria like state, type of severe weather event, and the event's date range. Start by adding a multi-select dropdown to allow users select a specific state. Name this element as State, and pre-fill it with the names of all U.S. states in alphabetical order:

["Alabama","Alaska","Arizona","Arkansas","California","Colorado","Connecticut","Delaware","District of Columbia","Florida","Georgia","Hawaii","Idaho","Illinois","Indiana","Iowa","Kansas","Kentucky","Louisiana","Maine","Maryland","Massachusetts","Michigan","Minnesota","Mississippi","Missouri","Montana","Nebraska","Nevada","New Hampshire","New Jersey","New Mexico","New York","North Carolina","North Dakota","Ohio","Oklahoma","Oregon","Pennsylvania","Rhode Island","South Carolina","South Dakota","Tennessee","Texas","Utah","Vermont","Virginia","Washington","West Virginia","Wisconsin","Wyoming"]

1. Include a checkbox group element named Event. This component will enable users to select the type of severe weather event they are interested in, such as hail, tornadoes, or wind, with one option set as the default.

1. Add two date pickers, one named as StartDate and the other EndDate. These components will define the timeframe of the event, providing default start and end dates to guide the user's selection. For the provided map example, let's ensure we are matching the temporal frame of the weather events by setting the start date to 1950-01-03 and end date to 2022-01-03.

1. Create a transformer named mapUrlParameters to dynamically construct the iframe's URL based on the user's selections. Use JavaScript to fetch the values from the UI components and assemble them into the URL parameters.

const paramState = {{State.value}}
const eventType = {{Event.value}}
const startDate = {{StartDate.value}}
const endDate = {{EndDate.value}}

const urlParameters = `?param_state=${paramState}&param_event_type=${eventType}&param_event_date_from=${startDate}&param_event_date_to=${endDate}`

return urlParameters

1. Add a button component labelled Apply that, when clicked, updates the iFrame URL with the new parameters selected by the user. This action ensures the map is only refreshed when the user has finalized their choices, making the map interaction more efficient and user-friendly.

1. To further enhance user experience, implement a secondary event that zooms to the map's focal point in the iFrame when the "Apply" button is clicked. This ensures the map is centered and zoomed appropriately for the user.

Additionally, customize your application by adding headers, more interactive elements, and so on, to increase usability and aesthetic appeal.

What is CARTO Workflows

CARTO Workflows is a visual modeling tool that allows you to create multi-step analyses without writing any code. With Workflows, you can orchestrate complex spatial analyses with as many steps as needed which can be edited, updated, duplicated, and run as many times as needed.

Why Workflows?

Workflows is completely cloud-native, which means that behind the scenes Workflows compiles native SQL to your data warehouse or database and runs the Workflow directly within the database or data warehouse. What does this mean for you?

• Speed: Since Workflows uses native SQL in the data warehouse, the speed of your analysis is comparable to running the analysis directly on the data warehouse itself. For example, a spatial point in polygon count of US Counties (3k+ polygons) to 26 million+ points can take ~6 seconds to run (depending on your infrastructure)

• No data transfer: Your data never leaves your data source. This means that compared to other tools that take data out of the source, the performance boosts are massive, and you ensure your data remains in the same place

• Faster analysis: You can assemble and modify analyses much faster than writing SQL and you can automate repetitive tasks

• Lower costs: In nearly all cases, Workflows is a lower-cost analysis method compared to other desktop-based tools

Our goal with Workflows is to bring the power of spatial SQL to a much larger audience including GIS Analysts, Data Analysts, and Business Users who can now create complex spatial analysis without writing code. It reduces the need of specialist knowledge and for those specialists, they can now automate the repetitive tasks and focus on more complex and valuable analytical tasks.

Workflows UI elements

Before we jump into workflow's tutorials and templates, let's take a quick look of the Workflows interface so you know your way around before getting started.

Canvas

First is the Canvas where you will design your Workflow. This is a free-form Canvas meaning you can drag nodes onto any part of the canvas. You can zoom in and out to see different parts of your workflow and see the layout of the workflow in the mini viewer in the lower right corner. As you add nodes to the canvas they will snap to a grid to align.

Sources & Components panel

On the left side, you will find a menu where you can add data sources from the connection you created the Workflow. You can add any data source that you want that exists in your connection. You also have all the components, or nodes, that you can add to the canvas. You can search for components or scroll to find the component you want.

Results panel

The bottom panel is the results space where you will see four different tabs:

• Messages: Messages about the status of your Workflow including success and error messages.

• Data: After clicking on a node, you can see the tabular data outputs of that specific workflow step.

• Map: After clicking on a node, if that step returns a valid geometry, it will show up in the map. If there is more than one geometry you will have an option to pick which one to show.

• SQL: The compiled SQL of the workflow. This includes different steps and procedural language.

Resources in CARTO Academy

In order to accelerate your onboarding to CARTO and get you ready to get the most out of Workflows for your day-to-day data pipeline and analytics, we have carefully curated a set of detailed step-by-step tutorials and workflow templates, from basic introductory ones to others covering more advanced and industry-specific use-cases.

Keep learning...

Deep dive in some of the advanced features available in CARTO Workflows to ensure you get the most out of this tool by reading the specifications in our Product Documentation.

In this tutorial, you will learn how to optimize the site selection process for EV charging stations at scale. While this guide focuses on EV charging stations, you can adapt this process to optimize site selection for any service or facility.

You will need...

• A subscription to the USA H3 Spatial Features dataset (free) from our Spatial Data Catalog. You can replace this with any Spatial Index dataset that includes a population field.

• A subscription to the OpenStreetMap places dataset, available for all countries in the Spatial Data Catalog. If you're following our example, you'll want to use the USA version.

• Electric Vehicle charging location data. Our example uses data downloaded from the National Renewable Energy Laboratory here. Prior to following the tutorial, you'll need to load this data into your cloud data warehouse.

Step 1: Identifying areas of high demand for EV charging locations

In this first step of this tutorial, we'll be building the workflow below to understand where has the highest likely demand for EV charging locations in the USA. We will do this by identifying which H3 cells are furthest from an existing charging location, but also have a high population.

1. First, log into the CARTO Workspace and Create a new workflow and Select a Connection (this should be to wherever you have loaded the EV charging location data).

2. Drag the EV charging location data onto the map. 💡 If the table doesn't include a geometry field, use ST GeogPoint to create point geometries from the latitude and longitude columns.

3. Next, drag the H3 population data onto the canvas. The H3 Spatial Features table from the Spatial Data Catalog contains a vast number of fields. To make your life easier, you may wish to instead use the Select component to only select the fields of interest, using the SQL "SELECT geoid AS h3, population FROM..."

4. We first need to calculate the distance from each H3 cell to its closest charging location:

   1. Use H3 Center to convert each H3 cell to a point geometry.

   2. Use ST Distance to calculate the distance from each H3 cell to the closest EV charging station.

5. Next, use a Simple Filter to filter out any H3 cells which are closer than 4 km to an EV charging station, assuming that these locations are already well served for vehicle charging.

6. Next, Join the results of this filter to the input H3 selection to access its population data.

7. Finally, the BigQuery SQL PERCENTILE_CONT() function is used in a Custom SQL Select to select only areas with a high population (>97th percentile). You can see the SQL used to perform this below - note we can use placeholders like $a to call other components in the workflow.

WITH
  stats AS (
  SELECT
	h3,
	nearest_distance AS percentile,
	population_joined,
	PERCENTILE_CONT(population_joined, 0.97) OVER() AS percentile_97
  FROM
	$a)
SELECT
  *
FROM
  stats
WHERE
  population_joined >= percentile_97

The result of this workflow should be a H3 grid covering all areas further than 4km from a charging station, and the 97th population percentile. Select the final Custom SQL Select component, open the map preview on the bottom of the screen then select Create Map to explore your results.

Section 2: Pinpointing potential EV charging locations

Now we know areas of likely high demand for EV charging locations, we can identify possible infrastructure which could accomodate future charging locations such as gas stations, hotels or parking lots.

To do this, we'll extend the workflow we created above.

1. First, drag the “OSM places” layer onto your canvas.

2. As your workflow is starting to become more complex, consider adding annotations to keep it organized.

3. First, convert the OSM Places to a H3 index using H3 from GeoPoint.

4. Secondly, use an inner Join is used to join the H3 cells to the result of Custom SQL Select from earlier; this will retain only "places" within these high demand areas. This process acts a lot like a Spatial Filter, but as we are using Spatial Indexes there is no geometry processing required, making the process much faster and more efficient.

The results of this are a query containing only infrastructure in areas of high demand for EV charging - perfect locations for future charging infrastructure!

Continue learning...

Learn more about how this analysis can be used in the blog post below.

In this tutorial, we will calculate the population living within 1km of cell towers in the District of Columbia. We will be using the following datasets, all of which can be found in the demo tables section of the CARTO Data Warehouse:

• Cell towers worldwide

• USA state boundaries

• Derived Spatial Features H3 USA

Step 1: Convert cell towers to a Spatial Index

In this step we will filter the cell towers to an area of interest (in this example, that's the District of Columbia), before converting them to a H3 index. For this, we'll follow the workflow below.

1. Create a workflow using the CARTO Data Warehouse connection and drag the three tables onto the canvas.

2. Connect the USA state boundaries table to a Simple Filter dataset, and set the filter condition for the name to equal the Colorado (or any state of your choosing!).

3. Next, connect the outcome of the Simple Filter to the bottom input (filter table) of a Spatial Filter component, and then connect the Cell towers table to the top input (source table). This should automatically detect the geometry columns in both tables. We'll keep the spatial predicate as the default "intersects"; this predicate filters the source table where any part of its geometry intersects with any part of the filter geometry.

4. Finally, connect the output of the Spatial Filter to a H3 from GeoPoint component to encode the point location as a H3 index. Ensure the resolution is the same as the Spatial Features population data; 8.

Step 2: Finding the population within 1km of each cell tower

Next, we will use K-rings to calculate the population who live roughly within 1km of each cell tower.

1. Connect the result of H3 from Geopoint to a new H3 KRing component, and set the size. You can use this documentation and this hexagon properties calculator to work out how many K-rings you need to approximate specific distances. We are working at resolution 8, where a H3 cell has a long-diagonal of approximately 1km, so we need a H3 of 1 to approximate 1km.

2. You can see in the image above that this generates a new table containing the K-rings; the kring_index is the H3 reference for the newly generated ring, which can be linked to the original, central H3 cell.

3. Next, use a Join to join the K-ring to the Spatial Features population data. Ensure the K-ring is the top input and the population data is the bottom field. Then set up the parameters so the main table column is kring_index, the secondary table column is h3 and the join type is Left.

You can see this visualized below.

Step 3: Summarizing the results

Finally, we will calculate the total population within 1km of each individual cell tower.

1. Connect the result of your last Join component to a Group by component. Set the group by column to H3 and the aggregation to population_joined with the aggregation type SUM (see above).

2. You should now know the total population for each H3 cell which represents the cell towers. The final step is to join these results back to the cell tower data so we can identify individual towers. To do this, add a final Join component, connecting H3 from GeoPoint (created in Step 1, point 4) to its top input, and the result of Group by to the bottom input. The columns for both main and secondary table should be H3, and you will want to use a Left join type to ensure all cell tower records are retained.

Run!

Altogether, your workflow should look something like the example below. The final output (the second Join component) should be a table containing all of the original cell tower data, as well as a H3 index column and the population_joined_sum_joined field (you may wish to use Rename Column to rename this!).

And here are the results!

In this tutorial, we will calculate the rate of traffic accidents (number of accidents per 1,000 people) for Bristol, UK. We will be using the following datasets. The first one is available in the demo tables section of the CARTO Data Warehouse, while the latter two are freely available in our Spatial Data Catalog.

• Bristol traffic accidents (CARTO Data Warehouse)

• Census 2021 - United Kingdom (Output Area) [2021] (Office for National Statistics)

• Lower Tier Local Authority (Office for National Statistics)

Step 1: Converting accident data to Spatial Indexes

In this step, you'll convert the individual accident point data to aggregated H3 cells.

1. Create a Workflow using the CARTO Data Warehouse connection.

2. First, drag the Lower Tier Local Authority data onto the canvas. It can be found under Sources > Data Observatory > Office for National Statistics.

3. Connect this to a Simple Filter component. Set the filter to do_label is equal to "Bristol, City of".

4. Next, connect the filter results to a H3 Polyfill component, and set the resolution to 9. This will create a H3 grid covering the Bristol area.

5. Now, drag the Bristol traffic accidents table onto the canvas. It can be found under Sources > Connection > CARTO Data Warehouse > demo tables.

6. Connect this to a H3 from GeoPoint component, setting the resolution of this to 9 also. This will create a H3 index for each input point.

7. Connect the output of H3 from GeoPoint to a Group by component. Set the group by column to H3, and the aggregation column to H3 (count). The result of this will be a table with a count for the number of accidents within each H3 cell.

8. In the final stage for this section, add a Join component. Connect the H3 Polyfill component to the top input, the Group by component to the bottom input, and set the join type to Left.

9. Run!

The result of this will be a H3 index covering the Bristol area with a count for the number of accidents which have taken place within each cell. Now let's put those counts into context!

Step 2: Enrich the grid with population data

In this section of the tutorial, we will enrich the H3 grid we have just created with population data from the UK Census.

1. Drag the Census 2021 - United Kingdom (Output Area) [2021] table onto the canvas from Sources > Connections > Office for National Statistics.

2. Drag an Enrich H3 Grid onto the canvas. Connect the Join component (Step 1 point 4) to the top input, and the Census data to the bottom output.

3. The component should detect the H3 and geometry columns by default. From the Variables drop down, add "ts001_001_ff424509" (total population, you can reference Variable descriptions for any dataset on our Data Observatory) and specify the aggregation method as SUM. This will estimate the total population living in each H3 cell based on the area of overlap with each Census Output Area.

Run the workflow.

Step 3: Calculating the accident rate & hotspot analysis

Now we have all of the variables collected into the H3 support geography, we can start to turn this into insights.

1. First, we'll calculate the accident rate. Connect the output of Enrich H3 Grid to a new Create Column component. Call the new column "rate".

2. Set the expression as CASE WHEN h3_count_joined IS NULL THEN 0 ELSE h3_count_joined/(ts001_001_ff424509_sum/1000) END. This code calculates the number of accidents per 1,000 people, unless there has been no accident in the area, in which case the accident rate is set to 0.

3. Now, let's explore hotspots of high accident rates . Connect the output of Create Column to a new Getis Ord component which is the hotspot function we will be using. Set the value column as "rate" (i.e. the variable we just created), the kernel to gaussian and the neighborhood size to 3. Learn more about this process here.

4. Finally, connect the results of this to a Simple Filter, and the filter condition to where the p_value is equal to or less than 0.05; this means we can be 95% confident that the locations we are looking at are a statistically significant hotspot.

You can explore the results below!

💡 Note that to be able to visualize a H3 index in CARTO Builder, the field containing the index must be called H3.

Spatio-temporal analysis is crucial in extracting meaningful insights from data that possess both spatial and temporal components. By incorporating spatial information, such as geographic coordinates, with temporal data, such as timestamps, spatio-temporal analysis unveils dynamic behaviors and dependencies across various domains. This applies to different industries and use cases like car sharing and micromobility planning, urban planning, transportation optimization, and more.

In this example, we will perform spatio-temporal analysis to identify areas with similar traffic accident patterns over time using the location and time of accidents in London in 2021 and 2022, provided by Transport for London. This tutorial builds upon this previous one, where we explained how to use the spacetime Getis-Ord functionality to identify traffic accident hotspots.

Data

The source data we use has two years of weekly aggregated data into an H3 grid, counting the number of collisions per cell. The data is available at cartobq.docs.spacetime_collisions_weekly_h3 and it can be explored in the map below.

Spacetime Getis-Ord

We start by performing a spacetime hotspot analysis to better understand our data. We can use the following call to the Analytics Toolbox to run the procedure:

CALL `carto-un`.carto.GETIS_ORD_SPACETIME_H3_TABLE(
 'cartobq.docs.spacetime_collisions_weekly_h3',
 'cartobq.docs.spacetime_collisions_weekly_h3_gi',
 'h3',
 'week',
 'n_collisions',
 3,
 'WEEK',
 1,
 'gaussian',
 'gaussian'
);

CALL `carto-un-eu`.carto.GETIS_ORD_SPACETIME_H3_TABLE(
 'cartobq.docs.spacetime_collisions_weekly_h3',
 'cartobq.docs.spacetime_collisions_weekly_h3_gi',
 'h3',
 'week',
 'n_collisions',
 3,
 'WEEK',
 1,
 'gaussian',
 'gaussian'
);

CALL carto.GETIS_ORD_SPACETIME_H3_TABLE(
 'cartobq.docs.spacetime_collisions_weekly_h3',
 'cartobq.docs.spacetime_collisions_weekly_h3_gi',
 'h3',
 'week',
 'n_collisions',
 3,
 'WEEK',
 1,
 'gaussian',
 'gaussian'
);

For further detail on the spacetime Getis-Ord, take a look at the documentation and this tutorial.

By performing this analysis, we can check how different parts of the city become “hotter” or “colder” as time progresses.

Finding time series clusters

Once we have an initial understanding of the spacetime patterns of our data, we proceed to cluster H3 cells based on their temporal patterns. To do this, we use the TIME_SERIES_CLUSTERING procedure, which takes as input:

• input: The query or fully qualified name of the table with the data

• output_table: The fully qualified name of the output table

• partitioning_column: Time series unique IDs, which in this case are the H3 indexes

• ts_column: Name of the column with the value per ID and timestep

• value_column: Name of the column with the value per ID and timestep

• options: A JSON containing the advanced options for the procedure

One of the advanced options is the time series clustering method. Currently, it features two basic approaches:

• Value characteristic that will cluster the series based on the step-by-step distance of its values. One way to think of it is that the closer the signals, the closer the series will be understood to be and the higher the chance of being clustered together.

• Profile characteristic that will cluster the series based on their dynamics along the time span passed. This time, the closer the correlation between two series, the higher the chance of being clustered together.

Clustering the series as-is can be tricky since these methods are sensitive to the noise in the series. However, since we smoothed the signal using the spacetime Getis-Ord before, we could try clustering the cells based on the resulting temperature. We will only consider those cells with at least 60% of their observations with reasonable significance.

CALL `carto-un`.carto.TIME_SERIES_CLUSTERING(
 '''
   SELECT * FROM `cartobq.docs.spacetime_collisions_weekly_h3_gi`
   QUALIFY PERCENTILE_CONT(p_value, 0.6) OVER (PARTITION BY index) < 0.05
 ''',
 'cartobq.docs.spacetime_collisions_weekly_h3_clusters',
 'index',
 'date',
 'gi',
 JSON '{ "method": "profile", "n_clusters": 4 }'
);

CALL `carto-un-eu`.carto.TIME_SERIES_CLUSTERING(
 '''
   SELECT * FROM `cartobq.docs.spacetime_collisions_weekly_h3_gi`
   QUALIFY PERCENTILE_CONT(p_value, 0.6) OVER (PARTITION BY index) < 0.05
 ''',
 'cartobq.docs.spacetime_collisions_weekly_h3_clusters',
 'index',
 'date',
 'gi',
 JSON '{ "method": "profile", "n_clusters": 4 }'
);

CALL carto.TIME_SERIES_CLUSTERING(
 '''
   SELECT * FROM `cartobq.docs.spacetime_collisions_weekly_h3_gi`
   QUALIFY PERCENTILE_CONT(p_value, 0.6) OVER (PARTITION BY index) < 0.05
 ''',
 'cartobq.docs.spacetime_collisions_weekly_h3_clusters',
 'index',
 'date',
 'gi',
 JSON '{ "method": "profile", "n_clusters": 4 }'
);

Even if it can feel like some layers of indirection, this provides several advantages:

• Since it has been temporally smoothed, noise has been reduced in the dynamics of the series;

• and since it has been geographically smoothed, nearby cells are more likely to be clustered together.

This map shows the different clusters that are returned as a result:

We can immediately see the different dynamics in the widget:

• Apart from cluster #3, which clearly clumps the “colder” areas, the rest start 2021 with very similar accident counts.

• However, from July 2021 onwards, cluster #2 accumulates clearly more collisions than the other two.

• Even though #1 and #4 have similar levels, certain points differ, like September 2021 or January 2022.

This information is incredibly useful to kickstart a further analysis to understand the possible causes of these behaviors, and we were able to extract these insights at a single glance at the map. This method “collapsed” the results of the space-time Getis-Ord into a space-only result, which makes the data easier to explore and understand.

In this tutorial, we’ll create a workflow to improve portfolio management for real estate insurers by identifying vacant buildings in areas experiencing anomalously high rates of violent crime.

By the end of this tutorial, you will have:

✅ Built a workflow to detect spatio-temporal emerging anomalous regions

✅ Prepared the results for interactive map visualization to monitor at-risk properties

Let's get started!

You'll need...

This is data that you'll need to run the analysis:

• Crime counts: the cartobq.docs.CHI_crime_counts_w_baselines public table reports the observed and expected counts for violent crimes in Chicago from 2001 to present. The individual crime data, which were extracted from the Chicago Police Department's CLEAR (Citizen Law Enforcement Analysis and Reporting) system and is available in Google BigQuery public marketplace, where aggregated by week and H3 cell at resolution 8. The expected counts were obtained using a statistical model that accounts for external covariates as well as endogenous variables, including spatial lag variables to account for the influence of neighboring regions, counts at previous time lags to model the impact of past values on current or future outcomes, and seasonal terms to account for repeating seasonal behaviours,

• Vacant buildings: the cartobq.docs.CHI_311_vacant_buildings_2010_2018 public table reports the 311 calls for open and vacant buildings reported to the City of Chicago since January 1, 2010.

That's all you need for now - let's get going!

Step 1: Setting up your workflow

1. Sign in to CARTO at app.carto.com

2. Head to the Workflows tab and select the Import Workflow icon and import this template.

3. Choose the CARTO Data Warehouse connection or any connection to you Google BigQuery project.

Step 2: Check that there are no missing data

For this method to work, we need first to ensure that the data is complete, i.e. that there are no weeks and/or H3 cells without data or with missing data. This can be easily verified by ensuring that each H3 cell has the same number of timesteps (and vice versa), as done in the first node where the Group By component is used to count the number of timestamps cells per H3 cell (and vice versa). This check allows us to verify that there are no gaps in the data. If gaps are detected, filling them is relatively straightforward for count data — it simply involves inserting zeros for the missing data points. However, for non-count variables, the process can be more complex. While simple techniques, like those available in Google Bigquery GAP_FILL function, might be a good initial approximation, more advanced modelling strategies are generally required.

Step 3: Find the most anomalous regions

Next, we add the Detect Space-time Anomalies component, which uses a multi-resolution method to search over a large and overlapping set of space-time regions, each containing some subset of the data, and find the most significant clusters of anomalous data. For a complete tutorial on how this method works, you can take a look at this guide.

We run this component with the following settings:

• The index, data and variable column (h3, week, counts)

• The time frequency of the data, WEEK for weekly data

• That the analysis is of kind prospective, meaning that we are interested in emerging anomalies, i.e. anomalies in the final part of the time series

• The POISSON distributional model, which is appropriate for count data

• The EXPECTATION estimation method, which assumes that the observed values should be equal to the baseline for non-anomalous space-time regions.

• The spatial extent of the regions, with a k-ring between 2 and 3.

• The temporal extent of the regions, with a window between 4 and 12 weeks.

• The we are looking for high-mean anomalies, i.e. we search for regions where the observed crimes are higher than expected.

• The number of permutations to compute the statistical significance of the score.

• The maximum number of results returned, that we set to 1 to select the most anomalous region only.

The output of the component is a table indexed by a unique identifier called index_scan. Each identifier corresponds to a specific anomalous space-time region. For each region, the following information is provided: the anomalous score (score, the higher the more anomalous), its statistical significance (gumbel_pvalue), the relative risk (rel_risk, which represents the ratio of the sum of the observed counts to the sum of the baseline counts), and the H3 cells (locations) and weeks (times), which are both stored as arrays.

Step 3: Prepare the data for visualization

To join the output from the component to the input table, which is indexed by the cell id and time, we need to first unnest the arrays. We then pivot the resulting table in order to obtain a table indexed by the H3 cell id and the week, with a 'key' column indicating either counts or counts_baseline and a 'value' column storing the corresponding count.

Finally, we join the results with a table containing 311 calls for open and vacant buildings reported to the City of Chicago between January 1, 2010 and December 2018: we first extract the distinct H3 cell in the space-time region using the Select Distinct component, then create a geometry column from the H3 Boundary component and finally use a Spatial Join component to intersect the tables based on their geometries.

Step 4: Visualize the data

Now let's turn this into something a bit more visual!

1. Select the Transpose / Unpivot as Table component. Open the Map preview at the bottom of the screen and select Create Map. This will take you to a fresh CARTO Builder map with your the H3 cells of the anomalous regions and their counts pre-loaded.

2. To also add the vacant buildings geometries, go back to the workflow and select the last Spatial Join component. Open the Map preview at the bottom of the screen and select Create Map. This will take you to a fresh CARTO Builder map with your data pre-loaded. Click on the three dots in the Sources panel, select the Query this table option and copy the code. Then go back to the first map and again in the Sources panel and click on Add source from layers, select the Add Custom Query (SQL) option and paste the SQL code. This will add to the map a layer with the vacant buildings within the anomalous region.

3. In the Layer panel, click on Layer 1 to rename the layer "Anomalous region" and style your data.

4. In the Layer panel, click on Layer 2 to rename the layer "Vacant buildings" and style your data.

5. To the right of the Layer panel, switch to the Widgets panel, to add a couple of dashboard elements to help your users understand your map. We’d recommend:

   1. Time series widget: SUM, value, and Split By key - to show the total number of observed and expected counts by week.

   For each of the widgets, scroll to the bottom of the Widget panel and change the behaviour from global to viewport, and watch as the values change as you pan and zoom.

6. Head to the Legend panel (to the right of Layers) to ensure the names used in the legend are clear (for instance we've changed the title of the legend from "Anomalous Region" to "Space-time region exhibiting an anomalous number of violent crimes").

7. Now, Share your map (top right of the screen) with your Organization or the public. Grab the shareable link from the share window.

Here's what our final version looks like:

Looking for tips? Head to the Data Visualization section of the Academy!

Context

Understanding population distribution has important implications in a wide range of geospatial analysis such as human exposure to hazards and climate change or improving geomarketing and site selection strategies.

In this tutorial we are going to represent the distribution of the most populated places by applying colours to each type of place and a point size based on the maximum population. Therefore, we can easily understand how the human settlement areas is distributed with a simple visualization that we can use in further analysis.

Steps To Reproduce

1. Access the Maps section from your CARTO Workspace using the Navigation menu and create a new Map using the button at the top right of the page. This will open the Builder in a new tab.

1. Let's add populated places source. To do so, follow the next steps:

   • Select the Add source from button at the bottom left on the page.

   • Select Custom Query (SQL) and then Type your own query under the CARTO Data Warehouse connection.

   • Click on the Add Source button.

The SQL Editor panel will be opened.

To add populated places source, run the query below:

1. Change the layer name to "Populated Places". Click over the layer card to start styling the layer.

1. In the Fill Color, we will use the 'Color based on' functionality to color by featurecla. It has information about what kind of places there are, so we will pick a palette for a categorical variable (versus a gradient). Additionally, we will remove the Stroke Color so we are able to differentiate the different categories.

1. Now click on the options for the Radius configuration and in the section “Radius Based On” pick the column pop_max. Play with the minimum/maximum size to style the layer as you like.

1. Go to Widget tab and click on 'New widget' to add a new Widget for "populated_places" source.

1. Select the Category widget, choose COUNT as the operation method and select the column admin0name. Then, rename your widget to 'Populated places by country'.

1. Using the Category widget on the right panel, select “United States of America” to filter out the rest of countries. You can also lock your selection to ensure the selection is not removed by mistake.

2. Let's now add another widget, this time a Pie widget based on featurecla. We will add a Markdown note for this widget to provide users with further information about each category type. We will also set the behaviour mode of this widget to global, so the represented date is for the whole dataset without it being affected by the viewport intersection.

1. Finally, we will rename this widget to 'Places by type' and move it to the top of the Widgets panel by dragging the card on the left panel.

2. The third and final widget we will add to our dashboard is a Histogram widget using pop_max column. This will allow users to select the cities based on the population. Finalise the widget configuration by setting the buckets limit to 10 and formatting the data to be displayed. Finally, rename the widget to 'Max population distribution'.

1. Interactions allow users to gather information about specific features, you can configure this functionality in the Interaction panel. First, select the type of interaction to Click and Info Panel. Then, add the attributes you are interested in, renaming and changing the formatting as needed.

1. Finally we can change our basemap. Go to Basemaps tab and select “Dark matter” from CARTO.

1. Rename the map to “Populated Places”.

1. Add a map description that will allow users understand the nature of your map.

1. We can make the map public and share it online with our colleagues. For more details, see .

Finally, let's export our map into a portable, easy-to-share PDF.

1. In the window that appears, select Include map legend. You can also include comments here (such as the version number or any details about your approval process). In the Share drop-down menu, select Download PDF Report.

2. Select Preview, and when you're happy Download PDF Report.

In this tutorial, we'll share a low code approach to calculating a composite score using Spatial Indexes. This approach is ideal for creating numeric indicators which combine multiple concepts. In this example, we'll be combining climate and historic fire extents to calculate fire risk - but you can apply these concepts to a wide range of scenarios - from market suitability for your new product to accessibility scores for a service that you offer.

You will need...

• Climate data. Fires are most likely to start and spread in areas of high temperatures and high wind. We can access this information from our Spatial Features data - a global grid containing various climate, environmental, economic and demographic data. You can subscribe to this from the , or access the USA version of this data in the CARTO Data Warehouse.

• USA Counties data. This can also be subscribed to from the , or accessed via the CARTO Data Warehouse.

• Historic fires data. We’ll be using the LA County Historic Fires Perimeter data to understand areas where fires have been historically prevalent. You can download this data as a geojson .

We’ll be creating the below workflow for this:

Step 1: Climate data

Before running our composite score analysis, we need to first filter the Spatial Features data to our area of interest (LA County). The climate data we are interested in is also reported at monthly levels, so we need to aggregate the variables to annual values.

We’ll be running this initial section of the workflow in this step.

💡 You can run the workflow at any point, or wait to the end and run then! Only non-edited components will run each time you execute.

1. Set up First, in your CARTO Workspace, head to Workflows and Create a workflow, using the CARTO Data Warehouse connection.

2. In the workflow, on the Sources panel (left of the screen), in the Connection panel you’ll see the CARTO Data Warehouse. Navigate to demo data > demo tables > usa_counties and derived_spatialfeatures_usa_h3res8_v1_yearly_v2. Drag these onto the canvas.

3. Beside sources, switch to Components. Search for and drag a onto the canvas, then connect the usa_counties source to this. Set the name as equal to Los Angeles.

4. Next, connect the Simple Filter to a component, ensuring the resolution is set to 8. This will create a grid across LA, which we can use to filter the climate data to this area.

5. Connect the H3 Polyfill output to the top input and the Spatial Features source to the bottom input of a component. Ensure both the main and secondary table join fields are set to H3 (this should autodetect), and then set the join type to Left. This will join only the features from the USA-wide Spatial Features source which are also found in the H3 polyfill component, i.e. only the cells in Los Angeles.

6. Now, we want to use two subsequent components to create two new fields. 💡 Please note that if you are using a data warehouse that isn't Google BigQuery, the SQL syntax for these calculations may need to be slightly different.

   1. Temp_avg for the average temperature:(tavg_jan_joined + tavg_feb_joined + tavg_mar_joined + tavg_apr_joined + tavg_may_joined + tavg_jun_joined + tavg_jul_joined + tavg_aug_joined + tavg_sep_joined + tavg_oct_joined + tavg_nov_joined + tavg_dec_joined) / 12

   2. On a separate branch, Wind_avg for the average wind speeds: (wind_jan_joined + wind_feb_joined + wind_mar_joined + wind_apr_joined + wind_may_joined + wind_jun_joined + wind_jul_joined + wind_aug_joined + wind_sep_joined + wind_oct_joined + wind_nov_joined + wind_dec_joined) / 12

7. Finally, connect the second Create Column to an component, selecting the columns h3, temp_avg and wind_avg.

Next up, we'll factor historic wildfire data into our analysis.

Step 2: Historic wildfires

In this step, we'll calculate the number of historic fires which have occurred in each H3 cell.

1. Locate the LA County Historic Fires Perimeter dataset from where you’ve downloaded it and drag it directly onto your workflow canvas. Alternatively, you can into your cloud data warehouse and drag it on via Sources.

2. Like we did with the LA county boundary, use another (resolution 8) to create a H3 grid across the historic fires. Make sure you enable Keep input table columns; this will create duplicate H3 cells where multiple polygons overlap.

3. Run the workflow!

4. With a component, set the Group by column to H3 and the aggregation to H3 (COUNT) to count the number of duplicate H3 cells, i.e. the number of fires which have occurred in each area.

5. Now, drag a onto the canvas; connect the Group by to the bottom input and the Edit schema component from Step 1.7 to the top input. The join type should be Left and both input columns should be H3.

6. Do you see all those null values in the h3_count_joined column? We need to turn those into zeroes, indicating that no fires occurred in those locations. Add a component, and use the calculation coalesce(h3_count_joined,0) to do this - calling this column wildfire_count.

Step 3: Creating a composite score

There are two main methods for calculating a composite score. Unsupervised scoring (which this tutorial will focus on) consists in the aggregation of a set of variables, scaled and weighted accordingly, , whilst supervised scoring leverages a regression model to relate an outcome of interest to a set of variables and, based on the model residuals, focuses on detecting areas of under and over-prediction. You can find out more about both methods and which to use when , and access pre-built workflow templates .

There are three main approaches to unsupervised scoring:

• Principal Component Analysis (PCA): This method derives weights by maximizing the variation in the data. This process is ideal for when expert knowledge is lacking and the sample size is large enough, and extreme values are not outliers.

• Entropy: By computing the entropy of the proportion of each variable, this method, like PCA, makes it ideal for those without expert domain knowledge.

• Custom Weights: Recommended to use for those with expert knowledge of their data and domain, this method allows users to customize both scaling and aggregation functions, along with defining a set of weights, enabling a tailored approach to scoring by incorporating domain-specific insights.

We'll be using Custom Weights here.

1. First, we need to drop all superfluous columns. With a component, drop all fields apart from h3, temp_avg, wind_avg and wildfire count.

2. Connect this to a component, using the method, and set the following parameters:

   1. Set the weights as: temp_avg = 0.25, wind_avg = 0.25, fire_count = 0.5. Alternatively choose your own weights to see how this affects the outcome!

   2. Leave the user-defined scaling as min-max and the aggregation function as linear, but change the output formatting to jenks. This will partition the results into classes based on minimizing within-class variance and maximizing between-class variance. Keep the number of buckets as 5 - and run!

Once complete, head into the map preview and select Create map. Set the fill color of your grid to be determined by the spatial score and add some widgets to help you explore the results.

With historic fires and climate data factored into our risk score, we can begin to understand the complex concept of risk. For instance, risk is considered much high around Malibu, the location of the famous , but low to the southeast of the county.

Check out how we’ve used a combination of widgets & interactive pop ups to help our user interpret the map - head over to the section to learn more about how you can do this!

Context

For many geospatial use cases, it is common to work with identical static geometries where attributes vary over time or across different records. This is particularly relevant when working with administrative boundaries, infrastructure, or road networks, where multiple entries share the same geometry but contain different data attributes.

In this tutorial, you’ll learn how to easily visualize static geometries with changing attributes over time using the functionality in Builder.

For this example, we’ll use the Global Historical Climatology Network managed by NOAA that provides historical weather and climate data from weather stations worldwide. It includes observations such as temperature, precipitation, wind speed, and other climate indicators. In our case, we'll focus on USA weather station with a timeline covering 2016. By aggregating identical geometries, we can efficiently explore patterns, trends, and interactions while improving map performance.

Step 1: Visualize USA Weather Stations

1. Access the Maps tab from your CARTO Workspace using the Navigation menu and create a "New map".

1. To start, let's name our map "GHCN USA Weather Stations" and add the GHCN USA weather stations:

   • Select the Add source from button at the bottom left on the page.

   • Click on the Data Explorer.

   • Navigate to CARTO Data Warehouse > carto-demo-data > demo_tables.

   • Search for ghcn_usa_weather_stations.

   • Select the table and click "Add Source".

1. A map layer is automatically added from your source. Rename it to "Weather Stations."

Our source dataset contains over 19 million records, but many rows share identical geometries since weather metrics are recorded over time at the same exact location. To assess this, let's add a Category Widget that counts records for each weather station.

1. Navigate to the Widgets tab, choose Category Widget and set the following configuration:

   • Operation: COUNT

   • Source Category: station_id

   • Formating: 12.3k

   • Behavior: Filter by viewport

As you’ll see, some stations have hundreds or even thousands of records, meaning there are overlapping points. To effectively analyze patterns and trends, we’ll use the functionality in Builder, which groups features based on their identical geometries, as defined in the spatial column of the data section.

1. Navigate back to the Layer panel and open the advanced options in the Visualization section. Activate Aggregate by geometry functionality. This will aggregate your layer by identical geometries in the spatial column defined in your data source.

As you can see, the Category Widget is still pointed out to the original source, as widgets are link to source level. However, your layer has been aggregated and now the properties link to it require to have an aggregation of choice both for the styling and when defining interactions.

Step 2: Extract and transform weather values

Before we start working further with this data, it's essential to correctly extract and transform the weather values on our GHCN-daily dataset because:

• The value column contains data for multiple weather elements, such as temperature, precipitation, and snow.

• The element column defines what type of data each row represents, meaning we must filter and assign the correct interpretation to each value.

• All values are stored in different units (e.g. tenths of ªC for temperature, mm for precipitation, etc.) and require conversions.

We can do the pertinent adjustments to our data source by leveraging custom SQL Query as a source in Builder.

1. Go to your source card, click on the three dots and click Query this table.

The SQL Editor panel will open.

1. To make it easier to analyze, you can copy the query below and click "Run". In this query, we'll be tackling the following:

   • Convert raw values into meaningful units (e.g., tenths of °C to °C, tenths of mm to mm).

   • Provide user -friendly labels for each weather element so end-users can easily interpret the data.

   • Normalize values so that different weather elements (e.g., temperature vs. precipitation) can be styled together without distorting the map.

   • Filter out unnecessary elements using a WHERE clause to reduce noise and focus on key variables.

Step 3: Extract insights using Widgets

• Now let's add some more Widgets to allow users retrieve insights. Go to the Widgets panel and select Category Widget, name it "Weather Metrics" setting the following configuration:

• Operation: AVG

• Source Category: element_friendly_label

• Aggregation column: raw_value

• Formating: 1.23

• Behavior: Filter by viewport

This will allow users to easily select the weather metric of choice to perform drill down analysis.

1. GHCN-daily dataset contains a timestamp covering 2016. To visualize the temporal pattern of each of the weather metrics, we'll add a new widget. Navigate to Widgets and choose Time Series Widget. Name it "Time Series" and set up the following configuration:

• Data:

  • Date: date

• Metric:

  • Operation: AVG

  • Aggregation column: raw_value

• Multiple series:

  • Split by: element_friendly_label

• Collapsible: True

In this widget, users can see the temporal variation of the weather metrics across 2016. They can either select the weather metric of interest by using the Category widget or leveraging the Time Series widget legend.

1. Add a Histogram widget to allow users inspect weather station elevation. Navigate to Widgets, select Histogram widget type and configure it as follows:

• Property: elevation

• Custom min. value: -61

• Formatting: 1.23

Step 4: Style your layer and add interactions

Now let's proceed to style our layer and add properties using aggregated properties.

1. First, let's style our weather station layer. Navigate to the Layer Panel and set the following styling configuration:

• Fill Color:

  • Property: AVG(normalized_value)

  • Palette: Sunset

  • Color Scale: Quantize

• Stroke:

  • Simple: #6b083f

  • Stroke weight: 0.8

1. Now, navigate to the Interactions tab, and enable Interactions for this layer. Select Click-type and Light with highlighted 1s value as the style. Now add the following properties with the corresponding label:

• ANY_VALUE(station_id) labelled as Station Id

• ANY_VALUE(State) labelled as State

• ANY_VALUE(Name) labelled as Name

• MODE(element_friendly_label) labelled as Weather Metric Type (Mode)

• AVG(raw_value) labelled as Weather Metric Value (Avg)

• AVG(normalized_value) labelled as Norm Weather Metric Value (Avg)

• ANY_VALUE(elevation) labelled as Elevation

1. Customize your legend by setting a label for the property used for styling. Simply rename it to "normalized weather metric value".

Now let's change the default basemap. You can do so by using the basemap menu located below the zoom control. Choose CARTO Basemap > Voyager.

Step 5: Filter sources via USA state boundaries

We want to allow users filtering weather stations by state. To achieve so, we'll add a dataset containing USA state boundaries and the state codes so we can use it to filter both the state boundary as well as the related stations.

1. To include USA State boundaries, let's add the source as a Custom SQL Query by:

• Add source from..

• Custom SQL Query (SQL)

• Choose CARTO Data Warehouse connection

• Add source

1. Open the SQL Editor, add the following query which retrieves the state code as well as the geometry boundary and click "Run".

1. A new layer will appear in the layer panel. Move the layer down just below the Weather Stations layer and rename it "USA State Boundaries"

2. Style your layer following the configuration below:

   • Stroke:

     1. Simple: #16084d

     2. Opacity: 30%

     3. Stroke weight: 2

1. Now, let's add which will allow us to upload the state codes to the parameter control so we can use them within placeholders of our custom SQL Query. Go to SQL Parameters button located in the top right of your source card.

1. Choose SQL Text Parameter and add the state codes using the state_code property available in the recently added source. Define your parameter name as State and the SQL name as {{state}}. Then, click "Add".

The parameter control will be added to the right side of your panel with a disabled status. Now let's use it in both of our queries.

1. Open the SQL Editor for the USA state boundaries and edit your query as below, including the WHERE statement. Then, click "Run".

1. Now, in the Weather Stations source, include the following statement in the existing query source. Then, click "Run".

1. Now, the parameter control should appear enabled and you can use the multi-selector to choose which boundaries and weather stations should be visible in the map. The parameter will take action both on your layers as well as the linked widgets.

Step 6: Add map description and share your map

1. Before sharing the map, let’s add a map description to provide context on the data source and guide users on how to interact with it. Click on the "i" icon in the top right of the header bar. Then copy and paste the following Markdown syntax into the description field.

1. Use the "Preview" option to see how your map will appear to others before publishing. Once you're satisfied, click the Share icon to distribute the map within your organization, to specific users, SSO groups, or publicly. Copy the link to share access.

Now, end-users will be able to explore historical weather statistics from USA weather stations across 2016, analyzing trends in temperature, precipitation, and snowfall with interactive widgets and time-series visualizations.

Context

In this tutorial, we're going to build a dynamic map dashboard that reveals the administrative layers of the United States, ranging from state-level down to zip codes. Ever curious about how to display different administrative regions at specific zoom levels? We're about to delve into that. Our journey will start by setting up a map in Builder that responds to zoom, transitioning smoothly from states to counties, and finally to zip codes, allowing users to access detailed statistics pertinent to each administrative area.

Using CARTO Builder, we'll craft a dashboard that not only informs but also engages users in exploring their regions of interest. Whether it's understanding demographic trends or pinpointing service locations, this guide will equip you with the knowledge to create an interactive map dashboard tailored to varying levels of administrative detail. Ready to unlock new levels of geographical insights? Let's dive in!

Steps To Reproduce

1. Access the Maps section from your CARTO Workspace using the Navigation menu and create a new Map using the button at the top right of the page. This will open the Builder in a new tab.

1. Now let's add USA States source to our Builder map. To add a source as a SQL query, follow the steps below:

   • Select the Add source from button at the bottom left on the page.

   • Select Custom Query (SQL) and then Type your own query under the CARTO Data Warehouse connection.

   • Click on the Add Source button.

The SQL Editor panel will be opened.

To add USA States, run the query below:

1. A map layer is automatically added from your SQL Query source. Rename it to 'USA States'.

Now let's add the remaining sources following Step 2 to add USA Counties, USA Zip Codes and USA Census Tracks.

1. Add USA Counties as a SQL query source using the below query. Once the layer is added, rename it to 'USA Counties'.

1. Add USA Zip Codes as a SQL query source using the below query. Once the layer is added, rename it to 'USA Zip Codes'.

1. Finally, let's add USA Census Tracks as a SQL query source using the below query. Once the layer is added, rename it to 'USA Census Tracts'.

1. Next in our tutorial, after adding the administrative layers of the USA to our map, we'll set specific zoom level ranges for each layer. This step will optimize our map's clarity and usability, allowing users to see States, Counties, Zip Codes, and Census Tracts at the most appropriate zoom levels. Set the zoom level visibility in the Layer panel as follows:

   • USA States: 0 - 3

   • USA Counties: 4 - 8

   • USA Zip Codes: 9 - 11

   • USA Census Tracts: 12 - 21

With the zoom level visualization configured for each layer, our next step is to customize the dashboard for enhanced user insights. Our focus will be on understanding the population distribution across each administrative region of the USA.

1. To achieve this, we will style our layers – USA States, Counties, Zip Codes, and Census Tracts – based on the 'Total_Pop' variable. This approach ensures users can easily grasp the spatial population distribution as they navigate and zoom in on the map. Let's set up the Fill Color for all four layers to effectively represent population data as follows:

   • Color based on: Total_Pop

   • Palette Steps: 4

   • Palette Name: ColorBrewer BuGn 4

   • Data Classification Method: Quantile

1. Now let's set the Stroke Color to Hex Code #344c3a for all four layers.

1. Set the map title to 'USA Population Distribution'.

1. Now, let's add some Widgets to provide users with insights form the data. First, let's add a Formula Widget linked to USA Census Tracts source with the following configuration:

   • Operation method: SUM

   • Variable: Total_Pop

   • Formatting: 12.3k

   • Markdown note: Total population (2014) by Viewport for Census Tracts layer

You can check how the widget is updated as you move to the map. You can also make use of our Feature Selection Tool to select a custom area to gather the population that intersects with that specific area.

1. We will add a second Formula Widget linked to USA Census Tracts source with the following configuration, to display the unemployment rate:

   • Operation method: AVG

   • Variable: Unemp_rate

   • Formatting: 12.35%

   • Markdown note: Unemployment rate (2014) by Viewport for Census Tracts layer

1. The last widget we will add to our dashboard will be a Category widget linked to USA States layer. It will be a global widget, displaying the total population by state to provide users with stats but it won't interact with the viewport extent and the cross-filtering capability will be disabled. To configure this widget, follow the below steps:

   • Operation method: SUM

   • Source variable: name

   • Aggregation column: Total_Pop

   • Markdown note: Total population by state (2014) for States layer. Please note this widget does not interact with the viewport extent and cannot be filtered.

   • Behaviour mode: Global

   • Cross-filtering: Disabled

1. Enable Interactions for the relevant layers. To do so, activate Interaction feature for each layer and add the desired attributions. On this occasion, we will select interaction Click mode using Light type and add just the relevant information with the renamed label. Repeat this process for the rest of the layers.

1. In the Legend tab, under 'More legend options', set the legend to open when loading the map.

1. Before publishing our map, let's add a map description so users can have more information about it while reviewing the map for the first time.

1. We can make the map public and share it online with our colleagues. For more details, see .

1. The final map should look something similar to the below:

Polyfill a set of polygons with H3 indexes

This example demonstrates how to use Workflows to generate an H3 grid from a set of polygons.

Aggregate point data into a Quadbin grid

This example demonstrates how to use Workflows to aggregate data from a set of points into a grid using the Quadbin spatial index.

Aggregate point data into an H3 grid

This example demonstrates how to use Workflows to aggregate data from a set of points into a grid using the H3 spatial index.

Use H3 KRings to define areas of influence

This example demonstrates how to use Workflows to generate areas of influence from a set of points, using the KRing functionality on the Spatial Index category of componentes. In this case using H3.

Flood Risk Assessment

Many government agencies such as FEMA in the United States, provide flood zone data for long term flooding risk, but what about areas that may be prone to flash floods or wildfire? This analysis takes synthetic policy data in Florida and analyzes it for Flash Flood risk using historic storms and historic fires, along with current weather warnings.

Assess the damage of a volcano eruption in the island of La Palma

This example demonstrates how an insurance company could use Workflows to assess the number of people and the value of the properties affected by a volcano eruption, in the spanish island of La Palma. It takes into account the actual lava flow, but also, separately, the surrounding area.

Insurance Underwriting

Underwriting or reinsuring a home or property insurance combines many different factors about the property but also the location where the property sits. While nationwide datasets exist for analysis like this such as the national FEMA risk index, other datasets like crime or emergency facilities are often shared by municipalities.

This workflow shows how you can combine many different data layers to make a spatial determination about a property.