Analytics Toolbox for Redshift

Analytics Toolbox for Redshift

Running queries from Builder

Once you have created your Redshift 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 Redshift.

To get started, let’s run a simple example query to compute the paths that interconnect a set of points using the ST_GREATCIRCLE function from the transformations module.

  1. Click on the Add source from button in Builder, that can be found at the bottom left of the screen.
Choosing a connection
  1. Select the second tab Custom Query (SQL) and pick the Redshift connection that you will use to run the query. Please make sure this connection has access to the Analytics Toolbox database.
Picking a connection to run a custom query
  1. Click on Add source. A SQL console will be displayed.
Picking a connection to run a custom query
  1. Copy and paste the following query:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
WITH data AS(
   select  'JKF' as iata, 'John_F_Kennedy_International_Airport' as name, ST_POINT(-73.77890, 40.63980) as geom 
   union all select 'LAX','Los_Angeles_International_Airport', ST_POINT(-118.40800,33.94250) 
   union all select 'SEA','Seattle_Tacoma_International_Airport',ST_POINT(-122.30900,47.44900) 
   union all select 'MIA','Miami_International_Airport',ST_POINT(-80.29060,25.79320)
)
SELECT t1.iata as iata1, t2.iata as iata2, carto.ST_GREATCIRCLE(t1.geom, t2.geom, 25) AS geom
FROM data AS t1
CROSS JOIN data AS t2
WHERE t1.iata != t2.iata

You can also take advantage of this function directly from a table from your database:

1
2
3
4
5
6
7
8
WITH data AS(
    SELECT *
    FROM mydatabase.myschema.table
)
SELECT t1.column as column1, t2.column as column2, carto.ST_GREATCIRCLE(t1.geom, t2.geom, 25) AS geom
FROM data AS t1
CROSS JOIN data AS t2
WHERE t1.column != t2.column
  1. Run the first query. In this example we will showcase how easily we can compute all the paths that interconnect the main four US airports using the Analytics Toolbox.
Picking a connection to run a custom query

This query first creates all the possible combinations between airports and then generates the paths between them using the ST_GREATCIRCLE function. The resulting paths contain 25 points, but you can set the number of points in order to make the lines smoother if needed.