Tips for geospatial analysis

Learn how to work with geometries, centroids, buffers, areas and more!

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.

CARTODB_ID

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.

POLYGON CENTROID

SELECT ST_Centroid(the_geom) FROM {table_name}

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

POINT BUFFER

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.

THE_GEOM_WEBMERCATOR

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 using only PostGIS functions you’d first have to make a POINT and second, set its projection SRID, but thanks to CARTO helper functions you can do that in a single shot using CDB_LatLng.

CREATE POINT

SELECT CDB_LatLng(22.24244, -31.23543) as the_geom

Remember, PostGIS always expects longitude followed by latitude, but CARTO function expects the opposite, first a latitude and then a longitude.

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.

POLYGON SIZE

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.

GEOGRAPHIC JOINS

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.

DISTANCE

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.