Running queries from Builder

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

To get started, let’s run a simple example query to subdivide the area into Quadbin grid cells using the QUADBIN_POLYFILL function from the quadbin module.

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 Postgres connection that you will use to run the query. Please make sure this connection has access to the Analytics Toolbox database.

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

   
4. Copy and paste the following query:

   WITH data AS(
   select 'New York' as city, ST_POINT(-74.00597, 40.71427) as geom 
   union all select 'Boston', ST_POINT(-71.05977, 42.35843) 
   union all select 'Philadelphia',ST_POINT(-75.16379, 39.95233) 
   union all select 'Washington',ST_POINT(-77.03637, 38.89511)
   )
   SELECT carto.QUADBIN_FROMGEOGPOINT((geom),9) as quadbin from data

   You can also take advantage of the function directly from a table from your database and combine it with other functions, such as the QUADBIN_BOUNDARY function, to get the geography boundary for a given quadbin:

   WITH data as (SELECT geom
   FROM mydatabase.myschema.table),
   quadbins as (
   SELECT carto.QUADBIN_POLYFILL(geom
   , 14) quadbins_polyfill from data
   )
   SELECT carto.QUADBIN_BOUNDARY(unnest(quadbins_polyfill)) as geom from quadbins

5. Run the first query. In this example we will showcase how to get the quadbin representationat at resolution level 9 for the geographic coordinates of four US cities dusing the QUADBIN_POLYFILL function.

Try executing any of the queries included in every function definition in the SQL Reference.