Tips for Geospatial Analysis

Learn how to work with geometries, centroids, buffers, areas and more!

Working with cartodb_id

The cartodb_id column is automatically added to every CARTO table and serves as a unique numeric key for each row. This identifier persists even if rows are deleted and enables mapping, infowindows, and other functionality.

SELECT * FROM {table_name} WHERE cartodb_id = 1

Working with the_geom

The primary geometry column in CARTO tables is the_geom, which stores all geospatial data from uploaded files in WGS 84 projection (EPSG:4326). The column uses a spatial index for efficient querying and is stored in well-known binary format. Convert it using ST_AsText or ST_AsGeoJSON for viewing, or transform geometries as needed.

Polygon Centroid

SELECT ST_Centroid(the_geom) FROM {table_name}

Converts polygon geometries into their center points.

Point Buffer

SELECT ST_Buffer(the_geom, 0.001) FROM {table_name}

Creates a buffer around any geometry by a specified distance, converting points into polygons.

About the_geom_webmercator

CARTO uses the_geom_webmercator (Web Mercator projection, EPSG:3857) to optimize tile rendering. When running analyses, wrap results in ST_Transform and name the output column the_geom_webmercator for proper map display.

SELECT cartodb_id, ST_Transform(ST_Buffer(the_geom, 0.001), 3857) as the_geom_webmercator FROM {table_name}

Creating a Point from Latitude and Longitude

Use the CDB_LatLng helper function to convert latitude and longitude values into geometries:

SELECT CDB_LatLng(22.24244, -31.23543) as the_geom

Note: PostGIS expects longitude first, but CARTO’s function expects latitude first.

Calculate Area of a Polygon

Cast geometries to geography type to obtain measurements in meters rather than coordinate system units:

SELECT ST_Area(the_geom::geography) as area FROM {table_name}

Spatial Intersection of Two Tables

Join tables based on geometric relationships using ST_Intersects:

SELECT {table_name_1}.cartodb_id, {table_name_2}.the_geom
FROM {table_name_1}, {table_name_2}
WHERE ST_Intersects({table_name_1}.the_geom, {table_name_2}.the_geom)

Measuring Distance

Use geography casting with ST_Distance to measure distances in meters:

SELECT ST_Distance(
    the_geom::geography,
    ST_SetSRID(ST_Point(-74.0064, 40.7142), 4326)::geography
) as area
FROM {table_name}