Tips for Advanced Analysis

Learn how to work with bbox and joins in SQL.

Get All Records at a Point

One of the most common geospatial queries web-services experience is one to find all records that occur at a given location. CARTO makes this very simple, with just a few parameters and the SQL API, you can quickly build the results of these queries into your application.

Records at a Point

SELECT
  cartodb_id,
  the_geom,
  the_geom_webmercator
FROM
  {table_name}
WHERE
  ST_Intersects( the_geom, cdb_latlng(40.7248057566452,-73.9967118782795))

This retrieves all records intersecting a point created dynamically. The query selects three columns, performing analysis on the_geom while including the_geom_webmercator in results for map visualization.

Query Records in a Bounding Box

Querying records within a bounding box is a common application requirement, easily accomplished using SQL.

Bounding Box

SELECT
  *
FROM
  {table_name}
WHERE
  the_geom && ST_SetSRID(ST_MakeBox2D(ST_Point(-73.9980, 40.726), ST_Point(-73.995, 40.723)), 4326)

The && operator uses geometry bounding boxes, unlike ST_Intersects which evaluates full shapes. This approach significantly improves query performance when bounding box comparison suffices.

Join Data from Two Tables

After mastering basic SQL, combining data from multiple tables through a single query becomes essential.

Table Joins

SELECT
  {table_name_1}.iso,
  {table_name_1}.value,
  {table_name_2}.the_geom
FROM
  {table_name_1},
  {table_name_2}
WHERE
  {table_name_1}.iso = {table_name_2}.iso

This example returns columns iso and value from the first table with geometry from the second, matching rows based on shared iso codes – a frequent pattern when merging values with geometric data.

Sort Records by Distance to a Point

A compelling use case: identifying the 10 nearest database entries to a specific location, particularly valuable for mobile and location-aware applications.

Distance Sort

SELECT
  ST_X(ST_Centroid(the_geom)) as longitude,
  ST_Y(ST_Centroid(the_geom)) as latitude,
  description,
  ST_Distance(the_geom::geography, ST_PointFromText('POINT(-73.999548 40.71954)', 4326)::geography) AS distance
FROM
  {table_name}
ORDER BY
  the_geom <-> ST_PointFromText('POINT(-73.999548 40.71954)', 4326)
LIMIT 10

The statement extracts longitude and latitude using ST_X and ST_Y functions, calculates distance to a dynamically defined point, and orders results by ascending distance with a limit of 10 closest points.

The <-> operator functions as a PostGIS bounding box index, improving performance when paired with ORDER BY and LIMIT clauses.

Common Table Expression

Common Table Expressions enhance SQL readability but constrain the query planner by forcing execution order.

It is strongly recommended not to use CTE statements in CARTO queries, particularly in maps where the Maps API wraps queries to optimize tile creation. When the query planner cannot access complete statements, it cannot optimize effectively.