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. To learn more about spatial SQL check out this course.

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))

Here we get all records that intersect a point we create on the fly. We grab three columns here, take note that you can perform an query based on the_geom column, but still include the_geom_webmercator in the result so you can visualize it on the map easily.

Query Records in a Bounding Box

Like querying all records at a point, querying all records within a bounding box will be a fairly common task for many applications. It is another straight forward request 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)

Here, we use some of the same functions you have seen previously. The opperator, &&, always uses the bounding box of geometries, unlike ST_Intersects which uses the full shape of a geometry. This can greatly speed up your queries in cases where bounding box comparison is sufficient.

Join Data from Two Tables

Now that you have some SQL skills under your belt, let’s do another advanced query that you will use all the time: the ability to join data from two different tables through one single query.

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

The above example will return the columns, iso and value from the first table with a geometry from the second, joining rows based on the column iso matching from one table to the other. We used ISO here because it is a common use case, where you join new values from one table with the geometries in a second based on the shared ISO code value.

Sort Records by Distance to a Point

Here is a really fun use-case for CARTO: what are the 10 closest database entries to my current location? This is especially useful for mobile applications, but also for location-aware websites.

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

In this statement, we extract the longitude and latitude from geometries using the ST_X and ST_Y functions respectively. Then we are calculating the ST_Distance of each record to a POINT that we define on the fly. Finally you can see that we are ordering the results by the calculated distance in ASC (ascendent) and LIMITing to the closest 10 points.

Common Table Expression

The Common Table Expression (CTE) is a really useful tool for making SQL more readable. It has a drawback if you are not 100% sure what you are doing, it puts constraints on the query planner. Unlike nested queries, CTEs force the order of execution to follow the way you write you queries.

We strongly recommend not using CTE statements in your CARTO queries. This is especially true in maps, where the Maps API wraps your queries in further query statements to optimize the creation of tiles from your data. If the query planner cannot access the full statement, it cannot make it fast!