# Tips for 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.

#### CARTODB_ID

## 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

`ST_Centroid`

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

#### POINT BUFFER

`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

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

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

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

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.