# PostGIS in CARTO

CARTO is built on top of PostgreSQL using the PostGIS extension. This means that you have all the power of relational databases combined with hundreds of geospatial functions.

In this lesson, we will introduce several commonly used functions in PostGIS with the goal of extending your geospatial analysis of data within CARTO and show you some of the analysis you can do with your geospatial data.

Our goal with this lesson: Geospatially revisit blues musician birthplaces along Highway 61 by using important functions in PostGIS and SQL. Yep, this is a reference to the Bob Dylan album Highway 61 Revisited.

By the end of this lesson, you will be able to make this map:

If you haven’t gotten your feet wet with SQL in CARTO, first check out Lesson 1. This lesson relies exclusively on the CARTO Editor. If you’re not familiar with the Editor, first get started with Online Mapping for Beginners.

## Data

Mississippi portion of US Route 61

We’re going to use data derived from the Federal Highway Administration. Copy and paste the following URL into the CARTO Importer:

Birthplaces of Mississippi Blues Musicians

The second data set contains the coordinates of the birthplaces of a selection of Mississippi-born blues musicians as listed on Wikipedia.

## Show Buffers

As the name suggests, `ST_Buffer` takes a geometry as an argument and returns that geometry but with a buffer around it. It always returns a polygon. In its simplest usage, you can create a polygon centered on a point. The same applies to lines and polygons, but you always get out a polygon.

Like other PostGIS functions, it preserves the projection units. That is, if you put in a point in WGS 84, you’ll get out a polygon in WGS 84. For more about WGS 84, check out the last lesson’s section on measurement units.

The two examples below show how a point or line geometry can be transformed to a polygon geometry using `ST_Buffer`.

Function definitions

The function `ST_Buffer` takes a geometry such as a point as the first argument, and the radius for the buffer as the second argument. For more information, see the PostGIS documentation page for additional information and function definitions.

As was mentioned in the previous lesson, there is a hidden column in CARTO called `the_geom_webmercator` which is responsible for making the data appear on your map in the correct location. It is usually in the projection Web Mercator, but it doesn’t have to be. To use it with other projections, just project the data and alias the column as `the_geom_webmercator`.

For our work here, we will be needing to work in distances on the earth’s surface such as meters. Therefore, we need to cast our geometry in `the_geom` to geography, buffer off of that in meters, and then transform to Mercator to display along with our Web Mercator-projected basemap.

To visualize a 25 mile corridor around U.S. Route 61, put the following command into the CARTO SQL editor. Go to MAP VIEW to see the result of the query.

This statement draws a 25 mile buffer around our road segment, the Mississippi portion of US Route 61. Note that we convert from meters to miles with the conversion 1609 meters ≈ 1 mile.

Also notice that the `cartodb_id` column was also selected so that interactivity (click events, hovers) can be enabled.

To extend this to see which musicians are in the buffer, create a multilayered map by clicking on “+ Add Layer” at the top of the CARTO Sidebar. Select the dataset `mississippi_blues_musicians`. Finally, style it to your liking.

There we go! We have a visual of what’s going on to aid in our spatial analysis of the data.

### Find Musicians within the Buffer

Now that we’ve visualized our buffer, let’s find which blues musicians were born within that buffer. While it might seem like the following statement would work well in a `WHERE` clause, it is very inefficient.

`ST_Intersects()` returns true/false depending on whether or not two entries have an overlap, which is why it is good for a `WHERE` condition.

The better choice is to use `ST_DWithin()`.

Function definition

Given two columns of geospatial data, you can use `ST_DWithin` to find out which points are within a given distance of each other. Notice that the return value is a boolean, so it is a good function for the `ON` part of `JOIN` or a `WHERE` part of SQL statements. If you’re not familiar with `JOIN`s, check out a great discussion at Coding Horror.

We’ll continue our usage of `the_geom` cast into geography type, so we will use the middle definition for `ST_DWithin` above.

The SQL query we’ll run is the following:

This command grabs all locations, names, cities, and cartodb_ids from rows in the `mississippi_blues_musicians` table that are within a 25 mile distance of the highway. Recall from Lesson 1 that the `AS` keyword lets you define table names and column names with a more concise or less confusing alias.

The embedded map below has the bottom layer as the buffered highway, and the top layer as the SQL statement applied to the `mississippi_blues_musicians` table to only show the musicians within our buffer.

Let’s take a different tack now and calculate the distance each musician is from the highway. In Lesson 1 of this course, we found the distance between a point for each row of our table. Now we have points and a line. Luckily, PostGIS is flexible and can calculate the distance to the closest point on a line.

Notice that we’re rounding up with the `ceil()` function, and dividing the distance by 1609 meters/mile to convert to miles.

You could visualize the data in this newly created table by making a choropleth on the column `d`. Since we already did something very similar to that in Lesson 1, we’ll move on to new functionality.

### Visualizing Lines from Musicians to the Road

`ST_MakeLine()` returns a line geometry given two or more points. When working on a collection of points, it returns the path of connect-the-dot points ordered by cartodb_id. Check out the documentation for more on this.

In our case, we’re interested in drawing as-the-crow-flies lines of any one musician to the highway’s nearest respective point. Because the highway is a line and the musician birthplaces are points, we need to find a way to get the nearest point to the musician birthplaces.

Looking through the PostGIS docs, you’ll find `ST_ClosestPoint()`, which fits the bill. We will use `ST_ClosestPoint` to find the closest point, and then make a line from this point to the musician birthplaces.

Here is a screenshot of the result of this query.

## Bringing It All Together

We can bring together all the SQL statements and create a chroropleth of the lines based on how far away the birth places are from the highway. We’re basically just making a new column in the `SELECT` for each of the commands that we’ve run before.

One new additional piece is `UNION ALL`. These SQL keywords allow us to concatenate tables together as long as the schema are the same. Notice that `the_geom_webmercator` has type geography, name and city are strings, and distance is a float. Since it doesn’t make sense to talk about how far an object is away from itself, we can put `null` for the value of `d` for the highway.

Note that comment lines begin with a double hyphen. Also, please make sure to remove the comments from the query while copying directly from here and pasting into the editor to avoid undue errors.

To get your hover window to customize so that US Route 61’s `null` value distance and name don’t appear, you can update the HTML template to this instead: