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.
Click on the Add source from button in Builder, that can be found at the bottom left of the screen.
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.
Click on Add source. A SQL console will be displayed.
Copy and paste the following query:
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:
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
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.
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.
Last updated
Was this helpful?