CARTO Functions

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

While developing CARTO, specialized SQL functions were created using PL/pgSQL to enhance the platform’s capabilities. These functions operate similarly to standard PostgreSQL/PostGIS functions but are specifically designed for CARTO.

Basic Functions

WGS84 Point Geometry

SELECT CDB_LatLng(float, float)

This function simplifies WGS84 point geometry creation. Users provide latitude and longitude as float values, and it returns a valid, projected point geometry.

Grid Visualization Functions

CARTO offers several functions for creating density grids from point data.

Make a Hexagon

SELECT CDB_MakeHexagon(point, side)

Returns a hexagon geometry from a specified center point and side length (maximum radius) as a float.

Hexagon Grid

SELECT CDB_HexagonGrid(polygon, side) AS the_geom FROM table_name

Creates a hexagon grid covering a specified polygon area. The size parameter uses the same units as the projected geometry. An optional third parameter defines the grid’s origin point.

Rectangle Grid

SELECT CDB_RectangleGrid(polygon, width, height)

Generates a mesh grid covering an area, requiring three parameters: the coverage polygon, cell width, and cell height. An optional origin geometry parameter is available.

Webmercator and TMS Functions

Functions for managing tile-based web mapping operations.

Tile Extent

SELECT CDB_XYZ_Extent(x, y, z)

Converts tile coordinates (x, y, z integers) into polygon extent in webmercator projection.

Tile Resolution

SELECT CDB_XYZ_Resolution(z)

Returns the pixel resolution for a given Z tile value.

Safely Transform to Webmercator

SELECT CDB_TransformToWebmercator(geometry)

Provides reliable geometry transformation to webmercator, preventing errors through lossy transformation when necessary.

Statistical Functions

Methods for binning numerical data in choropleths.

Jenks Bins

SELECT CDB_JenksBins(array_agg(numeric), integer)

Implements Jenks Natural Breaks optimization to minimize deviations within bins while maximizing deviations between bins. Accepts an array of numeric values and desired bin count, returning upper bounds for each bin.

Quantile Bins

SELECT CDB_QuantileBins(array_agg(numeric), integer)

Distributes data into equal-sized groups. Requires a numeric array and bin count, returning upper bounds for each bin.

Heads/Tails Bins

SELECT CDB_HeadsTailsBins(array_agg(numeric), integer)

Optimizes visualization for long-tail distributions. Takes a numeric array and bin count, returning upper bounds for each bin.