Tips for geospatial analysis
Working with cartodb_id
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.
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.
ST_Centroid finds the centroid of any PostGIS geometry, turning say, a POLYGON into a POINT.
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.
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.
Here we buffer our point geometry, then translate the result to EPSG:3857 and finally, report it in a column called
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
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.
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.
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 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.
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.