Your search returned no results

Try another search term or view Stack Exchange, where we have active conversations with Developers about GIS.

    http://gis.stackexchange.com/
    Support channel in Stack Exchange

    Thousands of developers around the world have helped make CartoDB a success, and we have active conversations with them about GIS in Stack Exchange.

    Go to Stack Exchange

    Back-End Data Overviews

    This section describes how CARTO optimizes datasets and visualizations, by using “overview” representations of back-end data. See the following tips for how data is internally processed for different APIs, and through CARTO Builder.

    Data Overviews in CARTO Builder

    Large datasets, that contain over 500,000 points in a table, often need some optimizations to improve rendering performance. To solve this, CARTO creates “overviews”, which are simplified representations of the data. These data overviews are applied to a range of zoom levels for your visualizations, depending on the number of points per tile.

    Note: The following current behavior is supported for overview representations of data:

    • Only point geometries are supported
    • Overview representations can be managed through the SQL API
    • Overview representations apply an additional _feature_count column to your table, which specifies if multiple features have been grouped into a single marker. See Data Overview Tips for details

    Performance Optimization

    Overviews are used to speed up map rendering, with the exception of when analyses are applied to map layers. If you apply analyses to a large dataset, and the analysis generates a large dataset as the result, map rendering is not optimized and the duration time for processing may be longer.

    When you are exploring data with widgets, overviews are also used to optimize the selected results. In some cases, depending on the parameters of the widget (e.g. maximum/minimum aggregation in formula widgets), optimization is not possible, and the entire dataset is processed using extended computation time.

    Data Overviews in the Import API

    Similar to CARTO Builder, when a dataset with more than 500,000 points is imported through the Import API, overview representations of data are automatically created.

    Managing Data Overviews

    Overview representation tables can be managed through SQL API requests, included in the PostgreSQL extension for CARTO. The following examples describe some of the common functions that you might use to manage data overviews.

    Recreate a Data Overviews Table

    To recreate the data overviews for a table, use the following cURL request and use your account username, api_key, and tablename.

    1) Remove the existing data overviews with CDB_DropOverviewsfunction

    curl "http://{username}.carto.com/api/v2/sql?api_key={api_key}&q=SELECT%20CDB_DropOverviews('{tablename}');"
    

    2) Create new data overviews with the CDB_CreateOverviews function

    Once the existing overviews is dropped, you can create new data overviews. It is recommended to apply a SQL Batch Query to create new overviews, as this may involve long-running CPU processing times:

    curl -X POST -H "Content-Type: application/json" -d '{ 
    "query": "SELECT CDB_CreateOverviews('"'"'{tablename}'"'"')",
      "api_key": "{api_key}"
    }' "https://{username}.carto.com/api/v2/sql/job"
    

    Tip: To check the progress of the job, note the job_id from the command:

    {"job_id":"{jobid}","user":"{username}","status":"pending","query":"SELECT CDB_CreateOverviews({tablename})","created_at":"2016-04-28T13:50:43.656Z","updated_at":"2016-04-28T13:50:43.656Z"}
    

    Use this job_id to check the status of the job:

    curl -X GET "http://{username}.carto.com/api/v2/sql/job/{jobid}?api_key={apikey}"
    

    3) Check which data overviews appear for your table

    curl "http://{username}.carto.com/api/v2/sql?api_key={apikey}&q=SELECT%20CDB_Overviews('{tablename}'::regclass);"
    

    For a complete list of functions for managing data overviews, see https://github.com/CartoDB/cartodb-postgresql/blob/master/doc/CDB_Overviews.md.

    Data Overview Tips

    When overview representations of data are created (for datasets containing more than 500,000 points), note these other tips about how they are internally processed.

    • Zoom levels: Data overviews are used to represent large data in smaller, grouped forms. For each dataset, data overviews are applied to a range of zoom levels for your visualizations. Any zoom level higher than the overviews zoom level will display your original base table data, not the overviews data. The maximum zoom level for overviews data is also is dependent on the feature density of the dataset, and may vary from dataset to dataset

      Note: The same number of features can be spatially distributed in different ways, leading to to various zoom levels for datasets of equal size

    • _feature_count column: When overviews are created for a dataset, the _feature_count column is automatically added the your table. This column specifies the feature shown on a map, and displays how many records from the original table are represented as a single, grouped element

      • When the original table is used, the _feature_count column will always contain the value of 1
      • When the overview representation of data is used, the _feature_count column may contain a value greater than one
      • In the case when a map element represents a grouping of multiple records, its attributes are aggregated from the attributes of the original elements

      Note: If the _feature_count column is used in a Choropleth map, CARTO Builder is unable to calculate proper ranges for the visualization (due to the column containing only the ‘1’ value). As a solution, you can manually edit the CartoCSS to specify appropriate ranges for the Choropleth visualization. You will also have to manually edit the Legend.

    • column aggregations: See the following general notes about how data columns are aggregated when multiple values are grouped in an overview table:

      • The values for numeric columns are averaged
      • The values for textual columns retain its value when few distinct values are present, otherwise an asterisk appears, indicating that there is a large number of distinct values
      • For “category” columns, which indicate that there are fewer than 20 distinct values for the dataset, the mode of the value is shown with grouped features

      Tip: You can obtain the sum of an attribute, which has been averaged, by multiplying it by the _feature_count.

    • Pop-Up Hover values: When configuring a map in CARTO Builder with overview representations of data, the values shown in the hover pop-up information window correspond to the grouped features. However, when clicking on a point in the map, the data is retrieved from the original base table and corresponds to a single element

    • automatic updates: Overview representations of data are not automatically updated if the original, base table changes. If a record is added, deleted, or changed in the original dataset table, the change will not be reflected at the grouped data overviews zoom level. When significant changes have been made to the original table, you can manually update the correlated overviews representations of data through the SQL API, by using the SELECT CDB_DropOverviews(table); SELECT CDB_CreateOverviews(table); function. Similarly, if you change the schema of a table with overviews by adding or removing columns, you will need to manually recreate the data overviews. See the Managing Data Overviews section for an example

    Note: The overviews representations of data are specific to vector or point data, and should not be confused with raster overviews of data - which are similarly created for raster datasets.