Your search returned no results

Try another search term or view Stack Exchange, where we have active conversations with Developers about GIS.

    http://gis.stackexchange.com/
    Support channel in Stack Exchange

    Thousands of developers around the world have helped make CartoDB a success, and we have active conversations with them about GIS in Stack Exchange.

    Go to Stack Exchange

    Advanced Analysis

    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

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

    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.

    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!