Your search returned no results

Try another search term or view Stack Exchange, where we have active conversations with Developers about GIS.
    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

    Geospatial Analysis

    Working with cartodb_id

    The column, cartodb_id, is added to every CARTO table. It is used internally and externally for mapping, infowindows, and a few other things. You can use this column also as a numeric key for every row in your table. Each number, within a table, is a unique reference to a row. If you drop a row, the cartodb_id for that row will not be used again.


    SELECT * FROM {table_name} WHERE cartodb_id = 1

    Working with the_geom

    The primary geometry column in any CARTO table is called, the_geom. When you upload a SHP file, or any other supported file, any geospatial data ends up in this column. The the_geom column is projected in WGS 84 (EPSG:4326), and is built with a spatial index for fast querying and sorting. Geometries are stored in the database in a format called well-known binary. This is useful for many things, but if you want to view your data, it is handy to convert the_geom using ST_AsText or ST_AsGeoJSON. Sometimes it can be useful to turn polygons into points or points into polygons. There are many ways to do this, here are a couple.


    SELECT ST_Centroid(the_geom) FROM {table_name}

    ST_Centroid finds the centroid of any PostGIS geometry, turning say, a POLYGON into a POINT.


    SELECT ST_Buffer(the_geom,0.001) FROM {table_name}

    ST_Buffer pads the outer edge of any geometry by some variable (in this case 0.001). If you add a buffer to POINT you will get a POLYGON.

    About the_geom_webmercator

    CARTO uses an invisible column called, the_geom_webmercator, to speed up the rendering of tiles for our mapping services. We are working to deprecate the use of this column, but it will take some time. For now, if you want to run an analysis on your data and then see the result in the Map tab, you need to have a column called the_geom_webmercator in the result. The the_geom_webmercator column is the same geometry as your the_geom column but projected in Web Mercator (EPSG:3857). Any operation you run on the_geom, can be wrapped in a ST_Transform function to reproject it. If you tell PostgreSQL to call the result of that reprojection, the_geom_webmercator, the Maps API and tiler will work perfectly.


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

    Here we buffer our point geometry, then translate the result to EPSG:3857 and finally, report it in a column called the_geom_webmercator.

    Creating a Point from Latitude and Longitude

    Often times you will want to turn a numerical latitude and longitude into a projected geometry in PostGIS. This can be handy for geospatial queries, or simply for storing and updating your data. To do this, you will first have to make a POINT and second, set its projection SRID.


    SELECT ST_SetSRID(ST_Point(-31.23543, 22.24244),4326) as the_geom FROM {table_name}

    First, we created a point. Remember, PostGIS always expects longitude followed by latitude. Next, we use the ST_SetSRID function to apply EPSG:4326 to our new POINT.

    Calculate Area of a Polygon

    Knowing the size of a polygon can be very useful. PostGIS works in coordinate systems, so anytime you calculate distances and sizes in PostGIS, be aware that it may be in the units of the coordinate system. You can use geography types to have PostGIS quickly convert those results into more familiar meters.


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

    First, we recast the geometry to a geography type, this lets us calculate area in meters. Next, we run the ST_Area function to return the size of the polygon.

    Spatial Intersection of Two Tables

    Very similar to the JOIN performed above, spatial intersections can be used to join data based on spatial conditions such as overlap, exclusion, or contains.


    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)

    This use of ST_Intersects allows you to JOIN the data from two tables at each point where the geometry of the first intersects the geometry of the second. This is a geometric variation of the common join in PostgreSQL.

    Measuring Distance

    Measuring distance has the same consideration as measuring size: knowing what units the measurement is in. You can use the same geography typing trick as we used above to measure area. Then you just have to use a simple PostGIS function, ST_Distance, to get a distance in meters.


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

    Here, we measure the distance from our geometry, the_geom, and a geometry we make on the fly, in this case a point in New York City.