CARTO Functions

SQL functions to geocode, create custom grids and get statistical data in CARTO.

While developing CARTO, we have run into many cases where we need specialized SQL functions to make the most of the CARTO stack. In those cases, we have written the functions using PL/pgSQL and make them easily available for everyone to use. These functions act very much like all other PostgreSQL/PostGIS functions you have used, but they are specific to CARTO.

Basic Functions

Sometimes we see SQL run so much that it seems to make sense to provide a simplified wrapper for the method.

WGS84 POINT GEOMETRY

SELECT CDB_LatLng(float, float)

There are a few ways to make a WGS84 point geometry using PostGIS, all of them take a lot of keystrokes. We decided to wrap it up in a very simple function, where you provide very simply, a float latitude value and a float longitude value. The function returns a valid, projected, point geometry.

Grid Visualization Functions

CARTO makes use of several functions to create density grids of point data. Each of the functions below can be used as part of that workflow or independently.

MAKE A HEXAGON

SELECT CDB_MakeHexagon(point, side)

Return an hexagon geometry from a given center (point) and side (or maximal radius) as a float.

A HEXAGON GRID

SELECT CDB_HexagonGrid(polygon, side) AS the_geom FROM table_name

The CDB_HexagonGrid takes a geometry that describes the coverage area of your grid (a polygon) and the size of each hexagon cell. The size is in the same units of your projected geometry, so for example, if you use the_geom and so WGS84, your size will be in degrees. An optional third parameter can be passed to the function, a geometry for the origin of the grid to be placed.

A RECTANGLE GRID

SELECT CDB_RectangleGrid(polygon, width, height)

Much like the CDB_HexagonGrid above, here you can create a mesh grid that covers an area. The function takes three parameters: the area you want your grid to cover (a polygon), the width of your cells, and height of your cells. An optional third parameter can be passed to the function, a geometry for the origin of the grid to be placed.

Webmercator and TMS Functions

Webmercator is the common projection of Leaflet, and a host of other online mapping tools. While those tools can pretty effortlessly handle latitude and longitude values, what you are actually seeing on map tiles is different. We manage several functions to make measuring and creating tiles simple.

TILE EXTENT

SELECT CDB_XYZ_Extent(x, y, z)

Using the XYZ extent function, you can pass any tile coordinate in the form of integer x, y, and z values and receive back the extent of that tile in the form of a polygon. The extent is in webmercator.

TILE RESOLUTION

SELECT CDB_XYZ_Resolution(z)

It can be useful to know the resolution of tiles presented on your maps. Internally, we use this function, which takes any Z value, and returns the pixel resolution of that tile.

SAFELY TRANSFORM TO WEBMERCATOR

SELECT CDB_TransformToWebmercator(geometry)

Transformations happen all the time on CARTO. In the process of developing the tool, it became apparent that the more common function, ST_Transform, could result in errors. In the case of CARTO, transformations happen on a copy of a user’s data, so a lossy transformation was preferrable to an error producing function. The above function allows us to consistently transform geometries behind the scenes in CARTO.

Statistical Functions

For the visualization of choropleths, CARTO needed a few different ways to bin numerical data in a single column of a CARTO table. The various binning methods are described below and implemented as part of the visualization wizard.

JENKS BINS

SELECT CDB_JenksBins(array_agg(numeric), integer)

The Jenks Natural Breaks optimization uses a method of reducing the deviations within each bin while increasing the average deviation between bins. This method is great for patchy or non-evenly distributed data. Here, we collect a numeric column into a single array using the array_agg method, and also declare the number of bins we want to create. The result is an array with the upper bound for each bin.

QUANTILE BINS

SELECT CDB_QuantileBins(array_agg(numeric), integer)

Quantile bins very simply attempt to bin data into groups that each contain the same number of total records. Like the above, you provide an array of numerical values and an integer of the number of bins you wish to create. The result is an array with the upper bounds of each bin.

HEADS/TAILS BINS

SELECT CDB_HeadsTailsBins(array_agg(numeric), integer)

The Heads/Tails (PDF) method breaks data in a way to best visualize data falling along a long-tail distribution. Like the above, you provide an array of numerical values and an integer of the number of bins you wish to create. The result is an array with the upper bounds of each bin.