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.
- 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 geomunion 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 dataYou can also take advantage of the function directly from a table from your database and combine it with other functions, such as the
QUADBIN_BOUNDARYfunction, to get the geography boundary for a given quadbin:WITH data as (SELECT geomFROM 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