The very basics
Infinite looping
Processing graphs with recursive CTE
24. Graphs in computer science


Very well! Since you already know how to tackle hierarchical structures represented in the form of trees, it's time to move on and introduce the second type of real-world situations we can tackle with recursive CTEs: graphs

Graphs consists of vertices and edges. In real life graphs can often correspond to road maps. Take a look:


In a database, we may store vertices (cities) in one table, and the edges (roads between them) in another one:

name_from name_to distance_km
London Cambridge 78
... ... ...

For educational purposes, we will simplify things a bit. We will only use a single table city with the city name and gps coordinates and we assume that you can go directly from each city to another.

First, let's take a look at this table.


Select all data from table city.

The table contains the following columns:

  • name - name of the city
  • lat - latitude of the city.
  • lon - longitude of the city
Last two columns indicates coordinates. Based on those coordinates, we will calculate the distance between two cities in a straight line and treat it as the distance.