PostGIS in CARTO
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.
Mississippi portion of US Route 61
Don’t worry about downloading and then uploading the data–just directly import it into your CARTO account using the above link.
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.
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 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
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
The better choice is to use
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
JOINs, 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
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.
Finding Distance from Road
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.
The final map is at the top of this page.
If you’re interested in copying the CartoCSS I used to make this map, paste the following into the CartoCSS panel in the sidebar:
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:
Functions mentioned in this lesson:
Here are some of the most commonly used PostGIS functions in CARTO: