CARTO User Manual

CARTO User Manual

Add source

When you open a map, the Layers tab will appear on the left side panel. There you can add data as layers to the map by clicking on Add source from, where you can access the contents from your existing data warehouse connections. If you haven’t added a data layer to the map yet, you will see the following page:

Add source to your map

Data source types

Aggregated grids

Based on Discrete Global Grid (DGG) systems, this kind of data source uses a spatial index (H3 or Quadbin) to reference each cell of the grid. Think of a spatial index as an id that always makes reference to the same portion of the surface on Earth.

  • this portion of the Earth is called a cell.
  • the shape of the cell depends on the type of index. For example, H3 uses hexagons; while Quadbin use squares.
  • the size of the cell depends on the resolution. The higher the resolution, the smaller the size of the cell.

DGG systems are hierarchical, which means that every cell contains a constant number of smaller cells at a higher resolution:

Source types H3

The above is an example of how each H3 cell is sub-divided into smaller cells at higher resolutions.

One of the advantages of working with spatial indexes is that operating with them in data warehouses is way more efficient and cost-effective than computing geometries. They are also smaller in size and help saving storage and reducing the volume of transfered data.

When working with DGGs, Builder will dynamically aggregate your data into cells at a meaningful resolution depending on the current map zoom level. See the animation below for an example:

This is what a table containing H3 indexes looks like, with some additional columns that contain aggregated socio-demographic data for each hexagon:

h3 population avg_rent
8a0c0036a49ffff 103.0 1344.56
8a0c002e4c0ffff 1093.0 2087.04
8a0c002e4caffff 209.0 3098.39

The h3 column contains the indexes for H3 cells at level 10. That’s what we call the native resolution of the data.

However, if you load the table in a Builder map and zoom out to a low zoom level, it will be shown at a lower resolution, which means we would actually be visualizing an aggregated version of our table. This aggregation will be generated on the fly, using SQL queries that are pushed from CARTO into the data warehouse where the table lives.

The above implies that hexagons will be aggregated into their parents: the bigger hexagons that contain them at a lower resolutions.

But data also needs to be aggregated, so Builder will always need you to pick an aggregation method for the data used in the map. This applies to all selectors where you can pick a property for cartography settings, pop-ups and widgets.

There are some performance and processing cost optimizations that should be applied to this kind of table. Find them in this section.

Simple features

Simple features are defined as a standard which specifies digital storage of geographical data (usually point, line or polygon) with both spatial and non-spatial attributes.

Most data warehouses support simple features through different data types, such as geometry or geography.

Simple features are widely spread and have been traditionally used by GIS software to store the shape and properties of phenomena that occur on the surface of the Earth.

CARTO supports simple features stored as geometry or geography in cloud data warehouses. There are different methods to load a data source that contains simple features in a Builder map. These methods ensure the most performance when rendering data on a map, and they’re selected automatically based on the type and size of the data source:

  • For small data sources, data can be fully loaded at once on the map.
  • For bigger data sources, and also those defined as arbitray SQL queries, data is loaded progressively via vector tiles. The data for these tiles is extracted by pushing down SQL queries to the data warehouse, and they’re are requested as you zoom in and out or pan the map.

Find more information about the different methods mentioned above in this section.

Add source from a connection

From the Layers tab, go to the Sources panel and click on Add source from…. A new dialog screen will open allowing you to select a table or a tileset from one of your connections and click on .

Add source to your map

Add source table

Once the process is finished, the table or tileset is included in the Builder map as a new layer. The map displays the basemap and the new layer on top. You can add additional layers, or start applying styling and analysis features.

Map created

Once you have added your datasets to the map, you can visualize the data table. Click on the three dots icon, select Show data table and your dataset table will be displayed.

Map source options view data table

By clicking the tree dots icon the Column Context menu will reveal additional options such as: Sort on this column, ascending or descending, Pin the column so you can freeze it in the first position, and copy column data.

Map table column

Once you have added your datasets to the map, you can always add a new layer or delete the source. Click on the three dots icon and select Add layer or Delete source. When you click the Delete quick action, a dialog will appear allowing you to confirm that you want to delete the selected data source and warning you that it will be affect the layers created with this source.

Map source options delete source

You can also rename a source by clicking on the “Rename” option.

Map source options view data table

Rename the source and press enter to save your changes.

Map source options rename source

Map source options rename source

Once the source is renamed, the new name will replace the old one in the data table or when we add a new widget.

Map source options rename source

Map source options rename source

Add source from a custom query

From the Layers tab, go to the Sources panel and click on Add source from…. A new dialog screen will open allowing you to create your own query or run a SQL analysis to data on your connection. Select an option and click Add source.

Add source custom query

Once the process is finished, the SQL Editor appears in the Builder interface, where you can type your query and then click Run when it is active to execute the query. Please make sure to use Fully Qualified Table names.

Map new sql panel

From the SQL Editor, you can easily toggle to full screen mode, minimize, maximize or close the panel.

Map sql panel enter full screen

Map sql panel minimize screen

Map sql panel close

Full screen mode allows you to easily work with long SQL queries. Once your query is executed, you can always restore to default screen by clicking on Exit full screen.

Map sql panel full screen

Also note that while typing the query, a label will indicate that the SQL Editor is in edit mode.

Map sqñ panel edited

For BigQuery data sources, when you enter a query in the SQL Editor, a query validator (dry-run) verifies the query syntax and provides an estimate of the number of bytes read by the query. You can check out this documentation page for more information.

If the query is valid, then a check mark automatically appears along with the amount of data that the query will process.

Map sql panel valid query(bq)

Map sql panel add query

If the query is invalid, then an exclamation point appears along with an error message (syntax error, permission issues, etc.).

Map sql panel syntax error(bq)

Map sqlm panel syntax error

When you click on Run, a timer will appear while the query is running informing you how long the query is taking until it’s finished. A label will also appear along indicating the connection being used to execute the query. In this example, the BigQuery connection named as custom_name.

Map sql panel run query

You can click on Cancel at any time to stop running the query. At this poing, the layer loading will also be cancelled.

Map add cancel query

If you keep the query running and it executes successfully, the table will be included in the Builder map tool as a layer.

Remember, when using running queries that return geometries, you should use an alias in the query to make sure the column that contains the geometry is called geom. For example:

1
SELECT population, geometry as geom FROM demographic_data

Use spatial indexes in custom queries

CARTO supports H3 and Quadbin spatial indexes. In order to render a map from a data source that contains a spatial index instead of a geometry, there are some nuances to take into account.

First, if you are going to type a query that returns an spatial index (H3 or Quadbin), you should use the spatial data type selector on your SQL Panel to select the type of data that you’re working with:

Spatial data type selector

  • If your query is going to return H3 indexes, select H3 and make sure the column that contains the H3 indexes is called h3. For example:
1
2
3
4
5
SELECT 
  carto.H3_FROMGEOGPOINT(geom, 10) as h3,
  count(*) as num_points
FROM 10M_points_table
GROUP BY h3
  • If your query is going to return Quadbin indexes, select Quadbin and make sure the column that contains the indexes is called quadbin. For example:
1
2
3
4
5
SELECT 
  carto.QUADBIN_FROMGEOGPOINT(geom, 15) as quadbin,
  count(*) as num_points
FROM 10M_points_table
GROUP BY quadbin

Learn more about using the CARTO Analytics Toolbox to work with spatial indexes here.

Also, when working with spatial indexes, there are a few important details to take into account in order to optimize performance and reduce the associated computing cost. Learn more about it here.

Create a table from query

Additionally, you can also persist the query as a table by clicking on Create table from query button that will be available when the query is successfully completed.

Map sql panel create table from query

Click on Create table from query button. A new dialog will open allowing you to create your table from query into the available connections. This interface will allow you to set the location and name of the output table. Click on Create table from query to continue or click on Cancel if you don’t want the changes to be applied.

Map sql modal create table from query

This option is only available for the same connection used for running the query. If you try to select a different location, a message will appear warning you that the connection cannot be changed in this process.

Map sql modal create table from query warning

Select the location and click on Create table from query.

Map sql modal create table from query OK

Once the process is finished, the table is included in the Builder map as a new layer. The map displays the basemap and the new layer on top. You can add additional layers, or start applying styling and analysis features.

Map sql panel table from query created

Custom queries using the Analytics Toolbox

You can also use the Analytics Toolbox functions in your custom SQL queries. For example, you can perform a simple clustering using the ST_CLUSTERKMEANS function by running this query from your CARTO Data Warehouse connection:

1
2
3
4
5
6
7
with clustered_points AS
(
    SELECT `carto-un`.clustering.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

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. By styling the layer by this cluster attribute, we get the following result:

Map add query

To learn more, please visit the Documentation page of the CARTO Analytics Toolbox for each provider:

Add source from a local or remote file

CARTO allows to create geospatial tables in an organization’s CARTO Data Warehouse, BigQuery connection and Snowflake connection, by importing files from your computer or via URL. Once a file is imported, the resulting table can be previewed in Data Explorer and used in Builder and external applications to create maps.

Supported formats

Currently, the import of CSV, KML, KMZ, TAB, GeoJSON, GeoPackage and Shapefiles (in a zip file) is supported. The size limit for a single import process is 512MB. Please get in touch with us if you need a higher limit.

For CSV files, CARTO will try and autodetect the geometry column or create the geometries from latitude/longitude columns. The supported column names are:

  • For geometry: geom,Geom,geometry,the_geom,wkt,wkb
  • For latitude: latitude,lat,Latitude
  • For longitude: longitude,lon,Lon,Longitude,lng,Lng

The expected delimiters are: comma (,), semi-colon (;) or a tabulation.

From the Layers tab, go to the Sources panel, click on Add source from… and select the Import file tab. A new dialog will open allowing you to import your data into the available connections.

Add source import file

You can upload your data through two different methods: Local or Remote.

  • Local

To import a local file, select the icon on the left.

Map import local

This interface will allow you to upload data from your computer. Once you have selected the file, click on Continue.

Map add source import file

Map select local file

The next screen will allow you to set the location and name of the output table. Once you have completed this configuration, click on Continue.

Map import select connection

Map import select file local continue

The last screen will show you a summary with the import details. Click on Add Source to confirm or click on Edit details if you want to edit anything before importing.

Map import add source

A new dialog will open informing you that the import may take a while to process and giving you the option to follow the status from a new dialogue box that appears at the top right corner of the screen.

Map importing file

Once the data has been imported, the dataset is included in the Builder map tool as a new layer. You can then add additional layers, or apply styling and analysis features.

Map imported file

  • Remote

To import a remote URL, select the icon on the right.

Map import remote

This interface will allow you to enter a supported URL file. Once you have entered the URL, click on Continue.

Map select remote

The next screen will allow you to set the location and name of the output table. Once you have completed this configuration, click on Continue.

Map import remote selected folder continue

The last screen will show you a summary with the import details. Click on Add Source to confirm or click on Edit details if you want to edit anything before importing.

Map import remote add source

A new dialog will open informing you that the import may take a while to process and that once the file has been imported, it will be added as a data source to the map.

Map importing remote file

Once the data has been imported, the dataset is included in the Builder map tool as a new layer. You can then add additional layers, or apply styling and analysis features.

Map imported remote file

Add source from Data Observatory

From the Layers tab, go to the Sources panel and click on Add source from…. Go to the “Data Observatory” tab. A new dialog screen will open allowing you to select your subscriptions or samples from one of your connections. Select a subscription or a sample and click on Add source.

Add source select a connection bq

Add source select a table

If you try to visualize a sample from a unavailable connection, a message will appear warning you that this sample is not available from that connection.

Add source select a table

To learn more about how to visualize your Data Observatory datasets in Builder, please visit its dedicated documentation page.