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 SQL check out this Map Academy course.
RECORDS AT A POINT
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.
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.
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.
In this statement, we extract the longitude and latitude from geometries using the
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.
Note: The ORDER BY clause includes the
<-> distance operator as a PostGIS bounding box index. This operator only returns sorted index results when it is combined with the ORDER BY clause, and a defined LIMIT, drastically improving performance. For more details, see Indexed Nearest Neighbour Search in PostGIS.
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!