Running queries from Builder

Once you have created your BigQuery connection in the CARTO Workspace (see Getting Access for details), you can create custom SQL layers in Builder that make use of the Analytics Toolbox for BigQuery.

The Analytics Toolbox functions are available from the carto-un and carto-un-eu BigQuery projects. These projects are deployed in the US and EU multi-regions, respectively, and you may choose one or the other depending on the location of your data.

To get started, let’s run a simple example query to cluster a set of points using the ST_CLUSTERKMEANS function.

1. Click on the Add source from button in Builder, that can be found at the bottom left of the screen.

   
2. Select the second tab Custom Query (SQL) and pick the BigQuery or CARTO Data Warehouse connection that you will use to run the query.

   
3. Click on Add source. A SQL console will be displayed.

   
4. Copy and paste the following query:

with clustered_points AS
(
    SELECT `carto-un`.carto.ST_CLUSTERKMEANS(ARRAY_AGG(geom ignore nulls), 6) AS cluster_arr
    FROM carto-demo-data.demo_tables.sample_customer_home_locations
)
SELECT cluster_element.cluster, cluster_element.geom AS geom FROM clustered_points, UNNEST(cluster_arr) AS cluster_element;

with clustered_points AS
(
    SELECT `carto-un-eu`.carto.ST_CLUSTERKMEANS(ARRAY_AGG(geom ignore nulls), 6) AS cluster_arr
    FROM carto-demo-data.demo_tables.sample_customer_home_locations
)
SELECT cluster_element.cluster, cluster_element.geom AS geom FROM clustered_points, UNNEST(cluster_arr) AS cluster_element;

with clustered_points AS
(
    SELECT carto.ST_CLUSTERKMEANS(ARRAY_AGG(geom ignore nulls), 6) AS cluster_arr
    FROM carto-demo-data.demo_tables.sample_customer_home_locations
)
SELECT cluster_element.cluster, cluster_element.geom AS geom FROM clustered_points, UNNEST(cluster_arr) AS cluster_element;

1. Run the query. This query computes five clusters from the points of the sample_customer_home_locations table. As a result, each point is assigned a cluster ID.

2. Style the layer by the cluster attribute.

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